Hi, i have written code for report open such that the person is prompted to enter an ID which will then bring up that ID's corresponding report.That part is working fine, however I want a msgbox to display if this ID does not exist in the records. The following code is what i have, any help would be appreciated!!

Private Sub Report_Open(Cancel As Integer)

Dim strID As String
Dim strWhere As String

strID = InputBox("Please enter the patients hospital ID.")
strWhere = "[general_info.HospitalNumber] = " & "'" & strID & "'"
strDocName = "Home_Oxygen_Report"

If strWhere = "" Then

If MsgBox("Invalid Hospital ID. Click OK to try another ID or Cancel.", vbOKCancel) = vbOK Then

strID = InputBox("Please enter the patients hospital ID.")
Else

DoCmd.Close

End If
Else

DoCmd.OpenReport strDocName, acPreview, , strWhere

End If

End Sub

Thanks

Tania :)

its not clear where you put all this. I'm not sure about trying to set a report's properties in the report open event, as it might not work, but try this anyway.

if you need to put it in a form button, you will just have to take out the "cancel=vbcancel" lines

i've not checked it, but hopefully this will work straight off


another way to do this is open the report from a form, and p[rovide a combo box drop down of all the avaialble hospitals, so users don't have to remember a code. then you can't get any misses.

Private Sub Report_Open(Cancel As Integer)

Dim strID As String
Dim strWhere As String

top:
strID = InputBox("Please enter the patients hospital ID.")

'first see if anything was entered at all
if nz(strid,vbnullstring)=vbnullstring then
If MsgBox("No Hospital ID Entered. Click OK to try another ID or cancel. ",vbokcancel+vbexclamation) = vbcancel then
cancel = vbcancel
exit sub
end if
goto top 'ask again
end if

'if here we did enter something - so check the hospital id exists
'change these references to suit your table etc
strid = nz(dlookup("HospitalNumber","hospitaltable","[hospitalid] = " & chr(34) & strID & chr(34)),vbnullstring)

'if the id was wrong then give a similar message
if strid=vbnullstring then
If MsgBox("No such Hospital ID Found. Click OK to try another ID or cancel. ",vbokcancel+vbquestion) = vbcancel then
cancel = vbcancel
exit sub
end if
goto top 'ask again
end if

'if here we have a real hospital id
strWhere = "[general_info.HospitalNumber] = " & chr(34) & strID & chr(34)
strDocName = "Home_Oxygen_Report"

DoCmd.OpenReport strDocName, acPreview, , strWhere

End Sub

thanks heaps,working exactly how i want it to now :-) The user will ahve the patients id ona form inf ornt of them so this is the bst way i think.I already have methods for opening the correct report from a form,im just trying to account for all the ways in which the users may use the database (knowing that many users will be technology-scared !!)

thanks again :-)