Data Entry form with last used values

Jeff H
3StarLounger
Posts: 279
Joined: 31 Oct 2017, 20:07

Data Entry form with last used values

Post by Jeff H »

I'm inching along here...
I have separated the Service Event data from the Services Provided data and now I'm working on the data entry form.

When the user enters a new Service Event, they have to specify a Date, a Patient, a Volunteer, and the Mileage, then enter whatever Services were provided during the visit and the Hours for each.

I want the frmSvcEvents form to open for data entry only (not for look-up) and be populated with the PatientID and VolunteerID from the last Event recorded for each new record added. Those ID controls are combo boxes where the user enters initials and sees the names in the drop down.The idea is to minimize the user's need to select patients and volunteers from the combo boxes.

In the Videos db, you had given me code to do this based on a table I had set up to store the last TitleID when the form unloaded, then recall it when the form loaded. I tried to adapt that code with no success:

Code: Select all

Private Sub Form_Load()
    Dim lngID_P As Long
    Dim lngID_V As Long
    lngID_P = DLookup("LastPatient", "tblLastPID")
    With Me.RecordsetClone
        .FindFirst "PatientID=" & lngID_P
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With

    lngID_V = DLookup("LastVolunteer", "tblLastVID")
    With Me.RecordsetClone
        .FindFirst "VolunteerID=" & lngID_V
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With

End Sub


Private Sub Form_Unload(Cancel As Integer)
On Error GoTo GetOut
    If Not IsNull(Me.PatientID) Then
        CurrentDb.Execute "UPDATE tblLastPID SET LastPatient=" & Me.PatientID, dbFailOnError
    End If
    
    If Not IsNull(Me.VolunteerID) Then
        CurrentDb.Execute "UPDATE tblLastVID SET LastVolunteer=" & Me.VolunteerID, dbFailOnError
    End If

GetOut:

End Sub
I've attached a stripped down version of the db (after modifying the actual names of people). Maybe there's a more direct way to do this, like taking those values from the last record in tblSvcEvents. But I'm still just groping around with the methods and syntax of Access.

Thanks,
- Jeff
Sample (2).zip
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 68783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Data Entry form with last used values

Post by HansV »

Create an On Load event procedure for the form:

Code: Select all

Private Sub Form_Load()
    Me.PatientID = DLookup("LastPatient", "tblLastPID")
    Me.VolunteerID = DLookup("LastVolunteer", "tblLastVID")
End Sub
This sets the PatientID and VolunteerID fields to the values stored in the tables when the form was last closed.
Regards,
Hans

Jeff H
3StarLounger
Posts: 279
Joined: 31 Oct 2017, 20:07

Re: Data Entry form with last used values

Post by Jeff H »

Great, thanks Hans. It looks easy when you show me, but not so much when I'm fishing around for the right combination!