Automation Object error

[hilbertm]

I am very new to VBA and using Access 97.

I have a form that opens and displays filtered data. The query that I am using to filter the data uses an input box to specify the data (from a field) to be displayed. I would like to have a message be displayed when there are no matching records. Right now, when there are no matching records only a blank form is displayed.

I tried to test the result of the query with:

If DCount("

", "query", "[field]") = 0 Then
MsgBox ("There are no matching records to display." & Chr(13) & Chr(10) & _
Chr(13) & Chr(10) & _
"Please make another selection.")
Exit Sub
End If

I get the following error message: The expression you entered as a query parameter produced this error: ‘The object doesn’t contain the Automation object ‘Enter_ICAO’

The field is ICAO
‘Enter ICAO’ is the prompt in the criteria section for the ICAO field in the query.

My guess is that the ‘Enter ICAO’ needs to go in the Dcount statement, I just don’t know where.

I would like to know what I am doing wrong, and if it is possible to not display a blank form if no records match the query.

Thanks for any help

 

[Rich]

Private Sub Form_Open(Cancel As Integer)
If (RecordsetClone.RecordCount = 0) Then
DoCmd.Close
Beep
MsgBox "There are no matching records to display. Please make another selection.", vbInformation, "No Records"
End If

End Sub

 

[hilbertm]

Rich,
Thanks for the quick response. I put your suggested code in the on open event for the form that loads after the query is ran, but it does not work. I still get a blank form if no records match the search.
I'm sure I am missing something simple, I just don't know what yet.

Mike

 

[Rich]

Not using a subform are you?

 

[hilbertm]

Yes, there is more than one subform on the main form I call up with the query.

 

[Rich]

Since subforms open before the main form you cannot easily refer to them in the on open event of the main form,Use the timer event of the main to check the subforms.
HTH

 

[hilbertm]

Rich,
the timer event worked for me. Thanks I have been beating my head on my desk for about a week trying to figure this out.

 

[Rich]

 

[hilbertm]

Rich,
Any chance you can help with another problem? I am trying to get a form to open that opens to the current record on a subform.

Private Sub EnterNewPTAS_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "PTASEnter"
stLinkCriteria = "[PROCEDURE ID]=" & Me![PROCEDURE ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_EnterNewPTAS_Click:
Exit Sub
Err_EnterNewPTAS_Click:
MsgBox Err.Description
Resume Exit_EnterNewPTAS_Click
End Sub

This does not work. BTW this is posted in more detail in this section with the title OPEN FORM WITH CURRENT RECORD.

I think my problem may be subform related. Or maybe lack of VBA Knowledge related.

Sorry to keep bugging you.

Mike

 

[Rich]

Haven't got a filter on PTASEnter have you?

 

[hilbertm]

No, no filter on the PTASEnter form. Does it look like my code should work? Maybe I should look for a different way to have data entered for PTAS information rather than a pop up form.

 

[Rich]

I've found it easier to use a command button in conjunction with the record selector on the sub to do this, may work. Let the wizard create the code and if the button works copy the code.
HTH

 

[hilbertm]

The thing is that I can get records that are already in the database on my form to enter new data, but nomatter how I try to enter a new record, all I get is a total new record.( the field from the main menu does not match the field from the subform) So I have the new PTASEnter form opening, but I can only edit existing data from the PTAS table. There must be a way to get a new record in the PTAS table with the PROCEDURE ID that is On the main form.
Since the data that is already in the database is showing up like I want, I feel I must be close.
Maybe I have to pound my head a little harder on the old desk.
Any ideas?