Solved Please help with VBA to update only one record and it's children (1 Viewer)

slharman1

Member
Local time
Today, 18:50
Joined
Mar 8, 2021
Messages
467
I have a form along with underlying tables. The form (form1) Tied to a table and its child tables. form1 has these fields: itemID, itemDesc, and itemPrice. Form1 gets its data from from another set of records (table2) with the same fields whose price changes regularly when material or labor prices change.

Form1 description and pricing are filled in by selecting a record in table2 from a combo box on form1, then using Dlookup in table2 to fill in the data on form1.

I must do this in order to keep the price for a specific item on form1 the same until the quote expires - even if the data in table2 has changed due to price changes.

Once the quote on form1 expires I am updating the price by clicking a command button on the form which runs the following code on the currently selected record:

Code:
Private Sub cmdUpdateItem_Click()
newestimateid = cboEstID

    Dim dblPrice As Currency
    'Dim strDescription As String
    Dim strModelNo As String
    'Dim accesstotal
    
    
   ' accesstotal = DLookup("SumofExPrice", "qryAccTotalByQuoteDetail", "[QuoteDetailID]= [forms]![frmQuotes]![SfrmQuoteDetails]![QuoteDetailID]")
    strModelNo = DLookup("ModelNumber", "tblEstimates", "[EstimateID]= [cboEstID]")
    dblPrice = DLookup("EstPrice", "tblEstimates", "[EstimateID]= [cboEstID]")
    'strDescription = DLookup("Description", "tblEstimates", "[EstimateID]= [cboEstID]")
    
        If currentestimateid = newestimateid Then
        
        Else
            'Me.Description.Value = strDescription
            Me.Price.Value = dblPrice
            Me.ModelNo = strModelNo
            'Me.AccessPrice.Value = accesstotal
            
        End If
End Sub

This works great however, I have a similar sub-form for accessories for each record that I would like to update as well.
I really don't want the user to have to select each accessory and click and update button for it's data.
I would like to have the subform update all of it's records when I update the main Items data.

I hope I am explaining this clearly.
Let me know if you know how to accomplish this.
Thanks
 

Ranman256

Well-known member
Local time
Today, 19:50
Joined
Apr 9, 2015
Messages
4,339
run an update query on the subtable, using the key from the master.

update tSubTable set field1 = something where [Key] = forms!fMasterForm!txtKey
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:50
Joined
Feb 19, 2002
Messages
42,981
I would do this the other way around. When the price changes, run an update query that updates all OPEN quotes with that item's new price.

Doing it your way, you would have to open each quote in order to make the update happen. If you have only a couple and you have their contents in your head, you can handle this but my way is better since it doesn't matter how many open quotes there are.
 

slharman1

Member
Local time
Today, 18:50
Joined
Mar 8, 2021
Messages
467
I would do this the other way around. When the price changes, run an update query that updates all OPEN quotes with that item's new price.

Doing it your way, you would have to open each quote in order to make the update happen. If you have only a couple and you have their contents in your head, you can handle this but my way is better since it doesn't matter how many open quotes there are.
Phil, I don’t know if you remember from helping me in the past. The custom fabricated parts use an update query to update parts when the price changes. Those are my products that I sell. My products go into a quote for a customer. My quotes are valid until I say so (this can vary depending on situation) so my quote price does not change just because a product price changes. But when a particular quote does expire then I want to update only that particular quote.
so I want to re-dlookup so to speak the price from the product table and update only the quote in question. The code I posted earlier does this fine but there is a sub form with more products which are listed as accessories in an accessories table ties back to products and dlookup is also used the same way to get the price on the quote sub form. So I need to add to the code earlier posted to run through all records added to the quote as accessories and update those prices as well. Each quote line item can have an unlimited number of accessories.
Any help would be appreciated.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:50
Joined
Feb 19, 2002
Messages
42,981
Phil?

I'm sorry, I don't understand why you would want to change the price of an expired quote. I do remember trying to help you with this before but there is something about the logic that escapes me. Maybe someone else can figure it out. I'm pretty sure I told you last time to use an update query. You would join the detail items to the pricing table to pick up the current price.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 23:50
Joined
Sep 12, 2006
Messages
15,614
I would be inclined to change your database so that instead of storing a volatile price in a "bill of materials" say, you just look it up when you need it. That wouldn't be a hard change to do, and would save you all the issues you currently have. All you should need is the product code, quantity and the date.

If you have a "fixed" price, then store the price and a flag to say it's fixed, rather than volatile. It's hard to have both at the same time though. It's hard to have logic that "knows" it needs to update a particular fixed price some times but not others.

Managing redundancy (duplicating data) is an important concept. If you choose to store a price twice, that's fine, but then you know you need robust price management routines.

If you avoid the redundancy, the data never goes out of synch. The trade off is you have to look up the prices when you need them. It's a matter of taste and judgement which option you prefer, but looking up the price can never be wrong, whereas storing the price can go wrong.

