If no match, how do I stop display of empty query results? 1

[deduct]

I have a form, on it are two combo boxes, one for the year and one for the division. The user selects the values in each combo box, then clicks a command button that runs a docmd.openquery (the query has criteria that come from the combo box values). This works like a charm.

However, sometimes there are no records for the year and division combination selected and a blank query results is displayed. How can I trap this and instead perhaps display a msgbox saying "No data"?

Karl

 

[jrbarnett]

Karl,

In the code for the command button, I would use a DCount statement to check that the query will return greater than 0 rows with your selected parameters, otherwise it will display an appropriate message.

John

 

[deduct]

OK, if table name = tbl_data
year field = fld_yr
division field = fld_div
query name = qry_data (query criteria on fld_yr and fld_div come from the combo boxes on the form)
form name = frm_main
year is selected with cbo_yr (on the form)
division is selected with cbo_div (on the form)

command button name = cmd_run_qry

Right now the command button

DoCmd.OpenQuery "qry_data", acNormal, acEdit

Where and how do I use this dcount function?



Karl

 

[jrbarnett]

This will do it:

If DCount ("*", "qry_data", "fld_yr = " & Me!cbo_yr & " And fld_div = " & Me!cbo_div) > 0 Then

    DoCmd.OpenQuery "qry_data", acNormal, acEdit

Else
   MsgBox "No data matching your selection", vbOkOnly+vbInformation
End If

Put this code into the on click event of the command button to replace the single OpenQuery line.

John

 

[deduct]

I'm getting error '62506' - Data type mismatch in criteria expression (on the dcount statement).

fld_yr and fld_div are both text.

cbo_yr has typed in values 1998;1999;2000
cbo_div has a table as the row source type and the div field (the bound column) is text.

I tried changing cbo_yr to "1998";"1999";"2000" but it gave me the same error.

Karl

 

[jrbarnett]

Use this first line instead:

If DCount ("*", "qry_data", "fld_yr = '" & Me!cbo_yr & "' And fld_div = '" & Me!cbo_div & "'&quot > 0 Then

Basically it has put apostrophes around the fieldnames to delimit text fields.

John

 

[deduct]

Thank You! That did the trick and it works for what I needed. STAR.

Karl