Vbyesno

[m0aje]

Hello,

I am using Access 2003 I have a database I have created for a church cemetery. It has approximately 425 records. I created a form that I can query a last name and bring up the form if the record exists. If no record is found, a MSG box will say it doesn't exist. I added some code to search again and it works when I say yes to continue searching. If after more than one new search within this code, it will error out at the 'DoCmd.OpenForm "Form Query LAST NAME" if I say NO. If I click the NO after the first search, it works fine.

Any help would be greatly appreciated. The code is as follows.

Thanks!

Private Sub Form_Open(Cancel As Integer)
Dim NewSearch
StartSearch:
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
NewSearch = MsgBox("There is no record of this person in this cemetery. Search again?", vbYesNo)
If NewSearch = vbYes Then
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "Form Query LAST NAME"
GoTo StartSearch
Else
DoCmd.Close acForm, Me.Name
Exit Sub
End If
End If
End Sub

 

[arnelgp]

the validation should be done on the first form and not on "form query last name". if you have a command button on the first form after surname is entered into the textbox:

private sub button_click()

dim lngCount as long
lngcount = nz(dcount("*","[yourtabletosearch]","[surnamefield] = '" & me.yoursurnametextbox.value & "'"), 0)
if lngcount = 0 then
msgbox "not found"
else
docmd.openform "form query last name"
end if
end sub

 

[m0aje]

the validation should be done on the first form and not on "form query last name". if you have a command button on the first form after surname is entered into the textbox:

private sub button_click()

dim lngCount as long
lngcount = nz(dcount("*","[yourtabletosearch]","[surnamefield] = '" & me.yoursurnametextbox.value & "'"), 0)
if lngcount = 0 then
msgbox "not found"
else
docmd.openform "form query last name"
end if
end sub

Hello,

Thank you for your reply. The "form query last name" form was created from a simple query. It is accesses from the Switchboard Manager. When I select "Search For Record" from the Switchboard Manager, a prompt will ask for the 'Last Name' then the First Name. It will search the CemeteryDatabase table. If it is not found, then it displays 'There is no record.....search again'. I am not sure about the 'first form' you are referring to as everything originates from the switchboard manager.
Again... thank you for your reply. I am an amateur with VBA and get lucky sometimes, but I appreciate all the help I can get.

Thanks again!

m0aje