Finding a record, and then saving a new record after the found record has been amended.

[nica12]

Hi there,


I am very new to Access so apologies if any of this is obvious/not clear.


I am creating a data collection tool for health care employees to complete. It consists of a number of forms they will fill in. One of my forms relates to a goal based measure that the employee will fill in each time they meet with the patient (each appointment). The form is structured with patient ID, goal description, goal agreed by, goal type, goal progress and appointment date. All of this information except the goal progress and the appointment date will remain the same each time the form is filled in, so I want to create a search function by which the employee can locate the record and then amend the appointment date and goal progress and save a new record. Currently, my record which the search function is locating is just being overwritten. This is the code I have so far:




Private Sub Command318_Click()
'Save the current record
DoCmd.RunCommand acCmdSaveRecord

' Create a new record
DoCmd.GoToRecord , , acNewRec



Dim searchValue As String
searchValue = Me.Text293.Value


If IsNull(DLookup("[Client ID]", "[Goal Based Outcome Entry Form]", "[Client ID]='" & searchValue & "'")) Then
' Client ID doesn't exist
If MsgBox("Client ID does NOT exist. Add as new?", vbYesNoCancel + vbQuestion, "Add New?") = vbYes Then
' Add as a new customer
Me.[Client ID].Value = searchValue
End If
Else
' Client ID exists
Dim strSQL As String
strSQL = "SELECT * FROM [Goal Based Outcome Entry Form] WHERE [Client ID] Like '*" & searchValue & "*'"

' Apply the filter to your form
Me.RecordSource = strSQL

' Refresh the form to display the filtered results
Me.Refresh
End If
End Sub




Is anyone able to suggest a way I can make this work? Perhaps a better way would be to somehow search for a record, and then copy the values of each field to a new record? I am unsure what code would allow me to do this.

[Welshgasman]

If only two fields are changing, I might just have the rest as a separate record in a table? PatientGoals.
Have a dated added field in that record and then just look for the Max Dated record for that patient.

Please post code between code tags # icon. That keeps indentation.
Perhaps use that strsql for a recordset, then use

DoCmd.GoToRecord acDataForm, , acNewRec
Me.Control1 = rst.Field1
etc, etc.... 'This is not code

All your code is doing is setting the form recordsource to that one record, no wonder it is being overwritten.

[nica12]

Thank you very much for your suggestion - this is really helpful.