Check Query Results

[andy_dyer]

It didn't like the "Else" as it said there was no "If", there was so I thought I'd change it to spell it out to the code what I wanted and make 2 "If"s.

Still doesn't like it as it now syas that the first End If doesn't have a Block If??

What am I doing wrong??

If .RecordCount > 0 Then
Dim objWord As Word.Application
Set objWord = CreateObject("Word.Application")
With objWord
.Visible = True
.Documents.Open ("C:\Address Book\Address Book - Birthday Month List.doc")
End If

If .RecordCount = 0 Then
DoCmd.OpenForm "frmError1"
End If

 

[dcx693]

Try modifying the code slightly like this:

If .RecordCount > 0 Then
Dim objWord As Word.Application
Set objWord = CreateObject("Word.Application")
   With objWord
     .Visible = True
     .Documents.Open ("C:\Address Book\Address Book - Birthday Month List.doc")
   [COLOR=red]End With[/COLOR]
End If

If .RecordCount = 0 Then
   DoCmd.OpenForm "frmError1"
End If

 

[andy_dyer]

Ok... slowly but surely working through the code problems!

Now:

rst.Open "qryBirthdayList", cnn, adOpenStatic, adLockPessimistic

it says there is a SQL error and it expects a DELETE, INSERT, PROCEDURE, SELECT, or UPDATE

It is a RunTime error -2147217900 (80040e14)

 

[dcx693]

I started looking through my Access 2002 help for that error, but just FYI: If you ever upgrade to Access XP, realize that the help system really *bites*.

Anyway, I played a hunch and I think I was right. I think you misspelled the name of your query. Make sure "qryBirthdayList" is actually the name of your query.

EDIT: WRONG! I was wrong, sorry. Change the rst.Open command to take out the part about locking like this:
rst.Open "qryBirthdayList", cnn, adOpenStatic

 

[andy_dyer]

still the same error on the same line...

Thought I'd post all the code I am using to see if there are any problems elsewhere that could be causing the problem with this line...

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 "qryBirthdayList", cnn, adOpenStatic

' 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
If .RecordCount > 0 Then
Dim objWord As Word.Application
Set objWord = CreateObject("Word.Application")
With objWord
.Visible = True
.Documents.Open ("C:\Address Book\Address Book - Birthday Month List.doc")
End With
End If

If .RecordCount = 0 Then
DoCmd.OpenForm "frmError1"
End If

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

End With

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

End Sub

 

[dcx693]

OK, try changing the line to:
rst.Open "qryBirthdayList", cnn, adOpenKeyset
Also, you didn't say if you checked the spelling of "qryBirthdayList". If it's misspelled you might also get that message.

 

[andy_dyer]

Hi,

Yes I've checked the spelling and it is correct.

Also this new line of code still has the same response...

 

[dcx693]

Try taking off all the options like this:
rst.Open "qryBirthdayList", cnn

I gotta say I have no idea why it's failing. Are all the tables in the query local?

 

[andy_dyer]

Still doesn't work!



I have stripped out everything I think you don't need and have zipped up a copy of the database!

I have taken out all the data so if we can get this to work it should when the Birthday List button is clicked on frmBirthdayList I should get frmError1.

I ultimately want to be able to be able to get a master Birthday List with no filtering just showing months, dates and names.

And also what we are working on at present where I can filter the month and just get those birthdays for that month (which I could get before) but without getting the ambiguous Access error if no records are found.

Hope this is able to shed some light on this...

 

[dcx693]

There's some type of problem with that qryBirthdayList. I tried creating a new table and query and the code ran fine. I tried pointing the code to the qryBirthdayListMaster that you also had, and the code ran.

Sorry, but I don't have time right now to look further. I'll ty to pick this up again later.

 

[andy_dyer]

IT WORKS!!!!!!

Working around the problem I've got it to work!!



I created a query that counted the number of birthdays in the month in question.

A created a text box on my frmBirthdayList called txtCount that displays this.

This txtCount refreshes if the month changes.

I've then hidden this text box.

I then used this code:

Private Sub cmdViewBirthdayMonthList_Click()

If IsNull(txtCount) Then
DoCmd.OpenForm "frmError1"
Else
DoCmd.RunMacro "OpenBirthdayMonthList"
End If

End Sub

That opens the document using a macro instead of code which i know isn't ideal but it DOES work!!

I get my error if there are no records and I get my document if there are!!!

THANK YOU EVER SO MUCH FOR YOUR HELP AND PATIENCE!!

 

[dcx693]

I'm so glad this is over....