Populate fields on subform based on what is selected in main form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I can't figure out how to take data from my main form and use it to populate
a field in the subform. The main form is the ServiceAgreeement table and the
subform is the ServicesbyServiceAgreement. What I want to to is pick a price
group in the main form and either "A", "B", or "C" as the Price Schedule
(this is not a linked field, just a drop down list of "A", "B", or "C").
Then I want to pick a Service in the Subform, have it look at the price group
table and based on the service entered and the price group and price schedule
picked in the main form, populate the unit price in the subform. Tables are
listed below. Thanks for your help

Tables:
SERVICEAGREEMENT
ServiceAgreementID (PK)
PriceGroupID (FK to PRICEGROUPS)
PriceSchedule (list box of A, B, C)

SERVICESBYSERVICEAGREEMENT
ServiceAgreeementID (FK to SERVICEAGREEMENT)
ServiceCode (FK to Services)
Unit Price

PRICEGROUPS
PriceGroupID (PK)
PriceGroupDescr

PRICES
PriceGroupID (FK to PRICEGROUPS)
ServiceCode (FK to Services)
PriceSchedA
PriceSchedB
PriceSchedC

SERVICES
ServiceCode (PK)
ServiceDesc
 
I am so frustrated. I have figured out how to update the subform if the main
form changes. Now, I am trying to figure out how to populate the correct
fields based on what is selected in the main form if the subform changes.
Here is the code that works (the main form)

If Standard_Price And Price_Schedule = "A" Then
With Rs
.MoveFirst
Do Until Rs.EOF
Me.subfrm_ServicesbyServiceAgreement.Form.Bookmark = .Bookmark
servcode = .Fields("ServiceCode")
.Edit
.Fields("UnitPrice") = DLookup("[Price Schedule A]", _
"[tbl_Prices]", "[Service Code]= '" & servcode & "'" _
& "AND" & "[Price Group Code] = Price_Group.Value")
.Update
.MoveNext
Loop

Now, the DLookup function in the subform does not work the same way...Can
someone help with why?

Dim prGroup As Variant
prGroup = Me.Parent.Price_Group.Value

ElseIf Forms("frm_ServiceAgreement")("Standard Price") And
Forms("frm_ServiceAgreement")("Price Schedule") = "C" Then
Unit_Price.Value = DLookup("[Price Schedule C]", "[tbl_Prices]", _
"[Service Code] = '" & Service & "'" & "AND" & "[Price Group Code] =
prGroup ")
 
Back
Top