Results 1 to 5 of 5
  1. #1
    gene_yuss is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    13

    Exclamation HELP! Need a form that will add invoices to user input orders.

    I desperately need some help. I would like to make a form that will allow the user to input a new invoice number, and has fields for user input that will allow them to input order numbers, once the user inputs a number it will pull up certain fields from the orders table so that the user knows which order it is. I need them to be able to input up to 30 different orders per invoice, and then the invoice number will be added to the records from the orders table they've input.



    I know very little about macros or SQL, but I'm willing to learn. Any help you could possibly give me would be greatly appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What is data structure? Something like:

    tblOrders
    InvoiceID (pk)
    OrderDate
    CustomerID (fk)

    tblOrderDetails
    LineID (pk)
    ItemID (fk)
    InvoiceID (fk)
    Quantity

    Data entry would be a form/subform arrangement. Main form bound to tblOrders, subform bound to tblOrderDetails. Master/Child links of subform container would synchronize the related records. InvoiceID entered on main form will automatically save to the detail records. Is that what you are looking for?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    gene_yuss is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    13
    Kind of, so the orders and the invoices tables when the form is loaded have no relationship. What I am trying to accomplish is at the top of the form the user will add a new invoice record, at that point they will manually input the first order that they want to out add to the invoice, this record exists in the orders table, and should populate the fields to the right of the manual entry of the order number (remember it is just basically finding the record they typed). Then they add the next one. Then I was thinking there could be a button that runs a append query on the orders that they have typed and it adds the invoice# to that same field (invoice_num) on the orders table.

    Should I draw it up and post it with my database?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    So instead of an InvoiceDetails table, you want to backfill the Invoice number to the Orders records? It wouldn't be an append query if you are updating existing Orders records, that would be an UPDATE.

    Instead of running UPDATE, how about a form that opens to show all of a customer's uninvoiced orders, select or enter an invoice number into an unbound combo or textbox in form header. Click on each order record you want to include on that invoice and trigger code to set value of the Invoice field for that current record.

    Or maybe a form/subform with Invoices/Orders tables, again with filtering for specific customer.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    gene_yuss is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    13
    on my! if you could instruct me on how to pull off the second option with the unbound text box, I can make a query that will show all of a certain customers unbilled but shipped orders. just don't know how the unbound boxes work exactly. If you can pull this off and prove that you are pod enough wherever you reside I will send you a nice bottle of 2008 barleywine reserve. you have been so kind so far, and I appreciate the help thus far. and I am embarrassed of my ignorance.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to take a user input
    By iamraja5 in forum Access
    Replies: 1
    Last Post: 05-12-2011, 10:17 AM
  2. Reporting with user input
    By tazbergy in forum Reports
    Replies: 1
    Last Post: 09-12-2010, 11:26 PM
  3. Printing Invoices
    By Alex Motilal in forum Reports
    Replies: 4
    Last Post: 05-11-2010, 01:20 AM
  4. User Input Query
    By ManC in forum Queries
    Replies: 2
    Last Post: 03-04-2010, 07:09 PM
  5. Set required user input
    By ZeusOsiris in forum Database Design
    Replies: 2
    Last Post: 12-04-2006, 07:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums