Hi all. I've received some great help over here recently and hope you can help me out once more. I am looking to create navigation buttons to scroll through "visits" forms for specifically chosen people. I have a "demographics" form where I pass the primary key to the "visits" form using open args. I would then like the the "visits" form to open up to that persons first visit and then be able to navigate through the visit records for only that person who was selected from the "demographics" page. Some extras that I would like to include is a stop on the last record so that the a create new page isn't opened when next is clicked when the last record is selected, and an identifier of what record is being shown out of the total (ie. record n of N is shown).
I originally tried to use the the filter command along with the gotorecord command but got nowhere. I then searched the internet for the code that follows but I'm still not able to do what I would like. I'm not taken to the first record, nor can I navigate through the records. I've been working on this one area on and off for about a week and I officially need help, so thanks for any support that can be provided.
This is my code to take me to the visit page from the demographics form while passing the personID, last, and first name, but it doesn't take me to their initial visit.
Private Sub Command36_Click()
If Me.Dirty Then Me.Dirty = False
DoCmd.OpenForm "visit", , , , acFormReadOnly, , Me.PersonID & ";" & Me.LAST_NAME & ";" & FIRST_NAME
DoCmd.Close acForm, "demographics", acSaveYes
This is the code on the visit form where I am trying to insert the navigation buttons.
Private Sub Form_Load()
Dim args As Variant
If Not IsNull(Me.OpenArgs) Then
args = Split(Me.OpenArgs, ";")
Me.PersonID = args(0)
Me.Text50 = args(1)
Me.Text52 = args(2)
Private Sub cmdAdd_Click()
DoCmd.GoToRecord , , acNewRec
Private Sub cmdFirst_Click()
DoCmd.GoToRecord , , acFirst
Private Sub cmdLast_Click()
DoCmd.GoToRecord , , acLast
Private Sub cmdNext_Click()
DoCmd.GoToRecord , , acNext
Private Sub cmdPrevious_Click()
DoCmd.GoToRecord , , acPrevious
Private Sub Form_Current()
Dim rsClone As Recordset
Set rsClone = Me.RecordsetClone
If Me.NewRecord = True Then
Me!cmdAdd.Enabled = False
Me!cmdNext.Enabled = False
Me!cmdPrevious.Enabled = True
Me!cmdFirst.Enabled = True
Me!cmdLast.Enabled = True
ElseIf rsClone.Bookmarkable = True Then
Me!cmdAdd.Enabled = True
rsClone.Bookmark = Me.Bookmark
cmdFirst.Enabled = Not (rsClone.BOF)
cmdPrevious.Enabled = Not (rsClone.BOF)
cmdNext.Enabled = Not (rsClone.EOF)
cmdLast.Enabled = Not (rsClone.EOF)
I'm still fairly new to VBA and don't know if this is a large task or not, but again, I appreciate any help.
Just to be clear, you have a form where a record is selected (a person ID), and that opens another form and you want the second form to show only records related to the one selected on the first form?
1) Skip the "On Dirty" stuff - when the user selects a person and clicks a button, use that event to open the second form.
2) To disable the "New Record" feature of form navigation when you reach the end of the records, set the form property "Allow Additions" (under data) to No. This turns "New Record Navigation" off.
3) To limit the scope of the data on your second form to just the data you are interested in. Several ways to do this, but in your case I'd probably set the query to sort the visits in order of data and use the form filter property in code to limit the records (don't forget to set "FilterOn" to apply it) in the On Open event.
Something like this in the Form Open event:
If Me.OpenArgs <> "" Then
Me.Filter = "[PersonID] = " & Me.OpenArgs
Me.FilterOn = True
The records will be limited to only that person ID and the first record will be the first visit if you have the sort in the underlying query.