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:
Expand|Select|Wrap|Line Numbers
- 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