Composite Key in a ComboBox - Bound Columns

[Elana]

I am developing a property insurance management system. We have a table called tblBuildings that has a composite primary key of LocNo and BldgNo fields. I have a table called tblPremiums where I will place calculated premiums for a particular building. tblPremiums has a primary key of called PremID (autonumber). It also has a foreign composite key using LocNo and BldgNo to refer to the appropriate building in tblBuildings.

I have created an autolookup query so when the user places the appropriate LocNo and BldgNo in the tblPremium record, the address of the building automatically fills in. Works fine, but...

Here's my problem. On my data entry form, I want the user to be able to select the appropriate building from a combo box and have the information fill in appropriately. This will only work after I've manually placed bldgno in the BldgNo field. I've created a lookup query that shows the LocNo and BldgNo, but it only allows for one bound column (LocNo) when you select the record and it will only autolookup the first building (BldgNo 1) of any chosen location.

How do I get the bound column to store the composite key?

I hope this is clear enough for someone to give me some direction. Many thanks for any help you can provide.

 

[jstutz]

Are the two fields you are using for Building and Location a consistant length? If so, since your update works if you set the values of the two fields manually, instead of trying to auto-update the address from the combobox, use the combo box to populate the two key fields and THEN call the AfterUpdate event of the keyfields to populate the address. Does this make sense?

I did a little test and this works. Here's the code I used in the AfterUpdate of the combobox to fill in the key fields:

----------------
Me.id = Left(Me.Combo5.Column(0), 1)
Me.id2 = Right(Me.Combo5.Column(0), 2)

Call id2_AfterUpdate
----------------

In my example, id was a single digit field and id2 had two digits.

Even if they aren't a consistant length, you could probably figure out a way to split them if you are using a "-" between the two fields.

Hope this help... please let me know if it does b/c I'm really curious now!

Thanks!
js

 

[Elana]

That got me on the right track. With a few revisions, it works great. Thanks for taking the time to help me out!