Accessing a different table form the one in the open form. 2

[LuRSH]

Hi Folks!

I'll try to be brief...

I have a database with eigth different tables and relationships stablished among then.

In a specific form I'll be grabing information from typing (user dataentry) and from other two tables, I mean, not the one the form is linked to.

To get the information form that two other tables I'm using combo boxes, and I'm able to store the link field (foreign key) in the destiny table without problem.

So what is the problem? Well when the user edits records using this same form I want to be able tho show him not only one field from the "other" tables but two fields. the code (the one that appears in the combo box) and the description of that code in a second textbox.

Note that I do not want to show a second column in the combo box (this I know how to do - smart, am I not?) I want the description to appear all the time for each record.

Which is the best way to do that? I considered two ways.
One that I know how to: subforms for each "Foreign Description" but I think this would slow down my application.

Or the one that I don't know how to: locate the "Foreign Description" in the "other" tables at run time and just store the value in a text box, just for the screen, updating it every time the record being edited is changed by the user. The question (and it may be a stupid one to more experienced users) how do I search for a field in a table different from the one my form is linked to? Is there something in Access like the "select" command in the old dBase III?....

I NEED details...


Thanks to you all.

 

[jitter]

Well, you could approach it this way. So you are using combo boxes with the foreign key(ID) but you don't want to show the description in the combo box drop down but you do want the description displayed somehow to the user. I would do this:

1. Add a second column to the combo box that has the description

2. Make sure this column is invisible by setting the width of it to 0.

3. Create a text field on the form

4. Set the new text field Control Source to
=[TheComboBoxName].[Column](1)

5. I would lock this field personally since it is controlled by the drop down.

Let me know if this is what you are looking for and if I can be of anymore help. Note that the column index is zero based and that index (1) actually relates to the second column in the combo box drop down.

Hope this helps.....

 

[jett2000]

I'm not quite sure what you are trying to do but you can access a field from another table (not the record source) by using domain functions: dlookup

Do a help on "dlookup". Would this be helpful or is this to simple an answer? Please let me know.

 

[LuRSH]

Thank you, guys!

Jitter, your tip worked just fine and smooth!

Jett2000, I didn't use your tip in this situation (Jitter's tip is really simple...) but it'll help me with another part of my system.

Thank you both again!