Dependent Combo Box Problem

[JoshLewis]

Hey all, first time poster. I've only been using Access and VBA for about a week, so pardon my inexperience. I am trying to synchronize two combo boxes on a form. One combo box is used to select a name of an athlete (cboAthleteID) and the other is to select a corresponding injury (cboInjuryID). Athletes can have any number of injuries, but an injury can only be associated with one athlete. Using the code below, I have everything working the way I'd like it to except for one issue:

The cboInjuryID value I choose on the initial record carries over when I switch to the next record. If I start a new record, the cboInjuryID value I chose on the previous record will automatically carry over. The cboAthleteID value does not do this. I would like for neither to do that.



Private Sub cboAthleteID_AfterUpdate()
Me.cboInjuryID = Null
Me.cboInjuryID.Requery
Me.cboInjuryID = Me.cboInjuryID.ItemData(0)
End Sub

How do I prevent this from occuring? I appreciate any help! If I need to post a screenshot or more info, let me know.

[June7]

Would you like to post db for analysis? Follow instructions at bottom of my post.

I don't understand how injury can be associated with only one athlete. Can't more than one athlete have a sprained ankle?

[JoshLewis]

Yes, more than one athlete can have a sprained ankle but in terms of this database, one "injury" refers to one incident (athlete gets hurt, an evaluation is performed, the entire evaluation is saved as the record, not just the type of injury incurred).

I have 3 tables (one with athlete information, one with injury information, and one with treatment information). The goal is to link Injury records with an Athlete's record when injuries occur. Furthermore, to link the treatment of an injury to both the Injury record and Athlete record.

[June7]

cboInjuryID is not bound to field. Set ControlSource to InjuryID field. However, I don't think this form design is appropriate.

Don't need AthleteID in tblTreatments.

I suggest you use a form/subform/subsubform arrangement. Main form bound to tblAthletes, subform bound to tblInuries, subsubform bound to tblTreatments. Or tblInjuries and tblTreatments forms can be side-by-side subforms with this technique http://www.fmsinc.com/microsoftacces...edsubforms.asp

tblTreatments is not normalized structure. You might have issues with filtering records. The multiple exercise fields really should be a dependent child table.

Tables and queries have a limit of 255 fields. I counted 192 fields for all 3 tables. Will be a big query with all 3 tables. If tblInjuries and tblTreatments have a 1-to-1 relationship and every injury will have treatment then really could be one table.


Data looks real so I have deleted the attachment from your post.

[JoshLewis]

Great, thank you for all the advice. I'll see what I can do using form/subform/subsubform arrangement and create a child table for exercises in the next few days.

Every injury will have treatment(s), but there will be instances where there will be treatment without an evaluated injury (in the event an athlete just gets taped as prevention, or uses a bag of ice after pitching, etc). Still recommend one table, or leave as two?

[June7]

Okay, that changes the scenario. Then would need the AthleteID in tblTreatments. Also probably want option to open the Treatments form independently.