Results 1 to 8 of 8
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Pass Parameter To Form From Button Click

    I am using the onclick() event of a button to open up a second form. The second form is based off a query (hypothetical) let's say the query is
    Code:
    select * from employeedata
    now I want to be able to add a where clause and a parameter to the syntax based off a button press. If my button press event looks like this
    Code:
    Private Sub cmdbtn1_Click()
    Dim stDocName As String, stLinkCriteria As String, Dim WC As String
    stDocName = "frmTest"
    set WC = "Nancy"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End Sub
    So based off the above I would want to modify the query the form is based on to be
    Code:
    Select * from employeedata where employeename = WC
    There will always be a parameter passed from the button click event. What is the proper way to set this up in a button click event?

    EDIT ------


    Or would it be easier to pass a vba variable to the where clause of onload() event of the form, as opposed to altering a query?

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The stLinkCriteria will be "employeename='" & somevaluefield & "'"
    This will filter the query (and thence the form) as if you manually added a criteria to it, no need to do anything else.

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by aytee111 View Post
    The stLinkCriteria will be "employeename='" & somevaluefield & "'"
    This will filter the query (and thence the form) as if you manually added a criteria to it, no need to do anything else.
    If I Just leave my query as
    Code:
    Select * from employee
    And set my onclick code to be
    Code:
    Private Sub cmdbtn1_Click()
    Dim stDocName As String, stLinkCriteria As String, Dim WC As String
    stDocName = "frmTest"
    set WC = "Nancy"
    DoCmd.OpenForm stDocName, , , "employeename='" & somevaluefield & "'"
    End Sub

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Perfect! You don't need all those variables - stDocName, etc - this can be one line. And I am assuming that the field "employeename" exists in the record source for the second form.

  5. #5
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Yes, employeename is a field in the query, as well as displayed on the form!

    I tried to just hardcode a value, but I got a prompt asking me for input...is there a way to do it all on one line as you mention?

    Code:
    DoCmd.OpenForm stDocName, , , "employeename=Mary"

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Do not use Set just to populate a string variable, simply:

    WC = "Nancy"

    or

    WC = Me.yourtextboxname

    or

    WC = Me!yourfieldname

    or don't even bother with variables that are used only once.

    DoCmd.OpenForm "frmTest", , , "employeename='" & Me.yourtextboxname & "'"

    Actually, using employee ID would probably be better than employee name.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    @June7 - how then do I pass the string variable in one line?

    If I do the below it asks for the parameter:
    DoCmd.OpenForm "frmTest", , , "employeename=Mary"

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You need quotes around Mary, it is a text field
    "employeename='Mary'"

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

Similar Threads

  1. Replies: 1
    Last Post: 09-29-2015, 02:25 AM
  2. Replies: 7
    Last Post: 03-11-2015, 12:48 PM
  3. run button countinous form with one click
    By adilos in forum Access
    Replies: 27
    Last Post: 08-11-2014, 05:27 PM
  4. On Click Event For Button On Form
    By Desstro in forum Forms
    Replies: 3
    Last Post: 08-09-2010, 02:36 PM
  5. Pass a Parameter From a form to a Query
    By DDillesha in forum Forms
    Replies: 1
    Last Post: 10-28-2009, 12:49 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