Billing DB form

[Symlink]

I have a DB with some tables but only 3 of them are important for this question. I have an Order table, an Order_details table and a Products table.



Fields :
Order : ID, Order_date, Payment_mode
Order_details : Order_key, Order_ID, Product_ID, Quantity, Discount
Products : Product_ID, Description, Price

I want to create a form that does a couple of things.

1- When you open it, it creates a new record (because you are creating a new order). I think I have that done with :
Private Sub Form_Load()
DoCmd.GoToRecord acDataForm, "order_form", acNewRec
End Sub

2- Basically the top fields of the Form are the fields from the Order table because I want to see the order # and the date.

3- I want to have a list of Products added to the order for which the Order_ID would be the ID in the Order table.

I managed to create the form to generate new orders but can't seem to be able to build the list of products that would add themselves to the Order_details table as you enter them. It doesn't seem like it's a very complicated task but something is missing somewhere I can't seem to find it.

For any details you can ask I will answer via PM or here.

Thanks alot for your time.

[June7]

Create a subform for Order_details on the Order form. Have a combobox on subform for selecting product. http://office.microsoft.com/en-us/ac...010098674.aspx

[Symlink]

Create a subform for Order_details on the Order form. Have a combobox on subform for selecting product. http://office.microsoft.com/en-us/ac...010098674.aspx

Oh nice Thanks alot. I had some troubles figuring out how to fetch data from other tables but I think I might have found the trick with table relations and also the wizard to create subforms helps alot to get fields from various tables to get my description matching with the product ID I need to figure out how to make it all work. Then I have to print the bill so if I have other questions I'll continue with this thread if that's ok.

[Symlink]

I managed to create the subform and have all the articles, quantities, description, subtotals. I created a table Order_tax_total because later at some point I want an Order viewer and I think since the taxes may change I need to be able to store them per Order_ID to recall them in the viewer. Also I have a table named General_information in which the user can modifiy the current tax.

Fields :
Order_tax_total : Order_ID, Order_TPS, Order_TVQ, Order_total (Yes I do live in Canada :P TPS and TVQ are both taxes on products)
General_information : Order_TPS, Order_TVQ

My Order_details subform has Product_ID, Quantity, Description, Price and Total =[Quantity] * [Price]

Now the part I am having trouble with is to have the form calculate the total of all my "subtotals" per Product_ID line I added in the subform and "fetch" the TPS and TVQ in my General_information table and "write" it in the Order_tax_total table under the correct Order_ID.

Thanks for you time

[June7]

Don't know enough about your db to offer specifics for your situation. Methods to retrieve data from the other tables:

1. join in form's RecordSource query

2. DLookup() domain aggregate function

3. combobox

Records on form can be summed by:

1. form in datasheet view, click Totals on the ribbon

2. form in continuous view, textbox in form footer with expression: =Sum([fieldname])

[Symlink]

I will provide my DB for specifics. There might be some parts in french on forms just disregard them. The form I am looking to build is the order_form in which I needed as I say the Order_ID the list of product bought and the total before and after tax.

There are most probably (100% being the probability) design errors. If you have time you can point them to me I'd like to learn. I THINK my overall DB layout is somewhat correct just not very good at putting all together with access. I used to code everything with VB.net so there are no form or whatnot only SQL queries :P

[June7]

The Order_details_sub form is okay. Set the product Description and Prix textboxes as Locked Yes, TabStop No. Set the Total textbox as TabStop No. The Product_ID control should be a combobox.

Will every product be taxed?

Shouldn't have to "write" the tax total to table, it should be calculated on a report. Save the tax rate in effect at time of sale or create a new tax record each time the tax rate changes and save that record ID.

[Symlink]

Now that you mention it... no not every product will be taxed. (I think it depends on the user) I think I'd have to add a "taxable YES/NO" field in the Product table and the calculate taxes accordingly.

I have done the textbox locks and tabstops.

Not very used to comboxes and why should it be a combobox instead of a textbox?

And when I said "write" i mostly wanted to save the taxes in the table with the order_ID so I can recall the tax that were in effect this particular date for that particular Order_ID

And I still can't figure out how to show the Subtotal then multiply by the taxes (fetched in the General_information table) then save those taxes to the Order_tax_total table and calculate the Total at the bottom of the Order_details_sub

[June7]

Combobox aids user in making valid product selection. They can be restricted to selecting items showing in the combobox list. Combobox will display product description, not just an obscure ID.

Need to get the tax structure properly set up.

