Filter query based on current record

[dixxy]

Hello,

I woull like to know how i can filter a query based on the currently selected record?

I am trying to copy a record from inventory table to purchase order table, and i would like to filter it based on the currently selected record.

I think i can do this with record count, but i don't know how to formulate this in a query criteria.

Can this be done?

Thanks,

Sylvain

 

[PHV]

What is the actual SQL code and which criteria will filter it ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dixxy]

this is what i have so far in terms of sql statement

INSERT INTO tblPurchaseOrderDetails ( ItemNumber, PurchaseOrderNumber, Description, QuantityOrdered, Price )
SELECT tblInventory.ItemNumber, tblPurchaseOrders.PurchaseOrderNumber, tblMultiPricing.MultiProductNumber, tblMultiPricing.Qts, tblMultiPricing.Price
FROM tblPurchaseOrders, (tblInventory INNER JOIN tblSupplierProduct ON tblInventory.ItemNumber = tblSupplierProduct.ItemNumber) INNER JOIN tblMultiPricing ON tblSupplierProduct.ProductNumber = tblMultiPricing.ProductNumber
WHERE (((tblInventory.ItemNumber)=[Forms]![frmPurchaseOrders]![frmTabs].[Form]![ItemNumber]) AND ((tblPurchaseOrders.PurchaseOrderNumber)=[Forms]![frmPurchaseOrders]![PurchaseOrderNumber]) AND ((tblMultiPricing.ProductNumber)=[Forms]![frmPurchaseOrders]![frmTabs].[Form]![frmSupplierProduct].[Form]![frmMultiPricing].[Form]![ProductNumber]));

and the where has to be something like this:

[Forms]![frmPurchaseOrders]![frmTabs].[Form]![frmSupplierProduct].[Form]![frmMultiPricing].[Form]!["currently selected record"]

where "currently selected record" is the currently selected record


Thanks,

Sylvain

 

[JoeAtWork]

If all the information you need to insert is displayed on the form, I would do it more like this:

  Dim SQL_Insert As String

  SQL_Insert = "INSERT INTO tblPurchaseOrderDetails ( ItemNumber, PurchaseOrderNumber, Description, QuantityOrdered, Price ) "

  SQL_Insert = SQL_Insert & " VALUES (" & [Forms]![frmPurchaseOrders]![frmTabs].[Form]![ItemNumber] & ", " & _
[Forms]![frmPurchaseOrders]![PurchaseOrderNumber] & ", '" & _
[Forms]![frmPurchaseOrders]![frmTabs].[Form]![frmSupplierProduct].[Form]![frmMultiPricing].[Form]![Description] & "', " & _
[Forms]![frmPurchaseOrders]![frmTabs].[Form]![frmSupplierProduct].[Form]![frmMultiPricing].[Form]![QuantityOrdered] & ", " & _
[Forms]![frmPurchaseOrders]![frmTabs].[Form]![frmSupplierProduct].[Form]![frmMultiPricing].[Form]![Price] & ")"

  CurrentDb.Execute SQL_Insert

Note - I am making guesses as to where the Description, QuantityOrdered, and Price controls are - adjust according to your realities.

Also, fields that are not numeric, such as Description, should have quotes around them.

 

[dixxy]

Joe,

how would this know which record from the 'multi pricing' form to insert, cuase the way i have it set up is that 1 item can have many 'order number' based on the quantity ordered.

That is why i am trying to find out which record the user selects and insert that particular one.

Thanks,

Sylvain