Msg box when form returns no records (1 Viewer)

[AnnPhil]

I have a form with a command button that when clicked on opens a second form. I need a message box to come up when the second form returns no records. I need the code to be on the command button Here is what i have that does not work


Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Second frm"

stLinkCriteria = "[Person_ID]=" & Me![Person_ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

If Me.Recordset.RecordCount = 0 Then
DoCmd.Close
MsgBox "There are no details for this record! See Help button for details"

End If


Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

Not sure why this doesn't work, any suggestion are welcome, thanks

 

[pbaldy]

You would need to test the form being opened, not the form the code is in. "Me" refers to the form the code is in.

 

[AnnPhil]

Is this the correct way to referr to the other form?

If Forms.Second Form Name.Recordset.RecordCount = 0 Then

 

[pbaldy]

Yes, but you'll have to bracket the form name due to the inadvisable spaces.

 

[AnnPhil]

I tried the brackets around the form name and got this error message "Object doesn't surrport this property or method"

 

[pbaldy]

I tested this and it worked fine:

Forms.frmEmployee.Recordset.RecordCount

What is your full code now?

 

[AnnPhil]

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Second form name"

stLinkCriteria = "[Person_ID]=" & Me![Person_ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

If Forms.[Second form name].RecordCount = 0 Then
DoCmd.Close
MsgBox "There are no details for this record! See Help button for details"

End If


Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

 

[pbaldy]

You didn't use the syntax you posted before, or the syntax I posted. Try

If Forms![Second form name].Recordset.RecordCount= 0 Then

 

[AnnPhil]

I am sorry i did try the syntax you mentioned, i just typed it wrong in the reply, still getting the same message.

 

[pbaldy]

Can you post the db?

 

[AnnPhil]

Yes but i will have to do it tomorrow, please check back then, i appreicate your time thanks

 

[AnnPhil]

Paul

I was able to get the code to work! I had to make one small change and it seems to work fine, thanks so much for your help.

I just changed the . for ! and it worked

If Forms![Second form name].RecordCount = 0 Then

 

[gemma-the-husky]

another way is to test the query that dirves the second form

like this

if dcount("*","thatquery") = 0 then
  msgbox("no details")
else
  docmd.openform "secondform"
end if

 

[pbaldy]

if dcount("*","thatquery") = 0 then

You'd have to account for the wherecondition, Dave.

if dcount("*","thatquery", stLinkCriteria) = 0 then

 

[gemma-the-husky]

i assumed that whichever query "drove" the second form would include criteria to limit the selected rows,which is what i normally do, so i wouldn;t need the criteria condition.

it was the principle of the idea, more than the execution, as an alternative to the form!recordcount approach