If applying tax is dependent on customer then the "taxable Yes/No" field goes in customer table. If the tax depends on product then it goes in Product table. The approach will determine how and where tax info is saved and calculated.

[Symlink]

The taxable depends on the product.

And as far as using the ComboBox I guess since I am using a USB scanner to scan UPC codes (bar codes) on products I don't really need a combobox right?

Can you point me in the direction as to how to calculate totals at the bottom of my form ? Fetching taxes and that stuff ?

[June7]

What are TPS and TVQ? Are these tax categories? What are the rates? Will these change in the future?

Still not sure should save total tax for the order to a table. Saving aggregate data is usually a bad idea and will definitely require VBA code that adjusts the total tax as products are added or deleted from order. This is main reason for not saving aggregate data - it can become 'out of sync' with the raw data.

Conventional options:

1. save into Order table the tax rate(s) or record ID(s) of tax rate from a tax rate table and apply calculation of rate to products that are taxable

2. don't save tax info into Order - tax rate table has dates tax rate is effective - apply the pertinent tax rate according to the order date

[Symlink]

TPS (5%) and TVQ (9.975%) are tax names it's GST and HST

1. That's what I'm trying to do or almost I was trying to use another table to save the taxes because there would only be 1 TPS and 1 TVQ number / Order_ID. Am I wrong design-wise?

2. That would work as well and I will modify the table to take that in account but that would require me to work with dates instead of just saving the TPS and TVQ numbers saved for the Order_ID

The part I am having trouble with is to put the tax textboxes, subtotal and grand total because I cannot seem to know how to SUM the txt_total from the subform at the bottom of the Order_form and how to fetch the taxes in the General_information table. Sorry if I'm not being clear enough :S I just did a little Paint image of what I would like to happen in the form. I do not wish to save the Total or Grand total as I can calculate them anyway.

[June7]

Options:

1. If the tax rate will NEVER change, don't need to save to table. Calculate the tax for each product on the subform. Create the Taxable field in the Product table. Since you aren't using a combobox for the product info and you already have product table in the subform RecordSource, use the Taxable field in expressions:

TPS
=Prix * IIf([Taxable]=True, 0.05, 0)

TVQ
=Prix * IIf([Taxable]=True, 0.09975, 0)

2. To save the tax rates with each order, would be easier to have fields in the Order table instead of a related table. Easy way to save the rates is to set DefaultValue property of textboxes with the 0.05 and 0.09975 rates. When the rates change, modify the DefaultValue properties. Other methods will involve table described in post 11 and will probably need VBA code. The tax calculations in subform will reference the fields on main form:

TPS
=Prix * IIf([Taxable]=True, [Forms]![Orders]![TPSrate],0)

TVQ
=Prix * IIf([Taxable]=True, [Forms]![Orders]![TVQrate],0)


The tax calculations can be replicated on report.

I made suggestions in post 5 about how to show sums of fields of subform.

[Symlink]

Yes taxes are changing all the time.

Records on form can be summed by:

1. form in datasheet view, click Totals on the ribbon

2. form in continuous view, textbox in form footer with expression: =Sum([fieldname])

I am not in continuous view and I seem to understand it's purpose is to show 1 record of one table at the time but anyway I tried the =SUM option and it failed because I cannot seem to get the data in the subform to show in the main form (probably something I do not understand) and when I tried adding "bottom" fields (fields to show only once instead of 1 for each record) they do not appear in the form. Also I can't seem to get the data in the main form. I'm trying to add a textbox which record source is something like =SUM(Formulaires![Order_details_sub]![txt_total]) and it doesn't work

And the Totals in the ribbon it does show the Total on a new line but it doesn't calculate I have only 2 options None and Number which would show 1-2-3-4 depending on the numbers of rows I added. I can SUM the Prix (Price) field but that's obviously no the field I want summed.

Now if I figure or you point me in the right direction and I do get the total calculated is it possible to get that calculation and use it in the main form instead of only in the subform ?

[June7]

The subform is not in continuous or datasheet view? Continuous view will make the report footer section available. Then can have textbox in footer with =Sum(Prix). Aggregate functions only work with fields in the RecordSource. Will not work on textboxes.

txtTPSTotal
=Sum(IIf([Taxable]=True, 0.05, 0) * Prix)


Textboxes on main form can refer to textboxes on subform.

= Formulaires![Order_details_sub]![txtPrixTotal] + Formulaires![Order_details_sub]![txtTPSTotal] + Formulaires![Order_details_sub]![txtTVQTotal]


Continuous view can be made to look like datasheet view by rearranging the textboxes (ungroup the controls - select all controls, right click > Layout > Remove).