Automatic fill of fields from another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know this shouldn't be hard, but I have worked on this for 3 days. This has
to be the most common use of a database, to lookup prices and desctriptions
of items from an inventory and have them entered onto the invoice. I have a
subform based on a table(invoicedetail) that I want to automaticaly fill in
two fields(Item)(Cost) when I choose a (partno) from a combobox that is
linked to my Inventory table. I have tried every suggestion posted that is
similar to my situation and can't get it to work.
Thank you

(e-mail address removed)
 
I know this shouldn't be hard, but I have worked on this for 3 days. This has
to be the most common use of a database, to lookup prices and desctriptions
of items from an inventory and have them entered onto the invoice. I have a
subform based on a table(invoicedetail) that I want to automaticaly fill in
two fields(Item)(Cost) when I choose a (partno) from a combobox that is
linked to my Inventory table. I have tried every suggestion posted that is
similar to my situation and can't get it to work.
Thank you
Click to expand...

You should be able to put some VBA code in the combo box's AfterUpdate
event. You should NOT be storing the item name in the invoice table
(when you print the invoice, use a Query linking to the Inventory
table to pick up the name); but you probably should store the current
cost since the cost may change in the future and you don't want that
changing your invoices.

Assuming that you have textboxes txtItem and txtCost, and that the
combo box bound to the partno is named cboPartno; and that it is based
on a query containing the Partno, Item and Cost, you can:

- Set the Control Source of txtItem to

=cboPartno.Column(1)

The Column property is zero based so this will be the item name

- View the Properties of cboPartno; click the ... icon by the
AfterUpdate event property and invoke the Code Builder. Access will
give you a Sub and End Sub line; edit the code to

Private Sub cboPartno_AfterUpdate()
Me!txtCost = cboPartno.Column(2)
End Sub


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thanks, John
I finally got it to work. I had tryed that before but I had one of the field
names wrong. One other thing I would like this to do is to subtract the
quantities off of my inventory table when I enter them into the invoices. I
haven't researched this yet, but if you could help with this too, that would
be great.
 
Back
Top