If Null Question

  • Thread starter Thread starter George
  • Start date Start date
G

George

In my Query I use the following formula to look up data
based on employee man number - what can I add to this so
that when it does not find any matching data it gives the
user a message box stating "No Matching Records Found"

Like [Forms]![F-Issued-Tools-History-By-Man-Number]!
[Combo14]

Thanks - George
 
Hey George,

I used this recently in a similar situation - I've made a few edits
and comments to suit what your situation sounds like. The idea is not
to test and msgbox in the query, but _before_ the query even runs -
why bother collecting records if you know there aren't any?!

This should work in the ON_CHANGE function for the control you set the
MAN NO in, if you can't find a better place for it.

Of course, before you even read it, I'm sure you know the drill: if
this code doesn't work, crashes MSAccess, or causes aliens to abduct
you for a series of somewhat uncomfortable examinations, don't be
complaining at me!

DO let me know if I can explain anything better tho...

Replace all the code below IN_CAPS with your own controls, tables and
fields respectively...

Dim db As Database
Dim rst As Recordset

'Make sure there's a value in the field that identifies the MAN NO
If Not (IsNull(TXT_MANNO)) Then

'Populate the recordset with the tool issue data to test
Set db = CurrentDb
Set rst = db.OpenRecordset("TBL_TOOLISSUES", dbOpenDynaset)

'Test the recordset for any issues to this MAN NO
rst.FindFirst "FLD_MANNO = " & TXT_MANNO

If rst.NoMatch Then
MsgBox "No Matching Records Found"
End If

rst.Close
Set rst = Nothing
Set db = Nothing

End If

HTH - Matt
 
George

Using the LIKE wildcard feature without any wildcard characters ("*", "?",
....) is the same as leaving the LIKE command out. Your expression looks for
an exact match with:

[Forms]![F-Issued-Tools-History-By-Man-Number]![Combo14]

By the way, if your naming convention is standard, you are attempting to
match something from a combo box. A combo box, as typically designed,
displays one value, but underneath, stores a "key". If that's true for your
example above, your query is trying to find something that matches the key,
not the displayed value.

Good luck!

Jeff Boyce
<Access MVP>
 
Jeff - My query works off of a selection made in a form
and it works OK - I am just trying to add a Message when
there is any data to display.

Thanks - George
 
George

Adding a message won't happen in a query. You'll have to build some code
that includes that query, tests for number of rows return, and pops the
message if there are none. You'd run the procedure, not the query directly.

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top