Check Query Results

andy_dyer

Registered User.
Local time
Today, 23:32
Joined
Jul 2, 2003
Messages
806
Hi,

I am doing a mail merge into word using a query from access.

The user gets to select the criteria from a combo box and then clcik a button to open the word document based on the query.

If there are no results for that criteria I get all kinds of weird error messages...

Is there a simple way I can run the query see if there are any results open my own Error Form if none or go to the word document if there are??

Thanks

Andy
 
You can open the query results into a recordset using DAO or ADO and check the record count. Better yet, when the user selects criteria, do a record count check, show the user the number of records, and only enable the command button if there are records.
 
Ok...

How do I do any of that?

:confused: :confused:
 
What version of Access are you using? And do you know if you use DAO or ADO?
 
Access 2000 and no I don't know whether it is ADO or DAO! (or even what ADO or DAO are!)
 
That's OK, check out this posting for info on how to do some recordset operations with DAO or ADO. Can VBA code access results of a query? Look under the sections of code where it says: ' get a count of the records in our recordset to learn how to get a count of records in a recordset.

Chances are if you're using Access 2000 and it's a new db you made yourself on Access 2000, you're not working on a legacy db, and you haven't done much programming, you should use ADO.

Let me know if you get that far.
 
Ok.

It doesn't seem to like much of the code at present is there an extra reference I need to add in order to get it recognise some of the commands?
 
What code have you tried and where does it fail? Please post it.
 
Here is the code, it doesn't like "Set"...

Also how do I get it to open my word document using code if there are some records or open my error form if not?

Private Sub cmdViewBirthdayMonthList_Click()

' create connection and recordset objects
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

' set connection object to current database
Set cnn = CurrentProject.Connection
' set recordset object to your query (assumes query is qryExample)
Set rst = New ADODB.Recordset

' open the recordset
' note that in ado, the standard recordset is forward- and read-only
rst.Open "qryBirthdayList", cnn
' this would open the recordset as static with pessimistic locking:
'rst.Open "qryExample", cnn, adOpenStatic, adLockPessimistic

' things we can do with our recordset
With rst
' navigate our recordset
'.MoveNext
'.MovePrevious
'.MoveLast
'.MoveFirst

' add a record to our recordset
'.AddNew
'.Fields("MyField") = "NewValue"
'.Update

' edit a record in our recordset
' note that ADO does not require or provide a .Edit method
'.Fields("MyField") = "NewValue"
'.Update

' delete a record from our recordset
'.Delete

' get a count of the records in our recordset
' be careful in ado since the default type of recordset (forward-only)
' will always return -1 for the recordcount
Debug.Print .RecordCount

' close the recordset object (connection to the query)
.Close

End With

' clear the defined objects
Set rst = Nothing
Set cnn = Nothing

End Sub
 
' note that in ado, the standard recordset is forward- and read-only
rst.Open "qryBirthdayList", cnn
' this would open the recordset as static with pessimistic locking:
'rst.Open "qryExample", cnn, adOpenStatic, adLockPessimistic


You are using the same recordset object here. When you open "qryExample" rst replaces "qrybirthdayLIst"

Opening "qryBirthdayList" does nothing in this instance.
 
Which "set" statement fails? The very first one?

Also, in the section that reads:

' open the recordset

do NOT use the form of the rst.Open command that you are currently using. Comment it out and use the one two lines down. The version of the command you are using will not provide a recordcount (well, it will but it will always be -1).

Also how do I get it to open my word document using code if there are some records or open my error form if not?
You need to do something with this line:
Debug.Print .RecordCount
Right now, it's set to print a recordcount number to the Debug window (don't worry if you don't know what that is). You need to have something like:
Code:
If .RecordCount > 0 Then
     'Do This
Else
     'Do this instead
End If
 
Mile, the user actually has the second rst.Open command commented out.
 
lol, they should make apostrophes bigger so I can see them :(
 
Trying to make head or tail of those posts... :confused:

Yes it is the first "Set" that it is failing on.

I've tried to comment out the stuff that I didn't think I would need, but wasn't sure enough to actually delete them!

Am i right in thinking that I will only need one recordset open and then only the instructions to count the recordset?

:confused:
 
P.S. what is the code required to actually open a word document?

Have trawled through every possibility on the docmd. and nothing looks right...

Was looking for a RunApp or something...
 
Regarding the "set" failure. Let's check your references in the VBA editor window. Go to the Tools menu and choose References. You should have "Microsoft ActiveX Data Objects 2.x Library" checked. If you already do, then write back here with a list of all the references that you do have checked. Must be some other problem.

Yes, basically all you need is the code to open and count the recordset - but don't delete anything if you don't feel comfortable doing it.

Regarding opening a Word document, check out this link: Microsoft Office 2000 Automation Help File Available . In it are instructions on sending contents of a record to Word, but you don't need to do all of that. Just take the code that shows you how to open the Word document.
 
Thanks for your help so far, here are the references I already have:

visual basic for applications

microsoft access 9.0 object library

ole automation

Microsoft ActiveX Data Objects 2.1 Library

microsoft visual basic for applications extensibility 5.3

microsoft word 9.0 object library
 
I have also tried to get the code to work to open my word document...

If .RecordCount > 0 Then
'Do This
Dim objWord As Word.Application
Start Microsoft Word.
Set objWord = CreateObject("Word.Application")
With objWord
' Make the application visible.
.Visible = True
' Open the document.
.Documents.Open ("C:\Address Book\Address Book - Birthday Month List.doc")

But it doesn't like the Start Microsoft Word. line

Throws up a compile error?

I thought thta by having microsoft word 9.0 object library in my database this should be ok?
 
The
Start Microsoft Word.
line should have an apostrophe character in front of it to comment it out.

And yes, you need the Microsoft Word Object Library in there if you're going to be referring to any Word objects.
 

Users who are viewing this thread

Back
Top Bottom