R
ryguy7272
Sorry for double-posting; I put this question over in the Access programming
area, but as I work on it more and more, it seems to be an Update Query
issue. Let me explain…
I tried to modify some VBA to help me add a few records to a table. When
the macro fires, the code runs and it DOES update the Table, but the updates
always occur on the first record of the Table, it doesn’t update the correct
record. When I type in an MR number (which is a unique ID number), several
TextBoxes on my Form update automatically (use DLookUp). This is fine! I can
see a patient’s status, and add some new information via several TextBoxes,
the problem is that when the VBA code fires, the updates aren’t made to the
correct records. How can I ensure that updates are made to the appropriate
record? The appropriate record is the number in the MR TetxBox?
This is my SQL for the Update Query:
UPDATE PatientTable SET PatientTable.SIM_Comments =
Forms!SimForm!SIM_Comments
WHERE (([SIM_Comments]<>"") AND ((PatientTable.MR)=[MR]));
Field = SIM_Comments
Table = PatientTable
Update To = Forms!SimForm!SIM_Comments
Criteria = <>â€â€
also
Field = MR
Table = Patient Table
Update To =
Criteria = [MR]
I think this Criteria has to be MR, because I have to reference the
appropriate MR in the Table, based on the MR in the Form and then update the
correct SIM_Comments in the Table, based on this MR. Is that right?
In any event, when I hit the ‘bang’ button, I get prompted for a Parameter,
which doesn’t seem right at all, and then zero records are updated.
Here is my VBA code now:
Private Sub Command19_Click()
Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String
'Dim mydb As DAO.Database
Dim mydb As String
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn
Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR,
cnn1
If Not rstPatientTable.EOF Then
rstPatientTable.Update
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments
rstPatientTable.Update
End If
MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & " has
been successfully updated!!"
rstPatientTable.Close
cnn1.Close
Else
MsgBox "An Error has occurred, please check and try again"
End If
End Sub
How can I get the SQL working and then pop that into my VBA code?
Thanks,
Ryan---
area, but as I work on it more and more, it seems to be an Update Query
issue. Let me explain…
I tried to modify some VBA to help me add a few records to a table. When
the macro fires, the code runs and it DOES update the Table, but the updates
always occur on the first record of the Table, it doesn’t update the correct
record. When I type in an MR number (which is a unique ID number), several
TextBoxes on my Form update automatically (use DLookUp). This is fine! I can
see a patient’s status, and add some new information via several TextBoxes,
the problem is that when the VBA code fires, the updates aren’t made to the
correct records. How can I ensure that updates are made to the appropriate
record? The appropriate record is the number in the MR TetxBox?
This is my SQL for the Update Query:
UPDATE PatientTable SET PatientTable.SIM_Comments =
Forms!SimForm!SIM_Comments
WHERE (([SIM_Comments]<>"") AND ((PatientTable.MR)=[MR]));
Field = SIM_Comments
Table = PatientTable
Update To = Forms!SimForm!SIM_Comments
Criteria = <>â€â€
also
Field = MR
Table = Patient Table
Update To =
Criteria = [MR]
I think this Criteria has to be MR, because I have to reference the
appropriate MR in the Table, based on the MR in the Form and then update the
correct SIM_Comments in the Table, based on this MR. Is that right?
In any event, when I hit the ‘bang’ button, I get prompted for a Parameter,
which doesn’t seem right at all, and then zero records are updated.
Here is my VBA code now:
Private Sub Command19_Click()
Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String
'Dim mydb As DAO.Database
Dim mydb As String
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn
Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR,
cnn1
If Not rstPatientTable.EOF Then
rstPatientTable.Update
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments
rstPatientTable.Update
End If
MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & " has
been successfully updated!!"
rstPatientTable.Close
cnn1.Close
Else
MsgBox "An Error has occurred, please check and try again"
End If
End Sub
How can I get the SQL working and then pop that into my VBA code?
Thanks,
Ryan---