Access Query- Do not display blank record when query does not find a match

Question

Been round the block a few times. Trained on COBOL and FORTRAN IV back in early 70's but find ACCESS really frustrating- probably an age thing!

Anyway- help please!!.

A charity has a client file (over 700 clients) that will have records that need editing. The user is presented with a form(1) holding a text box to enter the parameter (Client Surname) for a query. They can use starting letters and wildcards to narrow a search e.g. Jo*

The click a command button that opens another form(2) based on a query that uses the parameters from the first form.

This Works just as planed if matches are found. If not, it displays a blank/new record. I want to either 1) stop the blank form being displayed  2) tell the user that their query was unsuccessful. I Do not want them to use this facility to CREATE a NEW Client record.

The parameter query is working fine and form(2) shows the matching records, it's reacting to a situation where no records are found, that is my problem.

I really have no idea where to start- a complete novice at this but the charity I’m doing this for is really short of cash.

I need detailed advice on not only the code but where to place it- which property/event etc.

Thanks

Answer 1

I would test whether there are matching records in the On Click event procedure of the command button on form(1) that you use to open form(2).

Let's say:

• The text box is named txtSearch.
• The command button is named cmdOpenForm2.
• The client table is named tblClient.
• The field you search on is named Surname.
• The name of form(2) is frmClient.

The code could look like this:

Private Sub cmdOpenClientForm_Click()

    Dim strWhere As String

    ' Assemble where-condition from text box

    strWhere = "[Surname] Like" & Chr(34) & Me.[txtSearch] & Chr(34)

    ' Check if there are matches

    If DCount("*", "[tblClient]", strWhere) = 0 Then

        ' If not, select the search box

        Me.[txtSearch].SetFocus

        ' Display a message

        MsgBox "There are no clients matching the criteria that you entered!", vbExclamation

        ' And get out

        Exit Sub

    End If

    ' If we get here, there are matches

    ' So we can open the client form

    DoCmd.OpenForm FormName:="frmClient", WhereCondition:=strWhere

End Sub