Creating an invoice - very basic questions!

  • Thread starter Thread starter TLC
  • Start date Start date
T

TLC

I'm creating a basic retail invoice. I have 2 tables -
tblSales (for the bulk of the invoice, contains customer
info and such) and tblLineItems (for just the products
purchased on that invoice).
Now it's time to create the invoice form. I know I'm going
to set up a subform of tblLineItems in the main tblSales
form.
But...
I need to bind a field in tblLineItems to reflect the
current invoice number, which is an autonumber in
tblSales. It obviously doesn't need to show up on the
subform, but I need it to tie the tables together.
How do I create a field on my subform that will capture
the value in my main form and populate the table with it?
Furthermore, my relationship between the 2 tables is going
to be the invoice number, but it's an autonumber on
tblSales. Won't this be a problem since it won't be an
autonumber data type in tblLineItems?
Small, easy to understand words would be appreciated -
thanks!
 
Your tables need to be:
TblSale
SaleID
SaleDate
CustomerID
etc

TblLineItem
LineItemID
SaleID
ItemDesc
Quantity
PriceEach
DisCount (If Applicable)

Use the form wizard and create a form starting with it based on TblSale then
continue by basing it also on TblLineItem. The wizard then will ask you if you
want to build a form/subform system and answer Yes. When the form is completed,
open the main form in design view and you will see the subform control as a
white box. Select the subform control, open properties, go to the data tab and
make sure the LinkMaster and LinkChild properties are set to SaleID. Each time
you add a lineitem now, Access will automatically add the SaleID to the subform
for you because of the LinkMaster and LinkChild properties.
 
TLC,

Make sure that you have the tables linked in the Relationships window. Go To
"Tools / Relationships " and add the two tables to the window. After you've
done that, drag the SaleID field from TblLineItem to TblSale.

Also, don't forget to make LineItemID an autonumber also.
 
Back
Top