Error Message for Query/Form.

[student2]

Hi :-)

I've designed a query that gathers its information for two tables:-

1)Author and
2) Category

My selection criteria for this query is Author.AuthorNa me [Enter Author Name] and
Category.Catego ryName [Enter Category].

This query works well. However, if I do the following:

"Joe Public" as the Author and
"Faith" as the Category

If this author has books on faith then they will be shown HOWEVER

If he has none, I get a blank form or blank Datasheet view.

For the Form I desire to have some message stating there are no records
when such a situation arises (Any Help rendered would be greatly appreciated)

(Previously someone on this forum highlighted a method with search box criteria from one table and that worked fantastic. NOW I dont know how to apply this for two tables)

Thanks a Mil!

[ChipR]

This will be easy, since it requires very little change from the previous code. You'll have two text or combo boxes for the user to enter criteria, something like txtAuthor and txtCategory. I like combo boxes usually because they autocomplete, but you probably don't want one with many, many entries. Plus with the text box, you might eventually let a user enter partial search criteria and use LIKE to match them. This shouldn't work correctly if either text box is empty or null, so you probably want to check for that.

Private Sub cmdSearchButton_Click() 
Dim intMatches As Integer 
Dim strWhereCondition As String 
    strWhereCondition = "[AuthorName] = """ & txtAuthor _
        & """ AND [CategoryName] = """ & txtCategory & """"
    intMatches = DCount("AuthorName", "myQuery", strWhereCondition) 
    If intMatches > 0 Then 
        DoCmd.OpenForm "frmViewRecords", , , strWhereCondition 
    Else 
        MsgBox "No records found." 
    End If 
End Sub 

[student2]

Thanks ever so much ChipR, I'm going to look into it.
What I did in the interim was to present a message box on the form's load that would advise the user that a blank form would mean--- no records.
This I thought was unprofessional and not nice design but I did need something to advise the user and not have him/her be at a lost.

Again I thank you ever so much for your responses.
Appreciative!
C.R.

[student2]

Hi ChipR.
I tried it, however, it does not seem to be functional on my end.
If my datatype for CategoryName and AuthorName is "text" does that make a difference? (just a wild guess)
Thanks again!
C.R.

[student2]

Ok ChipR. Thanks a mil, I now got it to work.
Appears that I had line of code in an incorrect order......well all I did was adjust the order and it works thanks again :-)