record not found

[rschultz]

I have the following code. I would like to know how I can add a message box that will tell me/the user if no records are found rather than open the display/results form.

Private Sub Command16_Click()
Dim strSource As String
strSource = "SELECT * FROM [Adminstration]" & _
"where (([Adminstration].[User]) LIKE (""*"" & 'Printer' & ""*"" ))" & _
" ORDER BY [Division],[Location]"
DoCmd.OpenForm "Printer Results", , , , , , strSource
End Sub

 

[jimbrooking]

R-

You could use the DCount function:

If Dcount("User","Administration","User LIKE ""(*" & Printer & "* ))""") = 0 then
Msgbox "No printers for you..."
Exit Sub
End If

(You will need to fool with the third argument in the DCount call as I'm not sure it parses correctly the way it's written here.)

Maybe put it just above the "strSource = " statement.

HTH
Jim

 

[rschultz]

Initially I don't think this is what I'm looking for but I'll give it a shot. Thanks

 

[joeyreyma]

i can see that you are using the strSource as OPENARGS argument to the OpenForm. am thinking that you will assign this during the OnOpen of the form as the form's recordsource. if you do, then subsequently, you can:

Forms.RecordSource = strSource
Forms.RecordsetClone.RecordCount = 0 then Msgbox "no records"

to test for the any records.

 

[rschultz]

Joey: That was it. The final product looks like this:
If Len(Me.OpenArgs) Then
Me.RecordSource = OpenArgs
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox " No Records Found"
End If
End If

Thanks again