Access 2007, Control Record Access with Intermediate Form

dhartwig35805 · #1 (**permalink**) 01-05-12, 10:48

Greetings to all Access Wizards,

I am developing an application and I have a main menu (not using Switchboard) that controls all aspects of my application.

However, there is one table that contains the 'meat' of the database, and this table can be manipulated by numerous people all at the same time.

What I would like to do is to insert a form between my main menu and the form where they can manipulate the data, and on the intermediate form, have them select their "Organization" from a drop down list, and then hit a button to open the data manipulation form.

I only want them to get the data for their particular "Organization", hence the form for them to choose the "Org.", but here is where I get lost. I am not sure of the best way to select the proper records for display after they choose their organization. Any suggestions? Less complicated is better than extremely complicated, not worried about myself, but those who might come after me.

I would like to thank you all in advance for your understanding and cooperation in this matter.

Sincerely,

DH

dhartwig35805 · #2 (**permalink**) 01-05-12, 15:17

Quote:

Originally Posted by dhartwig35805

Greetings to all Access Wizards,

I am developing an application and I have a main menu (not using Switchboard) that controls all aspects of my application.

However, there is one table that contains the 'meat' of the database, and this table can be manipulated by numerous people all at the same time.

What I would like to do is to insert a form between my main menu and the form where they can manipulate the data, and on the intermediate form, have them select their "Organization" from a drop down list, and then hit a button to open the data manipulation form.

I only want them to get the data for their particular "Organization", hence the form for them to choose the "Org.", but here is where I get lost. I am not sure of the best way to select the proper records for display after they choose their organization. Any suggestions? Less complicated is better than extremely complicated, not worried about myself, but those who might come after me.

I would like to thank you all in advance for your understanding and cooperation in this matter.

Sincerely,

DH

Disregard this post. Found a different way. Thanks to all who responded.

Sinndho · #3 (**permalink**) 01-05-12, 15:23

There are several solutions in Access to open a form on a data set that is a subset of a table, using a criteria. You can:

1. Use the third ("FilterName") or fourth ("WhereCondition") parameter of the "DoCmd.OpenForm" method.

2. Use a query that has a reference to a control from another form in its WHERE clause as the RecordSource property of the form you want to open.

3. Manually change the Filter pproperty of the form once it's open (from "outside", using a parameter passed as "OpenArgs" (7th parameter of the "DoCmd.OpenForm" method) or retrieving a Public variable or property.

4. Builld a dynamic query or modify the SQL property of a QueryDef object, according to the selection.

5. Probably several other methods that I don't think of at the moment.

Did you encounter a specific problem or are you looking for advices or opinions.

Not knowing your application, my favourite would probably be something like this (air code):

Code:

Private Sub CommandOpen_Click()

    Dim strCriteria As String
    
    If Not IsNull(Me.ComboOrg.Value) Then
        strCriteria = "Organization = '" & Me.ComboOrg.Value & "'"
        DoCmd.OpenForm "Intermediate_Form", , , strCriteria
        
        ' Possibly:
        '
        ' DoCmd.OpenForm "Intermediate_Form", , , strCriteria , , acDialog
        '
    End If
    
End Sub

Internet Services

Web Development

Digital Marketing

Consumer Tech