Combo Box Mystery

[JimS]

I have a form with half a dozen subforms (each on its own tab....) I have
combo box on the main form whose record source is different from the form's
record source. When I select an item from the combo box (cbFilterforaMICAP),
here's the code that's fired. Essentially, it filters all the subform
controls for the "MICAP" selected in the combo box.

Private Sub cbFilterforaMICAP_AfterUpdate()
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "Projectnbr= '" & cbFilterforaMICAP.Column(3) & "'"
If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
Dim lngMICAPID As Long
lngMICAPID = cbFilterforaMICAP.Column(5)
Me.sfmProjectBudgetTab.Form.Filter = "CPMICAPID=" & lngMICAPID
Me.sfmProjectBudgetTab.Form.FilterOn = True
Me.sfmPOLinesTab.Form.Filter = "POMICAPID=" & lngMICAPID
Me.sfmPOLinesTab.Form.FilterOn = True
Me.sfmStaffAugtab.Form.Filter = "MICAP='" & cbFilterforaMICAP.Column(1)
& "'"
Me.sfmStaffAugtab.Form.FilterOn = True
Me.sfmInventoryDetailTab.Form.Filter = "MICAP='" &
cbFilterforaMICAP.Column(1) & "'"
Me.sfmInventoryDetailTab.Form.FilterOn = True
Me.FilterOn = True
' Commented on 06/23/08 by JS
#If False Then
Me.sfmAllMatreqIssuesTab.Form.Filter = ""
Me.sfmAllMatreqIssuesTab.Form.FilterOn = True
Me.sfmAllLaborTab.Form.Filter = ""
Me.sfmAllLaborTab.Form.FilterOn = True

Me.sfmAllPCardTab.Form.Filter = ""
Me.sfmAllPCardTab.Form.FilterOn = True
#End If
'cbFilterforaMICAP = Null
End Sub

Unfortunately, the combo box works, then mysteriously changes value, and
works for that new value. I don't know why that new value shows up, it's not
the first value on the list, though it may be the first MICAP on the newly
current form record.

I commented out the "ON CURRENT" event, and nothing changed, so I've ruled
that out.

Ideas on debugging strategy?

 

[Jeanette Cunningham]

Jim,
your code suggests that your combo filters the main form for the record that
matches the value in the combo.
I suggest that first you comment out all the code for the subforms.
Check that the combo is definitely unbound and that it filters the main form
correctly.
Add back in the code to filter one of the subforms - if that works OK, then
comment out this subform and test the next subform.

With this many subforms on a tab control, I suggest that you leave the
source object for each subform empty until the user clicks on that page of
the subform.
This means that the form only has to load the records for the currently
active subform.
When user clicks a different page of the subform, you can then set the
recordsource for that subform.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[JimS]

Jeanette, what a world we live in when my humble query is answered by someone
13 time zones away!

The main form is moving to the correct record. It's only the subforms that
end up being misdirected. Strangely, they're all misdirected to the same key,
predictably.

This combo box is not the only way to "find" a Project on the main form's
record source. There's another combo box that can also be used, which doesn't
affect the subforms.

I searched the entire project and found cbFilterforaMICAP is only assigned
to a value in one place...this event procedure. cbFilterforaMICAP is unbound.
Its recordsource is NOT the same recordsource as the main form.

Thanks again.

 

[Jeanette Cunningham]

Jim,
if the form and subform are linked through the link master and child fields,
when you filter the main to a particular micap, the subform will
automatically show all the records for the micap selected on the main form.
Check the link master and child fields for the form and subform - they would
be linked using the Primary key for the main form which is the Foreign key
for the subform.
You shouldn't need to set the subform's filter, the link master and child
fields will do it for you.
Get it working with just one subform at first.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[JimS]

Jeanette, I've been fascinated with that notion for some time, but the main
form recordsource doesn't have a "MICAP" field in it. The main form's record
source is essentially the Project Master table. Four out of the 6 subforms
have a MICAP field I can filter for. The other two do not (their expenses are
project-related. MICAP is essentially an asset tag for a piece of equipment.
A project might address a dozen MICAPs...and a MICAP might be found in a
dozen projects...)

Can I use the combo box as a master key? How would I specify "ALL"?

 

[Jeanette Cunningham]

Jim,
as the main form's record source is the project master table - set up the
combo so that after you make a choice in the combo, the combo's bound column
(the hidden one) provides the projectID and the main form moves to the
project which matches the projectID selected in the combo.
Make the link master and child fields the projectID - projectID needs to be
the foreign key for the subform.

If the above is too far away from what you really want, there is a
completely different way to filter a form and show the results in a subform.
This way is described with a sample database on this link

http://allenbrowne.com/ser-62.html



Jeanette Cunningham -- Melbourne Victoria Australia