Solved Double Click a Record Selector to add that record to a table (1 Viewer)

[jdlewin1]

Hi,

I have a form which carries out a filter / search on a table and displays it in a subform using via query. The search essentially uses criteria selected on the form to filter down the data.

What I want to do is in the subform showing the filtered results, double click the record selector (or anywhere on the record line) and have that record "appended" to a table to temporarily house the data. I then want to be able to repeat this on another record and append that one to the same table etc.

Here is the SQL taken from the query which is used on the subform (Order_Product_Search subform) to display the filtered result set, this is displayed on the form (Purchase_Requisition):

SELECT Products.Product_ID, Products.Supplier, Products.Principle, Products.Category, Products.Item, Products.Description, Products.Product_Code, Products.Price_£, Products.ISO_17025_Certified, Products.Notes
FROM Products
WHERE (((Products.Supplier) Like "*" & [Forms]![Purchase_Requisition]![Supplier_Search] & "*") AND ((Products.Principle) Like "*" & [Forms]![Purchase_Requisition]![Principle_Search] & "*") AND ((Products.Category) Like "*" & [Forms]![Purchase_Requisition]![Category_Search] & "*") AND ((Products.Retired_Date) Is Null))
ORDER BY Products.Supplier, Products.Principle, Products.Category;

I want it to append the record to the "Order_Holding" table, this destination table essentially ahs the exact same fields as the query.

Thanks in advance for your help!!!

Jon

 

[Minty]

The selected record seems to have a unique ID (Product_ID), in which case a simple INSERT query will suffice;

Dim strSql as String

strSql = "INSERT INTO ORDER_HOlDING (Supplier, Principle, Category, Item, Description, Product_Code, Price_£, ISO_17025_Certified, Notes) "
strSql = strSql & " SELECT Supplier, Principle, Category, Item, Description, Product_Code, Price_£, ISO_17025_Certified, Notes from Products WHERE Products.Product_ID = " & Me.Product_ID

Debug.Print strSql

Currentdb.Execute strSql, dbSeeChanges

Add this to the double click event of the control you want to run it from. This assumes Product_Id is a number

 

[gemma-the-husky]

I doubt you can achieve this with the record selector. I would add a button to do this, I think.

 

[jdlewin1]

The selected record seems to have a unique ID (Product_ID), in which case a simple INSERT query will suffice;

Dim strSql as String

strSql = "INSERT INTO ORDER_HOlDING (Supplier, Principle, Category, Item, Description, Product_Code, Price_£, ISO_17025_Certified, Notes) "
strSql = strSql & " SELECT Supplier, Principle, Category, Item, Description, Product_Code, Price_£, ISO_17025_Certified, Notes from Products WHERE Products.Product_ID = " & Me.Product_ID

Debug.Print strSql

Currentdb.Execute strSql, dbSeeChanges

Add this to the double click event of the control you want to run it from. This assumes Product_Id is a number

Thanks for the response..

Yes Product_ID is indeed a number, how do I do an insert query using the Product_ID from the record i am currently on in the table list shown on the form?

 

[jdlewin1]

Thanks for the response..

Yes Product_ID is indeed a number, how do I do an insert query using the Product_ID from the record i am currently on in the table list shown on the form?

The selected record seems to have a unique ID (Product_ID), in which case a simple INSERT query will suffice;

Dim strSql as String

strSql = "INSERT INTO ORDER_HOlDING (Supplier, Principle, Category, Item, Description, Product_Code, Price_£, ISO_17025_Certified, Notes) "
strSql = strSql & " SELECT Supplier, Principle, Category, Item, Description, Product_Code, Price_£, ISO_17025_Certified, Notes from Products WHERE Products.Product_ID = " & Me.Product_ID

Debug.Print strSql

Currentdb.Execute strSql, dbSeeChanges

Add this to the double click event of the control you want to run it from. This assumes Product_Id is a number

never mind my previous ques....completely missed your coding!....it has been a long day. I will try that now.

 

[jdlewin1]

The selected record seems to have a unique ID (Product_ID), in which case a simple INSERT query will suffice;

Dim strSql as String

strSql = "INSERT INTO ORDER_HOlDING (Supplier, Principle, Category, Item, Description, Product_Code, Price_£, ISO_17025_Certified, Notes) "
strSql = strSql & " SELECT Supplier, Principle, Category, Item, Description, Product_Code, Price_£, ISO_17025_Certified, Notes from Products WHERE Products.Product_ID = " & Me.Product_ID

Debug.Print strSql

Currentdb.Execute strSql, dbSeeChanges

Add this to the double click event of the control you want to run it from. This assumes Product_Id is a number

I put the code in the double click event on the Product_ID, works perfectly!

I have some subforms on the bottom of the form which I cant seem to have update after doing the double click, mainly Order_PReq subform, i have added the bleow code after the "CurrentDb.Execute strSql, dbSeeChanges" of your code but it errors.

I know it must be some basic code but ant for the life of me put my finger on it.

 

[Minty]

Are you trying to get the subform Order_PReq to display the newly added records?

You will need to issue a requery on the subform control after doing the insert.

 

[Pat Hartman]

I'm fine with using the double-click event of a control to open a form or report for the selected record but I don't like the idea of assigning an update action to that event, I would prefer using an actual button and give the button a tooltip so that people can tell what will happen with the click of the button. Using the dbl-click event is not wrong, I just don't like it for this purpose.

 

[jdlewin1]

Thanks all for you help, the application works perfectly.