If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
creating an invoice/invoice details form/subform
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. |
#3
|
|||
|
|||
creating an invoice/invoice details form/subform
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. . |
#4
|
|||
|
|||
creating an invoice/invoice details form/subform
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. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Want to hide all calendar appointment details and only show free/busy times | Brady | Calendar | 1 | June 21st, 2004 04:01 PM |
Problem Creating Report | [email protected] | Using Forms | 1 | June 7th, 2004 07:51 AM |
Creating a Survey | Terri | Database Design | 1 | May 19th, 2004 09:07 PM |
Calendar details only available for certain OL users | Jay A | Calendar | 0 | May 10th, 2004 07:30 PM |
don't want to see calendar details | bbailey | Calendar | 0 | April 26th, 2004 08:14 PM |