Relathionships for invoicing

[Guest]

I have a database invoice that I have spent over a year creating. So far I
can use it but not the way I want to. I have the following fields that I
manually enter the data for each invocie.

Item Number, Qty. Product Name, Price Each,

I would like to setup a relationship so that when I key in the item number,
the product name and price each box will automatically fill in.

I have the invoice table created and have created a new table with all the
products, I just can not get them to 'grab' the information and put it in the
invoice form.

Can anybody help me???????

 

[Guest]

tx sales:

I have attached modified code from an application I wrote which will answer
your question. The answer to the question has nothing to do with
relationships.

My objects:

Invoice form - feInvoice
Product subform in Invoice form - fseProduct
Product field (combo box) in Product subform - ProductNo
Table where product data is stored - tb_tlkpProduct

I have a product field in the subform which stores the Product # selected by
the user and its width is 1/4". It is bound and is only for the purpose of
selecting and storing the product # sold. I then poplulate the other
controls (Price, Quantity, Description) with the routine I included below.

Something to note about my design that may be different from your design -
my users needed the ability to overwrite the stored description to create a
custom description if they so chose, your application may not have this
requirement and thus you may not need a Description field.

Instead, you can manipulate your ProductNo combo box to show the
description, but yet store the selected ProductNo by setting the Row Source
Column Width to zero for ProductNo.

Just as you see in the procedure below, you will need to apply this code to
the 'AfterUpdate' event of your Product # combo box. Anytime a user selects
a new product the other fields will update.

Seth Schwarm


Private Sub ProductNo_AfterUpdate()

'Dimension your variable to store looked up values
Dim curBasePrice As Currency, strDesc As String

'Look up your desired values and store those values in the variables you
defined above
curBasePrice = DLookup("[Price]", "tb_tlkpProduct", "[ProductNo] =
Forms!feInvoice!ProductSubform.Form!ProductNo")

strDesc = DLookup("[Description]", "tb_tlkpProduct", "[ProductNo] =
Forms!feInvoice!ProductSubform.Form!ProductNo")

'*****************************************************
'update subform fields with the looked up values you stored in the variables
Me!Price = curBasePrice

Me!Quantity = 0

Me!Description = strDesc

End Sub