Docmd.OpenReport - where clause ?

[ooops]

Hi,

I have a Docmd.OpenReport with Where clause

Docmd.OpenReport "myReport",acViewPreview,,"[ID] = '" & me![txtID] & "'"

If it couldn't find the ID entered in the table, I want to have a msgbox for that. Could you please help me the syntax to do it ? Thanks.

 

[jazzz]

Try something like this. I assume you are calling the report from a command button? It doesn't matter where it is being called from but you need to check if the ID exists this will do it you may need to tweek it to fit your needs. Also, in your vb reference you need to add DAO and be sure to move it above the ADO reference.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT ID " & _
"FROM yourtablename " & _
"WHERE ID = " & Chr(34) & Me("ID&quot & Chr(34)

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

With rs
If .BOF And .EOF Then
'* This is a good number
Docmd.OpenReport "myReport",acViewPreview,,"[ID] = '" & me![txtID] & "'"

Else
'* No go no number.
MsgBox "ID does not exist." & vbNewLine _
& "Please select a New ID and try again!", vbCritical, "ERROR ID#"

End With

SubExit:
If Not (rs Is Nothing) Then
rs.Close
Set rs = Nothing
End If

If Not (db Is Nothing) Then
db.Close
Set db = Nothing
End If

Life's a journey enjoy the ride...

jazzz

 

[danvlas]

If IsNull(me!txtID) Then
MsgBox "OOPS"
Else
Docmd.OpenReport "myReport",acViewPreview,,"[ID] = '" & me![txtID] & "'"
End If

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ooops]

Hello Jazz,

I tried what you suggested and it always goes to ELSE (ID does not exist).

I put strSQL to a msgbox and it looks like :

SELECT ID from myTable WHERE ID = "002"

ID is a text field in my table. I can't tell what's wrong with the code. Can you please help.
Thanks so much for your response.

 

[danvlas]

"it always goes to ELSE (ID does not exist)."

Else is executed if ID exixts,

If ID is numeric, drop the single quotes:

Docmd.OpenReport "myReport",acViewPreview,,"[ID] = " & me![txtID]




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ooops]

Silly me! Thanks so much.

 

[jazzz]

Thank you Daniel I got busy and just got back to check this.

Life's a journey enjoy the ride...

jazzz