Update Data in Table with Form

R

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

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


ryguy7272 said:
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---
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query; Find Correct ID and Update Value in Correct Field 1
Update Correct Records in Table; Based on Certain ID 9
Update Table from Form 6
Update unbound TextBoxes on Form; Need to Read AND Write 1
trying to add data from a form to a table 8
Read From Table and Write to Table As Well 2
Problem with Yes/No DataType. Also, Split a DB. How? 6
Add Login Name and Date/Time Stamp to Table 2

Top