Defining the WHERE of SELECT INTO using a listbox?


I have defined a Many-to-Many relationship between tables ‘tblComponent’ and ‘tblProduct’ using a Joining Table ‘tblMasterBOM’ and created a Product form with a Component Subform

I would like to use an existing product as a template for the next one by using a listbox to select the product to copy.
The subset of components for the new product would then be inserted into the tblMasterBOM

I doesn’t HAVE to be a listbox but I do want to be able to select the product from a list of some sort without resorting to popup boxes that require the user to enter data or ID’s.

There are a number of examples available showing how to insert either pre-defined values or selected records but I have not been able to find any that show how to automatically specify the current recordID or Listbox value on the Main form as the filter in the WHERE statement

Please help.

(Note: I created a .txt version of a bare-bones example but being very new here I’ll need another 2 points before I can upload the file should it be wanted)

(Currently running LibreOffice Base , Version:

Edit (attempting to clarify)

Basically I’m trying to achieve the same result as would be produced by the follow statement but without having to use the Tools>SQL box or requiring User to enter the ID’s manually

INSERT INTO "tblMasterBOM" ("ProductID", "ComponentID", "Quantity") SELECT 15 AS "NEWProductID", "ComponentID", "Quantity" FROM "tblMasterBOM" WHERE "ProductID" = 2
  • tblMasterBOM is the Joining Table between the Product and Component tables
  • 15 is the ID of the new product
  • 2 is the ID of the original product that I’m using as a template

The objective is not entirely clear. You will be using a macro, and a list box, that much is clear. The example will help. For a topic involving picking a listbox entry see this one. For a filter topic see this other one.

Thanks doug, I’ve edited the original post to try and make my request clearer