Referential Fields 1

[slaman]

I am trying to create a subform with the following fields

AutoNumberID
CorporationID
PersonID
Position
DateHeld

Now I have this form related to the "Corporations" database using a One-to-Many relationship and same with the "Persons" database. Now, I can get the CorporationID working fine (as soon as the form for the Corporation ID is opened, the subform automatically creates the appropriate CorporationID link. I am having problems with the PersonID. I want the user to be able to pull down from a Combo list (MUST choose from the list) displaying a Field in the "Persons" database. I want to be able to DLookup that name in the Persons database, and have the subform' PersonID point to the matching record. It seems fairly simple and straightforward but I can't seem to get it to work. Here is what I've tried:

Since I can get the correct CorporationID, I will only explain the PersonID field. I tried

SELECT subformName.*, Person.* both with and without the Person database. I've tried binding the pulldown list to the "Field" in Person and unbinding it. I've made a text box that is linked to Person ID in both Person and in subformName. Ive tried DLookups both in code and in default value.

I dont know what the correct method for doing this is. Please help me out!

 

[JoeMiller]

Make a query that pulls the data you need from the person table and has the first field being the PersonID, and the second field being the Name. Now go into your subform and make a combo box using the wizard. Have the wizard use the query you made to select the names. The wizard will ask you what field to store (PersonID) and ask you if you want to hide the key field (PersonID) select yes. Now tell the wizard to store the value from the combo box in the field on your subform that should hold the PersonID. You should be all set now, having a combo box to select the name and have the name show up when you pull the record up later.

HTH Joe Miller
joe.miller@flotech.net

 

[slaman]

Well done... I don't know why I make things so complicated for myself when Access has all these neato pre-defined wizards =P