Change toggle based on data - current record

[Guest]

I have a current post going for this but I think I may have exhausted my
expert and it's getting a bit confusing.

What I have is a Main Form. In the detail is an option group (toggle
buttons) to go to the various tabs which each have a subform. Each subform
has a unique primary key but is also linked to the Main Form by Record_ID.

I need the toggle buttons to appear with red font if the particular Record
in the main form that the user is on has data in the subform that the toggle
button goes to.

The code so far is below but what it does is once I click on the toggle
button for the first time, it turns red and stays red no matter what record I
go to. I believe that it is thinking that there is data in the subtable (vs.
the specific record). I think I need some criteria in the DCount but I can't
figure out what! I have tried all sorts of things with the Record_ID and the
subtable ID's to no avail. I also think I may need a Refresh or Requery?

Any help you can give is much appreciated!

Private Sub Toggle_Tabs_Click()
Dim x As Integer
x = 0
Select Case Toggle_Tabs.Value
Case 1
Me!TABS.Value = 0
x = Nz(DCount("Aband_ID", "Subtbl_Abandonments"), 0)
If x > 0 Then
Me.Tog_Abd.ForeColor = vbRed
Else
Me.Tog_Abd.ForeColor = vbBlack
End If
Case 2
Me!TABS.Value = 1
x = Nz(DCount("D13_ID", "Subtbl_D13_MAIN"), 0)
If x > 0 Then
Me.Tog_D13.ForeColor = vbRed
Else
Me.Tog_D13.ForeColor = vbBlack
End If

etc......

End Select
End Sub

 

[Carl Rapson]

I have a current post going for this but I think I may have exhausted my
expert and it's getting a bit confusing.

What I have is a Main Form. In the detail is an option group (toggle
buttons) to go to the various tabs which each have a subform. Each
subform
has a unique primary key but is also linked to the Main Form by Record_ID.

I need the toggle buttons to appear with red font if the particular Record
in the main form that the user is on has data in the subform that the
toggle
button goes to.

The code so far is below but what it does is once I click on the toggle
button for the first time, it turns red and stays red no matter what
record I
go to. I believe that it is thinking that there is data in the subtable
(vs.
the specific record). I think I need some criteria in the DCount but I
can't
figure out what! I have tried all sorts of things with the Record_ID and
the
subtable ID's to no avail. I also think I may need a Refresh or Requery?

Any help you can give is much appreciated!

Private Sub Toggle_Tabs_Click()
Dim x As Integer
x = 0
Select Case Toggle_Tabs.Value
Case 1
Me!TABS.Value = 0
x = Nz(DCount("Aband_ID", "Subtbl_Abandonments"), 0)
If x > 0 Then
Me.Tog_Abd.ForeColor = vbRed
Else
Me.Tog_Abd.ForeColor = vbBlack
End If
Case 2
Me!TABS.Value = 1
x = Nz(DCount("D13_ID", "Subtbl_D13_MAIN"), 0)
If x > 0 Then
Me.Tog_D13.ForeColor = vbRed
Else
Me.Tog_D13.ForeColor = vbBlack
End If

etc......

End Select
End Sub

You're right, you need to qualify the DCount with the specific criteria
you're interested in. As it is, you're counting the number of records in the
entire table. Have you tried this:

x = Nz(DCount("Aband_ID", "Subtbl_Abandonments", "Record_ID=" &
Me.Record_ID), 0)

Of course, I don't know the actual name of the Record ID field in either
table, so I used a dummy name (RecordID). The string in the quotes should be
the name of the field in the table Subtbl_Abandonments, and the Me.Record_ID
should be the name of the field on your main form.

Carl Rapson

 

[Guest]

Thanks Carl, I inserted this in just as you have it (and yes, it is RecordID
and Record_ID in the Main table/forms) however now, when I click on a toggle
button I can't get to a new tab/subform and nothing turns red! Any other
ideas?

x = Nz(DCount("Aband_ID", "Subtbl_Abandonments", "RecordID=" &
Me.Record_ID), 0)

 

[Carl Rapson]

Are you certain that the names match between the DCount and the table/form?
The way I read it, in the table that the subform is based on the field name
should be RecordID and on your main form the control name should be
Record_ID. Is this correct? Put a breakpoint on the line that contains the
DCount call, then run the form. When the code window opens, open the
Immediate window (Ctrl-G) and enter:

? DCount(...

reproducing the DCount code exactly. What do you get as a result? If you
"hover" the mouse over the Me.Record_ID in the DCount call in the code
window (not the Immediate window), you should see the actual value that is
being used. Are you certain there are records for that value?

As a note, forms and subforms don't have primary keys; tables do. The forms
must be based on a table or query. In the table or query that the form is
based on, is the field name RecordID or Record_ID? In the main form and the
subforms, what is the name of the control that is bound to the record id
field of the underlying table?

Lastly, when you say "can't get to", what does that mean? Is the tab control
disabled or locked? What happens when you click on each tab?

Carl Rapson

 

[Guest]

My mistake Carl - something actually must have happened to my database. I
deleted the whole thing, re-did it and it is working great with your code but
I still seem to be missing something.

Below is how each Case looks. What is happening now is that the button does
only show red if there is data on the subform, AFTER I click on the toggle
button. And, if I go to a different record, the subform that had data
previously, will still have a red font toggle button until I click it again
(then it turns black).

Is there a way to make this work so that when I go to a new record, either
by scrolling, or searching that the correct toggle buttons will appear red or
black for the appropriate Record_ID (Frm_MAIN which has a record source of
Tbl_MAIN) without having to click first?

Thanks a lot!

Case 1
Me!TABS.Value = 0
x = Nz(DCount("Aband_ID", "Qry_Abandonments", "RecordID=" & Me.Record_ID),
0)
If x > 0 Then
Me.Tog_Abd.ForeColor = vbRed
Else
Me.Tog_Abd.ForeColor = vbBlack
End If

 

[Carl Rapson]

My mistake Carl - something actually must have happened to my database. I
deleted the whole thing, re-did it and it is working great with your code
but
I still seem to be missing something.

Below is how each Case looks. What is happening now is that the button
does
only show red if there is data on the subform, AFTER I click on the toggle
button. And, if I go to a different record, the subform that had data
previously, will still have a red font toggle button until I click it
again
(then it turns black).

Is there a way to make this work so that when I go to a new record, either
by scrolling, or searching that the correct toggle buttons will appear red
or
black for the appropriate Record_ID (Frm_MAIN which has a record source of
Tbl_MAIN) without having to click first?

Thanks a lot!

Case 1
Me!TABS.Value = 0
x = Nz(DCount("Aband_ID", "Qry_Abandonments", "RecordID=" &
Me.Record_ID),
0)
If x > 0 Then
Me.Tog_Abd.ForeColor = vbRed
Else
Me.Tog_Abd.ForeColor = vbBlack
End If

<snipped remainder>

Try putting code in the Form_Current event to set the button color.

Carl Rapson