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:
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
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