dcount issue

M

Mark

Hi,

On a form (Switchboard: Continuous form view), I have 3
labels (each label's "On Click" event opens a different
report)... I would like the labels to be visible only if
the label's report contains records. And if any report
contains records the label will flash red and yellow. And
to get around the switchboard/continuous-form issue of
having a objects timer flashing... I set the timer to =0
when a command button or label (in this case) is clicked.

So, I am having a problem with the switchboard/form
looking up records to see if they are any records or not.
The queries and reports are working fine, but I am not
getting the switchboard/form to make the labels visible
and flash. The label's visible property is set to false
(the label itself on the form). I have tried
using "Dcount" in the forms open event, but I am not sure
I am referencing the query right. I know you can use
dcount to look up the number of records in a table, but
can I do this for a query? As in the below code. I would
rather just refernce the queries, so if I go back and
change the criteria in one of the three queries I do not
want to have to remember to change the code in the form as
well. Any help would be apreciated. Thanks!!!!!!!


Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim intCount1 As Integer
intCount1 =_
Dcount "[ConsultExpireLicenseDate]", "queryExpireLicenseMes
sage", "[ConsultExpireLicenseDate]")

Dim intCount2 As Integer
intCount2 =_
DCount "[ConsultNextAppraisalDate]", "queryAppraisalDateMes
sage", "[ConsultNextAppraisalDate]")

Dim intCount3 As Integer
intCount3 =_
DCount "[DateDue]", "queryAssessmentDueDateMessage", "[Date
Due]")


If intCount1 > 0 Then
Me.cmdReportExpireLicenseMessage.Visible = True
Me.ReportNotifications_Label.Visible = True
'Timer for Color***********
Me.TimerInterval = 300
'*****************
Else
'Timer for Color************
Me.TimerInterval = 0
'*****************
Me.cmdReportExpireLicenseMessage.Visible = False
Me.ReportNotifications_Label.Visible = False
End If

If intCount2 > 0 Then
Me.cmdReportAppraisalDateMessage.Visible = True
Me.ReportNotifications_Label.Visible = True
'Timer for Color***********
Me.TimerInterval = 300
'*****************
Else
'Timer for Color************
Me.TimerInterval = 0
'*****************
Me.cmdReportAppraisalDateMessage.Visible = False
Me.ReportNotifications_Label.Visible = False
End If

If intCount3 > 0 Then
Me.cmdreportAssessmentDueDateMessage.Visible = True
Me.ReportNotifications_Label.Visible = True
'Timer for Color***********
Me.TimerInterval = 300
'*****************
Else
'Timer for Color************
Me.TimerInterval = 0
'*****************
Me.cmdreportAssessmentDueDateMessage.Visible =False
Me.ReportNotifications_Label.Visible = False
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:

Resume Exit_Form_Open
End Sub
 
M

Mark

The labels on the form/switchboard is working (flashing)
if 2 or 3 (out of the 3 queries) have records. But if
only 1 of the 3 queries has any records then the form only
shows the label not flashing. Do you know why the labels
flash if more than one label/query has records and is made
visible? It is probably in the coding I have... I am
guessing. I have the following code in the form's timer
event, which turns the font colors of the labels yellow
and red... would this cause a problem?:


Private Sub Form_Timer()

'Flashing Color Timer / Font
Red&Yellow************************
With Me.cmdReportExpireLicenseMessage
.ForeColor = (IIf(.ForeColor = vbRed, vbYellow, vbRed))
End With

With Me.cmdReportAppraisalDateMessage
.ForeColor = (IIf(.ForeColor = vbRed, vbYellow, vbRed))
End With

With Me.cmdreportAssessmentDueDateMessage
.ForeColor = (IIf(.ForeColor = vbRed, vbYellow, vbRed))
End With
'***********************

End Sub




-----Original Message-----
Hi,

On a form (Switchboard: Continuous form view), I have 3
labels (each label's "On Click" event opens a different
report)... I would like the labels to be visible only if
the label's report contains records. And if any report
contains records the label will flash red and yellow. And
to get around the switchboard/continuous-form issue of
having a objects timer flashing... I set the timer to =0
when a command button or label (in this case) is clicked.

So, I am having a problem with the switchboard/form
looking up records to see if they are any records or not.
The queries and reports are working fine, but I am not
getting the switchboard/form to make the labels visible
and flash. The label's visible property is set to false
(the label itself on the form). I have tried
using "Dcount" in the forms open event, but I am not sure
I am referencing the query right. I know you can use
dcount to look up the number of records in a table, but
can I do this for a query? As in the below code. I would
rather just refernce the queries, so if I go back and
change the criteria in one of the three queries I do not
want to have to remember to change the code in the form as
well. Any help would be apreciated. Thanks!!!!!!!


Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim intCount1 As Integer
intCount1 =_
Dcount "[ConsultExpireLicenseDate]", "queryExpireLicenseMe s
sage", "[ConsultExpireLicenseDate]")

Dim intCount2 As Integer
intCount2 =_
DCount "[ConsultNextAppraisalDate]", "queryAppraisalDateMe s
sage", "[ConsultNextAppraisalDate]")

Dim intCount3 As Integer
intCount3 =_
DCount "[DateDue]", "queryAssessmentDueDateMessage", "[Dat e
Due]")


If intCount1 > 0 Then
Me.cmdReportExpireLicenseMessage.Visible = True
Me.ReportNotifications_Label.Visible = True
'Timer for Color***********
Me.TimerInterval = 300
'*****************
Else
'Timer for Color************
Me.TimerInterval = 0
'*****************
Me.cmdReportExpireLicenseMessage.Visible = False
Me.ReportNotifications_Label.Visible = False
End If

