Check if a table is empty

[Guest]

I have a sub form with 8 cmdButtons on it that when clicked open of a form. I
have placed a rectangle that surounds the buttions of each rectange and made
the borders of the rectangles red. Now, what I want to do is when the main
form is opend on a specific patient visit, I want to check the contents of
the query that populates the forms to see if there are any records in that
query. If there are not records in any of the query then all rectangles would
be set to visible = False. If one or more of the queries have data, then the
rectangles (with there red borders) would be set to visible = True. This will
help the user when glancing at the form to know which forms (procedure) has
data behind them.

I believe using Case Select is the appropriate method, but I'm not sure how
to check the contents of the queries without opening the forms. Could
somebody help me with this?

Thanks, Rob

 

[Douglas J. Steele]

Me.Rectangle1.Visible = (DCount("*", "[NameOfQuery]") > 0)

 

[Guest]

Okay, here is a way to do that. Use the form's Load event:

Dim varQuery As Variant
Dim varCtl As Variant
Dim dbf As Database
Dim rst As Recordset
Dim lngCtr As Long

varQuery = Array("Qry1", "Qry2", "Qry3", "Qry4", "Qry5", "Qry6", "Qry7",
"Qry8")
varCtl = Array("Rct1","Rct2","Rct3","Rct4","Rct5","Rct6","Rct7","Rct8")

Set dbf = Currentdb

For lngCtr = 0 to 7
Set rst = dbf.OpenRecordset(varQuery(lngCtr))
Me.Controls(varCtl(lngCtr)).Visible = rst.RecordCount > 0
rst.Close
Next LngCtr

Set rst = Nothing
Set dbf = Nothing

 

[Guest]

As always, thank you Doug. works perfectly.

Me.Rectangle1.Visible = (DCount("*", "[NameOfQuery]") > 0)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a sub form with 8 cmdButtons on it that when clicked open of a form.
I
have placed a rectangle that surounds the buttions of each rectange and
made
the borders of the rectangles red. Now, what I want to do is when the main
form is opend on a specific patient visit, I want to check the contents of
the query that populates the forms to see if there are any records in that
query. If there are not records in any of the query then all rectangles
would
be set to visible = False. If one or more of the queries have data, then
the
rectangles (with there red borders) would be set to visible = True. This
will
help the user when glancing at the form to know which forms (procedure)
has
data behind them.

I believe using Case Select is the appropriate method, but I'm not sure
how
to check the contents of the queries without opening the forms. Could
somebody help me with this?

Thanks, Rob