Auto Fill Field

[fusion786]

Hi.

I have two tables, [invoice details] and [supplier details].
the supplier details table contains 2 fields, supplier code and supplier name. The invoice details contains many fields including supplier code and supplier name fields.

I use a form based on the invoice details table.

I have keyed in all the data in the supplier details table.

What I would like to be able to do is when I key in the supplier code, the supplier name which is the next field to be filled in automatically.


I can not use a combo box as I have a very big list of suppliers.

Can some one please show me how to do this?

Thanks

 

[mikehoot]

Create a qry based on your invoice details table.
In this qry, include the field [suppliername] from [tblsupplier]. Make sure a relationship exists between these tables.

Now, set the source for the form to this qry. From the field list, select the field [suppliername] from supplier table. This should be just a text box. Make this control locked.

You should now see this autocomplete when you enter a supplier code, and you will be able to tab straight past.

B-)

 

[Robertd]

Hi fusion786,


I don't quite understand your structure here
as you seem to repeat unneeded data.

but if you base the Invoice details on a query, in the query join the two tables together on supplier code and
have
invoiceDetails.SupplierCode, CustomerName.CustomerName, InvoiceDetails.OtherFields.....

this assumes that you have a relationship between Customers and suppliers that links the rows in InvoiceDetails to Supplier. when you fill in the Supplier Code the Supplier Name will appear, now if i understand your situation you actually want to include the Supplier Name in the Invoice Detail Table, this isn't the best way to do it.

The above will allow you to display the name after the user types in the Supplier Code.

If you must store the Supplier Name in the Invoice Table you could use a Dlookup function, but this means you will have use a form and use the afterupdate event for the supplier code textbox. there are problems here if the user actually enters a wrong non existent Supplier Code then you will have trouble as you need to write a lot of code to handle all the different situations.

without a clerer picture of exactly what you want to acheive and based on what you have indicated i would re-think the tables and structure of your data base. there is something wrong! if you need to repeat data from two tables in a relational database.




Robert Dwyer
rdwyer@orion-online.com.au