How to create "Go To Next" record button in Access Form that references an alternate form

Question

In my Access dB, I have a Form ("frmSearch") with a Subform ("subFrmData") that references a query ("qryDynamicData").

The Main Form has 6 drop downs that filter the data table in the Subform. The combo boxes are cascading so that once you select one, the entries that appear in the remaining combo boxes are only those entries in the query that correlate to the first selection. The goal is to keep making the data in the Subform data table shorter and shorter. Here are the fields in the combo boxes:

• Region
• Account Executive
• Manager
• Engineer
• Stage
• Project Number ("ProjectSLCT")

When the user filters down to where the list of projects they are looking at is small enough, they use the final combo box ("Project Number") to select an entry from the table. Doing this brings up another form ("MPC_ProjectNotes") as a pop-up form where they can keep track of project specifics. The event looks like this:

DoCmd.OpenForm "MPC_ProjectNotes", , , "Project_Number= '" & Me.ProjectSLCT.Value & "'"

I want to create a Next Record button on the "MPC_ProjectNotes" form that would in effect allow them to run through the steps of selecting the next item in the ProjectSLCT dropdown and thereby relaunching the MPC_ProjectNotes form with the next item from the combo box (and from the datasheet in "subFrmData") without having to close the MPC_ProjectNotes form and adjust the combo box.

Any thoughts? I don't really even know what to google to get myself pointed in the right direction on this. It seems "Next Buttons" aren't generally setup to work across forms.

Answer 1

Ok, here is what I would suggest:

In place of launching form MPC_ProjectNotes to ONE reocrd?

Why not launch the form with the SAME criteria and list as your combo box ProjectSLCT.

Now, we would of course land in the first reocrd of ProjectSLCT. but that's probably ok!

Now say if ProjectSLCT was NOT on the first record? Then we would simple have to add one extra step: move the form to the right record.

So, the logic is this:

Launch the 2nd form to the same "list" as the combo box. Move the 2nd form to the SAME item currently selected in the combo box.

Now, you don't even have to write a next/previous button, but can simple display the built in navigation buttons. Since the form is LIMITED to the same list, then regular navigation in that form will work and work without you having to write any code!

So someplace in your first form, you EVENTUALLY wind up with this:

combobox data source = select * from tblWhoKnows where (CRITERA GOES HERE!!!).

So, now we simple launch the 2nd form with this:

DoCmd.OpenForm "MPC_ProjectNotes", , , (CRITERA GOES HERE!!!)

Now the form is loaded with the SAME list. We just this extra line of code to move/jump the form to the current selected row in the combo.

forms("MPC_ProjectNotes").RecordSet.FindFirst "Project_Number= '" & Me.ProjectSLCT.Value & "'"

So we open the form to the "list", and then jump to the corrent combo box selected. The result is a form that you can navagate teh combo list, but we jump/started out on the same combox selected item from that list in the form.

So the code will look something like this:

DoCmd.OpenForm "MPC_ProjectNotes", , , (CRITERA GOES HERE!!!)
Forms("MPC_ProjectNotes").RecordSet.FindFirst "Project_Number= '" & Me.ProjectSLCT.Value & "'"

You will of course "change" the above "CRITERA" to the SAME criteria you used in your code to fill out the last combo box.