Auto Complete a form

[Very Basic User]

Hello,

I have two tables, one contains product information (Code and Name) the
other containes the bulk of infomration. This is variable depending on
situation. What I want to happen, is that I select a code # from a drop down
list. Once the code is selected, it automatically completes the name of the
product, I then just update the variable information. How can I do this in
laymans terms?

 

[Jae]

Hi,
First of all, if your database is well normalized, you probably don't need
to populate those data. But, in any case, if that's what you want to
accomplish, there are several ways of doing that

I.

1. Make your Code# (BTW, do not use '#' as part of field name) into a combo
box.
2. Open the properties of that combo box
3. Make the Row Source Type of that combo box a "Table/query"
4. Put the Row Source as SELECT Code#, Name FROM product_Info_table;
5. go to "After Update" event and put a code that has something like this
me.name = Me.combo_CodeNo.Column(1)

II.

1. Make a textbox/combo box for your code # and call it "txtCodeNo"
2. in the "After Update" event put this code in
me.name = dlookup ("Name", "Product_Info_Table", "CodeNo = " & me.txtCodeNo)

III.

1. Make a combobox for your "Name" field
2. Open the properties of that combo box
3. Make the Row Source Type of that combo box a "Table/query"
4. Put the Row Source as SELECT Code#, Name FROM product_Info_table;
5. Put 2 in "column count" property of the comboxbox
6. Put 0;1 in "column width" property of the comboxbox
7. Put 1 in "List Width" property of the comboxbox


Hopefully these make sense to you...
good luck w/ your developing.

 

[Pat Hartman]

The codeless way to do this is to create a query that joins the main table
with the lookup table. You can select columns from both tables. That
allows you to bind the lookup columns to the recordsource. On your form
when you select something from the look up combo and tab out of it, the
other columns will automagically populate. If you use this method, it is
best to set the locked property of the lookup fields to Yes to prevent
accidental updating.

This method does NOT save the lookup values in your main table. To do that
you would need to use the other method suggested by Jae. But storing the
lookup values is incorrect and violates proper table normalization.

 

[Very Basic User]

Both suggestions are helpful. I appreciate each of your time. I have to
determine if I need to update my main table with the sub level information
that I'll be auto completing. If I want to it sounds like I need to use the
code, if a # is ample for the main table, I'll be good using the query.
--
Thank you for your time!
John

The codeless way to do this is to create a query that joins the main table
with the lookup table. You can select columns from both tables. That
allows you to bind the lookup columns to the recordsource. On your form
when you select something from the look up combo and tab out of it, the
other columns will automagically populate. If you use this method, it is
best to set the locked property of the lookup fields to Yes to prevent
accidental updating.

This method does NOT save the lookup values in your main table. To do that
you would need to use the other method suggested by Jae. But storing the
lookup values is incorrect and violates proper table normalization.