Showing specific record on report open

[tania20]

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

 

[belovedcej]

So essentially, you want it to loop until an ID that exists is entered or they press cancel? I'm not seeing where you determine if the value is in the recordset being searched, so let me give you an idea. (sorry it's messy - hard to format stuff in here.)

'your beginning code, then. . .

Dim rst As Recordset
Set rst = CurrentProject.BaseConnectionString.Execute("Select * from table where field = """ & strID & """)

while rst.Recordcount < 1 
  If MsgBox("Invalid Hospital ID. Click OK to try           
  another ID or Cancel.", vbOKCancel) = vbOK Then 
  strID = InputBox("Please enter the patients hospital ID.")
  Set rst =        CurrentProject.BaseConnectionString.Execute("Select * from   table where field = """ & strID & """)
  Else
    DoCmd.Close
    Set rst = nothing
    Exit Sub      
  End If
Loop  
Set rst = nothing

'now the rest of your code.
End Sub

 

[tania20]

hi, thanks for the input-im getting an error on list seperator or missing ) for rst....

 

[belovedcej]

Looks like I have some syntax errors in there.

Try this:

CurrentProject.BaseConnectionString.Execute("Select * from table where field = " & strID)

To explain - usually a string needs to be surrounded by quotes in an SQL statement. If this doesn't work, you may need to play with it a little while to be sure that there are quotes around the string.

Multiple quotes can be annoying, sometimes.