Update Data in Table with Form

[ryguy7272]

I am trying to add data to a Table. First I lookup an ID from a Table, named
‘PatientTable’. My ID is a TextBox named ‘MR’.

I have a date in a TextBox named ‘Sim_Date’ and the controlSource is:
=DLookUp("[SIM_Date]","[PatientTable]","[MR] = " & [MR])

Similarly, I have a TextBox named ‘FirstName’ and the Control Source is:
=DLookUp("[FirstName]","[PatientTable]","[MR] = " & [MR])

Finally, I have a TextBox named ‘LastName’ and the Control Source is:
=DLookUp("[LastName]","[PatientTable]","[MR] = " & [MR])

First Question:
Is this the best way to set up my Form, or is there a better way than using
the Dlookup function? I was thinking of using VBA, and running the update
procedure with a CommandButton; this could update these TextBoxes. Is that a
better method?

Next Question:
How do I update the appropriate record; find the correct ID and update two
fields, ‘RT_Start_Date’ and ‘SIM_Comments’ in the 'PatientTable'? The code
below is under the second of two Forms. I use the another Form to collect
most of the data from users (that Form works fine). This second Form is to
collect some additional data, as a second step in the data collection process.


This is the code:
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 no errors insert data
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

' Open contact table.
Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "PatientTable", cnn1, , , adCmdTable


'get the new record data
rstPatientTable.AddNew

rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update

' Show the newly added data.
MsgBox "New patient: " & [Form].[SimForm]![FirstName] & " " &
[Form].[SimForm]![LastName] & " has been successfully added!!"


'close connections
rstPatientTable.Close
cnn1.Close


Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub



In summary, I need to update two fields in my ‘PatientTable’, but Access
seems to put the ‘RT_Start_Date’ and ‘SIM_Comments’ in a different row than
the row with the ID. Also, the code fails on this line:
MsgBox "New patient: " & [Form].[SimForm]![FirstName] & " " &
[Form].[SimForm]![LastName] & " has been successfully added!!" Some how the
reference to the Form is not correct, but I don't know why.


Any thoughts? TIA!!


Regards,
Ryan---

 

[ryguy7272]

This stuff is becoming more and more intuitive all the time! This seems to
be what was needed:

Instead of:
rstPatientTable.AddNew

It was:
rstPatientTable.Update
(updates the appropriate field in the PatientTable)

Since the code is directly under the Form:
MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & " has been
successfully updated!!"

Problem solved!! Hope this follow up helps others!

Regards,
Ryan---


--
RyGuy

I am trying to add data to a Table. First I lookup an ID from a Table, named
‘PatientTable’. My ID is a TextBox named ‘MR’.

I have a date in a TextBox named ‘Sim_Date’ and the controlSource is:
=DLookUp("[SIM_Date]","[PatientTable]","[MR] = " & [MR])

Similarly, I have a TextBox named ‘FirstName’ and the Control Source is:
=DLookUp("[FirstName]","[PatientTable]","[MR] = " & [MR])

Finally, I have a TextBox named ‘LastName’ and the Control Source is:
=DLookUp("[LastName]","[PatientTable]","[MR] = " & [MR])

First Question:
Is this the best way to set up my Form, or is there a better way than using
the Dlookup function? I was thinking of using VBA, and running the update
procedure with a CommandButton; this could update these TextBoxes. Is that a
better method?

Next Question:
How do I update the appropriate record; find the correct ID and update two
fields, ‘RT_Start_Date’ and ‘SIM_Comments’ in the 'PatientTable'? The code
below is under the second of two Forms. I use the another Form to collect
most of the data from users (that Form works fine). This second Form is to
collect some additional data, as a second step in the data collection process.


This is the code:
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 no errors insert data
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

' Open contact table.
Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "PatientTable", cnn1, , , adCmdTable


'get the new record data
rstPatientTable.AddNew

rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update

' Show the newly added data.
MsgBox "New patient: " & [Form].[SimForm]![FirstName] & " " &
[Form].[SimForm]![LastName] & " has been successfully added!!"


'close connections
rstPatientTable.Close
cnn1.Close


Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub



In summary, I need to update two fields in my ‘PatientTable’, but Access
seems to put the ‘RT_Start_Date’ and ‘SIM_Comments’ in a different row than
the row with the ID. Also, the code fails on this line:
MsgBox "New patient: " & [Form].[SimForm]![FirstName] & " " &
[Form].[SimForm]![LastName] & " has been successfully added!!" Some how the
reference to the Form is not correct, but I don't know why.


Any thoughts? TIA!!


Regards,
Ryan---