Multi Value Fields and Associated Fields

Question

Hi

There's something going on that I just cannot get to the bottom of.  I have a table to identify which options are available for each model vehicle and the production hours for each.  The vehicle model field is a multi value field - each option can be available for more than one model.

The main form of the database allows for the selection of the model from a combo box and then only makes available the options associated with that model to 10 combo boxes - the production hours are also pulled through. All of that works just fine.

But here's what else is happening; the option combo fields are also available for free typing for the purpose of unusual requests and I found yesterday that if I free type an option that is available to a different model but not this one, it still pulls through the hours from that option - but the option is not available to this model so how is Access managing to get the information?

Thanks for any help.

---

Chris | UK

Answer 1

The production hours attribute is apparently functionally determined by the option.  This results from the database's logical model.  If you could post details of the model we might be able to comment more categorically.  The best way to do this is to post an image of the relationships window, clearly set out so that the tables and relationship types between them are readily apparent, and all column names for each table are visible.

---

Ken Sheridan, Stafford, England

Answer 2

Hi Ken

I think I may have twigged how the information is being populated and I feel a bit daft now.  I have attached an 'After update' event to each option combo box to run a DLookup for the hours:

Private Sub cboCOpt1_AfterUpdate()
    Me.[COpt1BHrs] = DLookup("[BHrs]", "[tblConvOptions]", _
        "[Option]='" & Me.[cboCOpt1] & "'")
    Me.[COpt1EHrs] = DLookup("[EHrs]", "[tblConvOptions]", _
        "[Option]='" & Me.[cboCOpt1] & "'")
Me.COpt1BHrs.Requery
Me.COpt1EHrs.Requery
Me.COpt1THrs.Requery
End Sub

This obviously just looks for an option match and has no regard for the vehicle model it is associated to.  Completely obvious when you think about it.

So, sorry for wasting your time, but I found my answer.  Just off to look at this again to find a different way of doing it.

---

Chris | UK