Data Entry form with last used values

[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

[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.

[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!