Any field in the criteria must be a field in the domain - in this case, your
Qry_SCVF_Main. So you want to compare the field in your query, which I am
guessing is Qry_SCVF_Main.recordID with your subform recordID
x = Nz(DCount("SCVF_ID", "Qry_SCVF_MAIN", "Qry_SCFV_MAIN= "&
Me!Subfrm_SCVF_MAIN.Form![Record_ID]),0)
Although I am not sure what this query is for. Is this the query used to
load the main form? If so, how are your subforms populated? On the other
hand, if you have a query for each subform, then you don't need criteria to
compare with the main form's ID. If there are no records, then you would
get a zero result without any criteria.
As an example, suppose I had a main form for my Active customers. The query
would just select those customers that I have designated as "active". On my
tabbed form, the first page shows a subform with Open Orders for the
mainform's customers. The secondpage shows their buying habits, 3rd page,
something else. For each page I would need a query tied to the main form's
CustomerID. So in this example, if my query for OpenOrders was null or
empty, then my dCount would be
x=nz(dcount("OrderID", "qryOpenOrders"),0) and would return zero, because
there are no openorders. No criteria needed.
Damon
Carrie said:
Okay - clearly this is totally beyond me. I have tried all sorts of
criteria
to try and make this recognize that if the record I am on contains data in
the subform, then the toggle button should be red. This is what each of
my
cases looks like:
Case 1
Me!TABS.Value = 0
x = Nz(DCount("SCVF_ID", "Qry_SCVF_MAIN",
"Subfrm_SCVF_MAIN![Record_ID]='Frm_MAIN!Record_ID'"), 0)
If x > 0 Then
Me.Tog_SCVF.ForeColor = vbRed
Else
Me.Tog_SCVF.ForeColor = vbBlack
End If
Am I even on the right track? Thanks!
Damon Heron said:
The where part is the third part of the dcount function - like this:
x = Nz(DCount("SomeID", "Sometable or query", "Fieldname= " & [txtbox1]),
0)
If you use the help section in the VB Window, you should find complete
help
with the DCount function and examples.
Damon
Damon - you're awesome! I have used this code and it works great
except
that
it seems to be thinking that as long as any ID > 0 then it should be
red
(which I'm sure is the where clause you're talking about. Each toggle
button
also only seems to change color once I click it for the first time
(maybe
this will correct itself once I figure out the where clause?)
I couldn't find anything in Access by searching DCount and have been
searching on this board but am not finding many posts that contain a
"where".
I was thinking that the where should come after each x =
Nz(DCount.......
I was also thinking that it might have something to do with my
Record_ID
in
the subform equalling my Record_ID on the form but I'm not sure. Do
you
know
of any good posts that might help me out?
:
Small error in Case 2 - the toggle should be 13, not 12.
Use an option frame and select toggles as the display choice. Set
your
tabctl to none.
Each subform has a record source, either a table or query. Use
dcount
to
determine if there are records present. Check help for dcount
because
I
left off the where condition in my example.
On the click event of the frame,
Private Sub Frame9_Click()
dim x as integer
x=0
Select Case Frame9.Value
Case 1
Me!TabCtl5.Value = 0
x = Nz(DCount("SomeID", "Sometable or query"), 0)
If x > 0 Then
Me.Toggle12.ForeColor = vbBlue
Else
Me.Toggle12.ForeColor = vbRed
End If
Case 2
Me!TabCtl5.Value = 1
x = Nz(DCount("SomeotherID", "Someothertable or query"), 0)
If x > 0 Then
Me.Toggle12.ForeColor = vbBlue
Else
Me.Toggle12.ForeColor = vbRed
End If
Case 3
etc.....
Case 4
etc.....
End Select
End Sub
Damon
I have read a bunch of posts on changing tab colors and checked out
some
very
helpful websites however, I'm not sure it's exactly what I want to
do -
and
it seems like a lot of work.
I currently have Frm_MAIN. The Detail has 5 tabs which each have a
subform.
They are all linked to the header data by Record_ID. I need to
keep
the
header info at the top of each page.
What I need to do is have each tab where the Record_ID is not null
in
that
particular subform turn the font a different color. So, if three
tabs
contain data those would have red text and the other two would be
standard
black.
From everything I've read, I think I should make the tab control
invisible
and no buttons and then create command buttons to go to each tab?
I
am
not
really clear how to get them to go to each tab (vs. open a form).
Then I
need some code to change the font color where the subform contains
data -
is
that possible?
