none
Adding Bulk Similar Records RRS feed

  • Question

  • I'm attempting to add bulk data to a table via a form. The scenario is bulk inventory. The data consists of names (lookup values from an Items table), types (lookup values from a Types table), and quantities (number of items at time of inventory). When the inventory occurs, I'd like a form readied with a column of all names and a column of blank quantities fields. To indicate that the bulk records are of type "Inventory", I'm wanting a single drop-down box to select as such. Once quantities are entered, I'd like to be able to click a "submit" button which would bulk enter the data and clear the form upon confirmation. Is this doable and if so, how?
    Sunday, March 13, 2016 4:13 AM

Answers

  • If you wish to confirm the insertion of the rows en bloc, then you should bind the form, which should be in continuous forms view, to a 'holding' table which mirrors the structure of the operational table.  In the form header put an unbound combo box to select the 'type'.  In the AfterUpdate event procedure of the combo box, or the Click event procedure of a separate button, firstly build and execute an INSERT INTO statement to insert rows of the selected 'type' into the holding table, then Requery the form to show the inserted rows.  Include a 'Confirm' button which then executes a further INSERT INTO statement to insert all rows from the holding table into the operational table, followed by the execution of a DELETE statement to empty the holding table, and finally Requery the form to clear it of the deleted rows.

    Ken Sheridan, Stafford, England

    Sunday, March 13, 2016 4:50 PM

All replies

  • Your requirement is not understood as it seems like you want to add a single record but you are using the term 'bulk' which would indicate multiple records.

    Which is it?


    Build a little, test a little

    Sunday, March 13, 2016 4:27 AM
  • If you wish to confirm the insertion of the rows en bloc, then you should bind the form, which should be in continuous forms view, to a 'holding' table which mirrors the structure of the operational table.  In the form header put an unbound combo box to select the 'type'.  In the AfterUpdate event procedure of the combo box, or the Click event procedure of a separate button, firstly build and execute an INSERT INTO statement to insert rows of the selected 'type' into the holding table, then Requery the form to show the inserted rows.  Include a 'Confirm' button which then executes a further INSERT INTO statement to insert all rows from the holding table into the operational table, followed by the execution of a DELETE statement to empty the holding table, and finally Requery the form to clear it of the deleted rows.

    Ken Sheridan, Stafford, England

    Sunday, March 13, 2016 4:50 PM