Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opening a form based on criteria in sub form (Access 2003)

    Hello,

    I have viewing only form (incl. a sub form) that i would like users to be able to open based on various criteria. I have set up another form for them to select the criteria on. However, one of the criteria is actually on the sub form not the main form. How do i write this so that when a set of sub criteria is selected, the main form opens showing the appropriate main and sub record?

    Thank you!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening a form based on criteria in sub form (Access 2003)

    You can code like this:

    Dim strMainForm As String
    Dim strSubForm As String
    Dim strMainCriteria As String
    Dim strSubCriteria As String
    ' Name of the main form
    strMainForm = ...
    ' Name of the subform as a control on the main form
    strSubForm = ...
    ' Criteria for main form
    strMainCriteria = ...
    ' Criteria for subform
    strSubCriteria = ...
    ' Open the main form with WhereCondition
    DoCmd.OpenForm strMainForm, , , strMainCriteria
    ' Filter the subform
    With Forms(strMainForm).Controls(strSubForm).Form
    .Filter = strSubCriteria
    .FilterOn = True
    End With

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form based on criteria in sub form (Access 2003)

    Sorry - where do i put the different parts of this code! Have to bear with me - i'm new to all this.

    Are there any previous posts i could refer to?

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening a form based on criteria in sub form (Access 2003)

    I assumed that you had a command button on the form where the user can specify the criteria. The code I posted is supposed to go into the On Click event procedure of the command button. Let's say the command button is named cmdOtherForm.
    Open the form in design view.
    Select the command button.
    Activate the Event tab of the Properties window.
    Click in the On Click box.
    Select [Event Procedure] from the dropdown list.
    Click the ... (the builder button) to the right of the dropdown arrow.
    Copy and paste the code from my previous reply between the Sub... and End Sub.
    If you need help with filling in the missing parts in the code, please provide details.

  5. #5
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form based on criteria in sub form (Access 2003)

    Hello,

    I'm sorry, that's still not working - coming up with errors in the code.

    The main form that i am trying to open is a form of business details with the sub form being the details for the contacts we have there. I would like to users to be able to open the main form based on crietria selected on a seperate form - either business name, business key word (these two are easy as the link is on the main form) or surname of a contact (link is on sub form). The combo box that i have set up for them to select from shows surname, forename (as an expression) and business name. I therefore don't currently have business name as a seperate box on the criteria form to link to the main form (as i think your code requires).

    Very stuck now but keen to learn if you can help again!
    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening a form based on criteria in sub form (Access 2003)

    It is not necessary to have different controls for the criteria; you can refer to different columns of the combo box in strMainCriteria = ... and strSubCriteria = ... The first column in a combo box is ComboBoxName.Column(0), the second one is ComboBoxName.Column(1), etc.

    If you need more specific help, you'd have to provide detailed information. It would be easier if you could post a stripped down copy of your database, so that Loungers can look at it directly. See <post#=401925>post 401925</post#> for instructions.

  7. #7
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form based on criteria in sub form (Access 2003)

    Thanks, that makes much more sense with the combo box columns.

    I've got the code in now as e-mailed and it is saying invalid reference for the .filter section at the end. Any ideas?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening a form based on criteria in sub form (Access 2003)

    The value of strSubForm must be the name of the subform as a control on the main form. This is not necessarily the same as the name of the subform in the database window. To determine the control name of the subform:

    - Open the main form in design view.
    - Click once on the subform (do not click on it a second time, for that will select something in the subform).
    - The name will be displayed in the Name property in the Other tab of the Properties window and in the caption of that window.

    If that doesn't help, please post (the relevant part of) the code you have now.

  9. #9
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form based on criteria in sub form (Access 2003)

    THis is the code i have now:

    Private Sub Command2_Click()
    Dim strMainForm As String
    Dim strSubForm As String
    Dim strMainCriteria As String
    Dim strSubCriteria As String
    ' Name of the main form
    strMainForm = businessesfrm2
    ' Name of the subform as a control on the main form
    strSubForm = contactsqry1
    ' Criteria for main form
    strMainCriteria = Combo0.Column(2)
    ' Criteria for subform
    strSubCriteria = Combo0.Column(0)
    ' Open the main form with WhereCondition
    DoCmd.OpenForm strMainForm, , , strMainCriteria
    ' Filter the subform With
    Forms(strMainForm).Controls (strSubForm)
    Form.Filter = strSubCriteria
    Form.FilterOn = True
    End With

    End Sub

    It is coming up with an error of 'invalid use of property' for the .controls part 4 lines up from the bottom.

    Seems to come up with a new error each time i fix one though!

    Thanks.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening a form based on criteria in sub form (Access 2003)

    Apparently, something went wrong when copying the code into the module. The word "With" ended up on the wrong line. Instead of

    ' Filter the subform With
    Forms(strMainForm).Controls (strSubForm)
    Form.Filter = strSubCriteria
    Form.FilterOn = True
    End With

    you should have

    ' Filter the subform
    With Forms(strMainForm).Controls(strSubForm)
    Form.Filter = strSubCriteria
    Form.FilterOn = True
    End With

  11. #11
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form based on criteria in sub form (Access 2003)

    Thanks.

    Now it's coming up with ' Runtime error 2494 - this action of method requires a form name argument'. When i click to debug, it's showing the following line as the problem:
    DoCmd.OpenForm strMainForm, , , strMainCriteria

    Any ideas again?

    Thanks.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening a form based on criteria in sub form (Access 2003)

    The values of strMainForm and strSubForm must be strings, i.e. their values must be enclosed in quotes:

    Name of the main form
    strMainForm = "businessesfrm2"
    ' Name of the subform as a control on the main form
    strSubForm = "contactsqry1"

  13. #13
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form based on criteria in sub form (Access 2003)

    Ok got that one.

    Now it's saying 'Runtime error 3075 - syntax error (missing operator) in query expression - Business name'

    Will get there eventually!

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening a form based on criteria in sub form (Access 2003)

    The criteria must be of the form

    "[FieldName] = " & Value

    or something similar; the exact format depends on the type of field. Text values, for example, must be enclosed in quotes, and date values in #s. so you must have something like this:

    ' Criteria for main form
    strMainCriteria = "[Business name] = " & Chr(34) & Combo0.Column(2) & Chr(34)
    ' Criteria for subform
    strSubCriteria = "[Contact name] = " & Chr(34) & Combo0.Column(0) & Chr(34)

    The square brackets around the field name are required if the field name contains spaces or punctuation, and Chr(34) is the equivalent of a double quote ". I have assumed that the fields you want to filter on are named Business name and Contact name, and that they are text fields.

  15. #15
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form based on criteria in sub form (Access 2003)

    Thank you - we have progress - the main form is now opening correctly. However the filter is still not being applied to the sub form.

    The code i have now is:
    Private Sub Command2_Click()
    Dim strMainForm As String
    Dim strSubForm As String
    Dim strMainCriteria As String
    Dim strSubCriteria As String
    ' Name of the main form
    strMainForm = "businesses frm2"
    ' Name of the subform as a control on the main form
    strSubForm = "contact frm for businesses2 frm"
    ' Criteria for main form
    strMainCriteria = "[BTBL Business name] = " & Chr(34) & Combo0.Column(2) & Chr(34)
    ' Criteria for subform
    strSubCriteria = "[CTBL Surname] = " & Chr(34) & Combo0.Column(0) & Chr(34)
    ' Open the main form with WhereCondition
    DoCmd.OpenForm strMainForm, , , strMainCriteria
    ' Filter the subform
    With Forms(strMainForm).Controls(strSubForm)
    Form.Filter = strSubCriteria
    Form.FilterOn = True
    End With

    End Sub

    I think the problem lies with the definition of strsubform = .
    I am still unsure of the correct name for this. The name shown on the title bar of the sub form when in the main form is just 'form' which i have tried but 'form' is also the name of the main form when shown in the same way. I cannot find a 'name' property anywhere in properties and i have tried the caption name without success too.

    However an error did come up saying that the code didn't recognise the FIELD NAME 'form' but i thought we were defining the form name not field name.

    Please help - nearly there now hopefully!!
    Thanks.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •