OpenRecordset fails Error 3061 too few params expected 12

B

BlueWolverine

Hello,
MS Access 2003 on XP Pro

I am trying to write some error message for my user. Basically, I have a
search panel for the user to use, and if the search yields zero records,
rather than the form coming up with no results, I'd rather a mesage box pop
up and say "No Records".

So the way I thought I'd do this is if the query that feeds than form has an
empty recordset, I would pop a message box. Well, here's the code I used for
this and then here's the message. (Error Message pops on code surrounded
like <<<THIS>>>.)

Dim myDB As Database, rs As Recordset, tqn As String
Set myDB = CurrentDb
tqn = "q_search_exact"
<<<Set rs = myDB.OpenRecordset(tqn)>>>

If rs.EOF Or rs.BOF Then
DoCmd.Beep
MsgBox "Returned zero (0) results.", vbOKOnly, "Search Complete"
Else
DoCmd.OpenForm "f_search_exact", acNormal
End If

Here's the error message.

Error 3061 too few parameters. Expected 12.

The query is stored in Access. (Written in SQL but stored, so not generated
in VBA, but written as a query.) Here is the SQL for the query.

SELECT q_FA_DOW.DayOfWeek, q_FA_DOW.Fleet_Activity, q_FA_DOW.BinstoOther,
q_FA_DOW.[CCC's], q_FA_DOW.VFG, q_FA_DOW.VRT, q_FA_DOW.[GQRS CAT],
q_FA_DOW.ACTION, q_FA_DOW.[Work Element Description], q_FA_DOW.Frequency,
q_FA_DOW.[WES#], q_FA_DOW.Source, q_FA_DOW.ID, q_FA_DOW.Active
FROM q_FA_DOW
WHERE (((q_FA_DOW.Fleet_Activity)=Forms!f_SearchPanel!FASearch Or
isnull(Forms!f_SearchPanel!FASearch)) And
((q_FA_DOW.VRT)=Forms!f_SearchPanel!VRTSearch Or
isnull(Forms!f_SearchPanel!VRTSearch)) And
((q_FA_DOW.VFG)=Forms!f_SearchPanel!VFGSearch Or
isnull(Forms!f_SearchPanel!VFGSearch)) And
((q_FA_DOW.[CCC's])=Forms!f_SearchPanel!CCCSearch Or
isnull(Forms!f_SearchPanel!CCCSearch)) And
((q_FA_DOW.Action)=Forms!f_SearchPanel!ActionSearch Or
isnull(Forms!f_SearchPanel!ActionSearch)) And ((q_FA_DOW.[GQRS
CAT])=Forms!f_SearchPanel!GQRS Or isnull(Forms!f_SearchPanel!GQRS)) And
((q_FA_DOW.[Work Element Description]) Like "*" & Forms!f_SearchPanel!WED1 &
"*" Or isnull(Forms!f_SearchPanel!WED1)) And ((q_FA_DOW.[Work Element
Description]) Like "*" & Forms!f_SearchPanel!WED2 & "*" Or
isnull(Forms!f_SearchPanel!WED2)) And ((q_FA_DOW.[Work Element Description])
Like "*" & Forms!f_SearchPanel!WED3 & "*" Or
isnull(Forms!f_SearchPanel!WED3)) And ((q_FA_DOW.[Work Element Description])
Like "*" & Forms!f_SearchPanel!WED4 & "*" Or
isnull(Forms!f_SearchPanel!WED4)) And ((q_FA_DOW.[Work Element Description])
Like "*" & Forms!f_SearchPanel!WED5 & "*" Or
isnull(Forms!f_SearchPanel!WED5)) And
q_FA_DOW.Active=Forms!f_SearchPanel!SearchActive)
ORDER BY q_FA_DOW.ID;


Note: everything works perfectly, exactly as I want, except for the open
recordset error.

Ideas?

Thank you.
 
A

Allen Browne

When you run a saved query that contains something such as:
Forms!f_SearchPanel!WED4
JET calls the Expression Service (ES) to evaluate the parameter. The ES
looks to see if the form is open and has such as text box. If so, it reads
the value from there; if not, it pops up a dialog asking you for the value
to use.

When you OpenRecordset(), the ES is not available, and it doesn't pop up a
dialog. It expects you to supply the parameter before you open the
recordset. That's one reason why many of us find it easier to create the SQL
statement in VBA. You can concatenate the value from the form into the
string, e.g.:
strSql = "SELECT ... WHERE ((q_FA_DOW.Fleet_Activity = " & _
Forms!f_SearchPanel!FASearch " & " Or ...
You need to add delimiters to the string (quotes for text fields, # for date
fields), but that's the process.

If you want to use a saved query, you must supply the parameters to the
QueryDef before you OpenRecordset. This kind of thing:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set qdf = db.QueryDefs("q_search_exact")
qdf.Parameters("Forms!f_SearchPanel!FASearch") =
Forms!f_SearchPanel!FASearch
qdf.Parameters("Forms!f_SearchPanel!VRTSearch")=
Forms!f_SearchPanel!VRTSearch
...
set rs = qdf.OpenRecordset

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

BlueWolverine said:
Hello,
MS Access 2003 on XP Pro

I am trying to write some error message for my user. Basically, I have a
search panel for the user to use, and if the search yields zero records,
rather than the form coming up with no results, I'd rather a mesage box
pop
up and say "No Records".

So the way I thought I'd do this is if the query that feeds than form has
an
empty recordset, I would pop a message box. Well, here's the code I used
for
this and then here's the message. (Error Message pops on code surrounded
like <<<THIS>>>.)

Dim myDB As Database, rs As Recordset, tqn As String
Set myDB = CurrentDb
tqn = "q_search_exact"
<<<Set rs = myDB.OpenRecordset(tqn)>>>

If rs.EOF Or rs.BOF Then
DoCmd.Beep
MsgBox "Returned zero (0) results.", vbOKOnly, "Search Complete"
Else
DoCmd.OpenForm "f_search_exact", acNormal
End If

Here's the error message.

Error 3061 too few parameters. Expected 12.

The query is stored in Access. (Written in SQL but stored, so not
generated
in VBA, but written as a query.) Here is the SQL for the query.

SELECT q_FA_DOW.DayOfWeek, q_FA_DOW.Fleet_Activity, q_FA_DOW.BinstoOther,
q_FA_DOW.[CCC's], q_FA_DOW.VFG, q_FA_DOW.VRT, q_FA_DOW.[GQRS CAT],
q_FA_DOW.ACTION, q_FA_DOW.[Work Element Description], q_FA_DOW.Frequency,
q_FA_DOW.[WES#], q_FA_DOW.Source, q_FA_DOW.ID, q_FA_DOW.Active
FROM q_FA_DOW
WHERE (((q_FA_DOW.Fleet_Activity)=Forms!f_SearchPanel!FASearch Or
isnull(Forms!f_SearchPanel!FASearch)) And
((q_FA_DOW.VRT)=Forms!f_SearchPanel!VRTSearch Or
isnull(Forms!f_SearchPanel!VRTSearch)) And
((q_FA_DOW.VFG)=Forms!f_SearchPanel!VFGSearch Or
isnull(Forms!f_SearchPanel!VFGSearch)) And
((q_FA_DOW.[CCC's])=Forms!f_SearchPanel!CCCSearch Or
isnull(Forms!f_SearchPanel!CCCSearch)) And
((q_FA_DOW.Action)=Forms!f_SearchPanel!ActionSearch Or
isnull(Forms!f_SearchPanel!ActionSearch)) And ((q_FA_DOW.[GQRS
CAT])=Forms!f_SearchPanel!GQRS Or isnull(Forms!f_SearchPanel!GQRS)) And
((q_FA_DOW.[Work Element Description]) Like "*" & Forms!f_SearchPanel!WED1
&
"*" Or isnull(Forms!f_SearchPanel!WED1)) And ((q_FA_DOW.[Work Element
Description]) Like "*" & Forms!f_SearchPanel!WED2 & "*" Or
isnull(Forms!f_SearchPanel!WED2)) And ((q_FA_DOW.[Work Element
Description])
Like "*" & Forms!f_SearchPanel!WED3 & "*" Or
isnull(Forms!f_SearchPanel!WED3)) And ((q_FA_DOW.[Work Element
Description])
Like "*" & Forms!f_SearchPanel!WED4 & "*" Or
isnull(Forms!f_SearchPanel!WED4)) And ((q_FA_DOW.[Work Element
Description])
Like "*" & Forms!f_SearchPanel!WED5 & "*" Or
isnull(Forms!f_SearchPanel!WED5)) And
q_FA_DOW.Active=Forms!f_SearchPanel!SearchActive)
ORDER BY q_FA_DOW.ID;


Note: everything works perfectly, exactly as I want, except for the open
recordset error.

Ideas?

Thank you.
Click to expand...
 
D

Douglas J. Steele

Your references to controls on the form are parameters. To use the
OpenRecordset method, you must resolve them.

Try:

Dim myDB As Database, qdf As QueryDef
Dim rs As DAO.Recordset, tqn As String
Dim parm As DAO.Parameter

Set myDB = CurrentDb
tqn = "q_search_exact"
Set qdf = myDB.QueryDefs(tqn)

For Each parm in qdf.Parameters
parm.Value = Eval(parm.Name)
Next parm

Set rs = qdf.OpenRecordset(tqn)

If rs.EOF Or rs.BOF Then
DoCmd.Beep
MsgBox "Returned zero (0) results.", vbOKOnly, "Search Complete"
Else
DoCmd.OpenForm "f_search_exact", acNormal
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BlueWolverine said:
Hello,
MS Access 2003 on XP Pro

I am trying to write some error message for my user. Basically, I have a
search panel for the user to use, and if the search yields zero records,
rather than the form coming up with no results, I'd rather a mesage box
pop
up and say "No Records".

So the way I thought I'd do this is if the query that feeds than form has
an
empty recordset, I would pop a message box. Well, here's the code I used
for
this and then here's the message. (Error Message pops on code surrounded
like <<<THIS>>>.)

Dim myDB As Database, rs As Recordset, tqn As String
Set myDB = CurrentDb
tqn = "q_search_exact"
<<<Set rs = myDB.OpenRecordset(tqn)>>>

If rs.EOF Or rs.BOF Then
DoCmd.Beep
MsgBox "Returned zero (0) results.", vbOKOnly, "Search Complete"
Else
DoCmd.OpenForm "f_search_exact", acNormal
End If

Here's the error message.

Error 3061 too few parameters. Expected 12.

The query is stored in Access. (Written in SQL but stored, so not
generated
in VBA, but written as a query.) Here is the SQL for the query.

SELECT q_FA_DOW.DayOfWeek, q_FA_DOW.Fleet_Activity, q_FA_DOW.BinstoOther,
q_FA_DOW.[CCC's], q_FA_DOW.VFG, q_FA_DOW.VRT, q_FA_DOW.[GQRS CAT],
q_FA_DOW.ACTION, q_FA_DOW.[Work Element Description], q_FA_DOW.Frequency,
q_FA_DOW.[WES#], q_FA_DOW.Source, q_FA_DOW.ID, q_FA_DOW.Active
FROM q_FA_DOW
WHERE (((q_FA_DOW.Fleet_Activity)=Forms!f_SearchPanel!FASearch Or
isnull(Forms!f_SearchPanel!FASearch)) And
((q_FA_DOW.VRT)=Forms!f_SearchPanel!VRTSearch Or
isnull(Forms!f_SearchPanel!VRTSearch)) And
((q_FA_DOW.VFG)=Forms!f_SearchPanel!VFGSearch Or
isnull(Forms!f_SearchPanel!VFGSearch)) And
((q_FA_DOW.[CCC's])=Forms!f_SearchPanel!CCCSearch Or
isnull(Forms!f_SearchPanel!CCCSearch)) And
((q_FA_DOW.Action)=Forms!f_SearchPanel!ActionSearch Or
isnull(Forms!f_SearchPanel!ActionSearch)) And ((q_FA_DOW.[GQRS
CAT])=Forms!f_SearchPanel!GQRS Or isnull(Forms!f_SearchPanel!GQRS)) And
((q_FA_DOW.[Work Element Description]) Like "*" & Forms!f_SearchPanel!WED1
&
"*" Or isnull(Forms!f_SearchPanel!WED1)) And ((q_FA_DOW.[Work Element
Description]) Like "*" & Forms!f_SearchPanel!WED2 & "*" Or
isnull(Forms!f_SearchPanel!WED2)) And ((q_FA_DOW.[Work Element
Description])
Like "*" & Forms!f_SearchPanel!WED3 & "*" Or
isnull(Forms!f_SearchPanel!WED3)) And ((q_FA_DOW.[Work Element
Description])
Like "*" & Forms!f_SearchPanel!WED4 & "*" Or
isnull(Forms!f_SearchPanel!WED4)) And ((q_FA_DOW.[Work Element
Description])
Like "*" & Forms!f_SearchPanel!WED5 & "*" Or
isnull(Forms!f_SearchPanel!WED5)) And
q_FA_DOW.Active=Forms!f_SearchPanel!SearchActive)
ORDER BY q_FA_DOW.ID;


Note: everything works perfectly, exactly as I want, except for the open
recordset error.

Ideas?

Thank you.
Click to expand...
 
B

BlueWolverine

I like where this is going, and I am trying to use this method but I get an
error on the following line.

Set rs = qdf.OpenRecordset(tqn)

"Error 3421 Data type conversion error"

If you drop the (tqn) it works though. I felt I should post this to help
others.

Set rs = qdf.OpenRecordset
WORKS PERFECTLY.

Thank you
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


Douglas J. Steele said:
Your references to controls on the form are parameters. To use the
OpenRecordset method, you must resolve them.

Try:

Dim myDB As Database, qdf As QueryDef
Dim rs As DAO.Recordset, tqn As String
Dim parm As DAO.Parameter

Set myDB = CurrentDb
tqn = "q_search_exact"
Set qdf = myDB.QueryDefs(tqn)

For Each parm in qdf.Parameters
parm.Value = Eval(parm.Name)
Next parm

Set rs = qdf.OpenRecordset(tqn)

If rs.EOF Or rs.BOF Then
DoCmd.Beep
MsgBox "Returned zero (0) results.", vbOKOnly, "Search Complete"
Else
DoCmd.OpenForm "f_search_exact", acNormal
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BlueWolverine said:
Hello,
MS Access 2003 on XP Pro

I am trying to write some error message for my user. Basically, I have a
search panel for the user to use, and if the search yields zero records,
rather than the form coming up with no results, I'd rather a mesage box
pop
up and say "No Records".

So the way I thought I'd do this is if the query that feeds than form has
an
empty recordset, I would pop a message box. Well, here's the code I used
for
this and then here's the message. (Error Message pops on code surrounded
like <<<THIS>>>.)

Dim myDB As Database, rs As Recordset, tqn As String
Set myDB = CurrentDb
tqn = "q_search_exact"
<<<Set rs = myDB.OpenRecordset(tqn)>>>

If rs.EOF Or rs.BOF Then
DoCmd.Beep
MsgBox "Returned zero (0) results.", vbOKOnly, "Search Complete"
Else
DoCmd.OpenForm "f_search_exact", acNormal
End If

Here's the error message.

Error 3061 too few parameters. Expected 12.

The query is stored in Access. (Written in SQL but stored, so not
generated
in VBA, but written as a query.) Here is the SQL for the query.

SELECT q_FA_DOW.DayOfWeek, q_FA_DOW.Fleet_Activity, q_FA_DOW.BinstoOther,
q_FA_DOW.[CCC's], q_FA_DOW.VFG, q_FA_DOW.VRT, q_FA_DOW.[GQRS CAT],
q_FA_DOW.ACTION, q_FA_DOW.[Work Element Description], q_FA_DOW.Frequency,
q_FA_DOW.[WES#], q_FA_DOW.Source, q_FA_DOW.ID, q_FA_DOW.Active
FROM q_FA_DOW
WHERE (((q_FA_DOW.Fleet_Activity)=Forms!f_SearchPanel!FASearch Or
isnull(Forms!f_SearchPanel!FASearch)) And
((q_FA_DOW.VRT)=Forms!f_SearchPanel!VRTSearch Or
isnull(Forms!f_SearchPanel!VRTSearch)) And
((q_FA_DOW.VFG)=Forms!f_SearchPanel!VFGSearch Or
isnull(Forms!f_SearchPanel!VFGSearch)) And
((q_FA_DOW.[CCC's])=Forms!f_SearchPanel!CCCSearch Or
isnull(Forms!f_SearchPanel!CCCSearch)) And
((q_FA_DOW.Action)=Forms!f_SearchPanel!ActionSearch Or
isnull(Forms!f_SearchPanel!ActionSearch)) And ((q_FA_DOW.[GQRS
CAT])=Forms!f_SearchPanel!GQRS Or isnull(Forms!f_SearchPanel!GQRS)) And
((q_FA_DOW.[Work Element Description]) Like "*" & Forms!f_SearchPanel!WED1
&
"*" Or isnull(Forms!f_SearchPanel!WED1)) And ((q_FA_DOW.[Work Element
Description]) Like "*" & Forms!f_SearchPanel!WED2 & "*" Or
isnull(Forms!f_SearchPanel!WED2)) And ((q_FA_DOW.[Work Element
Description])
Like "*" & Forms!f_SearchPanel!WED3 & "*" Or
isnull(Forms!f_SearchPanel!WED3)) And ((q_FA_DOW.[Work Element
Description])
Like "*" & Forms!f_SearchPanel!WED4 & "*" Or
isnull(Forms!f_SearchPanel!WED4)) And ((q_FA_DOW.[Work Element
Description])
Like "*" & Forms!f_SearchPanel!WED5 & "*" Or
isnull(Forms!f_SearchPanel!WED5)) And
q_FA_DOW.Active=Forms!f_SearchPanel!SearchActive)
ORDER BY q_FA_DOW.ID;


Note: everything works perfectly, exactly as I want, except for the open
recordset error.

Ideas?

Thank you.
Click to expand...
Click to expand...
 
P

Paolo

Hi BlueWolverine,
I see another thing in your code that's not correct. The if that you use to
determine if the recordset is void doesn't work because you check If rs.EOF
Or rs.BOF Then
But rs.BOF is always true if you just opened the recordset. The correct if is

If rs.EOF AND rs.BOF Then
'cause to be an empty recordset both conditions must be true

Cheers Paolo

BlueWolverine said:
Hello,
MS Access 2003 on XP Pro

I am trying to write some error message for my user. Basically, I have a
search panel for the user to use, and if the search yields zero records,
rather than the form coming up with no results, I'd rather a mesage box pop
up and say "No Records".

So the way I thought I'd do this is if the query that feeds than form has an
empty recordset, I would pop a message box. Well, here's the code I used for
this and then here's the message. (Error Message pops on code surrounded
like <<<THIS>>>.)

Dim myDB As Database, rs As Recordset, tqn As String
Set myDB = CurrentDb
tqn = "q_search_exact"
<<<Set rs = myDB.OpenRecordset(tqn)>>>

If rs.EOF Or rs.BOF Then
DoCmd.Beep
MsgBox "Returned zero (0) results.", vbOKOnly, "Search Complete"
Else
DoCmd.OpenForm "f_search_exact", acNormal
End If

Here's the error message.

Error 3061 too few parameters. Expected 12.

The query is stored in Access. (Written in SQL but stored, so not generated
in VBA, but written as a query.) Here is the SQL for the query.

SELECT q_FA_DOW.DayOfWeek, q_FA_DOW.Fleet_Activity, q_FA_DOW.BinstoOther,
q_FA_DOW.[CCC's], q_FA_DOW.VFG, q_FA_DOW.VRT, q_FA_DOW.[GQRS CAT],
q_FA_DOW.ACTION, q_FA_DOW.[Work Element Description], q_FA_DOW.Frequency,
q_FA_DOW.[WES#], q_FA_DOW.Source, q_FA_DOW.ID, q_FA_DOW.Active
FROM q_FA_DOW
WHERE (((q_FA_DOW.Fleet_Activity)=Forms!f_SearchPanel!FASearch Or
isnull(Forms!f_SearchPanel!FASearch)) And
((q_FA_DOW.VRT)=Forms!f_SearchPanel!VRTSearch Or
isnull(Forms!f_SearchPanel!VRTSearch)) And
((q_FA_DOW.VFG)=Forms!f_SearchPanel!VFGSearch Or
isnull(Forms!f_SearchPanel!VFGSearch)) And
((q_FA_DOW.[CCC's])=Forms!f_SearchPanel!CCCSearch Or
isnull(Forms!f_SearchPanel!CCCSearch)) And
((q_FA_DOW.Action)=Forms!f_SearchPanel!ActionSearch Or
isnull(Forms!f_SearchPanel!ActionSearch)) And ((q_FA_DOW.[GQRS
CAT])=Forms!f_SearchPanel!GQRS Or isnull(Forms!f_SearchPanel!GQRS)) And
((q_FA_DOW.[Work Element Description]) Like "*" & Forms!f_SearchPanel!WED1 &
"*" Or isnull(Forms!f_SearchPanel!WED1)) And ((q_FA_DOW.[Work Element
Description]) Like "*" & Forms!f_SearchPanel!WED2 & "*" Or
isnull(Forms!f_SearchPanel!WED2)) And ((q_FA_DOW.[Work Element Description])
Like "*" & Forms!f_SearchPanel!WED3 & "*" Or
isnull(Forms!f_SearchPanel!WED3)) And ((q_FA_DOW.[Work Element Description])
Like "*" & Forms!f_SearchPanel!WED4 & "*" Or
isnull(Forms!f_SearchPanel!WED4)) And ((q_FA_DOW.[Work Element Description])
Like "*" & Forms!f_SearchPanel!WED5 & "*" Or
isnull(Forms!f_SearchPanel!WED5)) And
q_FA_DOW.Active=Forms!f_SearchPanel!SearchActive)
ORDER BY q_FA_DOW.ID;


Note: everything works perfectly, exactly as I want, except for the open
recordset error.

Ideas?

Thank you.
Click to expand...
 
B

BlueWolverine

I'm sorry I don't find that to be the case. The code works now... the way I
wanted it to.

My experience has been empty recordsets more reliably pass bos than eos off
the bat.

I will keep sthis in mind during testing and if i discover arbitrary
Emptysets when they shouldn't I will investigate further.

Hopefully someone else can expound upon this.

--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


Paolo said:
Hi BlueWolverine,
I see another thing in your code that's not correct. The if that you use to
determine if the recordset is void doesn't work because you check If rs.EOF
Or rs.BOF Then
But rs.BOF is always true if you just opened the recordset. The correct if is

If rs.EOF AND rs.BOF Then
'cause to be an empty recordset both conditions must be true

Cheers Paolo

BlueWolverine said:
Hello,
MS Access 2003 on XP Pro

I am trying to write some error message for my user. Basically, I have a
search panel for the user to use, and if the search yields zero records,
rather than the form coming up with no results, I'd rather a mesage box pop
up and say "No Records".

So the way I thought I'd do this is if the query that feeds than form has an
empty recordset, I would pop a message box. Well, here's the code I used for
this and then here's the message. (Error Message pops on code surrounded
like <<<THIS>>>.)

Dim myDB As Database, rs As Recordset, tqn As String
Set myDB = CurrentDb
tqn = "q_search_exact"
<<<Set rs = myDB.OpenRecordset(tqn)>>>

If rs.EOF Or rs.BOF Then
DoCmd.Beep
MsgBox "Returned zero (0) results.", vbOKOnly, "Search Complete"
Else
DoCmd.OpenForm "f_search_exact", acNormal
End If

Here's the error message.

Error 3061 too few parameters. Expected 12.

The query is stored in Access. (Written in SQL but stored, so not generated
in VBA, but written as a query.) Here is the SQL for the query.

SELECT q_FA_DOW.DayOfWeek, q_FA_DOW.Fleet_Activity, q_FA_DOW.BinstoOther,
q_FA_DOW.[CCC's], q_FA_DOW.VFG, q_FA_DOW.VRT, q_FA_DOW.[GQRS CAT],
q_FA_DOW.ACTION, q_FA_DOW.[Work Element Description], q_FA_DOW.Frequency,
q_FA_DOW.[WES#], q_FA_DOW.Source, q_FA_DOW.ID, q_FA_DOW.Active
FROM q_FA_DOW
WHERE (((q_FA_DOW.Fleet_Activity)=Forms!f_SearchPanel!FASearch Or
isnull(Forms!f_SearchPanel!FASearch)) And
((q_FA_DOW.VRT)=Forms!f_SearchPanel!VRTSearch Or
isnull(Forms!f_SearchPanel!VRTSearch)) And
((q_FA_DOW.VFG)=Forms!f_SearchPanel!VFGSearch Or
isnull(Forms!f_SearchPanel!VFGSearch)) And
((q_FA_DOW.[CCC's])=Forms!f_SearchPanel!CCCSearch Or
isnull(Forms!f_SearchPanel!CCCSearch)) And
((q_FA_DOW.Action)=Forms!f_SearchPanel!ActionSearch Or
isnull(Forms!f_SearchPanel!ActionSearch)) And ((q_FA_DOW.[GQRS
CAT])=Forms!f_SearchPanel!GQRS Or isnull(Forms!f_SearchPanel!GQRS)) And
((q_FA_DOW.[Work Element Description]) Like "*" & Forms!f_SearchPanel!WED1 &
"*" Or isnull(Forms!f_SearchPanel!WED1)) And ((q_FA_DOW.[Work Element
Description]) Like "*" & Forms!f_SearchPanel!WED2 & "*" Or
isnull(Forms!f_SearchPanel!WED2)) And ((q_FA_DOW.[Work Element Description])
Like "*" & Forms!f_SearchPanel!WED3 & "*" Or
isnull(Forms!f_SearchPanel!WED3)) And ((q_FA_DOW.[Work Element Description])
Like "*" & Forms!f_SearchPanel!WED4 & "*" Or
isnull(Forms!f_SearchPanel!WED4)) And ((q_FA_DOW.[Work Element Description])
Like "*" & Forms!f_SearchPanel!WED5 & "*" Or
isnull(Forms!f_SearchPanel!WED5)) And
q_FA_DOW.Active=Forms!f_SearchPanel!SearchActive)
ORDER BY q_FA_DOW.ID;


Note: everything works perfectly, exactly as I want, except for the open
recordset error.

Ideas?

Thank you.
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry, my typo. The perils of copy-and-paste!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BlueWolverine said:
I like where this is going, and I am trying to use this method but I get an
error on the following line.

Set rs = qdf.OpenRecordset(tqn)

"Error 3421 Data type conversion error"

If you drop the (tqn) it works though. I felt I should post this to help
others.

Set rs = qdf.OpenRecordset
WORKS PERFECTLY.

Thank you
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


Douglas J. Steele said:
Your references to controls on the form are parameters. To use the
OpenRecordset method, you must resolve them.

Try:

Dim myDB As Database, qdf As QueryDef
Dim rs As DAO.Recordset, tqn As String
Dim parm As DAO.Parameter

Set myDB = CurrentDb
tqn = "q_search_exact"
Set qdf = myDB.QueryDefs(tqn)

For Each parm in qdf.Parameters
parm.Value = Eval(parm.Name)
Next parm

Set rs = qdf.OpenRecordset(tqn)

If rs.EOF Or rs.BOF Then
DoCmd.Beep
MsgBox "Returned zero (0) results.", vbOKOnly, "Search Complete"
Else
DoCmd.OpenForm "f_search_exact", acNormal
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Hello,
MS Access 2003 on XP Pro

I am trying to write some error message for my user. Basically, I have
a
search panel for the user to use, and if the search yields zero
records,
rather than the form coming up with no results, I'd rather a mesage box
pop
up and say "No Records".

So the way I thought I'd do this is if the query that feeds than form
has
an
empty recordset, I would pop a message box. Well, here's the code I
used
for
this and then here's the message. (Error Message pops on code
surrounded
like <<<THIS>>>.)

Dim myDB As Database, rs As Recordset, tqn As String
Set myDB = CurrentDb
tqn = "q_search_exact"
<<<Set rs = myDB.OpenRecordset(tqn)>>>

If rs.EOF Or rs.BOF Then
DoCmd.Beep
MsgBox "Returned zero (0) results.", vbOKOnly, "Search Complete"
Else
DoCmd.OpenForm "f_search_exact", acNormal
End If

Here's the error message.

Error 3061 too few parameters. Expected 12.

The query is stored in Access. (Written in SQL but stored, so not
generated
in VBA, but written as a query.) Here is the SQL for the query.

SELECT q_FA_DOW.DayOfWeek, q_FA_DOW.Fleet_Activity,
q_FA_DOW.BinstoOther,
q_FA_DOW.[CCC's], q_FA_DOW.VFG, q_FA_DOW.VRT, q_FA_DOW.[GQRS CAT],
q_FA_DOW.ACTION, q_FA_DOW.[Work Element Description],
q_FA_DOW.Frequency,
q_FA_DOW.[WES#], q_FA_DOW.Source, q_FA_DOW.ID, q_FA_DOW.Active
FROM q_FA_DOW
WHERE (((q_FA_DOW.Fleet_Activity)=Forms!f_SearchPanel!FASearch Or
isnull(Forms!f_SearchPanel!FASearch)) And
((q_FA_DOW.VRT)=Forms!f_SearchPanel!VRTSearch Or
isnull(Forms!f_SearchPanel!VRTSearch)) And
((q_FA_DOW.VFG)=Forms!f_SearchPanel!VFGSearch Or
isnull(Forms!f_SearchPanel!VFGSearch)) And
((q_FA_DOW.[CCC's])=Forms!f_SearchPanel!CCCSearch Or
isnull(Forms!f_SearchPanel!CCCSearch)) And
((q_FA_DOW.Action)=Forms!f_SearchPanel!ActionSearch Or
isnull(Forms!f_SearchPanel!ActionSearch)) And ((q_FA_DOW.[GQRS
CAT])=Forms!f_SearchPanel!GQRS Or isnull(Forms!f_SearchPanel!GQRS)) And
((q_FA_DOW.[Work Element Description]) Like "*" &
Forms!f_SearchPanel!WED1
&
"*" Or isnull(Forms!f_SearchPanel!WED1)) And ((q_FA_DOW.[Work Element
Description]) Like "*" & Forms!f_SearchPanel!WED2 & "*" Or
isnull(Forms!f_SearchPanel!WED2)) And ((q_FA_DOW.[Work Element
Description])
Like "*" & Forms!f_SearchPanel!WED3 & "*" Or
isnull(Forms!f_SearchPanel!WED3)) And ((q_FA_DOW.[Work Element
Description])
Like "*" & Forms!f_SearchPanel!WED4 & "*" Or
isnull(Forms!f_SearchPanel!WED4)) And ((q_FA_DOW.[Work Element
Description])
Like "*" & Forms!f_SearchPanel!WED5 & "*" Or
isnull(Forms!f_SearchPanel!WED5)) And
q_FA_DOW.Active=Forms!f_SearchPanel!SearchActive)
ORDER BY q_FA_DOW.ID;


Note: everything works perfectly, exactly as I want, except for the
open
recordset error.

Ideas?

Thank you.
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

QUERY - AND/OR for search panel 2

Top