Form Filtered on Previous Form Record

[Helping Hand]

I would like to open a form based on the record that is on the current form.

So, when I open [frm5_Activity] with a command button, I would like it to be on the same record as [frm4_Activity.FieldID]. Can you please tell me how to do that?

[frm5_Activity.FieldID] = [frm4_Activity.FieldID]

[Rudi]

Try this: DoCmd.OpenForm "frm5_Activity", , , "FieldID = " & Forms![frm4_Activity.FieldID]

[Helping Hand]

Sorry, I'm not good at code writing. I tried to follow some of the other code patterns that appeared but I botched it up.

This is what I tried:

Private Sub Command102_Click()
On Error GoTo Err_Command102_Click


DoCmd.OpenForm [frm5_Activity], acNormal, [FieldID], [frm5_Activity.FieldID] & Forms![frm4_Activity.FieldID]"

Exit_Command102_Click:
Exit Sub

Err_Command102_Click:
MsgBox Err.Description
Resume Exit_Command102_Click

End Sub

[Rudi]

Access VBA is not an area I am very comfortable with (certainly not as with Excel VBA), and the syntax of Access has always eluded me (personally!).

Try this code behind the command button to open the form:

Code: Select all

Private Sub Command102_Click()
    On Error GoTo Err_Command102_Click
    DoCmd.OpenForm "frm5_Activity", , , "FieldID = " & Forms![frm4_Activity.FieldID]
Exit_Command102_Click:
    Exit Sub
Err_Command102_Click:
    MsgBox Err.Description
    Resume Exit_Command102_Click
End Sub

If the DoCmd line debugs for any reason, try any of the following variants by replacing the DoCmd line (one at a time) with the ones below.
As I say, I am not as Expert as Hans with the syntax...

Variants:

Code: Select all

    DoCmd.OpenForm "frm5_Activity", , , "FieldID = " & Forms!frm4_Activity![FieldID]
    DoCmd.OpenForm "frm5_Activity", , , "FieldID = " & Forms![frm4_Activity]![FieldID]
    DoCmd.OpenForm "frm5_Activity", , , "FieldID = " & me![FieldID]

[Helping Hand]

Thank you Rudi. The second one worked as requested but not as expected. I'm going to have to give it more thought and go back to the drawing board.

[Rudi]

Were you expecting something different?

[HansV]

Does the record source of frm5_Activity already contain a record with the same FieldID as the form containing the command button, or do you want to create a new record with that FieldID?

[Helping Hand]

The code that Rudi provided does take me to the new form, filtered on the same record - it was perfect. What I wanted to do with it was my bad and didn't work out right.

I like to help the community education out and the person that tracks everything has been struggling to keep all of the information straight. It's a very manual process with sports, classes, building rental, farmers market, sponsors, vendors etc. to track. I told her that I would love to help her out at no charge because they do a lot for our small community and I've always enjoyed working with Access. I could just through it together and she would still be a lot further ahead but I wanted to make it as user friendly as possible. So where I'm at is...

I have a form where she'll enter the Activities [frm4_Activities]. I have tables for the instructors, payment, participants, locations and so on. The table for the locations, which could be a classroom, soccer field, baseball diamond, building rental etc. There's a join table [tblLocations], which is used as a subform on the Activity form [frm4_Location subform]. It includes:

FieldID (combo box using tblActivityLocations)
ActivityID - join for [frm4_Activity]
InstructorID (combo box using tblInstructors)
LocationType (combo box using stblLocations)

When she goes to pick the location, I don't want her to have to scroll through all of the sports fields if what she's trying to find is one of the classrooms.

If there a way to filter [tblActivityLocations.FieldID] on whatever is selected in [LocationType]?

[HansV]

Yes, that is possible. See for example Basing one combo box on another and Creating Cascading Combo Boxes and List Boxes on Microsoft Access Forms.

[Helping Hand]

Thank you Hans. I'll give it a try.

[HansV]

Feel free to post back if you need more specific help.