none
How to pass values/parameters from "Access form" to "SQL query" in Access 2007

    Question

  • Tools: Microsoft management 2008 R2 and MS.Access 2007
    Environment: Windows 7 and SQL server 2008

    Context: I created form in MS access 2007 to display data. The data is generated in the form using SQL query.

    Select event_id, event_name, name where event_date = ' 20120724'   and class_code='ASP2012'.

    I want the users to be able to insert class_code and event date in the user-interface. The value are being passed to the SQL query to generate the report. How to input a new value on the WHERE Clause every time a user clicks on a select date and class number. Keep in mind that date is YYYYMMDD.

    Input:

    Part A: ActiveX control: MS Date and time date picker is added in the form

    name: DTPicker1

    OLE class: DTPicker

    Class: MSComCtl2.DTPicker.2

    Part B: Textbox

    Name: Text23

    Form name: workshop registeration1

    Thursday, December 06, 2012 6:42 PM

Answers

  • I assume that you want to open a report that lists the selected event_id, event_name and name values.

    Create a report based on the table that contains these fields. Let's say the report is named rptData.

    On your form, create a command button and name it cmdReport.

    Set its Caption to Open Report (or whatever you prefer).

    With the form open in design view, select the command button, then activate the Event tab of the Property Sheet.

    Click in the On Click box.

    Select [Event Procedure] from the dropdown list in this box.

    Click the builder dots ... to the right of the dropdown arrow in the On Click Box.

    This creates the "skeleton" of the On Click code of the button for you:

    Private Sub cmdReport_Click()
    
    End Sub

    Make the code look like this:

    Private Sub cmdReport_Click()
        Dim strWhere As String
        strWhere = "event_date='" & Format(Me.DTPicker1, "yyyymmdd") & _
            "' AND class_code='" & Me.Text23 & "'"
        DoCmd.OpenReport ReportName:="rptData", View:=acViewPreview, _
            WhereCondition:=strWhere
    End Sub

    It's possible to add bells and whistles, such as checking that values have been selected/entered, but this is the basic idea.

    Regards, Hans Vogelaar

    • Marked as answer by Sandra VO Monday, December 10, 2012 4:05 PM
    Thursday, December 06, 2012 9:42 PM

All replies

  • If the code runs in the form's module:

        Dim strSQL As String
        strSQL = "SELECT event_id, event_name, name FROM sometable " & _
            "WHERE event_date='" & Format(Me.DTPicker1, "yyyymmdd") & _
            "' AND class_code='" & Me.Text23 & "'"

    If it runs in another module:

        Dim strSQL As String
        strSQL = "SELECT event_id, event_name, name FROM sometable " & _
            "WHERE event_date='" & Format(Forms![workshop registeration1]!DTPicker1, "yyyymmdd") & _
            "' AND class_code='" & Forms![workshop registeration1]!Text23 & "'"


    Regards, Hans Vogelaar

    Thursday, December 06, 2012 8:18 PM
  • Many thanks for taking the time. I have a fellow up a question. I forgot to ask about adding a button. when the user hit submit button. It will pass the values to the Query. I also wonder where should I embed the code and how to embed the code. I am novice :)


    • Edited by Sandra VO Thursday, December 06, 2012 9:32 PM
    Thursday, December 06, 2012 9:12 PM
  • I assume that you want to open a report that lists the selected event_id, event_name and name values.

    Create a report based on the table that contains these fields. Let's say the report is named rptData.

    On your form, create a command button and name it cmdReport.

    Set its Caption to Open Report (or whatever you prefer).

    With the form open in design view, select the command button, then activate the Event tab of the Property Sheet.

    Click in the On Click box.

    Select [Event Procedure] from the dropdown list in this box.

    Click the builder dots ... to the right of the dropdown arrow in the On Click Box.

    This creates the "skeleton" of the On Click code of the button for you:

    Private Sub cmdReport_Click()
    
    End Sub

    Make the code look like this:

    Private Sub cmdReport_Click()
        Dim strWhere As String
        strWhere = "event_date='" & Format(Me.DTPicker1, "yyyymmdd") & _
            "' AND class_code='" & Me.Text23 & "'"
        DoCmd.OpenReport ReportName:="rptData", View:=acViewPreview, _
            WhereCondition:=strWhere
    End Sub

    It's possible to add bells and whistles, such as checking that values have been selected/entered, but this is the basic idea.

    Regards, Hans Vogelaar

    • Marked as answer by Sandra VO Monday, December 10, 2012 4:05 PM
    Thursday, December 06, 2012 9:42 PM
  • Thanks

    I am currently working on the code. I'll let you know soon about the outcome.

    Again thanks for the prompt response

    Friday, December 07, 2012 12:13 AM