How to select a record from a different table based on info entered

**PianoMan64** · Nov 3 '08, 10:18 AM

Originally posted by crazyhouse

I have 2 tables - tblCertData and tblHL
tblCertData includes multiple fields, but the ones that matter at this point are
[1HL] (number field) and [Matl] (text field).

tblHL includes fields [HL] (number field) and [MatlDesc] (text field)

On my form that writes all of its data to tblCertData there is a dropdown box for [1HL]. The info in this dropdown box comes from tblHL [HL].

I would like to have an event run on the afterupdate of [1HL] that would update the fieid [Matl] from the record (from tblHL) that the [HL] was chosen from.

Maybe that got confusing after you read it, I am sorry, I am trying to give as much info as possible.

I am certain that this should require a Select statement, but please dont try to make me guess where to put it.

The statement

[Matl] = SELECT [MatlDesc] FROM [tblHL] WHERE me.[1HL] = [HL]

probably gives you more information from what i would like to do than my confusing ramble earlier. But as I am sure that you have already noticed from the above example........ ..I dont know anything about SELECT statements, lol.

If i had the statement correct.....i dont even know where to put it. Please Help. As always your help is much appreciated.

Hey crazyhouse:

If I understand what you've said, here is what you need to do:

1. Right-click on your dropdown box that you want this list of options to appear.
2. make sure you click on the ALL tab at the top of the properties window.
3. In the Control Source field, type the name of the field that will store the value for tblCertData (I think it is 1HL) that is coming from the tblHL table.
4. In the Row Source field on the far right-hand side of the properties window, there is a ... button, click that.
5. you will see the list of table and queries that you have available.
6. select the tblHL table from the list, and click Add and then close.
7. Drag each field that you want to appear from the tblHL table on to the design area where it says fields making sure that whatever field is the Primary Key for the tblHL table is in the first column.
8. Once all that is filled in with all the items that you want to display, then click the close window button in the upper-right hand corner, and it will ask you if you want to save changes, just simply click on YES. and it will right the SELECT Statement into the Row Source field.
9. in the Column Count field, set the number of Columns that are being returned from the previous step and put that number into the Column Count field.
10. If you would like the field names to display, then the next property ( Column Heads) just simply set that to Yes. if you don't want them to display, then set it to No.
11. The next field is Column Widths. This is to set the width if each column that is being displayed from the table you defined in the row source field. by default, they are set to 1in.
12. The next and final field that you need to set, is to tell which column number from the columns being displayed from the tblHL table you want to store in tblCertData table for the value that you put into Control Source.
13. then close the properties window and see if that is what you want.

Hope that helps,

Joe P.

How to select a record from a different table based on info entered

How to select a record from a different table based on info entered

Comment