Results 1 to 12 of 12

Code to open up form to specfic record from search query

  1. #1
    banpreet is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    101

    Code to open up form to specfic record from search query

    Hi guys, I have a main menu with a search button which allows the user to search for a record. This is the parent id which has many child ids connected to it. The user searchs the parent id and then has a form that brings up that id, now the user clicks on another button which runs another query which searches for all the ids associated with the parent id as mentioned before which brings up another form with search results. Now I have the form which works good and brings up all the ids associated with the parent id. Now I put a button and want to open up that form through the search query form. I have GTSR_ID as the parent, TR_Id as child and then revision field in this search form and the button to open up that form. When the user searches the for his/her form they can have multiple same TR_ID's(child id) for the one GTSR_ID(parent id). For example for GTSR_ID, the id is 50, and the TR_ID is 45. however revision will be 0 and there will be another record which GTSR_ID is 50 and TR_ID is 45, but revision is 1, which is another record but same id. I want to open up the form specfilly to the revision field. T



    This is the code to open up the form, which is does but not it does not open up to spefic revision which is another record, for example if the user clicks on the 2nd revision, it will open up to 0th(which is the defulat 1st revision for all forms) revision instead of the 2nd revision for the record. What do I do to get it to open up the specific record according to revision. Hope this makes sense!

    DoCmd.OpenForm "TR_Form", , , "GTSR_ID = '" & Me.GTSR_ID & "'"

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    15,856
    Along the lines of:

    DoCmd.OpenForm "TR_Form", , , "GTSR_ID = '" & Me.GTSR_ID & "' AND Revision = " & Me.Revision

    which presumes revision is numeric. Add the single quotes if it's text.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    1,594
    The syntax looks right.
    - is GTSR_ID a text field (as you have it) or a number?
    - does the Record Source of TR_Form contain GTSR_ID?
    - have you checked that there is a value in Me. GTSR_ID?

  4. #4
    banpreet is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    101
    I tried this code

    DoCmd.OpenForm "TR_Form", , , "GTSR_ID = '" & Me.GTSR_ID & "Revisions = '" & Me.Revisions & "TR_ID = '" & Me.TR_ID & "'"

    I get syntax errior (mission operator) in query expression

  5. #5
    banpreet is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    101
    GTSR is text and there is value for everything.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    15,856
    Quote Originally Posted by banpreet View Post
    I tried this code

    DoCmd.OpenForm "TR_Form", , , "GTSR_ID = '" & Me.GTSR_ID & "Revisions = '" & Me.Revisions & "TR_ID = '" & Me.TR_ID & "'"

    I get syntax errior (mission operator) in query expression
    Did you try what I suggested, using your names?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  7. #7
    banpreet is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    101
    What do you mean by names? everything is the same

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,064
    You left the AND's out of the criteria:

    DoCmd.OpenForm "TR_Form", , , "GTSR_ID = '" & Me.GTSR_ID & " AND Revisions = '" & Me.Revisions & " AND TR_ID = '" & Me.TR_ID & "'"

  9. #9
    banpreet is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    101
    still getting sytax error message

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    15,856
    Quote Originally Posted by John_G View Post
    You left the AND's out of the criteria:

    DoCmd.OpenForm "TR_Form", , , "GTSR_ID = '" & Me.GTSR_ID & " AND Revisions = '" & Me.Revisions & " AND TR_ID = '" & Me.TR_ID & "'"
    You have mismatched the single quotes around the variables. banpreet, if the data type of a field is text you need the single quotes around the variable, if numeric not.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  11. #11
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,064
    Thanks, Paul - I missed that.

    J.

  12. #12
    banpreet is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    101
    Thanks it worked!

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

Similar Threads

  1. Continuous search form won't open selected record in view form.
    By IncidentalProgrammer in forum Programming
    Replies: 20
    Last Post: 03-24-2015, 01:53 PM
  2. search record if found open form.
    By cysklement in forum Access
    Replies: 3
    Last Post: 12-20-2014, 12:00 AM
  3. Replies: 6
    Last Post: 11-13-2012, 03:29 PM
  4. Replies: 1
    Last Post: 09-08-2012, 04:51 AM
  5. Replies: 1
    Last Post: 05-03-2012, 01:25 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
  •  
Tech Forums: Microsoft Office Forums