MS Access Parameter Queris


N

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.
 
Ad

Advertisements

A

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()
 
C

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
 
N

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
 
N

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
 
Ad

Advertisements

N

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top