VBA Loop to Requery Combo Field with Lookup (1 Viewer)

Oreynolds

Member
Local time
Today, 01:30
Joined
Apr 11, 2020
Messages
110
Hi,

I have a main form "Quotes"
I have a subform "Quotes Subform"

The quotes subform is in datasheet mode and allows users to select all the different products required to build that particular quote. One of the fields is a Combo box "ProductID" that has a lookup from the products table and brings in the ProductCode and UnitPrice from the products table when a product is selected.

When suppliers update prices these are carried out in the product table and therefore a quote built say 6 months ago may not be based on the current product prices. I want to create a cmd button on the main form to allow the user if they wish to run some code to loop through all the products in the subform recordset and requery the ProductID combo box field so that it refreshes the UnitPrice field as it does when you individually re-select the same product on the subform.

I have the following code but am struggling with the process of the requery command if this is even the right one to use?

Has anyone done this before or can offer any assistance? Thanks

Code:
Private Sub cmdRefreshProductCosts_Click()

Dim rs As DAO.Recordset
'Dim db As DAO.Database

Set rs = Forms![Quotes]![Quotes Subform].Form.Recordset.Clone()

        rs.MoveLast
        rs.MoveFirst
            Do Until rs.EOF
                rs.Edit
                'rs.Requery
                rs!ProductID.Requery
                rs.Update
                rs.MoveNext
            Loop
        Set rs = Nothing
        'Set db = Nothing
        Me.Form.Refresh

End Sub
 

bob fitz

AWF VIP
Local time
Today, 01:30
Joined
May 23, 2011
Messages
4,222
Instead or "Requery" you would need to use DLookup() to get the current price and assign that to to Price field in the recordset. Something like:

rs!PriceFieldName = DLookup("PriceInTbale","ProdTableName", "ProductID = " & rs!ProductID

Alternatively you could just run an Update query,
 

arnelgp

error reading drive A:
Local time
Today, 08:30
Joined
May 7, 2009
Messages
12,011
what you need is an Update query to update the product price with the latest price?
 

Oreynolds

Member
Local time
Today, 01:30
Joined
Apr 11, 2020
Messages
110
Instead or "Requery" you would need to use DLookup() to get the current price and assign that to to Price field in the recordset. Something like:

rs!PriceFieldName = DLookup("PriceInTbale","ProdTableName", "ProductID = " & rs!ProductID

Alternatively you could just run an Update query,

Worked perfectly, thanks so much!
 

Users who are viewing this thread

Top Bottom