If there is a "final" price - then you can store that "final" price - say the price charged on an invoice. I think it's legitimate to store that final price, as it won't thereafter change, and now it will save you time.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:50
Joined
May 7, 2009
Messages
19,169
the logic is this, you may adjust for correct fieldnames.
Code:
Private Sub cmdUpdateItem_Click()
newestimateid = cboEstID

    Dim dblPrice As Currency
    'Dim strDescription As String
    Dim strModelNo As String
    'Dim accesstotal
    
    
   ' accesstotal = DLookup("SumofExPrice", "qryAccTotalByQuoteDetail", "[QuoteDetailID]= [forms]![frmQuotes]![SfrmQuoteDetails]![QuoteDetailID]")
    strModelNo = DLookup("ModelNumber", "tblEstimates", "[EstimateID]= [cboEstID]")
    dblPrice = DLookup("EstPrice", "tblEstimates", "[EstimateID]= [cboEstID]")
    'strDescription = DLookup("Description", "tblEstimates", "[EstimateID]= [cboEstID]")
    
        If currentestimateid = newestimateid Then
        
        Else
            'Me.Description.Value = strDescription
            Me.Price.Value = dblPrice
            Me.ModelNo = strModelNo
            'Me.AccessPrice.Value = accesstotal
            
        End If
        
    ' arnelgp
    ' update the subform price here
    '
    With Me![SubformName].RecordsetClone
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            dblPrice = Nz(DLookup("EstPrice", "tblEstimates", "[AccessoryID] = " & [AccessoryID]), -1)
            If dblPrice <> -1 Then
                .Edit
                !AccessoryPrice = dblPrice
                .Update
            End If
            .MoveNext
        Loop
    End With
    
            
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:50
Joined
Feb 19, 2002
Messages
42,981
I'm not sure why a VBA loop is better than an update query. If you care about efficiency, the update query is the best solution.

The logic issue remains. The price change is completely dependent on the user remembering to take some action.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:50
Joined
May 7, 2009
Messages
19,169
If you care about efficiency, the update query is the best solution
You only use update query when the table you are updating is not on your form you are working.
Remember that the form holds a memory copy of your table. It will get out synch doing query update.
That is the use of your form, right? To add and update. Otherwise remove the form and just use update query? This is more efficient?
 

slharman1

Member
Local time
Today, 18:50
Joined
Mar 8, 2021
Messages
467
the logic is this, you may adjust for correct fieldnames.
Code:
Private Sub cmdUpdateItem_Click()
newestimateid = cboEstID

    Dim dblPrice As Currency
    'Dim strDescription As String
    Dim strModelNo As String
    'Dim accesstotal
   
   
   ' accesstotal = DLookup("SumofExPrice", "qryAccTotalByQuoteDetail", "[QuoteDetailID]= [forms]![frmQuotes]![SfrmQuoteDetails]![QuoteDetailID]")
    strModelNo = DLookup("ModelNumber", "tblEstimates", "[EstimateID]= [cboEstID]")
    dblPrice = DLookup("EstPrice", "tblEstimates", "[EstimateID]= [cboEstID]")
    'strDescription = DLookup("Description", "tblEstimates", "[EstimateID]= [cboEstID]")
   
        If currentestimateid = newestimateid Then
       
        Else
            'Me.Description.Value = strDescription
            Me.Price.Value = dblPrice
            Me.ModelNo = strModelNo
            'Me.AccessPrice.Value = accesstotal
           
        End If
       
    ' arnelgp
    ' update the subform price here
    '
    With Me![SubformName].RecordsetClone
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            dblPrice = Nz(DLookup("EstPrice", "tblEstimates", "[AccessoryID] = " & [AccessoryID]), -1)
            If dblPrice <> -1 Then
                .Edit
                !AccessoryPrice = dblPrice
                .Update
            End If
            .MoveNext
        Loop
    End With
   
           
End Sub
Access can't find the field -1)
dblPrice = Nz(DLookup("EstPrice", "tblEstimates", "[AccessoryID] = " & [AccessoryID]), -1)
Thanks arnelpg, that's what i needed
 

slharman1

Member
Local time
Today, 18:50
Joined
Mar 8, 2021
Messages
467
the logic is this, you may adjust for correct fieldnames.
Code:
Private Sub cmdUpdateItem_Click()
newestimateid = cboEstID

    Dim dblPrice As Currency
    'Dim strDescription As String
    Dim strModelNo As String
    'Dim accesstotal
   
   
   ' accesstotal = DLookup("SumofExPrice", "qryAccTotalByQuoteDetail", "[QuoteDetailID]= [forms]![frmQuotes]![SfrmQuoteDetails]![QuoteDetailID]")
    strModelNo = DLookup("ModelNumber", "tblEstimates", "[EstimateID]= [cboEstID]")
    dblPrice = DLookup("EstPrice", "tblEstimates", "[EstimateID]= [cboEstID]")
    'strDescription = DLookup("Description", "tblEstimates", "[EstimateID]= [cboEstID]")
   
        If currentestimateid = newestimateid Then
       
        Else
            'Me.Description.Value = strDescription
            Me.Price.Value = dblPrice
            Me.ModelNo = strModelNo
            'Me.AccessPrice.Value = accesstotal
           
        End If
       
    ' arnelgp
    ' update the subform price here
    '
    With Me![SubformName].RecordsetClone
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            dblPrice = Nz(DLookup("EstPrice", "tblEstimates", "[AccessoryID] = " & [AccessoryID]), -1)
            If dblPrice <> -1 Then
                .Edit
                !AccessoryPrice = dblPrice
                .Update
            End If
            .MoveNext
        Loop
    End With
   
           
End Sub
Actually this is the error I am getting:

Object Doesn't Support this Property or Method​

 

Users who are viewing this thread

Top Bottom