Update Query; Find Correct ID and Update Value in Correct Field

[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---

 

[ryguy7272]

I am wondering if my DB structure is totally incorrect. I received no
responses here; still unable to resolve the issue based on my other post:
http://www.microsoft.com/office/com...dee-98b2468407da&cat=&lang=en&cr=US&sloc=&p=1

(sorry to double-post...wasn't sure if it was a Programming thing or a Query
thing...)

Any ideas? I've tried dozens of things; nothing has worked thus far.


Thanks,
Ryan---

--
RyGuy

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---