Forms with a filter

[GuillaumeJ]

Hi everyone

I have currently a form in my access db where a user can select a criteria, and then click on a button to open another form that displays records filtered with the given criteria.

However, sometimes, the second form is blank because the filter applied on records gives no result. I would like to prevent the second form's opening in those cases, how can I do that ?

Any help would be great !

Guillaume

 

[Norie]

You need to add additional code to the existing code for the button to see if there are any records and cancel opening the form if there aren't any.


There are various ways to do this.

Can explain further and perhaps post the code you already have for the button?

 

[GuillaumeJ]

Yes, I figured out I would have to add some code to check if there is any record with the selected criteria.

For the moment, I have only the following code (created with Access wizard)

Private Sub View_visits_Click()
On Error GoTo Err_View_visits_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim currentform As Form

stDocName = "Visits"

stLinkCriteria = "[Dealer]=" & Me![Dealer_name]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_View_visits_Click:
Exit Sub

Err_View_visits_Click:
MsgBox Err.Description
Resume Exit_View_visits_Click

End Sub

The goal of my database is to register visits done by our sales representatives at our dealers sales places.
The Visits table contains records for the visits with a lot of info, including the dealer visited.
The first form I was refering to is a kind of switchboard where the user can choose a dealer and then displays all the visits for this selected dealer, but there happens sometimes that there is no visit record, so I would like to display a pop up saying "no visit for the dealer selected" and to prevent the second form's opening

hope this is clearer now

 

[Andrew Fergus]

Following on from Norie's suggestion, if you have used a macro to open the form and to apply the filter, then you can add a "counter" to the form (e.g. Counter = Count([variable name]) - if you want to make it invisible set the colour to the same as the background and disable the tab stop ) - go to the macro in design view, Click -> View Conditions, insert a new line after you open the form, enter the condition :

[Forms]![form_name]![Counter]=0

In the action close the form.

You might also want to add another line in the macro before or after the close form (and using the same condition as before) but with a pop-up message box (Msgbox) informing the user there were no records found.

Keep any other lines in your macro the same (e.g. stop macro?) and save the macro.

HTH, Andrew.

 

[Norie]

What is the name of the query/table the Visits form is based on?

You could try something like this

NoRecords = DCount("*", "Visits", "[Dealer]='" & Me![Dealer_name] & "'")

If NoRecords = 0 then  
   MsgBox "That dealer has made no visits."
Else
   DoCmd.OpenForm stDocName, , , stLinkCriteria 
End IF

Replace Visits in the DCOUNT with the Table/Query the Visits form is based on.

 

[GuillaumeJ]

Thanks, that helps but I have now a data type mismatch error in the criteria in the DCOUNT function, after replacing the name of the table in the DCOUNT function

I am nearer the solution however

 

[Norie]

Try removing the single quotations.

ie

Replace

"[Dealer]='" & Me![Dealer_name] & "'"

with just

"[Dealer]=" & Me![Dealer_name]

I wasn't sure which data type Dealer_name was. Is it text or number?

 

[GuillaumeJ]

Everything is OK now

Thanks a lot, Norie