08-13-13, 09:17 #1Registered User
- Join Date
- Aug 2013
Unanswered: Best Event to trigger code for rowsource
Those able to help,
I have a Form/Subform setup in which the user selects from a combo box on the parent form. This selection on the main form then determines the options available to choose from in the subform's combo box (which feeds/reads from the junction table).
Specifically, if the user enters the caliber of a piece of ammunition, the user is then limited in their choices as to which training courses that ammunition can be associated with, ie, a pistol round cannot be used in a rifle course.
I used the following code in the Current Event:
Private Sub Form_Current()
'As individual records in this form are scrolled through the combobox rowsource changes based on the caliber selected in the parent form.
Form_sfrmAmmo_CoF.cboCourse.RowSource = _
"SELECT DISTINCT tblCoF.ID, tblCoF.Course " _
& "FROM tblCoF INNER JOIN (tblCalibers INNER JOIN (tblAmmo INNER JOIN tblAmmo_CoF ON tblAmmo.ID = tblAmmo_CoF.tblAmmoID) " _
& "ON tblCalibers.ID = tblAmmo.tblCallibersID) ON tblCoF.ID = tblAmmo_CoF.tblCoFID " _
& "WHERE tblCalibers.ID=" & Me.cboCaliber & ";"
I use Inner Joins to get from my 'Calibers' table to my 'Course of Fire' table, which works exactly as I want it to. The problem that I'm having is when already-entered records are being scrolled through, the top record in the sub-form is blank whenever there is a transition from one caliber to another. For example, if I'm on a 9mm Caliber, my options are BPMC and PPC; but when I scroll to the next record I get to a 5.56mm Caliber, and my options become blank, and CRC. The blank should be BRMC. I believe the information is there, but for some reason it's not showing up. If I click on any other record within the subform the data that had bin 'invisible' appears as it should. In case it helps, I've attached a screen shot of the example above, after I've changed records.
I assumed that the Current Event would trigger the VBA code every time a Parent Form's record is incremented/decremented, but this problem makes me think otherwise.
Any advise would be greatly appreciated. Thank you for any help.
08-13-13, 09:34 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
if it were me
then I;d have a table for calibre of ammunition
I'd use that in an unbound combo box
when the user selects a value for the combo, then I'd use that to populate the course combo box
that means code in the combo's on click event. well actually I'd probably use a list box as opposed to a combo....I'd rather be riding on the Tiger 800 or the Norton
08-13-13, 14:22 #3Registered User
- Join Date
- Aug 2013
I have a caliber (one) table related to the ammunition (many) table, which is what feeds the combo box on the parent form for the caliber selection. Each row in the child table is a record from the Ammo_CoF junction table (CoF stands for Course of Fire). So, if I have a 9mm piece of ammunition it has two junction table records, one for the BPMC, and one for the PPC. Same with the 5.56mm piece of ammo, one for BRMC, and one for CRC. The problem isn't in entering date into records; everything happens as it should. The problem is when I'm scrolling through records that already exist. If I click the next-record arrow on the parent form, I expect the child table to visually update from the junction table. It does, most of the time. The screenshot in the original post is the result of transitioning from record six to record seven. Notice the top record in the child table is blank - it should not be, as there is data in the junction table for that very record. If I click any other record in the child table though, the information appears as it should. Is there a way to set the focus or select the 'new record' through VBA? I think that would solve the problem.