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

[nia12]

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.

[NeoPa]

Hi Nia.

As is often the way when people post an apology in advance for any offending omissions, there is none :-D We do require code to be posted within their tags, but otherwise perfect.

Your alternative approach suggestion is also a way forward, though please read on for why it may not suit you best.

If you have a consistent target or goal that gets progressed on each visit then perhaps you could design a table with the Goal details specified. Whether this included a link to a particular Patient depends on the specific details you require, but I suspect it might.

Thus you have entities for Employees, Patients, Goals (Presumably each Goal is specific to both Employee & Patient records.) & Progress. In your system you allow the employee to identify themselves first (Before opening this form but where the details are available to it). Thus, on your form, you can start with Employee selected and prompt them to select a patient and goal (It may be that goals are unique to an employee/patient pair but that's beyond my current understanding.) before allowing them to enter progress details.

With Normalisation (See Database Normalisation and Table Structures.) you want to avoid duplicating data as much as possible. This form is specifically for updating progress. More accurately to add new progress records. Progress changes on this form but not any of :

1. The Goal.
2. The Employee.
3. The Patient.

Does all that make sense?

[nia12]

Hi NeoPa,

Thank you very much for your advice on this - that does indeed make a lot of sense. I am coming to realise that I have a lot to learn around normalisation so will read up on this before designing the tool further.

[NeoPa]

Hi Nia.

That sounds like good thinking.

NB. I reset your Best Answer as you'd assigned it to your own post - I assume by mistake. Certainly that would not be appropriate.

I look forward to hearing more from you. We like problems to help deal with :-)