Results 1 to 6 of 6
  1. #1
    vad77 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101

    Question Query Criteria based on filed value from subform

    I have a form which consists of customer records. On that main form I have a sub-form that shows the contracts associated with the current customer that is being shown on the form.
    What I want to do is open another form based on what contract is currently showing in the sub-form.
    Which will be the best way to complete this action?
    Have the new forms data source query criteria set to open based on the sub-form
    or
    Have nothing in the new forms data source query criteria and use VBA on the command button.

    I tried setting the criteria on the new forms data source query criteria but can't get it to work.
    Like [forms]![sub-form].[field_name]
    Like [forms]![main-form]![sub-form].[field-name]

    Any assistance would be greatly appreciated.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Here's a link for the proper syntax for what you are attempting: http://access.mvps.org/access/forms/frm0031.htm

  3. #3
    vad77 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101
    Thank you RuralGuy I am going to bookmark that page for future reference.
    What I decided to do is what I did on another database of mine and that is to make the field on the sub-form a link. Then on the event tab for On Click use VBA code to open the form with a Where condition.
    This worked for me before and might be a better option instead of adding another command button.
    I still have a problem after doing so on my new database. The first line of code was copied from another of my databses and pasted into my new databse. Then I just changed the names of the forms and field.

    Works in other db
    DoCmd.OpenForm "fContactAdd", acNormal, , "[ContactID]=" & [Forms]![main_site_information]![sfrm_SiteInfoContacts].Form![ContactID], , acWindowNormal

    Doesn't work in new db
    DoCmd.OpenForm "frm_ContractEdit", acNormal, , "[ContractNumber]=" & [Forms]![frm_SitesMain]![frm_ContractSubForm].Form![ContractNumber], , acWindowNormal

    When I click the field link I get a Run-time error 2465: Can't find field "frm_ContractSubForm" referred to in your expression.
    I checked spelling of table names for errors and all is correct. What is error 2465?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You'll find a wealth of good stuff on that site. To start, use a dot "." rather than the bang "!" in the syntax. I think you will find it works better. As for the error, it is saying it cannot find a *SubFormControl* named "frm_ContractSubForm". It is looking for the name of the SubFormControl and not the name of the SubForm it is displaying.

  5. #5
    vad77 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101
    Thanks! Corrected my syntax and it works. The sub-form is named: frm_ContractSubForm and the sub-form control is named: frm_ContractSubForm1.
    You have been a big help.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    That's great! Thanks for posting back with your success and solution and marking this thread as Solved!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-26-2014, 06:35 PM
  2. Sum numerical filed based on other field
    By BorisGomel in forum Access
    Replies: 0
    Last Post: 07-11-2012, 09:54 AM
  3. Sum one filed based on its contsest
    By BorisGomel in forum Access
    Replies: 2
    Last Post: 03-29-2012, 03:01 PM
  4. Replies: 2
    Last Post: 11-15-2010, 03:57 PM
  5. Open form based on Subform criteria
    By Suzan in forum Programming
    Replies: 0
    Last Post: 04-25-2006, 02:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums