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.