Results 1 to 4 of 4
  1. #1
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163

    Where Clause For Opening Forms

    I'm having a hell of a time creating this code.

    I want to open a form to a specific record by clicking on the ID (click event on the textbox) of a record located in a subform of a currently opened form. The subform is in datasheet view (in case that matters)


    There is an ID in the subform in a text box...txtSubID and I'll be matching it up with the ID in text box txtFormToOpenID.

    I'm struggling with how to write the where statement in the DoCmd. Does the subform go first in quotes? Or does the one I want to open go first? Which should be in quotes and which should not?

    And Should I be using the control source name or the text box object name...or both?

    I'm losing my mind over here. I've found a lot of info on the internet but it doesn't work when I try the code.

    I had it set up but using a command button wizard to set it up and then copied the macro to the click event of the textbox on the subform. It worked for a few days and then tanked out on me.



    I then tried doing the same with a commend button again and converting the macro to code, but that didn't work either.

    Thanks very much.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    The Where is like a WHERE clause just without the where
    So something along the line sof
    "ID = " & Me.ID

    where ID is the field in the table and ID is also the control on the form. As the ID is *generally* numeric, no quotes are required

    Here is one of mine
    Code:
    DoCmd.OpenForm "frmSubmitterClient", , , "ClientID = " & Me.ClientID, , acDialog, Me.ClientID
    I was also using OpenArgs (last parameter) to determine a new record required or not?
    HTH
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    In the Click (I suggest you use the DoubleClick event as the Click one is to easy to trigger by accident) of the txtSubID on the subform add:
    Code:
    Docmd.OpenForm "frmYourFOrmToOPen",,,"[NameOfFieldThatISControlSourceFor_txtFormToOpenID]=" & Me.txtSubID 'assumes ID is numeric;if text you need to wrap it in singe quotes
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Awesome. Thank you. It worked brilliantly. So name of control field in form to open and then name of the actual control in the current form? There's smoke coming out of my ears.

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

Similar Threads

  1. Opening forms
    By fcbuek in forum Access
    Replies: 2
    Last Post: 11-11-2019, 03:21 PM
  2. referencing a tab in Where Clause when opening a form
    By mainerain in forum Programming
    Replies: 6
    Last Post: 05-28-2019, 01:50 PM
  3. Opening Forms Maximized
    By data808 in forum Forms
    Replies: 3
    Last Post: 04-29-2014, 04:37 PM
  4. Replies: 3
    Last Post: 10-23-2013, 08:11 AM
  5. Opening Report using WHERE CLAUSE not working
    By babui386 in forum Reports
    Replies: 2
    Last Post: 02-28-2013, 06:15 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