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

[Oreynolds]

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

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]

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]

what you need is an Update query to update the product price with the latest price?

 

[Oreynolds]

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!

 

[bob fitz]

Worked perfectly, thanks so much!

Glad it worked for you but I have to say that I think that using an Update query would be my preference.