MS Access Parameter Queris

[nuages]

I am trying to run a parameter query that accepts user input. The
query runs in the Query Grid but will not run in VBA as in line SQL.
The SQL from the Query Grid is:

SELECT tblTestTable.ID, tblTestTable.FirstName, tblTestTable.LastName,
tblTestTable.Age
FROM tblTestTable
WHERE (((tblTestTable.FirstName)=[Enter First Name]));

When I copy this into the VBA Module I get an error "Too Few
Parameters. Expected 1"

I then tried the following (From allen Brown)

strSql = "SELECT tblTestTable.ID, tblTestTable.FirstName,
tblTestTable.LastName, tblTestTable.Age " & vbCrLf & _
"FROM tblTestTable " & vbCrLf & _
"WHERE (((tblTestTable.FirstName)=[Enter First Name]));"

But I still get the same error ie "Too Few Parameters. Expected 1"

I am using MS Access 2003 and VB 5.5

It's driving me mad and any help would be greatly appreciated.

 

[Arvin Meyer [MVP]]

You have to supply a value to the parameter manually:

Dim db As DAO.Database
Dim qdf As DAO.Querydef
Dim rst As DAO.Recordset

Set db = Currentdb
Set qdf = db.Querydefs!qryYourParameterQuery

qdf.Parameters!prmParameterName = Forms!MyformName!MycontrolName

Set rst = qdf.OpenRecordset()

 

[CompGeek78]

I am trying to run a parameter query that accepts user input. The
query runs in the Query Grid but will not run in VBA as in line SQL.
The SQL from the Query Grid is:

SELECT tblTestTable.ID, tblTestTable.FirstName, tblTestTable.LastName,
tblTestTable.Age
FROM tblTestTable
WHERE (((tblTestTable.FirstName)=[Enter First Name]));

When I copy this into the VBA Module I get an error "Too Few
Parameters. Expected 1"

I then tried the following (From allen Brown)

strSql = "SELECT tblTestTable.ID, tblTestTable.FirstName,
tblTestTable.LastName, tblTestTable.Age " & vbCrLf & _
"FROM tblTestTable " & vbCrLf & _
"WHERE (((tblTestTable.FirstName)=[Enter First Name]));"

But I still get the same error ie "Too Few Parameters. Expected 1"

I am using MS Access 2003 and VB 5.5

It's driving me mad and any help would be greatly appreciated.

If you want a recordset you can interact with, do this.

Dim rs As DAO.Recordset
Dim sSQL As String
Dim sParam As String

sParam = InputBox("Message asking user to type in value:")
sSQL = "SELECT tblTestTable.ID, tblTestTable.FirstName,
tblTestTable.LastName, tblTestTable.Age" & _
" FROM tblTestTable" & _
" WHERE tblTestTable.FirstName = '" & sParam & "';"

rs = CurrentDb.OpenRecordset(sSQL)

if you just want to use the SQL string as the ControlSource/
RecordSource for a control, you can just do:

Dim sSQL As String
Dim sParam As String

sParam = InputBox("Message asking user to type in value:")
sSQL = "SELECT tblTestTable.ID, tblTestTable.FirstName,
tblTestTable.LastName, tblTestTable.Age" & _
" FROM tblTestTable" & _
" WHERE tblTestTable.FirstName = '" & sParam & "';"

Me.RecordSource = sSQL

Keven Denen

You can now use rs as if it were your table/query

 

[nuages]

You have to supply a value to the parameter manually:

Dim db As DAO.Database
Dim qdf As DAO.Querydef
Dim rst As DAO.Recordset

Set db = Currentdb
Set qdf = db.Querydefs!qryYourParameterQuery

qdf.Parameters!prmParameterName = Forms!MyformName!MycontrolName

Set rst = qdf.OpenRecordset()
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

I am trying to run a parameter query that accepts user input. The
query runs in the Query Grid but will not run in VBA as in line SQL.
The SQL from the Query Grid is:

SELECT tblTestTable.ID, tblTestTable.FirstName, tblTestTable.LastName,
tblTestTable.Age
FROM tblTestTable
WHERE (((tblTestTable.FirstName)=[Enter First Name]));

When I copy this into the VBA Module I get an error "Too Few
Parameters. Expected 1"

I then tried the following (From allen Brown)

strSql = "SELECT tblTestTable.ID, tblTestTable.FirstName,
tblTestTable.LastName, tblTestTable.Age " & vbCrLf & _
"FROM tblTestTable " & vbCrLf & _
"WHERE (((tblTestTable.FirstName)=[Enter First Name]));"

But I still get the same error ie "Too Few Parameters. Expected 1"

I am using MS Access 2003 and VB 5.5

It's driving me mad and any help would be greatly appreciated.- Hide quoted text -

- Show quoted text -

Thanks very much for the rapid reply.
Regards
Ian

 

[nuages]

Thanks very much for the suggestion. This is the module I am trying to
run


Public Sub modTestModule()
On Error GoTo Err_modTestModule

Dim rs As DAO.Recordset
Dim sSql As String
Dim sParam As String

sParam = InputBox("Enter First Name")
sSql = "SELECT tblTestTable.ID, tblTestTable.FirstName,&
tblTestTable.LastName, tblTestTable.Age" & _
" FROM tblTestTable" & _
" WHERE tblTestTable.FirstName = '" & sParam & "';"

Set rs = DBEngine(0)(0).OpenRecordset(sSql)

Do While Not rs.EOF
Debug.Print rs!FirstName;
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

Exit_modTestModule:
Exit Sub

Err_modTestModule:
MsgBox Err.Description:
Resume Next
End Sub

When I run this I get a syntax error. When I get rid of the syntax
error the code runs but the SParam value is ignored. Any suggestions
please?

Regards
Ian

 

[nuages]

Thanks very much for the suggestion. This is the module I am trying to
run

Public Sub modTestModule()
    On Error GoTo Err_modTestModule

    Dim rs As DAO.Recordset
    Dim sSql As String
    Dim sParam As String

    sParam = InputBox("Enter First Name")
    sSql = "SELECT tblTestTable.ID, tblTestTable.FirstName,&
tblTestTable.LastName, tblTestTable.Age" & _
            " FROM tblTestTable" & _
            " WHERE tblTestTable.FirstName = '" & sParam & "';"

    Set rs = DBEngine(0)(0).OpenRecordset(sSql)

    Do While Not rs.EOF
        Debug.Print rs!FirstName;
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing

Exit_modTestModule:
    Exit Sub

Err_modTestModule:
    MsgBox Err.Description:
    Resume Next
End Sub

When I run this I get a syntax error. When I get rid of the syntax
error the code runs but the SParam value is ignored. Any suggestions
please?

Regards
Ian

I solved the problem. Thanks so much for your help.

Regards
Ian