replacing ID with something else (not sure what to call this)

[samiuweb]

hi,
i have two tables, tblContact and tblorganization, linked on a many to one relationship through OrgID (OrgID is foreign key to tblContact).

I have created a subform, which is used in another form, to create a link between another table, tblProject and tblContact (these are many-to-many, hence the use of subforms).

I figured out how the combo box would work, and I can show the OrgID next to the appropriate Contact name (Contact name on a combo box, orgID shows up as textfield). What I can't seem to figure out is how to replace the OrgID with the actual organization name (in the Contact table it shows as the name, not the ID).

I pull the OrgID from the Combo Box (hidden column) using

=Combo2.Column(2)

Maybe I need to replace the ID with the Name in the Combo Box? How do I do this/

Thanks a ton to this forum
Making life much easier, for a good bunch of ppl

pmo

 

[PookaTech]

If I understand you there are two ways to do this.

One, Make the control source for your text box: Dlookup("OrgNameFieldhere","tblOrganization", "[OrgIDFieldName] =" & Combo2.Column(2)) You will need to enter the field name where your org names are and the field name where your orgIDs are where it says to. (The quotations are neccessary) There is a help file on Dlookup if you encounter problems.


Two, change your text box to a combo box. Have the record Source be tblOrgs. The combo box should have two columns the first is orgID the second being the Org Name. Have the orgID be the bound column. IF you set the column widths to 0",1" the orgID will disappear and the OrgName will be left.

Hope this helps,
Pookatech

 

[Pat Hartman]

You don't need to do a DLookup(). You already have the name available in the combo. In the AfterUpdate of the combo just copy the name to an unbound control.

Me.SomeControlName = Me.Combo2.Column(?)

Just replace the ? with the column number of the name field. Remember combo's are zero based arrays so the first column is actually .column(0)

 

[PookaTech]

Wow smoked, looks like Pat has the right answer on this one. I didn't realize you could reference columns in a combo as such. I'm sure this will come in handy soon.

Thanks for the correction Pat,
PookaTech