• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 870
  • Last Modified:

Pass Variable to SQL Parameter Query in VBA

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
0
KFFrench
Asked:
KFFrench
  • 2
1 Solution
 
Patrick MatthewsCommented:
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.

Regards,

Patrick
0
 
Leigh PurvisDatabase DeveloperCommented:
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...

Idea?
0
 
peter57rCommented:
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.

Pete
0
 
Patrick MatthewsCommented:
Pete,

> 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 :)

Regards,

Patrick
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now