none
VBA code is not working on an Order Form which contains two subforms RRS feed

  • Question

  • Hello,

    I have created a frmOrders which contains two subforms - fsubOrderDetails and fsubOrderLink. If I advance forward in the form - frmOrders  - from one customer to the next customer, the form do not show the specific order details which relate to the specific customer. Can you assist me in fixing the VBA code of these forms (parent form and subforms)? The database is attached.

    How do I make the form editable so that the user can add new records to the form?

    Your help in fixing these problems is greatly appreciated.

    Kind regards




    • Edited by wirejp Sunday, May 8, 2016 6:35 PM
    Wednesday, April 20, 2016 7:10 PM

Answers

  • Whether the combo box is bound or unbound is irrelevant.  The code for its NotInList event procedure in my demo would work exactly the same if the combo box were, as in your case, bound to a foreign key column in a table.  You'll have noted in my demo that the combo box's list has a second column showing the contact's address.  This is to differentiate between two people of the same name.  However, as only the name is entered into the combo box to trigger the NotInList event procedure, it would of course be impossible to add a second person of the same name in this way.

    If you think that the chance of there being two customers of the same name is remote, think again.  I was once present at a clinic where two patients arrived within minutes of each other.  Not only did both have the same first and last names, but both also had the same date of birth.

    For this reason I would advise you not to use the NotInList event procedure in this case, but to use the alternative method illustrated in my form, by adding a 'New Customer' button to the form.  I illustrate both methods in my demo because it is intended purely to demonstrate the methodology, not to replicate a realistic scenario.

    Of course you can't update the email, sports centre of trainer columns.  The place to do that is in a form based on whichever table the column is in, not in this form.  

    I'm sorry to say that you do appear to be struggling with some of the most basic principles of relational database design.  You would be well advised to put in some time and effort learning the principles of the database relational model, and how databases which conform with these principles are developed in MS Access.  Access is a development environment, and unless you understand how to operate in that environment you will inevitably continue to make the sort of basic errors which have been made in this application.  We are happy to guide you, but you cannot reasonably expect to keep posting your file, expecting us to fix every little detail.

    Ken Sheridan, Stafford, England

    • Marked as answer by wirejp Tuesday, April 26, 2016 8:25 PM
    Thursday, April 21, 2016 10:40 PM

