Pass Variable to SQL Parameter Query in VBA

Posted on 2006-03-24
Last Modified: 2008-02-01
Can you pass the value (text) from an InputBox directly to a strSQL query to build a parameter query? I have tried several ways to do this but, to no avail.

Function Pass_String()

Dim strSQL As String
Dim strPrompt As String
Dim strTitle As String
Dim strUserInput As String
Dim strParameter As String

strPrompt = "Enter Customer Name"
strTitle = "Enter Data"

strUserInput = InputBox(Prompt, Title)
strParameter = strUserInput

If strUserInput = "" Then
    Call Get_TECP
        If UserInput <> "" Then
        Call Get_SVPT
    End If
End If
End Function

Function Get_TECP()

strSQL = "SELECT  tblMasterData.[Customer], tblMasterData.[Customer Name], tblMasterData.[Customer Parent]"
strSQL = strSQL & " FROM tblMasterData WHERE tblMasterData.[Customer Parent]="'& strParameter'"&"

DoCmd.RunSQL strSQL

End Function
Question by:KFFrench
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hi KFFrench,

    strSQL = "SELECT  tblMasterData.[Customer], tblMasterData.[Customer Name], tblMasterData.[Customer Parent]"
    strSQL = strSQL & " FROM tblMasterData WHERE tblMasterData.[Customer Parent]='" & strParameter & "'"

    YOu were mixing up your single- and double-quotes.


    LVL 44

    Accepted Solution

    You can create QueryDef objects in DAO code and pass the parameters in there.
    (Not difficult - bound to be loads of examples on EE)

    Another way might be to use functions in your query as criteria.
    The functions simply retrieve that value of a public variable.
    So you'd just set the public variable values.
    Then fire your queries.

    As someone I know once said...

    LVL 77

    Expert Comment

    Hi KFFrench,

    I'm sure your SQL string is fine.

    But you can't run a Select query like this.  
    You must either open a recordset based on your string or save you string to a saved query and run that.

    LVL 92

    Expert Comment

    by:Patrick Matthews

    > But you can't run a Select query like this.  

    That loud sound you may have just heard was me banging my head into the wall for forgetting something so elementary :)



    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now