How to handle no records returned by query 1

[votegop]

In a macro, a make-table-query runs, the resultant table is then transferred to another database. That database is used as the source for a Word mail-merge (all automated w/i the macro). If the query returned no records, Word of course gives an error saying the source was blank.

I need to find a way to terminate the macro if the result of the query is no records (I don't want users dealing with the Word errors). Its not as easy as having an "on no data" event to work with. --Any help is appreciated, I'm a real novice at sql and VBA.

 

[grtammi]

I have a form which gives me an alphabetical listing of my customers; the user selects 1 of 27 options (A-Z or ALL) to filter the list. If there are no records returned, I display a message box saying "No Records Returned", and the macro stops. Try adding the following 2 statements to your macro, in the order listed:

Condition: [RecordsetClone].RecordCount = 0
Action: MsgBox

Condition: ...
Action: StopMacro

Hope this helps ...

Greg

 

[votegop]

Greg:

Thanks for the tip. Do you use this in a macro as you described? It won't work for me. MS Help says RecordsetClone is only available in VB.

I can muddle through some VB with a little coaching. Is it possible to do this as a VB function and call it using the RunCode action?

Jay

 

[grtammi]

Jay:

Post your e-mail address here, or send me an e-mail at the following address: gtammi@atsbell.com, and I'll send you the macro that I use.

Greg

 

[votegop]

Greg:

I installed AC97 and the macro worked. It seems that "[RecordsetClone].RecordCount = 0" will work as a condition on a macro only if the macro is called from a form (in this case a button) with the record set already open.

Without the record set already open, I tried using it in a macro after Openquery where the query results would be the record set - no luck. I tried it after Openform where the form's source is a table - no luck (you'd think that would work).

Obviously if the macro is called from a button on a switchboard, the record set will not be open. Thanks Greg, I know your solution is the right one, I now just have to figure out how to execute it. Anyone have any ideas?

Jay

 

[votegop]

I've got it.

It may be a little obtuse, but I simply split the macro. The first macro opens a form (that I don't really need) based on the table from the make-table-query. The second macro runs from the "On Open" event of the form. It tests the form with "[RecordsetClone].RecordCount = 0" to make sure it has records. If not, a message box notifies the user, closes form, and stops macro. If yes, the macro closes form, and continues...

It seem strange that it must be called this way. If someone has another work-around, I'd still appreciate it.

 

[grtammi]

Jay:

Try something like this:

(paste this code into the OnClick event of the button on the switchboard)

Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(&quot;<table name here>&quot
If rst.Recordcount = 0 Then
Exit Sub
Else
'insert code you need here
End If

HTH

Greg

 

[votegop]

Very, very helpful Greg. It took me hours to do, since I don't really know VBA, but I did it. This little gem opens a make-table-query, exports the result to an unsecured .mdb, opens Word, and performs mailmerge. Of course, it terminates if no records (thanks to your help).

Private Sub byDatebutton_Click()
On Error GoTo Err_byDatebutton_Click
Dim dbs As Database
Dim rst As Recordset
Dim stqueryname As String
stqueryname = &quot;CEMkqryByDateUniversalLetter&quot;
DoCmd.SetWarnings False
MsgBox &quot;This will allow you to print letter(s) using MS Word.&quot;, vbInformation, &quot;&quot;
DoCmd.OpenQuery stqueryname
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(&quot;CEmktable&quot
If rst.RecordCount = 0 Then
MsgBox &quot;There are no records matching your request.&quot;, vbInformation, &quot;&quot;
Exit Sub
Else
MsgBox &quot;There ARE records matching your request...here you go...&quot;, vbInformation, &quot;&quot;
DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, &quot;C:\DeveloTrack\FormLetterData.mdb&quot;, acTable, &quot;CEMkTable&quot;, &quot;CEMkTable&quot;, False
Dim stAppName As String
stAppName = &quot;C:\Program Files\Microsoft Office\Office\WINWORD.EXE C:\Develotrack\Universalviolationletter.doc /MMerge&quot;
Call Shell(stAppName, 1)
End If
Exit_byDatebutton_Click:
Exit Sub
DoCmd.SetWarnings True
Err_byDatebutton_Click:
MsgBox Err.Description
Resume Exit_byDatebutton_Click:
End Sub

Sorry for the poor formatting, I told you I stink at VBA. I even had to tweak my DAO/ADO references...guess I gotta go learn what ramifications that will have upon roll-out!

Jay Evans
Orlando, FL