All replies

  • I've taken a look at your file (which I notice draws quite heavily on my demo files) and have posted an amended version as wirejp.accdb to my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    There were a number of problems with your file:

    1.  The subforms should have been linked to the parent form on OrderID.

    2.  There was a one-to-one relationship type between Orders and OrderDetails, not one-to-many.

    3.  The ProductID combo box in the order details subform was referencing the wrong column when assigning the unit price to the bound control in the subform.

    I can't understand why you have a second subform, however.  Its controls are bound to columns in the Orders table, so can simply be placed in the parent form.  As it stands you can only insert one row into the subform, so it serves no real purpose.

    Ken Sheridan, Stafford, England

    Wednesday, April 20, 2016 9:55 PM
  • Hi Ken,

    Thank you for your help. I have the placed the second subform in the parent form. How do you make the parent form (frmOrders) and subform  (i.e fsubOrderDetails), editable so that a user can add new records automatically. When I try to add new records, I receive error messages. The updated database is attached.

    Thanks,

    wirejp

     



    • Edited by wirejp Sunday, May 8, 2016 6:35 PM
    Wednesday, April 20, 2016 10:44 PM
  • >>>How do you make the parent form (frmOrders) and subform  (i.e fsubOrderDetails), editable so that a user can add new records automatically. 

    According to your description, please correct me if I have any misunderstandings on your question, it seems that you want to jump to a new record in the parent form, refer to below code:
    In the form parent form, create a new public method:
    Public Sub MoveToNextRecord()
        DoCmd.GoToRecord acDataForm, Me.Name, acNext
    End Sub
    In the form Child, you call the method with
    Parent.MoveToNextRecord


    Thursday, April 21, 2016 2:03 AM
  • Hi David,

    Thank you for your response and help. Yes, I would like to jump to a new record in the parent form. I added your code to the database, but when I try to add information in a new record, the fields are in read-only mode and they are not editable. If I open a new record in the parent form, I try to enter a new customer name, I am unable to enter any information:


    • Edited by wirejp Sunday, May 8, 2016 6:35 PM
    Thursday, April 21, 2016 8:46 AM
  • I have not had a look at your amended file as you are clearly still way off the mark.  You don't need the second subform at all; the controls go directly in the parent form.  However, the parent form's query will not allow you to enter data into the controls you already have in the parent form when entering a record.  You need to simplify the form by binding a combo box control to the foreign key CustomerID column rather than to the non-key columns in the referenced tables.  The values from the referenced tables can then be shown in read-only bound text boxes in the parent form.

    To add a new customer invoke the NotInList event procedure of the new CustomerID combo box to open a Customers form in dialogue mode.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Thursday, April 21, 2016 11:07 AM Typo corrected.
    Thursday, April 21, 2016 11:06 AM
  • Hi Ken,

    I deleted the Customer's First Name and Customer Last Name referenced controls from the parent form and replaced them with a combo box control to the foreign key Customer ID column. I also followed in your instructions to invoke the NotInList procedure of the new CustomerID combo box to open a Customers form in a dialogue mode. However, I am still unable to add a new records to the form. 

    The idea is to have the form completely editable so that a user can add new records to the form. I have tried to simplify the reference query called "qryOrderFormCopy" to only 2 tables (Customers table and the Orders table) linked together. I can edit the edit and add new records. However I am unable to edit the form called frmOrders to add new records.

    Can you kindly help me with this problem.



    • Edited by wirejp Sunday, May 8, 2016 6:35 PM
    Thursday, April 21, 2016 2:52 PM
  • I've amended your form and have uploaded the file to my OneDrive folder as wirejp_2.mdb.

    1.  I've changed the form's query so that it includes the relevant referenced tables.

    2.  In the form I've changed the controls which show the data from the referenced tables to read-only text boxes.  These are automatically populated when you select a customer.

    3.  I've changed the primary key of the Orders table to an autonumber so that it is automatically given a value when a row is inserted.  Note that these values are entirely arbitrary and have no semantic significance.

    4.  I've removed the code from the subform's module which moves the parent form to a new record.  I'm afraid that David missed the pint in his earlier reply and misled you on this.

    5  I've added code to the form's Current event procedure to move focus to the customer control when you navigate to a new record, as this is the first thing you need to select.

    6.  I've added a button to move to an empty new order.

    Ken Sheridan, Stafford, England

    Thursday, April 21, 2016 4:31 PM
  • Hi Ken,

    Thank you very much for the edits and updates to my database. I was testing your changes in the frmOrders form, but I am experiencing a problem where the frmOrders form does not save new customers. Let me explain. When I select the New Order button on the frmOrders form to add a new order, I will start to enter the customer name (e.g. Customer A) and immediately a dialog box appears which asks me if I want to add a new customer. I say "yes" and the frmCustomer form is invoked. I typed Customer A's information in the frmCustomers form. Everything works well up to this point. If I then try to save and close the frmCustomers form, a message appears which say "Customer A was not added to the Customers table". I then close the frmOrders However, if I check the tblCustomers table, Customer A's information has been added to the Customers table.

    If I open and cycle through the frmOrders form, I do see any information for the new customer - Customer A. However, I click the combo box control for Customer, I can see Customer A's name in the drop-down combo box.

    How do you show Customer A's information has been added to the frmOrders form as a new Order?

    Thanks,

    wirejp

     

    Thursday, April 21, 2016 5:36 PM
  • Because the visible column in the customer combo box is a concatenated value of the first and last names, in the NotInList event procedure you would have to parse the name into its constituent columns.  This is possible, and is illustrated in the opening form of the NotInList demo in my OneDrive folder.  As noted there, however, this is not a good idea as personal names can legitimately be duplicated.  The same form illustrates a better method using a command button.

    I see that you have added a CustomerName column to the Customers table.  That is a bad idea as it introduces redundancy and opens the table to the risk of update anomalies.  The customer name should be stored as the two separate columns only.

    Ken Sheridan, Stafford, England

    Thursday, April 21, 2016 5:47 PM
  • Hi Ken,

    I have deleted the CustomerName column from the Customers table. I followed example of the NotInList demo in your OneDrive folder to update the Customer control of the frmOrders form.

    I just realized that the opening form of the NotInList demo is an unbound Contacts combo box which cause the Contacts form to pop-up and update this form. But the unbound Contacts combo box will always appear blank, unless you press the drop-box of the combo box to view the Customer names.

    When I applied this method to the Customer control of the frmOrders form, the Customers combo box will appear blank and as you advance from one record to the next record, the Customer combo box will appear blank. I also realized that I cannot update the CustomerEmail, Sports Centre or Trainer fields.

    Is there a way to show the customer name in the combo box, as you move from one record to the next record and update the CustomerEmail, Sports Centre or Trainer fields?

    My current database is attached.


    • Edited by wirejp Sunday, May 8, 2016 6:36 PM
    Thursday, April 21, 2016 8:26 PM
  • Whether the combo box is bound or unbound is irrelevant.  The code for its NotInList event procedure in my demo would work exactly the same if the combo box were, as in your case, bound to a foreign key column in a table.  You'll have noted in my demo that the combo box's list has a second column showing the contact's address.  This is to differentiate between two people of the same name.  However, as only the name is entered into the combo box to trigger the NotInList event procedure, it would of course be impossible to add a second person of the same name in this way.

    If you think that the chance of there being two customers of the same name is remote, think again.  I was once present at a clinic where two patients arrived within minutes of each other.  Not only did both have the same first and last names, but both also had the same date of birth.

    For this reason I would advise you not to use the NotInList event procedure in this case, but to use the alternative method illustrated in my form, by adding a 'New Customer' button to the form.  I illustrate both methods in my demo because it is intended purely to demonstrate the methodology, not to replicate a realistic scenario.

    Of course you can't update the email, sports centre of trainer columns.  The place to do that is in a form based on whichever table the column is in, not in this form.  

    I'm sorry to say that you do appear to be struggling with some of the most basic principles of relational database design.  You would be well advised to put in some time and effort learning the principles of the database relational model, and how databases which conform with these principles are developed in MS Access.  Access is a development environment, and unless you understand how to operate in that environment you will inevitably continue to make the sort of basic errors which have been made in this application.  We are happy to guide you, but you cannot reasonably expect to keep posting your file, expecting us to fix every little detail.

    Ken Sheridan, Stafford, England

    • Marked as answer by wirejp Tuesday, April 26, 2016 8:25 PM
    Thursday, April 21, 2016 10:40 PM
  • Hi Ken,

    Thank you for your help and advice. Yes, my knowledge is lacking in the principles of relational database design. I have a deadline to finish working on this application and so I am somewhat worried that I will not meet the deadline. I will continue working on it. Afterwards, I will spend some time learning the principles of the relational database design.

    Thank you again for your help.

    wirejp

    Thursday, April 21, 2016 11:18 PM
  • Hi, wirejp 

    Welcome, we are glad to help your resolve your issue. 

    In addition you could click here to refer about Create a form that contains a subform (a one-to-many form)
    Friday, April 22, 2016 7:44 AM
  • Hi David,

    Thank you for the link.

    Friday, April 22, 2016 1:12 PM