 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / May 2007Change toggle based on data - current record
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 >I have a current post going for this but I think I may have exhausted my > expert and it's getting a bit confusing. [quoted text clipped - 47 lines] > 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 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) > >I have a current post going for this but I think I may have exhausted my > > expert and it's getting a bit confusing. [quoted text clipped - 61 lines] > > 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 > Thanks Carl, I inserted this in just as you have it (and yes, it is > RecordID [quoted text clipped - 79 lines] >> >> 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 > 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 [quoted text clipped - 104 lines] > >> > >> 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 [quoted text clipped - 27 lines] > Me.Tog_Abd.ForeColor = vbBlack > End If <snipped remainder> Try putting code in the Form_Current event to set the button color. Carl Rapson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.