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.



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

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BlueWolverine" <(E-Mail Removed)> wrote in message
news:849FB679-7A6D-4FC8-A84F-(E-Mail Removed)...
> 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.
>
>
>
> --
> BlueWolverine
> MSE - Mech. Eng.
> Go BLUE!

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
http://I.Am/DougSteele
(no e-mails, please!)


"BlueWolverine" <(E-Mail Removed)> wrote in message
news:849FB679-7A6D-4FC8-A84F-(E-Mail Removed)...
> 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.
>
>
>
> --
> BlueWolverine
> MSE - Mech. Eng.
> Go BLUE!

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" wrote:

> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "BlueWolverine" <(E-Mail Removed)> wrote in message
> news:849FB679-7A6D-4FC8-A84F-(E-Mail Removed)...
> > 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.
> >
> >
> >
> > --
> > BlueWolverine
> > MSE - Mech. Eng.
> > Go BLUE!
>
>
>

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" wrote:

> 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.
>
>
>
> --
> BlueWolverine
> MSE - Mech. Eng.
> Go BLUE!

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" wrote:

> 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" wrote:
>
> > 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.
> >
> >
> >
> > --
> > BlueWolverine
> > MSE - Mech. Eng.
> > Go BLUE!

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"BlueWolverine" <(E-Mail Removed)> wrote in message
news:C7FF95E0-FC27-4B83-8D5B-(E-Mail Removed)...
>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" wrote:
>
>> 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
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "BlueWolverine" <(E-Mail Removed)> wrote in
>> message
>> news:849FB679-7A6D-4FC8-A84F-(E-Mail Removed)...
>> > 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.
>> >
>> >
>> >
>> > --
>> > BlueWolverine
>> > MSE - Mech. Eng.
>> > Go BLUE!
>>
>>
>>