CurrentDb.OpenRecordset & "Too Few Parameters expected 1".

  • Thread starter Thread starter RSteph
  • Start date Start date
R

RSteph

I've been scouring the web looking for solutions, and none I've found seem to
work for me. I've got a big query I'm running to pull some information. I'd
like to use this query to create a recordset to pull some information out.
The problem is I keep getting this error.

The query has one clause in the where part of the query, looking for
"CaseID". My original statement was just:

Set rs = CurrentDb.OpenRecordset("CaseLookup", dbOpenDynaset)

That didn't work, so I tried turning it into a search query, and pulling the
where clause out:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM CaseLookup WHERE
([CaseLookup].CaseID=" & CaseID & ")", dbOpenDynaset)

But that doesn't seem to work either. Does anyone have any suggestions of
what I might be doing wrong here? Any help would be greatly appreciated.

Thank you.
 
RSteph said:
I've been scouring the web looking for solutions, and none I've found seem
to
work for me. I've got a big query I'm running to pull some information.
I'd
like to use this query to create a recordset to pull some information out.
The problem is I keep getting this error.

The query has one clause in the where part of the query, looking for
"CaseID". My original statement was just:

Set rs = CurrentDb.OpenRecordset("CaseLookup", dbOpenDynaset)

That didn't work, so I tried turning it into a search query, and pulling
the
where clause out:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM CaseLookup WHERE
([CaseLookup].CaseID=" & CaseID & ")", dbOpenDynaset)

But that doesn't seem to work either. Does anyone have any suggestions of
what I might be doing wrong here? Any help would be greatly appreciated.
Click to expand...


Is CaseID a parameter to the query? It would help if you post the SQL of
CaseLookup. However, you can probably get away with doing something like
this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("CaseLookup")
qdf.Parameters(0).Value = Me.CaseID
Set rs = qdf.OpenRecordset(dbOpenDynaset)
 
RSteph said:
I've been scouring the web looking for solutions, and none I've found seem
to
work for me. I've got a big query I'm running to pull some information.
I'd
like to use this query to create a recordset to pull some information out.
The problem is I keep getting this error.

The query has one clause in the where part of the query, looking for
"CaseID". My original statement was just:

Set rs = CurrentDb.OpenRecordset("CaseLookup", dbOpenDynaset)

That didn't work, so I tried turning it into a search query, and pulling
the
where clause out:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM CaseLookup WHERE
([CaseLookup].CaseID=" & CaseID & ")", dbOpenDynaset)

But that doesn't seem to work either. Does anyone have any suggestions of
what I might be doing wrong here? Any help would be greatly appreciated.
Click to expand...


Is CaseID a parameter to the query? It would help if you post the SQL of
CaseLookup. However, you can probably get away with doing something like
this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("CaseLookup")
qdf.Parameters(0).Value = Me.CaseID
Set rs = qdf.OpenRecordset(dbOpenDynaset)
 
The SQL For CaseLookup is:

SELECT tblCase.CaseID, tblCaseIndiv.FirstName, tblCaseIndiv.LastName,
tblCase.CaseType, tblCase.Location, tblCase.CaseDate, tblCase.Notes,
tblCaseIndiv.EmailAddress
FROM tblCase LEFT JOIN tblCaseIndiv ON tblCase.SelectedIndiv =
tblCaseIndiv.IndivID
WHERE CaseID= [Forms]![CaseInfo]![Case ID #]

Dirk Goldgar said:
RSteph said:
I've been scouring the web looking for solutions, and none I've found seem
to
work for me. I've got a big query I'm running to pull some information.
I'd
like to use this query to create a recordset to pull some information out.
The problem is I keep getting this error.

The query has one clause in the where part of the query, looking for
"CaseID". My original statement was just:

Set rs = CurrentDb.OpenRecordset("CaseLookup", dbOpenDynaset)

That didn't work, so I tried turning it into a search query, and pulling
the
where clause out:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM CaseLookup WHERE
([CaseLookup].CaseID=" & CaseID & ")", dbOpenDynaset)

But that doesn't seem to work either. Does anyone have any suggestions of
what I might be doing wrong here? Any help would be greatly appreciated.
Click to expand...


Is CaseID a parameter to the query? It would help if you post the SQL of
CaseLookup. However, you can probably get away with doing something like
this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("CaseLookup")
qdf.Parameters(0).Value = Me.CaseID
Set rs = qdf.OpenRecordset(dbOpenDynaset)



--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
The SQL For CaseLookup is:

SELECT tblCase.CaseID, tblCaseIndiv.FirstName, tblCaseIndiv.LastName,
tblCase.CaseType, tblCase.Location, tblCase.CaseDate, tblCase.Notes,
tblCaseIndiv.EmailAddress
FROM tblCase LEFT JOIN tblCaseIndiv ON tblCase.SelectedIndiv =
tblCaseIndiv.IndivID
WHERE CaseID= [Forms]![CaseInfo]![Case ID #]

Dirk Goldgar said:
RSteph said:
I've been scouring the web looking for solutions, and none I've found seem
to
work for me. I've got a big query I'm running to pull some information.
I'd
like to use this query to create a recordset to pull some information out.
The problem is I keep getting this error.

The query has one clause in the where part of the query, looking for
"CaseID". My original statement was just:

Set rs = CurrentDb.OpenRecordset("CaseLookup", dbOpenDynaset)

That didn't work, so I tried turning it into a search query, and pulling
the
where clause out:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM CaseLookup WHERE
([CaseLookup].CaseID=" & CaseID & ")", dbOpenDynaset)

But that doesn't seem to work either. Does anyone have any suggestions of
what I might be doing wrong here? Any help would be greatly appreciated.
Click to expand...


Is CaseID a parameter to the query? It would help if you post the SQL of
CaseLookup. However, you can probably get away with doing something like
this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("CaseLookup")
qdf.Parameters(0).Value = Me.CaseID
Set rs = qdf.OpenRecordset(dbOpenDynaset)



--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
RSteph said:
The SQL For CaseLookup is:

SELECT tblCase.CaseID, tblCaseIndiv.FirstName, tblCaseIndiv.LastName,
tblCase.CaseType, tblCase.Location, tblCase.CaseDate, tblCase.Notes,
tblCaseIndiv.EmailAddress
FROM tblCase LEFT JOIN tblCaseIndiv ON tblCase.SelectedIndiv =
tblCaseIndiv.IndivID
WHERE CaseID= [Forms]![CaseInfo]![Case ID #]
Click to expand...

If the form "CaseInfo" is open, then you can get Access to resolve the
parameter for you, like this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("CaseLookup")
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)

If the form is not open, and you just want to supply the CaseID yourself
from a variable or a control on the current form, you can use the approach I
posted in my first reply, adapting it as needed.
 
RSteph said:
The SQL For CaseLookup is:

SELECT tblCase.CaseID, tblCaseIndiv.FirstName, tblCaseIndiv.LastName,
tblCase.CaseType, tblCase.Location, tblCase.CaseDate, tblCase.Notes,
tblCaseIndiv.EmailAddress
FROM tblCase LEFT JOIN tblCaseIndiv ON tblCase.SelectedIndiv =
tblCaseIndiv.IndivID
WHERE CaseID= [Forms]![CaseInfo]![Case ID #]
Click to expand...

If the form "CaseInfo" is open, then you can get Access to resolve the
parameter for you, like this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("CaseLookup")
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)

If the form is not open, and you just want to supply the CaseID yourself
from a variable or a control on the current form, you can use the approach I
posted in my first reply, adapting it as needed.
 
Back
Top