Results 1 to 13 of 13
  1. #1
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65

    Synchronized Updates to Multiple Forms and Tables After Data Entry

    I have an Order form with three tabbed subforms: Product Order details, Shipping Addresses, and credit card info. This is a standalone, one-user database that does not connected to the internet.

    The OrderDetails subform is a child of the Order form, and the ShippingAddress subform and CreditCard info subform and children of the Customer form. The Shipping Address and CC info subforms are used to update information for a given customer after the products to order have been chosen, so that the information can be selected at checkout time.

    There are two errors I'm getting, one is "error 7878, the data has changed." It's not clear to me the specific circumstances I get the error in. I have been creating transactions for dummy customers to test it out.

    The second error happens when I create a new transaction for a previous customer in which I select an address or a credit card different from the first order created for the customer. The resulting order does not contain the selected address or CC, it contains the first one used.

    Each of the data tables appear to have all of the data entered in them, it's as though the data isn't "available" to use.

    Any ideas?

    Click image for larger version. 

Name:	Err7878.png 
Views:	24 
Size:	75.0 KB 
ID:	37500

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    and the ShippingAddress subform and CreditCard info subform are children of the Customer form.
    But both subforms are on the Orders form, not the Customer Form. Are you able to edit the shipping address with this setup? How about payment details?

  3. #3
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    No, when I try to edit existing shipping address or existing payment details I get Run-time Err '2465' Application-defined or object-defined error, and no changes are made.

  4. #4
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Actually the edited information is updated, but only after I close out and re-open the database.

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I think you need to rearrange your tabs and subforms. You have subforms (on tabs) for the customers form actually on the orders form. Move those tabs to the customers tab, and establish the parent/child linkage on the property Data tab.

  6. #6
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Thank you, this is helpful. I no longer get the Error 7878 or Run-time error '2465'. The updates I made to the Address or Payment details subforms (now correctly linked via the parent/child linkage on the property Data tab as recommended) now show up on my (Unbound) dropdowns when going through the order form, but are not captured into the actual order. The order still uses the first data entered for either shipping address or payment details.

    Am I supposed to have some code that refreshes the form to "forget" any previous data used for that customer?

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Do you have a foreign key field in the orders table to point to the shipping address table? That foreign key should be bound to the 'ship to address' combobox to be saved.

  8. #8
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    I do have a field called "fkShipAddID" in the Orders table. I've been using an Unbound control on the Order form with a Row Source of the ShippingAddress table filtered by the current customer on the form. Similar situation for the payment terms.

    Click image for larger version. 

Name:	ShipTableRowSource.PNG 
Views:	20 
Size:	13.7 KB 
ID:	37502Click image for larger version. 

Name:	UnboundCtrl.PNG 
Views:	19 
Size:	6.5 KB 
ID:	37503

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    On the propertys data tab for that combo box, the control source should be 'fkShipAddID', and it will be stored in the orders table.

  10. #10
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    When I did that, I get no information in the combobox anymore, it's blank. The payment terms still shows information to select, though, I didn't change that. Database uploaded if needed.PinkDusterv4.5C.zip

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    frmOrders has property data entry=yes. Make it data entry=no. Data entry=Yes means that the form can ONLY be used to enter new records. It will always open to a blank form.
    This thread is a continuation of https://www.accessforums.net/showthread.php?t=74902
    Myself and Orange contributed several hours of effort with that thread and thought that it was largely resolved. Now it's back with more logic and design problems that I am not willing to continue working on.
    Maybe someone else can jump in.
    Good luck with your project.

  12. #12
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Thank you very much for all of your review and assistance, it has been very much appreciated.

  13. #13
    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
    I tried your latest database - and I must advise that I am not familiar with the business process(es) involved.
    I opened the Customers form, selected B in left column, clicked Charlie Brown, then the View Previous Orders button.
    At that point I got an error. Reviewed the code and saw a spelling mistake.

    Code:
    Private Sub btnDtl_Click()
        If CurrentProject.AllForms("frmOrders").IsLoaded = True Then
            DoCmd.Close acForm, "frmOrders"
        End If
        DoCmd.OpenForm "frmOrders", , , , , , Me.CustID
        DoCmd.OpenForm "frmOrders", , , "fkCustID = " & Me.CustID
    Dim vID
    On Error GoTo err
    'vID = Me.frmCustomerOderTotals.Form.OrderID 'spelling error ORDER
    vID = Me.frmCustomerOrderTotals.Form.OrderID 'corrected --removes the error
    
    OpenOrder vID
    
    err:
    End Sub
    To me your forms are quite busy --that's just a personal observation. Only you and your users can determine what is acceptable.

    It appears that your logic may be changing as new "features" are considered. It may be counter intuitive, but building a flow chart of each process involved, and creating some test data and sample scenarios and testing each may save you considerable development time. It is much easier/efficient to correct the process logic/flow on a model, than creating code and trying to adjust that code to accommodate changes/corrections/refinements in physical code.

    You know your operation better than any reader, and you know what your forms and buttons are intended to do better than the readers. My feeling is that you need some test scenarios in which you identify the inputs and the expected outputs. Run these scenarios against your latest database with some selected users. Identify what you observe vs what you expected and resolve/reconcile the differences. Make a list of things-- don't attempt to solve with code/physical database 1 small fix at a time. Make sure that the business logic/process is well understood (this often gets refined as you experience more and more detail). See my stump the model pitch here.

    Good luck with your project.

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

Similar Threads

  1. Replies: 7
    Last Post: 01-01-2019, 01:47 PM
  2. Data entry form for multiple tables
    By Nowbodys in forum Access
    Replies: 1
    Last Post: 06-20-2016, 04:33 AM
  3. Data entry into multiple tables same field name
    By mpaulbattle in forum Access
    Replies: 6
    Last Post: 05-11-2016, 06:53 AM
  4. Replies: 5
    Last Post: 08-12-2013, 12:53 AM
  5. Replies: 1
    Last Post: 11-19-2011, 10:36 PM

Tags for this Thread

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