KFFrench
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
> 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
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