creating an invoice/invoice details form/subform

I am creating a sales database. I have tables to show
SalesInvoice, InvoiceDetails, Products and others. I am
trying to make a SalesInvoice form with a subform that
reflects the invoice details. The SalesInvoice will have
the typical info, ID, date, total, etc. The invoice will
have linenum, qty, price, color size, etc. My problem is
two fold. Many of the attributes are from the products
table and while others are clearly invoice details. I
have been requested to do this using the wizard and it
does not seem to like more than two sources for creating
forms. In other words, the form works fine for
displaying sales and invoice details, but not the product
information. I do not seem to be able to link the
information. Second part, the boss wants the product
fields to display price and color based on ProdID, but
since I am using a 3rd table, I can't get a lock on that
data.

Any help will be appreciated. Thank you.

Mike,
First, you should not have to use a subreport to accomplish this or the wizard. Base your report off a query. First, create a query with your three tables. You should be able to link your invoice header information to the invoice detail by invoice # (or something similar). Then, you could link your product table to the product ID on the invoice detail. Now, you can pull all the appropriate fields into your query. In the detail section of the report, put all your invoice line detail including the product info. Your report or page header/footer can include the invoice header info and totals. Normally, you will want to print the invoice total only on the last page. Also, you will probably want to print selected invoices, or all for a date range. Both of those items are an entirely different story and I would happy to help when you get to that point.

Hope this helps.

"Mike (AAA- AnotherAccessAmatuer)" wrote:

I am creating a sales database. I have tables to show
SalesInvoice, InvoiceDetails, Products and others. I am
trying to make a SalesInvoice form with a subform that
reflects the invoice details. The SalesInvoice will have
the typical info, ID, date, total, etc. The invoice will
have linenum, qty, price, color size, etc. My problem is
two fold. Many of the attributes are from the products
table and while others are clearly invoice details. I
have been requested to do this using the wizard and it
does not seem to like more than two sources for creating
forms. In other words, the form works fine for
displaying sales and invoice details, but not the product
information. I do not seem to be able to link the
information. Second part, the boss wants the product
fields to display price and color based on ProdID, but
since I am using a 3rd table, I can't get a lock on that
data.

Any help will be appreciated. Thank you.

Thanks for the help. I wasn't clear enough, but I am
trying to create the form that will allow me to enter
this data. I'll try writing a query and base the form
off of that. I need to remember how to pull up the
product info from the entered ProdID.
-----Original Message-----
Mike,
First, you should not have to use a subreport to
accomplish this or the wizard. Base your report off a
query. First, create a query with your three tables.
You should be able to link your invoice header
information to the invoice detail by invoice # (or
something similar). Then, you could link your product
table to the product ID on the invoice detail. Now, you
can pull all the appropriate fields into your query. In
the detail section of the report, put all your invoice
line detail including the product info. Your report or
page header/footer can include the invoice header info
and totals. Normally, you will want to print the invoice
total only on the last page. Also, you will probably
want to print selected invoices, or all for a date
range. Both of those items are an entirely different
story and I would happy to help when you get to that
point.

Hope this helps.

"Mike (AAA- AnotherAccessAmatuer)" wrote:

I am creating a sales database. I have tables to show
SalesInvoice, InvoiceDetails, Products and others. I
am
trying to make a SalesInvoice form with a subform that
reflects the invoice details. The SalesInvoice will
have
the typical info, ID, date, total, etc. The invoice
will
have linenum, qty, price, color size, etc. My problem
is
two fold. Many of the attributes are from the
products
table and while others are clearly invoice details. I
have been requested to do this using the wizard and it
does not seem to like more than two sources for
creating
forms. In other words, the form works fine for
displaying sales and invoice details, but not the
product
information. I do not seem to be able to link the
information. Second part, the boss wants the product
fields to display price and color based on ProdID, but
since I am using a 3rd table, I can't get a lock on
that
data.

Any help will be appreciated. Thank you.

.

Mike,
Sorry about the report/form thing. Anyway, the way I take product info and put into detail lines is to create a combo box for the prod ID. First, your form should be based off the invoice detail table or a query joining the invoice header to invoice detail. Create a combo box that has the record source as Prod ID field. Base the combo box off your product table and include all fields you might want to add to your invoice detail. You can just show the prod ID and description by changing the column widths, or Show flags, on the combo box properties. Then, on the After Update property of the combo box, put something like:
[Description]=Me.NameofComboBox.Column(1)
[UnitofMeasure]=Me.NameofComboBox.Column(2)
[UnitPrice]=Me.NameofComboBox.Column(3)
etc...
The Column(#) represents the columns in your combo box starting at zero (ProdID). You will need to have the Description, UnitOfMeasure and UnitPrice fields in your table or query that is the record source of the form (Invoice detail). This way, the value is saved with that line of detail so changes can be made to price, etc.

Hope this make sense. )


"Mike (AnotherAccessAmatuer)" wrote:

Thanks for the help. I wasn't clear enough, but I am
trying to create the form that will allow me to enter
this data. I'll try writing a query and base the form
off of that. I need to remember how to pull up the
product info from the entered ProdID.
-----Original Message-----
Mike,
First, you should not have to use a subreport to
accomplish this or the wizard. Base your report off a
query. First, create a query with your three tables.
You should be able to link your invoice header
information to the invoice detail by invoice # (or
something similar). Then, you could link your product
table to the product ID on the invoice detail. Now, you
can pull all the appropriate fields into your query. In
the detail section of the report, put all your invoice
line detail including the product info. Your report or
page header/footer can include the invoice header info
and totals. Normally, you will want to print the invoice
total only on the last page. Also, you will probably
want to print selected invoices, or all for a date
range. Both of those items are an entirely different
story and I would happy to help when you get to that
point.

Hope this helps.

"Mike (AAA- AnotherAccessAmatuer)" wrote:

I am creating a sales database. I have tables to show
SalesInvoice, InvoiceDetails, Products and others. I
am
trying to make a SalesInvoice form with a subform that
reflects the invoice details. The SalesInvoice will
have
the typical info, ID, date, total, etc. The invoice
will
have linenum, qty, price, color size, etc. My problem
is
two fold. Many of the attributes are from the
products
table and while others are clearly invoice details. I
have been requested to do this using the wizard and it
does not seem to like more than two sources for
creating
forms. In other words, the form works fine for
displaying sales and invoice details, but not the
product
information. I do not seem to be able to link the
information. Second part, the boss wants the product
fields to display price and color based on ProdID, but
since I am using a 3rd table, I can't get a lock on
that
data.

Any help will be appreciated. Thank you.

.