Results 1 to 11 of 11
  1. #1
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149

    Copy the value from a form into a table

    Hi everybody



    I have a Form called FrmInvoice and a subform called FrmInvoice_Details_subform. The subform has the details of the products. The fields are linked to my Products table(Prduct_Code,Product and Price) and the Quantity field to my invoice table. The Product_Code is selected via lookup from Products table and is working fine, but since the info are from 2 seperate tables I had to make a textbox on the form to calculate the subtotal for each record. This also works fine. My problem is that I need t put the value in the Subtotal textbox in the Invoice_Details table in the subtotal field. Is there a way to copy the value from the form into the table?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Step back and tell us a little of the business/application. That is what business this database will support.
    It seems you are telling us of how you did something to accomplish something. Readers need to know more about what you are trying to do before offering advice on how to do it in Access.

    Can you show us a jpg of your tables and relationships to help understand your post?

    There are free, generic data models on various subjects at Barry Williams' site. Here is a link to one on Customers and Invoices that may be helpful to you.

    Good luck with your project.

  3. #3
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    The database is basically a type of accounts database. The user can create a qoutation and then either create an invoice from that qoutation or create a new invoice from scratch.Click image for larger version. 

Name:	invoice details.JPG 
Views:	19 
Size:	26.0 KB 
ID:	33256Click image for larger version. 

Name:	invoice.JPG 
Views:	19 
Size:	19.4 KB 
ID:	33257Click image for larger version. 

Name:	relationship.JPG 
Views:	19 
Size:	46.4 KB 
ID:	33258

  4. #4
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Invoice total should not be in tblInvoice, because it is a calculated field. You can easily create a totals query that will automatically sum the order details values.

    In the same way, tblInvoice_Details should not have Subtotal field, because it is a calculated value, and you can always use a query to get the values when you need them (but calculated controls on forms and reports are fine, and commonly used).

    The reason calculated fields are discouraged is that if the source data changes (quantity, price, etc), then you have to remember to update any calculated fields as well - they are not always updatred automatically.

    Have you made any provision for taxes, shipping, or other items that often occur on an invoice?

  5. #5
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    John_G, Ok I'll remove the calculated fields from th tables then. The oes on the form work fine

    Well , no, not yet. first am geting the form th way I want it and then Ill add the tax etc to it. Im not sure how to get acces to print onl the current invoice instead of all invoices via a report. Do I get the it to print the form(which means designing the form to lok like an invoice) or do I create a form with criteria to have it only print the currnt record?

  6. #6
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Do I get the it to print the form(which means designing the form to lok like an invoice) or do I create a form with criteria to have it only print the currnt record?
    You can design the form any way you need to to enter the data.

    You would design a report to look like the invoice, with logos, addresses,etc. The report can be based on a query which includes all the information it will need from (I think) all five of your tables shown above. Different sections of the report (header, details, and footer) would have data from different sections of the query, and some of the report values would be calculated.

    To print the report (invoice), you would include the invoice number in the criteria parameter (in blue below) of the openReport command:

    DoCmd.OpenReport "rptInvoice", acPreview, , "InvoiceID = " & Me![Invoice_Number]

    Replace the names in italics with the actual names you use for the report and invoice number on your form.

  7. #7
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Thank you very much Jogn_G, I will try that

  8. #8
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    I have just created a report containing all the fieds I need i it. When I try to open it I gt an Type Mismatch in expression errior? Thats just after creating the report...no coding done yet

  9. #9
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    How are you opening the report - through a form button? That error suggests there is an error in criteria argument of the docmd.openreport command. Most likely it is due to a numeric value (invoice number?) being enclosed in quotation marks when it should not be, or the reverse.

    It could also be an error in the report's source query - if is a saved query, does it work correctly when you run it from the query design?

  10. #10
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Nah, I havent even got to the button or the coe yet. I just created a standard report via th report wizard and tried openig it from navigation pane on the left.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If the field used for filtering is a text field, use text delimiters
    DoCmd.OpenReport "rptInvoice", acPreview, , "InvoiceID = '" & Me.TextField & "'"

    If its a number field, then as already stated, use
    DoCmd.OpenReport "rptInvoice", acPreview, , "InvoiceID = " & Me.NumberField
    If the field name contains a space, wrap in [] brackets e.g.
    DoCmd.OpenReport "rptInvoice", acPreview, , "InvoiceID = " & Me.[Number Field]
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 9
    Last Post: 07-11-2017, 07:51 AM
  2. Replies: 5
    Last Post: 02-21-2012, 07:33 AM
  3. Replies: 9
    Last Post: 12-19-2011, 06:30 PM
  4. Replies: 3
    Last Post: 03-23-2011, 11:37 AM
  5. Copy form field to another table
    By Dega in forum Forms
    Replies: 6
    Last Post: 05-21-2010, 02:57 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