Message window if query does not find any matching entries

[locosh]

Hello,

I am working on a school project db and I would like to know if it's possible to have a window pop up for the user if the item they try to look for is not on the table instead of having Access give you a blank table.

ie. If the user looks for a product-id=006 and it's not found, I would like a message to pop up saying "That product id was not found" or even better "Product ID 006 was not found". Right now all I get if a product id is not found is a blank table.

The simple query I have so far is:

SELECT product-id, product-name
FROM Products
WHERE product-id=[Enter the Product ID to find];


Thanks!

 

[GingerR]

if you already know some of this stuff, forgive me.

are your query results being displayed in a form? if not, create a form based on your table as the Record Source. Set Default View and Views Allowed both to datasheet. this makes it look like a table. call it frmSearchResults.

how are you launching that query? just double-clicking on it? if so, better to make a pretty little form with a button that when pressed will run your query. Using forms is how you get all that juicy code in there. We'll call this one frmSearchCriteria. Look up dividing lines, pop-up, modal, scroll bars for info on those things and change setting if you want to.

on your frmSearchCriteria, create a text box called txtSearchCriteria. it's where the user will put the Product ID they wanna look for. then make a button. go down to On Click and choose 'Event Procedure'. then between the "SUB" and "END SUB" type this (you will have to change table/field names to match your own):

'determines if there are any matching records
If DCount("ProductID", "ProductTable", "[ProductID] = " & Me.txtSearchCriteria) = 0 Then
        MsgBox "There are no matching products", vbOKOnly, "Status"
    Else
        DoCmd.OpenForm "frmSearchResults", acNormal, , "[ProductID] = " & txtSearchCriteria
    End If

this is assuming your product ID is a NUMBER. if it is text, then you have to put quotes around txtSearchCriteria:

'determines if there are any matching records
If DCount("ProductID", "ProductTable", "[ProductID] = '" & Me.txtSearchCriteria) & "'" = 0 Then
        MsgBox "There are no matching products", vbOKOnly, "Status"
    Else
        DoCmd.OpenForm "frmSearchResults", acNormal, , "[ProductID] = '" & txtSearchCriteria & "'"
    End If

 

[GingerR]

oh--as for the groovy message box...


change to:

MsgBox "There are no matching products for Product ID " & me.txtSearchCriteria, vbOKOnly, "Status"

 

[xbigblue]

Hi Locosh,

I know of no way to do exactly what you ask, but you could add lots of learning to your school project if you used the NoData Event of the Access Report. To do this, you would prepare a report design that uses your query as its data source. The report wizards might help here if you are not familiar with Access Reports. NoData is an event that "fires" if the data query returns an empty recordset. Check Access Help on "NoData". It can be used to trigger a macro which does two things: display the message box you want to show your user, and then cancel the report. VBA can also be used in place of a macro, but macros are much easier to learn.

I try to design jobs like this to have an Access Form as the front end. Controls on the Form can be referred to directly on the report that you eventually get to, or in the message box that you might use for the No report situation.

Good luck on your project.
xbigblue

 

[locosh]

Thanks guys!

This is very helpful and I have learned a lot. Ginger if you happen to come back to this thread or anyone who has knowledge of his advice, I have one last question: Is there any way to use wildcards with your method of searching? (ie. I want to be able to type the first few letters of a product ID instead of the whole thing)

Thanks again!

 

[GingerR]

look up the 'LIKE' operator.
also, have you built any queries? it's a good way to see how this stuff works. By the way i suggest you dont use dashes in your field or table names cause it will for sure cause you problems later. if you haven't done a lot with them already, i suggest you change them now

    If DCount("[Product-ID]", "Products", "[Product-ID] like '" & Me.txtSearchCriteria & "*'") = 0 Then
        MsgBox "There are no matching products", vbOKOnly, "Status"
    Else
        DoCmd.OpenForm "frmSearchResults", acFormDS, , "[Product-ID] like '" & txtSearchCriteria & "*'"
    End If

 

[locosh]

Thanks Ginger, it works perfectly! I have been looking at the SQL code of queries as well. And no I don't use dashes

This is sure going to make my project a star in the class.