If intCount2 > 0 Then
Me.cmdReportAppraisalDateMessage.Visible = True
Me.ReportNotifications_Label.Visible = True
'Timer for Color***********
Me.TimerInterval = 300
'*****************
Else
'Timer for Color************
Me.TimerInterval = 0
'*****************
Me.cmdReportAppraisalDateMessage.Visible = False
Me.ReportNotifications_Label.Visible = False
End If

If intCount3 > 0 Then
Me.cmdreportAssessmentDueDateMessage.Visible = True
Me.ReportNotifications_Label.Visible = True
'Timer for Color***********
Me.TimerInterval = 300
'*****************
Else
'Timer for Color************
Me.TimerInterval = 0
'*****************
Me.cmdreportAssessmentDueDateMessage.Visible =False
Me.ReportNotifications_Label.Visible = False
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:

Resume Exit_Form_Open
End Sub
.
Click to expand...
 
M

Mark

Hi all,

I was able to solve my problem (I probably did not make
much sense of all this). But I moved the timer event code
into a if/then statement by itself to get the flashing
label working and the dcount seems to be looking into the
queries. Thanks for being here if I needed help!!!!!

-----Original Message-----
The labels on the form/switchboard is working (flashing)
if 2 or 3 (out of the 3 queries) have records. But if
only 1 of the 3 queries has any records then the form only
shows the label not flashing. Do you know why the labels
flash if more than one label/query has records and is made
visible? It is probably in the coding I have... I am
guessing. I have the following code in the form's timer
event, which turns the font colors of the labels yellow
and red... would this cause a problem?:


Private Sub Form_Timer()

'Flashing Color Timer / Font
Red&Yellow************************
With Me.cmdReportExpireLicenseMessage
.ForeColor = (IIf(.ForeColor = vbRed, vbYellow, vbRed))
End With

With Me.cmdReportAppraisalDateMessage
.ForeColor = (IIf(.ForeColor = vbRed, vbYellow, vbRed))
End With

With Me.cmdreportAssessmentDueDateMessage
.ForeColor = (IIf(.ForeColor = vbRed, vbYellow, vbRed))
End With
'***********************

End Sub




-----Original Message-----
Hi,

On a form (Switchboard: Continuous form view), I have 3
labels (each label's "On Click" event opens a different
report)... I would like the labels to be visible only if
the label's report contains records. And if any report
contains records the label will flash red and yellow. And
to get around the switchboard/continuous-form issue of
having a objects timer flashing... I set the timer to =0
when a command button or label (in this case) is clicked.

So, I am having a problem with the switchboard/form
looking up records to see if they are any records or not.
The queries and reports are working fine, but I am not
getting the switchboard/form to make the labels visible
and flash. The label's visible property is set to false
(the label itself on the form). I have tried
using "Dcount" in the forms open event, but I am not sure
I am referencing the query right. I know you can use
dcount to look up the number of records in a table, but
can I do this for a query? As in the below code. I would
rather just refernce the queries, so if I go back and
change the criteria in one of the three queries I do not
want to have to remember to change the code in the form as
well. Any help would be apreciated. Thanks!!!!!!!


Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim intCount1 As Integer
intCount1 =_
Dcount "[ConsultExpireLicenseDate]", "queryExpireLicenseM
Click to expand...
e
s
sage", "[ConsultExpireLicenseDate]")

Dim intCount2 As Integer
intCount2 =_
DCount "[ConsultNextAppraisalDate]", "queryAppraisalDateM
Click to expand...
e
s
sage", "[ConsultNextAppraisalDate]")

Dim intCount3 As Integer
intCount3 =_
DCount "[DateDue]", "queryAssessmentDueDateMessage", "[Da
Click to expand...
t
e
Due]")


If intCount1 > 0 Then
Me.cmdReportExpireLicenseMessage.Visible = True
Me.ReportNotifications_Label.Visible = True
'Timer for Color***********
Me.TimerInterval = 300
'*****************
Else
'Timer for Color************
Me.TimerInterval = 0
'*****************
Me.cmdReportExpireLicenseMessage.Visible = False
Me.ReportNotifications_Label.Visible = False
End If

If intCount2 > 0 Then
Me.cmdReportAppraisalDateMessage.Visible = True
Me.ReportNotifications_Label.Visible = True
'Timer for Color***********
Me.TimerInterval = 300
'*****************
Else
'Timer for Color************
Me.TimerInterval = 0
'*****************
Me.cmdReportAppraisalDateMessage.Visible = False
Me.ReportNotifications_Label.Visible = False
End If

If intCount3 > 0 Then
Me.cmdreportAssessmentDueDateMessage.Visible = True
Me.ReportNotifications_Label.Visible = True
'Timer for Color***********
Me.TimerInterval = 300
'*****************
Else
'Timer for Color************
Me.TimerInterval = 0
'*****************
Me.cmdreportAssessmentDueDateMessage.Visible =False
Me.ReportNotifications_Label.Visible = False
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:

Resume Exit_Form_Open
End Sub
.
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ON FORM ACTIVATE 1
hh:mm:ss on a timer 2
hiding form 10
VBA HELP PLEASE!!!! 2
Cancel timer event if criteria not met 1
Timer event on switchboard 1
Timer Issue 2
Dcount Syntax Problem 5

Top