MySQL, ADO, VB6 and Query-based update error... oh my...
Related to my last question, Here's the error and the code... Why does this happen???? (seemingly randomly) It fails when multiple versions of this program are running and when updating a record whether by using update or movenext. The database is MySQL 5.0 and the connection string is further back in the code.
Error -2137467259 Query-based update failed because the row to update could not be found.
Public Function MakeEmergencyPhoneCall() As Boolean
Dim StudentRecords As String
Dim TeacherRecords As String
Dim RS As New ADODB.Recordset
Dim RSstudents As New ADODB.Recordset
Dim HomePhone As Variant
Dim CellPhone As Variant
Dim Status As Integer
StudentRecords = "SELECT Student_ID, Home_phone, Cell_Phone, Admin_Calling, Call_Received, Record_Busy FROM students WHERE Admin_Calling = 1"
TeacherRecords = "SELECT Employee_ID, Home_phone, Cell_Phone, Admin_Calling, Call_Received, Record_Busy FROM teachers WHERE Admin_Calling = 1"
With RS
.Index = "Employee_ID"
.CursorType = adOpenForwardOnly
.LockType = adLockPessimistic
.Open TeacherRecords, conn
End With
With RSstudents
.Index = "Student_ID"
.CursorType = adOpenForwardOnly
.LockType = adLockPessimistic
.Open StudentRecords, conn
End With
If RS.BOF = False Or RS.EOF = False Then
RS.MoveFirst
Do Until RS!Record_Busy = 0
RS.MoveNext
Loop
Do Until RS.EOF
Status = UNKNOWN
With RS
!Record_Busy = 1
.Update
End With
HomePhone = RS!Home_Phone
CellPhone = RS!Cell_Phone
If HomePhone <> "" Then
Status = MakeTheCall(HomePhone)
End If
Select Case Status
Case GOTTHEMESSAGE
With RS
!Admin_Calling = 0
!Call_Received = 1
!Record_Busy = 0
.MoveNext
End With
Case ANSWERINGMACHINE
With RS
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
Case LINEBUSY
With RS
!Call_Received = 2
!Record_Busy = 0
.MoveNext
End With
Case RNA
With RS
!Record_Busy = 0
.MoveNext
End With
Case UNKNOWN
With RS
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
End Select
Loop
End If
If RSstudents.BOF = False Or RSstudents.EOF = False Then
RSstudents.MoveFirst
Do Until RSstudents!Record_Busy = 0
RSstudents.MoveNext
Loop
Do Until RSstudents.EOF
Status = UNKNOWN
With RSstudents
!Record_Busy = 1
.Update
End With
HomePhone = RSstudents!Home_Phone
CellPhone = RSstudents!Cell_Phone
If HomePhone <> "" Then Status = MakeTheCall(HomePhone)
Select Case Status
Case GOTTHEMESSAGE
With RSstudents
!Admin_Calling = 0
!Call_Received = 1
!Record_Busy = 0
.MoveNext
End With
Case ANSWERINGMACHINE
With RSstudents
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
Case LINEBUSY
With RSstudents
!Call_Received = 2
!Record_Busy = 0
.MoveNext
End With
Case RNA
With RSstudents
!Record_Busy = 0
.MoveNext
End With
Case UNKNOWN
With RSstudents
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
End Select
Loop
End If
With RS
.Close
.Index = "Employee_ID"
.CursorType = adOpenForwardOnly
.LockType = adLockPessimistic
.Open TeacherRecords, conn
End With
With RSstudents
.Close
.Index = "Student_ID"
.CursorType = adOpenForwardOnly
.LockType = adLockPessimistic
.Open StudentRecords, conn
End With
If RS.BOF = False Or RS.EOF = False Then
RS.MoveFirst
Do Until RS!Record_Busy = 0
RS.MoveNext
Loop
Do Until RS.EOF
Status = UNKNOWN
With RS
!Record_Busy = 1
.Update
End With
HomePhone = RS!Home_Phone
CellPhone = RS!Cell_Phone
If RS!Call_Received <> 1 And CellPhone <> "" Then Status = MakeTheCall(CellPhone)
Select Case Status
Case GOTTHEMESSAGE
With RS
!Admin_Calling = 0
!Call_Received = 1
!Record_Busy = 0
.MoveNext
End With
Case ANSWERINGMACHINE
With RS
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
Case LINEBUSY
With RS
!Call_Received = 2
!Record_Busy = 0
.MoveNext
End With
Case RNA
With RS
!Record_Busy = 0
.MoveNext
End With
Case UNKNOWN
With RS
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
End Select
Loop
End If
If RSstudents.BOF = False Or RSstudents.EOF = False Then
RSstudents.MoveFirst
Do Until RSstudents!Record_Busy = 0
RSstudents.MoveNext
Loop
Do Until RSstudents.EOF
Status = UNKNOWN
With RSstudents
!Record_Busy = 1
.Update
End With
HomePhone = RSstudents!Home_Phone
CellPhone = RSstudents!Cell_Phone
If RSstudents!Call_Received <> 1 And CellPhone <> "" Then Status = MakeTheCall(CellPhone)
Select Case Status
Case GOTTHEMESSAGE
With RSstudents
!Admin_Calling = 0
!Call_Received = 1
!Record_Busy = 0
.MoveNext
End With
Case ANSWERINGMACHINE
With RSstudents
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
Case LINEBUSY
With RSstudents
!Call_Received = 2
!Record_Busy = 0
.MoveNext
End With
Case RNA
With RSstudents
!Record_Busy = 0
.MoveNext
End With
Case UNKNOWN
With RSstudents
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
End Select
Loop
End If
' Make sure to close recordsets
RS.Close
RSstudents.Close
Set RS = Nothing
Set RSstudents = Nothing
End Function
Error -2137467259 Query-based update failed because the row to update could not be found.
Public Function MakeEmergencyPhoneCall() As Boolean
Dim StudentRecords As String
Dim TeacherRecords As String
Dim RS As New ADODB.Recordset
Dim RSstudents As New ADODB.Recordset
Dim HomePhone As Variant
Dim CellPhone As Variant
Dim Status As Integer
StudentRecords = "SELECT Student_ID, Home_phone, Cell_Phone, Admin_Calling, Call_Received, Record_Busy FROM students WHERE Admin_Calling = 1"
TeacherRecords = "SELECT Employee_ID, Home_phone, Cell_Phone, Admin_Calling, Call_Received, Record_Busy FROM teachers WHERE Admin_Calling = 1"
With RS
.Index = "Employee_ID"
.CursorType = adOpenForwardOnly
.LockType = adLockPessimistic
.Open TeacherRecords, conn
End With
With RSstudents
.Index = "Student_ID"
.CursorType = adOpenForwardOnly
.LockType = adLockPessimistic
.Open StudentRecords, conn
End With
If RS.BOF = False Or RS.EOF = False Then
RS.MoveFirst
Do Until RS!Record_Busy = 0
RS.MoveNext
Loop
Do Until RS.EOF
Status = UNKNOWN
With RS
!Record_Busy = 1
.Update
End With
HomePhone = RS!Home_Phone
CellPhone = RS!Cell_Phone
If HomePhone <> "" Then
Status = MakeTheCall(HomePhone)
End If
Select Case Status
Case GOTTHEMESSAGE
With RS
!Admin_Calling = 0
!Call_Received = 1
!Record_Busy = 0
.MoveNext
End With
Case ANSWERINGMACHINE
With RS
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
Case LINEBUSY
With RS
!Call_Received = 2
!Record_Busy = 0
.MoveNext
End With
Case RNA
With RS
!Record_Busy = 0
.MoveNext
End With
Case UNKNOWN
With RS
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
End Select
Loop
End If
If RSstudents.BOF = False Or RSstudents.EOF = False Then
RSstudents.MoveFirst
Do Until RSstudents!Record_Busy = 0
RSstudents.MoveNext
Loop
Do Until RSstudents.EOF
Status = UNKNOWN
With RSstudents
!Record_Busy = 1
.Update
End With
HomePhone = RSstudents!Home_Phone
CellPhone = RSstudents!Cell_Phone
If HomePhone <> "" Then Status = MakeTheCall(HomePhone)
Select Case Status
Case GOTTHEMESSAGE
With RSstudents
!Admin_Calling = 0
!Call_Received = 1
!Record_Busy = 0
.MoveNext
End With
Case ANSWERINGMACHINE
With RSstudents
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
Case LINEBUSY
With RSstudents
!Call_Received = 2
!Record_Busy = 0
.MoveNext
End With
Case RNA
With RSstudents
!Record_Busy = 0
.MoveNext
End With
Case UNKNOWN
With RSstudents
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
End Select
Loop
End If
With RS
.Close
.Index = "Employee_ID"
.CursorType = adOpenForwardOnly
.LockType = adLockPessimistic
.Open TeacherRecords, conn
End With
With RSstudents
.Close
.Index = "Student_ID"
.CursorType = adOpenForwardOnly
.LockType = adLockPessimistic
.Open StudentRecords, conn
End With
If RS.BOF = False Or RS.EOF = False Then
RS.MoveFirst
Do Until RS!Record_Busy = 0
RS.MoveNext
Loop
Do Until RS.EOF
Status = UNKNOWN
With RS
!Record_Busy = 1
.Update
End With
HomePhone = RS!Home_Phone
CellPhone = RS!Cell_Phone
If RS!Call_Received <> 1 And CellPhone <> "" Then Status = MakeTheCall(CellPhone)
Select Case Status
Case GOTTHEMESSAGE
With RS
!Admin_Calling = 0
!Call_Received = 1
!Record_Busy = 0
.MoveNext
End With
Case ANSWERINGMACHINE
With RS
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
Case LINEBUSY
With RS
!Call_Received = 2
!Record_Busy = 0
.MoveNext
End With
Case RNA
With RS
!Record_Busy = 0
.MoveNext
End With
Case UNKNOWN
With RS
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
End Select
Loop
End If
If RSstudents.BOF = False Or RSstudents.EOF = False Then
RSstudents.MoveFirst
Do Until RSstudents!Record_Busy = 0
RSstudents.MoveNext
Loop
Do Until RSstudents.EOF
Status = UNKNOWN
With RSstudents
!Record_Busy = 1
.Update
End With
HomePhone = RSstudents!Home_Phone
CellPhone = RSstudents!Cell_Phone
If RSstudents!Call_Received <> 1 And CellPhone <> "" Then Status = MakeTheCall(CellPhone)
Select Case Status
Case GOTTHEMESSAGE
With RSstudents
!Admin_Calling = 0
!Call_Received = 1
!Record_Busy = 0
.MoveNext
End With
Case ANSWERINGMACHINE
With RSstudents
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
Case LINEBUSY
With RSstudents
!Call_Received = 2
!Record_Busy = 0
.MoveNext
End With
Case RNA
With RSstudents
!Record_Busy = 0
.MoveNext
End With
Case UNKNOWN
With RSstudents
!Call_Received = 3
!Record_Busy = 0
.MoveNext
End With
End Select
Loop
End If
' Make sure to close recordsets
RS.Close
RSstudents.Close
Set RS = Nothing
Set RSstudents = Nothing
End Function
I second leonstryker's comment
ASKER
Okay... the Update method does work when there is only one instance of the program running. But, I am open to using an SQL UPDATE statement instead. How do I write it? Perhaps that will actually work.
You basically concatenate the Update string and execute it on your connection object.
Leon
Leon
ASKER
I don't know how to program with SQL. I don't have much experience with it. So, an example would be a big help.
ASKER
Okay, I tried this
conn.Execute "UPDATE teachers SET Admin_Calling = 0, Call_Received = 1, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
When I MoveNext, I now get the same error...
conn.Execute "UPDATE teachers SET Admin_Calling = 0, Call_Received = 1, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
When I MoveNext, I now get the same error...
Did you change the cursor type from adOpenForwardOnly to adOpenStatic?
Leon
Leon
ASKER
I am using a server side cursor. I have it working but now there are other errors instead. Does the recordset update the values it is using at somepoint after calling the SQL UPDATE command? And the new error is: 3265 -- Item cannot be found in the collection corresponding to the requested name or ordinal.
Lets see your current code.
ASKER
Public Function MakeEmergencyPhoneCall() As Boolean
Dim StudentRecords As String
Dim TeacherRecords As String
Dim RSstudents As New ADODB.Recordset
Dim HomePhone As Variant
Dim CellPhone As Variant
Dim StudentID As Long
Dim Status As Integer
StudentRecords = "SELECT Student_ID, Home_phone, Cell_Phone, Admin_Calling, Call_Received, Record_Busy FROM students WHERE Admin_Calling = 1"
TeacherRecords = "SELECT Employee_ID, Home_phone, Cell_Phone, Admin_Calling, Call_Received, Record_Busy FROM teachers WHERE Admin_Calling = 1"
With RS
.Index = "Employee_ID"
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open TeacherRecords, conn
End With
With RSstudents
.Index = "Student_ID"
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open StudentRecords, conn
End With
If RS.BOF = False Or RS.EOF = False Then
RS.MoveFirst
Do Until RS!Record_Busy = 0
RS.MoveNext
Loop
Do Until RS.EOF
Status = UNKNOWN
conn.Execute "UPDATE teachers SET Record_Busy = 1 WHERE Employee_ID =" & RS!Employee_ID
With RS
HomePhone = !Home_Phone
CellPhone = !Cell_Phone
End With
If HomePhone <> "" Then
Status = MakeTheCall(HomePhone)
End If
Select Case Status
Case GOTTHEMESSAGE
conn.Execute "UPDATE teachers SET Admin_Calling = 0, Call_Received = 1, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case ANSWERINGMACHINE
conn.Execute "UPDATE teachers SET Call_Received = 3, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case LINEBUSY
conn.Execute "UPDATE teachers SET Call_Received = 2, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case RNA
conn.Execute "UPDATE teachers SET Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case UNKNOWN
conn.Execute "UPDATE teachers SET Call_Received = 3, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
End Select
RS.Resync
Loop
End If
If RSstudents.BOF = False Or RSstudents.EOF = False Then
RSstudents.MoveFirst
Do Until RSstudents!Record_Busy = 0
RSstudents.MoveNext
Loop
Do Until RSstudents.EOF
Status = UNKNOWN
conn.Execute "UPDATE students SET Record_Busy = 1 WHERE Student_ID =" & RS!Student_ID
HomePhone = RSstudents!Home_Phone
CellPhone = RSstudents!Cell_Phone
If HomePhone <> "" Then Status = MakeTheCall(HomePhone)
Select Case Status
Case GOTTHEMESSAGE
conn.Execute "UPDATE students SET Admin_Calling = 0, Call_Received = 1, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case ANSWERINGMACHINE
conn.Execute "UPDATE students SET Call_Received = 3, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case LINEBUSY
conn.Execute "UPDATE students SET Call_Received = 2, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case RNA
conn.Execute "UPDATE students SET Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case UNKNOWN
conn.Execute "UPDATE students SET Call_Received = 3, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
End Select
Loop
End If
If RS.BOF = False Or RS.EOF = False Then
RS.MoveFirst
Do Until RS!Record_Busy = 0
RS.MoveNext
Loop
Do Until RS.EOF
Status = UNKNOWN
conn.Execute "UPDATE teachers SET Record_Busy = 1 WHERE Employee_ID =" & RS!Employee_ID
HomePhone = RS!Home_Phone
CellPhone = RS!Cell_Phone
If RS!Call_Received <> 1 And CellPhone <> "" Then Status = MakeTheCall(CellPhone)
Select Case Status
Case GOTTHEMESSAGE
conn.Execute "UPDATE teachers SET Admin_Calling = 0, Call_Received = 1, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case ANSWERINGMACHINE
conn.Execute "UPDATE teachers SET Call_Received = 3, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case LINEBUSY
conn.Execute "UPDATE teachers SET Call_Received = 2, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case RNA
conn.Execute "UPDATE teachers SET Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case UNKNOWN
conn.Execute "UPDATE teachers SET Call_Received = 3, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
End Select
Loop
End If
If RSstudents.BOF = False Or RSstudents.EOF = False Then
RSstudents.MoveFirst
Do Until RSstudents!Record_Busy = 0
RSstudents.MoveNext
Loop
Do Until RSstudents.EOF
Status = UNKNOWN
conn.Execute "UPDATE students SET Record_Busy = 1 WHERE Student_ID =" & RS!Student_ID
HomePhone = RSstudents!Home_Phone
CellPhone = RSstudents!Cell_Phone
If RSstudents!Call_Received <> 1 And CellPhone <> "" Then Status = MakeTheCall(CellPhone)
Select Case Status
Case GOTTHEMESSAGE
conn.Execute "UPDATE students SET Admin_Calling = 0, Call_Received = 1, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case ANSWERINGMACHINE
conn.Execute "UPDATE students SET Call_Received = 3, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case LINEBUSY
conn.Execute "UPDATE students SET Call_Received = 2, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case RNA
conn.Execute "UPDATE students SET Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case UNKNOWN
conn.Execute "UPDATE students SET Call_Received = 3, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
End Select
Loop
End If
' Make sure to close recordsets
RS.Close
RSstudents.Close
Set RS = Nothing
Set RSstudents = Nothing
End Function
Dim StudentRecords As String
Dim TeacherRecords As String
Dim RSstudents As New ADODB.Recordset
Dim HomePhone As Variant
Dim CellPhone As Variant
Dim StudentID As Long
Dim Status As Integer
StudentRecords = "SELECT Student_ID, Home_phone, Cell_Phone, Admin_Calling, Call_Received, Record_Busy FROM students WHERE Admin_Calling = 1"
TeacherRecords = "SELECT Employee_ID, Home_phone, Cell_Phone, Admin_Calling, Call_Received, Record_Busy FROM teachers WHERE Admin_Calling = 1"
With RS
.Index = "Employee_ID"
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open TeacherRecords, conn
End With
With RSstudents
.Index = "Student_ID"
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open StudentRecords, conn
End With
If RS.BOF = False Or RS.EOF = False Then
RS.MoveFirst
Do Until RS!Record_Busy = 0
RS.MoveNext
Loop
Do Until RS.EOF
Status = UNKNOWN
conn.Execute "UPDATE teachers SET Record_Busy = 1 WHERE Employee_ID =" & RS!Employee_ID
With RS
HomePhone = !Home_Phone
CellPhone = !Cell_Phone
End With
If HomePhone <> "" Then
Status = MakeTheCall(HomePhone)
End If
Select Case Status
Case GOTTHEMESSAGE
conn.Execute "UPDATE teachers SET Admin_Calling = 0, Call_Received = 1, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case ANSWERINGMACHINE
conn.Execute "UPDATE teachers SET Call_Received = 3, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case LINEBUSY
conn.Execute "UPDATE teachers SET Call_Received = 2, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case RNA
conn.Execute "UPDATE teachers SET Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case UNKNOWN
conn.Execute "UPDATE teachers SET Call_Received = 3, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
End Select
RS.Resync
Loop
End If
If RSstudents.BOF = False Or RSstudents.EOF = False Then
RSstudents.MoveFirst
Do Until RSstudents!Record_Busy = 0
RSstudents.MoveNext
Loop
Do Until RSstudents.EOF
Status = UNKNOWN
conn.Execute "UPDATE students SET Record_Busy = 1 WHERE Student_ID =" & RS!Student_ID
HomePhone = RSstudents!Home_Phone
CellPhone = RSstudents!Cell_Phone
If HomePhone <> "" Then Status = MakeTheCall(HomePhone)
Select Case Status
Case GOTTHEMESSAGE
conn.Execute "UPDATE students SET Admin_Calling = 0, Call_Received = 1, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case ANSWERINGMACHINE
conn.Execute "UPDATE students SET Call_Received = 3, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case LINEBUSY
conn.Execute "UPDATE students SET Call_Received = 2, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case RNA
conn.Execute "UPDATE students SET Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case UNKNOWN
conn.Execute "UPDATE students SET Call_Received = 3, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
End Select
Loop
End If
If RS.BOF = False Or RS.EOF = False Then
RS.MoveFirst
Do Until RS!Record_Busy = 0
RS.MoveNext
Loop
Do Until RS.EOF
Status = UNKNOWN
conn.Execute "UPDATE teachers SET Record_Busy = 1 WHERE Employee_ID =" & RS!Employee_ID
HomePhone = RS!Home_Phone
CellPhone = RS!Cell_Phone
If RS!Call_Received <> 1 And CellPhone <> "" Then Status = MakeTheCall(CellPhone)
Select Case Status
Case GOTTHEMESSAGE
conn.Execute "UPDATE teachers SET Admin_Calling = 0, Call_Received = 1, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case ANSWERINGMACHINE
conn.Execute "UPDATE teachers SET Call_Received = 3, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case LINEBUSY
conn.Execute "UPDATE teachers SET Call_Received = 2, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case RNA
conn.Execute "UPDATE teachers SET Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
Case UNKNOWN
conn.Execute "UPDATE teachers SET Call_Received = 3, Record_Busy = 0 WHERE Employee_ID=" & RS!Employee_ID
RS.MoveNext
End Select
Loop
End If
If RSstudents.BOF = False Or RSstudents.EOF = False Then
RSstudents.MoveFirst
Do Until RSstudents!Record_Busy = 0
RSstudents.MoveNext
Loop
Do Until RSstudents.EOF
Status = UNKNOWN
conn.Execute "UPDATE students SET Record_Busy = 1 WHERE Student_ID =" & RS!Student_ID
HomePhone = RSstudents!Home_Phone
CellPhone = RSstudents!Cell_Phone
If RSstudents!Call_Received <> 1 And CellPhone <> "" Then Status = MakeTheCall(CellPhone)
Select Case Status
Case GOTTHEMESSAGE
conn.Execute "UPDATE students SET Admin_Calling = 0, Call_Received = 1, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case ANSWERINGMACHINE
conn.Execute "UPDATE students SET Call_Received = 3, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case LINEBUSY
conn.Execute "UPDATE students SET Call_Received = 2, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case RNA
conn.Execute "UPDATE students SET Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
Case UNKNOWN
conn.Execute "UPDATE students SET Call_Received = 3, Record_Busy = 0 WHERE Student_ID=" & RSstudents!Student_ID
RSstudents.MoveNext
End Select
Loop
End If
' Make sure to close recordsets
RS.Close
RSstudents.Close
Set RS = Nothing
Set RSstudents = Nothing
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Why does this work????
What does the & "" do???
What does the & "" do???
ASKER
Thanks for all your help by the way (to everyone)
The "" basically adds an empty string. This works because the value can not ever be NULL, which is what was causing the error. BTW, in case of numeric datatype you can do the following with similar results:
sngValue = CSng("0" & rs!dbValue)
Thanks for the grade,
Leon
sngValue = CSng("0" & rs!dbValue)
Thanks for the grade,
Leon
BTW, you will be much better off using SQL UPDATE instead of trying to Update with an ADO Update method.
Leon