Displaying Error Msg Box

[G. Gray]

I have a read-only form, with 4 selection fields. I use a customized toolbar
with Filter by Form and Apply Filter selections to bring up the results.
What I am trying to do is to display an error box msg, “No Record Foundâ€,
instead of a blank form when I input to 2 of the fields and the criteria does
not match. I have written the following code, using it in the Apply Filter
Event, but it does not work.

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
On Err GoTo ApplyFilter_Err
Dim strProd As String

strProd = "[ProdNumber]" = "[ShelfID]![RowID]![ProdType]*"
If DCount("*", "Product Location", strProd) > 0 Then
DoCmd.OpenForm "Product Location", acNormal, "Product Location", ,
acFormReadOnly, acWindowNormal

Else

MsgBox "No Records Found", vbOKCancel

End If

ApplyFilter_Exit:
Exit Sub

ApplyFilter_Err:
MsgBox Error$
Resume ApplyFilter_Exit

End Sub

Your help would be much appreciated.

Thanks
G.Gray

 

[storrboy]

This
strProd = "[ProdNumber]" = "[ShelfID]![RowID]![ProdType]*"
doesn't make sense, but I have no idea what they reference so I'd only
be guessing as to what it should look like.
Maybe..
strProd = "[ProdNumber] = " & [ShelfID]![RowID]![ProdType] & "*" ??

If DCount("*", "Product Location", strProd) > 0 Then

The first argument of DCount is the field to search, not sure that a
wildcard is acceptable. It is in Count, but not sure here.

Beyond these you didn't say what it's doing wrong. Are you getting an
error message? No messages at all? Filter not being applied?

 

[G. Gray]

This
strProd = "[ProdNumber]" = "[ShelfID]![RowID]![ProdType]*"
doesn't make sense, but I have no idea what they reference so I'd only
be guessing as to what it should look like.
Maybe..
strProd = "[ProdNumber] = " & [ShelfID]![RowID]![ProdType] & "*" ??

If DCount("*", "Product Location", strProd) > 0 Then

The first argument of DCount is the field to search, not sure that a
wildcard is acceptable. It is in Count, but not sure here.

Beyond these you didn't say what it's doing wrong. Are you getting an
error message? No messages at all? Filter not being applied?

I’m new at this and I hope this explains my problem a bit better. My
original problem was that I have a form that has 4 fields, Prod Number, Shelf
ID, Row ID and Prod Type, which is to identify a location. When using Filter
By Form and input to 2 of the fields, Prod Number and Shelf ID, when I Apply
Filter and there is no matching record it returns a blank form. If no
records are found, I would like to display a ‘OK’ msg box “No Records Foundâ€
and return to the input form when I click ok.

Now that I put in the above code, when I Apply Filter whether this is a
record found or not, it first brings up the msg box “No Records Foundâ€. When
I click the ok it continues on to the record or to blank form if nor matching
records.

I found the above code on another posting and I am trying to convert it to
work in my form, so I am not real sure how to apply the DCount.

I hope this clears up what I trying to ask.

 

[storrboy]

I don't normally use filters, I find them ungainly and a pain to
manage, but perhaps this will work. I haven't tested this so it may
need some modifying.

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
On Error GoTo stoprun
Dim strProd As String

If Me.Recordsetclone.RecordCount = 0 Then
MsgBox "No Records Found", vbOKCancel
Else
' ... Do what you want if there are records.
End If

ApplyFilter_Exit:
Exit Sub

stoprun:
MsgBox Err.Number & " - " & Err.Description
Resume ApplyFilter_Exit
End Sub