Evaluate query results

[alex]

Hello experts,

Using Access ’03…

I have the following code attached to a form:

Private Sub cmd_Click()

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryKeyWord", acViewNormal
DoCmd.SetWarnings True

DoCmd.OpenForm "frmResponse", acNormal
DoCmd.Close acForm, Me.Name

End Sub

The aforementioned query is a parameter query and frmResponse opens
fine if the query returns a result. If the query’s recordset is null,
however, I receive a code error as the form opens and attempts to set
the focus and selstart.

I need to modify this code to evaluate the results of qryKeyWord.

If the results of the query yield no results:
msgbox “String not found!”
else
DoCmd.OpenForm "frmResponse", acNormal
DoCmd.Close acForm, Me.Name

Any help would be appreciated.

alex

 

[strive4peace]

Hi Alex,

'~~~~~~~~~~~~~~~~~~~~~
dim mNumRecords as long

With CurrentDb
.Execute "qryKeyWord"
mNumRecords = .RecordsAffected
End With

if mNumRecords = 0 then
msgbox "message",,"title"
end if
'~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Ken Sheridan]

Firstly a definition of 'expert':

'x' is an unknown quantity, and 'spurt' is a drip under pressure.

Bearing that in mind, unless qryKeyWord is an 'action query' modifying data
there should be no need to 'open' it at all. Is it in fact the RecordSource
of frmResponse? If so then put something along these lines in the Open event
procedure of frmResponse:

If DCount("*", " qryKeyWord ") = 0 Then
MsgBox "No records returned.", vbInformation, "Warning"
Cancel = True
End If

and open it from the dialogue form with:

Const conFORMCANCELLED = 2501

On Error Resume Next
DoCmd.OpenForm "frmResponse"
Select Case Err.Number
Case 0
' no error
Case conFORMCANCELLED
' anticipated error
Case Else
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End Select

DoCmd.Close acForm, Me.Name

One other thing I'd draw attention to, although with the revised code as
above it no longer applies in this case. If you are calling the SetWarnings
method always include error handling and set warnings back to True in the
error handler. Otherwise if an error occurs after you've turned warnings
off, but before they've been turned back on again, they'll remain turned off,
which would not be desirable. You can do something like this:

On Error Goto Err_Handler

DoCmd.SetWarnings False
< some code>

Exit_Here:
DoCmd.SetWarnings True
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

Ken Sheridan
Stafford, England

 

[alex]

Hi Alex,

'~~~~~~~~~~~~~~~~~~~~~
   dim mNumRecords  as long

   With CurrentDb
      .Execute "qryKeyWord"
       mNumRecords = .RecordsAffected
   End With

    if mNumRecords = 0 then
       msgbox "message",,"title"
    end if
'~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Accesshttp://www.AccessMVP..com/strive4peace

  *
    have an awesome day
  *















- Show quoted text -

Thanks Crystal for your help...When I run the code, I get run-time
error telling me that a table already exists (qryKeyWord is a make
table query). It appears that the query is not executing completely.

alex

 

[alex]

Firstly a definition of 'expert':

'x' is an unknown quantity, and 'spurt' is a drip under pressure.

Bearing that in mind, unless qryKeyWord is an 'action query' modifying data
there should be no need to 'open' it at all.  Is it in fact the RecordSource
of frmResponse?  If so then put something along these lines in the Openevent
procedure of frmResponse:

     If DCount("*", " qryKeyWord ") = 0 Then
        MsgBox "No records returned.", vbInformation, "Warning"
        Cancel = True
    End If

and open it from the dialogue form with:

    Const conFORMCANCELLED = 2501

    On Error Resume Next
    DoCmd.OpenForm "frmResponse"
    Select Case Err.Number
        Case 0
        ' no error
        Case conFORMCANCELLED
        ' anticipated error
        Case Else
        ' unknown error
        MsgBox Err.Description, vbExclamation, "Error"
    End Select

    DoCmd.Close acForm, Me.Name

One other thing I'd draw attention to, although with the revised code as
above it no longer applies in this case.  If you are calling the SetWarnings
method always include error handling and set warnings back to True in the
error handler.  Otherwise if an error occurs after you've turned warnings
off, but before they've been turned back on again, they'll remain turned off,
which would not be desirable.  You can do something like this:

    On Error Goto Err_Handler

    DoCmd.SetWarnings False
    < some code>

Exit_Here:
    DoCmd.SetWarnings True
    Exit Sub

Err_Handler:
    MsgBox Err.Description, vbExclamation, "Error"
    Resume Exit_Here

Ken Sheridan
Stafford, England
















- Show quoted text -

Thanks Ken for the response. I should have mentioned the query was an
action query!

alex

 

[strive4peace]

Hi Alex,

"table already exists"

do you have a form or something else open that uses the table? If so,
you will have to close it or change the RecordSource (or RowSource, ...)

Ken gave you a solution to count records before the query is executed,
perhaps that would wok better for you


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[alex]

Hi Alex,

"table already exists"

do you have a form or something else open that uses the table?  If so,
you will have to close it or change the RecordSource (or RowSource, ...)

Ken gave you a solution to count records before the query is executed,
perhaps that would wok better for you

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Accesshttp://www.AccessMVP..com/strive4peace

  *
    have an awesome day
  *






- Show quoted text -

Am I wrong in assuming that the query needs to execute and make the
table before any evaluating can take place.

 

[strive4peace]

Hi Alex,

you can make a another query that is a copy of your action query -- and
then change its type to SELECT -- you can count records in that one
according to what Ken told you

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[alex]

Hi Alex,

you can make a another query that is a copy of your action query -- and
then change its type to SELECT -- you can count records in that one
according to what Ken told you

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Accesshttp://www.AccessMVP..com/strive4peace

  *
    have an awesome day
  *





- Show quoted text -

Hi Crystal,

With a little modification, your code is running. The current issue,
however, is that it's evaluating to true. Even though the make table
query has records.

Private Sub cmdGet3_Click()
Dim mNumRecords As Long

With CurrentDb
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryKeyWord", acViewNormal
DoCmd.SetWarnings True

mNumRecords = .RecordsAffected
End With
If mNumRecords = 0 Then
MsgBox "string does not exist!"

Else

DoCmd.OpenForm "frmResponse", acNormal
DoCmd.Close acForm, Me.Name

End If

End Sub

 

[alex]

Hi Crystal,

With a little modification, your code is running.  The current issue,
however, is that it's evaluating to true.  Even though the make table
query has records.

Private Sub cmdGet3_Click()
Dim mNumRecords As Long

With CurrentDb
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryKeyWord", acViewNormal
DoCmd.SetWarnings True

mNumRecords = .RecordsAffected
End With
    If mNumRecords = 0 Then
        MsgBox "string does not exist!"

    Else

    DoCmd.OpenForm "frmResponse", acNormal
    DoCmd.Close acForm, Me.Name

    End If

End Sub- Hide quoted text -

- Show quoted text -

Crystal,

I took your last advice and it worked...hated to create another query
to mask my stupidity in code writing, but I did it anyway. Thanks.

alex

 

[strive4peace]

Hi Alex

I took your last advice and it worked... Thanks.

great! you are welcome

use CurrentDb. Excecute
INSTEAD of -->
DoCmd.OpenQuery

The nice thing is that you do not have to worry about SetWarnings or
Echo -- and it is faster


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*