Results 1 to 2 of 2
  1. #1
    skumar_neo is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    1

    How to pass parameter value to a query in MS Access?

    Hello all,


    I have a question on Query execution in MS Access object using vba.


    #MS Access 2016

    I use the below statement to execute a query,


    DoCmd.OpenQuery queryname,acNormal,acEdit


    The above is working fine, for without parameter scenario.


    I am trying to add some parameter value for this query, please see below


    ----------------------------------------------------------------------------------------------------------------------------
    PARAMETER txtValue LongText
    SELECT DISTINCTROW Patient.PATIENTNUMBER, Patient.TITLE, Patient.LASTNAME, Patient.FIRSTNAME,
    Patient.ADDRESS, Patient.CITYPROVINCE, Patient.POSTALCODE, Patient.RPTITLE, Patient.RPLASTNAME, Patient.RPFIRSTNAME,
    Patient.RPADDRESS, Patient.RPCITYPROVINCE, Patient.RPPOSTALCODE, Patient.RPPHONEHOME, Patient.RPPHONEBUS,
    Dentist.DLASTNAME, Dentist.DFIRSTNAME, Dentist.DADDRESS, Dentist.DCITYPROVINCE, Dentist.DPOSTALCODE,
    txtValue AS Reported INTO tblMergeDataD
    FROM Dentist INNER JOIN Patient ON Dentist.DENTISTNUMBER = Patient.DENTISTNUMBER
    WHERE (((Patient.PATIENTNUMBER)=[Forms]![frmMainReportPatient]![ctlPatientNumber]));
    -----------------------------------------------------------------------------------------------------------------------------


    How to pass the parameter value to this query?


    Thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The param in your statement requires the form to be open.
    You can add more textboxes on the form, then add them too to the SQL.

    if you are talking about adding random field params,
    (ask for City once, then ask for zipcode another time)
    then you must use vb code to build the Where statement.
    i would have a continuous form of all data. User can enter random contols (city box,or zip box)
    then it filters the list.

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

Similar Threads

  1. Replies: 2
    Last Post: 07-20-2016, 03:32 AM
  2. Pass Date Parameter to MS Access Report
    By siddiqali87 in forum Programming
    Replies: 1
    Last Post: 07-12-2015, 06:40 AM
  3. Replies: 7
    Last Post: 03-11-2015, 12:48 PM
  4. Replies: 2
    Last Post: 10-20-2014, 08:11 AM
  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