Msgbox if no records to display

[nka]

Hi all.

I am not very good with VBA coding and manage to get around most of what I need using wizards and converted macros.

However, I have come unstuck slightly with what I am trying to do.

I have a form with an unbound field [searchuser]. I then use the following code to open the main form filtered to match [searchuser].

stDocName = "SHW_UserIssues"
stLinkCriteria = "[User]=" & "'" & Me![SearchUser] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

However, I don't want the form to open if there are no matching records.

Can someone please guide me on how to create the necessary code to check for a valid record before opening the filtered form (based on a table) and perhaps display a message box to say "no records".

Many thanks

 

[Banana]

You would have to do a Dlookup() prior to opening the form to verify if there is a record.

Something like this:

If Not IsNull(DlookUp("User", "SHW_UserIssues", "[User]=" & "'" & Me![SearchUser] & "'")) Then
   DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
   Msgbox("No Records")
End If

If you want to make it more efficient and avoid looking up the value twice, you can use Open Arguments instead of Where Condition in Open Form and use Open Arguments to pass the value of User and on Open Event have the form move to that record.

HTH.

 

[nka]

Thanks Banana,

I changed the code slightly as the form "SHW_UserIssues" has a subform (recordsource is a query "FLTR_User").

Now I am getting an error message when it steps through the code saying "You canceled the previous operation"... I haven't cancelled anything!

Here is the amended code:

stDocName = "SHW_UserIssues"
If Not IsNull(DLookup("User", "FLTR_User", "[User_ID]=" & "'" & Me! [SearchUser] & "'")) Then
DoCmd.OpenForm stDocName, , , stLinkCriteria

It's falling over on the If Not IsNull line....

Unfortunately I have no idea how to do "Open Arguments".

 

[Banana]

Doh!

You don't look up forms. You look up the tables. Substitute the subform FLTR_User with the query or table it is bound to.

Also, you don't need a space:

Me! [SearchUser]

Should be

Me![SearchUser]

 

[Oldsoftboss]

do you hve error handling on your sub. Hope So (If created with the wizard it should)

You should have a line saying:
Msgbox Err.Description

Change this to Err.Number

This will tell you the error that is being generated

The change the error handler to

Err_UserSearch_AfterUpdate: 'Where ever the error is being generated
If err = 94 then 'Error No generated above
Docmd.Close acForm "frmName"
Else
Msgbox Err.Description
End If
Exit Sub

You need to make the error handling work FOR you not just to alert you something has gone wrong.

Dave

 

[nka]

Hi Banana,

There is no space in the code - not sure how it displayed on here with one.

"FLTR_User" is the recordsource (query)

Going to have a look at the error messages.....