KaleKYlssen
Newbie
Joined: 31 Jan 2004
Posts: 19
|
 Posted: Mar 10th, 2004 10:15 AM Post subject: Wierd Problem |
 |
|
Hi i was hoping someone would be able to see if there are any errors in this code. The program updates a database from vb, but depending on number of records returned from a sql query it either updates an existing record or adds a new one.The reason i ask is that my program was working fine a few weeks ago but when it came to the start of march it started playing up, it no doesn't return any records from the database even though they are ther, consequently it means that the record count is 0 when it should be 1. I made no changes to code (that i know of). The data entry is controlled by the user selecting a satff id from a combo and the value of a textbox which automatically holds the date of the start of the week. I hope i explained it clear enough, as this is really puzzling me.
| Code: |
connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\Elco.mdb"
sqlefficiency = "SELECT * FROM Efficiency WHERE StaffID = '" & cboStaff.Text & "' AND WeekStarting = #" & txtWeek.Text & "#"
rsefficiency.Open sqlefficiency, connstring, adOpenKeyset, adLockOptimistic, adCmdText
rsStock.Open
With rsStock
IntUn = CInt(rsStock.Fields("Assembled Stock"))
IntAs = CInt(rsStock.Fields("Unassembled Stock"))
.Fields("Assembled Stock") = IntUn + Val(txtAmountMade.Text)
.Fields("Unassembled Stock") = IntAs - Val(txtAmountMade.Text)
.Update
.Close
End With
If cboStaff.Text = "" Or cboMotorTYpe.Text = "" Or txtTimeTaken.Text = "" Or txtAmountMade.Text = "" Then
MsgBox "One or more fields are empty please make a selection", vbOKOnly, "Data Missing"
Else
If IsNumeric(txtAmountMade.Text) = False Then
MsgBox "Data Entered is of wrong data type please check", vbOKOnly, "Input Error"
Else
If IsNumeric(txtTimeTaken.Text) = False Then
MsgBox "Data Entered is of wrong data type please check", vbOKOnly, "Input Error"
Else
If txtAmountMade.Text <= 0 Or txtAmountMade.Text > 500 Or txtTimeTaken.Text < 0 Or txtTimeTaken > 500 Then
MsgBox "Data entered is out of accepted range, please check and try again", vbOKOnly, "Input Error"
Else
If rsefficiency.RecordCount = 0 Then
With rsefficiency
.AddNew
.Fields("WeekStarting") = txtWeek.Text
.Fields("StaffId") = cboStaff.Text
.Fields("MotorsMade") = Val(txtAmountMade.Text)
.Fields("TimeTaken") = Val(txtTimeTaken.Text)
.Update
MsgBox "New record has been added", vbOKOnly, "New Record"
.Close
End With
Else
With rsefficiency
IntTime = CInt(rsefficiency.Fields("TimeTaken"))
IntMotor = CInt(rsefficiency.Fields("MotorsMade"))
.Fields("TimeTaken") = IntTime + Val(txtTimeTaken.Text)
.Fields("MotorsMade") = IntMotor + Val(txtAmountMade.Text)
.Update
MsgBox "Record Updated", vbInformation, "Update Successful"
.Close
End With
End If
txtTimeTaken.Text = ""
txtAmountMade.Text = ""
End If
End If
End If
End If
|
This is the code used to put th date in the textbox
| Code: |
MondayDate = DateAdd("d", -Weekday(Date) + 2, Date)
txtWeek.Text = MondayDate
|
|
|
