Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Invoice Problems (Access 2K)

    I have a table called tblOrders. This table contains all of the information i will need regarding a single order that is placed. I have fields called Item1, Qty1, Item2, Qty2,....etc. up to 10. The number of items ordered on a single invoice will not exceed 10 as there is currently only one product, although it can be ordered in Quart, Gallon, or Case sizes. I want to be able to store all of this information in a table so any order can be accessed in the future. tblOrders is currently joined with one other table, tblCustomers which contains the customer information. On the Order form, I am able to select the Cust_Id and the customer information fills in several fields on the form.

    Once the the customer is selected, I want the user to be able to select a product name from a combo box and enter a quantity for the item. Based on the product ID that is selected, I want several other fields on the form to automatically complete. I can create a relationship between Item1 and Prod_ID in the tblProducts, but this only works for the first Item. If I create additional Lookups for Item2, Item3, etc, I will get duplicate field names and won't be able to differentiate the field Prod_Description, or other fields from tblProducts.

    After spending time trying to get this method to work, I tried to use VBA in the afterupdate event of the combobox in which the user selects the Prod_ID. I thought I could use DLookup to obtain the Product Price from the table tblProducts where the Prod_ID equaled the combobox CboItem1, which is the control the user will update on the order form. My VBA skills are lacking. I am not sure if it is the code structure, or syntax, or what, but I get an error after I update the cboItem1 control.

    Code is listed below..

    Private Sub CboItem1_AfterUpdate()


    Select Case Me![Item1]

    Case "OE-Gallon"
    Me.txtSize1 = "GAL"
    Me.txtName1 = "Sample Product Name"
    Me.txtDesc1 = "132oz. Plastic Bottle"
    If Me.Cust_Type = "Wholesale" Then
    Me.txtUC1 = DLookup("[Prod_Price_Whole]", "tblProducts", "[Prod_ID]=" & Forms!frmOrder!CboItem1)
    Else
    Me.txtUC1 = DLookup("[Prod_Price_Retail]", "tblProducts", "[Prod_ID]=" & Forms!frmOrder!CboItem1)
    End If

    Case "OE-Quart"
    Me.txtSize1 = "QT"
    Me.txtName1 = "Sample Product Name"
    Me.txtDesc1 = "32oz. Plastic Spray Bottle"
    If Me.Cust_Type = "Wholesale" Then
    Me.txtUC1 = DLookup("[Prod_Price_Whole]", "tblProducts", "[Prod_ID]=" & Forms!frmOrder!CboItem1)
    Else
    Me.txtUC1 = DLookup("[Prod_Price_Retail]", "tblProducts", "[Prod_ID]=" & Forms!frmOrder!CboItem1)
    End If

    Case "OE-Case"
    Me.txtSize1 = "Case"
    Me.txtName1 = "Sample Product Name"
    Me.txtDesc1 = "12-32oz. Plastic Spray Bottles"
    If Me.Cust_Type = "Wholesale" Then
    Me.txtUC1 = DLookup("[Prod_Price_Whole]", "tblProducts", "[Prod_ID]=" & Forms!frmOrder!CboItem1)
    Else
    Me.txtUC1 = DLookup("[Prod_Price_Retail]", "tblProducts", "[Prod_ID]=" & Forms!frmOrder!CboItem1)
    End If

    Case Else
    Me.txtSize1 = ""
    Me.txtName1 = ""
    Me.txtDesc1 = ""
    Me.txtUC1 = ""

    End Select



    End Sub

    Ideally, of course, if I could get DLookup to work, I would use it for txtSize1, txtName1, and txtDesc1

    I would appreciate any assistance anyone can offer. There is also likely a better way to create the order form/invoice but I am in the middle of this workaround since I am not aware of any other way to do this.

    Thanks in advance for any help.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Invoice Problems (Access 2K)

    Your problem is really being caused by the table structure. Because there are multiple line items in an order, line items should be in a separate table, rather than have 10 of them in the order table.

    Then each line item will include a productID, and you can create a relationship between line items and products and just pull in the info with a query. The line items table needs these fields: lineitemID (an autonumber key),orderID, productID, quantity, price.

    The price field is needed to record what you actually sell the item for. It would default to the price in the products table.

    With this table structure, the line items on the order form would be in a subform.
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invoice Problems (Access 2K)

    OK, my head is still running too fast, as I have been working on this all day.

    You are saying that I should have 10 separate tables, each for example tblLineItem1, tblLineItem2, etc. Then with the fields you have listed, there would need to be relationships created between tblProducts and EACH of the LineItem tables.

    Or are you saying that all line items would reside in one table and that I should include a subform on the Order Form which I am assuming I could have list just those records/lineitems in which the OrderID matches the Order Id on the form?

    The line items are new to me, so I am having trouble conceptualizing this at present.

    If the second method is the one you have proposed, then tblOrders will not contain Item1, Qty1, etc, but all line items would be in a separate table and when the order form is opened, only those line items in the subform which relate to the order_ID will be shown on the form, or in the report.

    let me know if I am getting warm.

    Thanks.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Invoice Problems (Access 2K)

    Your second method is the correct one. There is one lineitems table, and the order form would contain a subform for line items.

    This would use Master and child fields to only show line items for the current order.
    The same would apply to an invoice, which would be a report.

    I attach a screen shot of the relationships window.
    Attached Images Attached Images
    Regards
    John



  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invoice Problems (Access 2K)

    I think I am beginning to understand. I have rarely if ever used subforms, and will work on this tomorrow after my regular job. Thanks for the help. You are an invaluable resource. I will come beggin for more assistance if I cannot get this resolved, or let you know that I did.

    Thanks again for the help.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invoice Problems (Access 2K)

    I was able to implement your advice into my project and it works like a charm. Thanks again for the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •