Display field based on selected criteria

[joecamel9166]

Not really sure where to post this question. This is for a repair DB. I Have a couple tables linked by model number. Each model# has 4 versions. Each version has 3 repair classes (a, b, c). Each version has a different price for each repair class.



Right now on my main units table i have a model field, repair class field and an estimate field. The model field has a direct relationship to a modelnumber table.

The modelnumber table contains all models and versions in a field along with their corresponding Aclass, Bclass, Cclass price.

On my form I want to input the Aclass, Bclass, or Cclass value of the modelnumber table into the estimate field of the main table depending on the model field value and the repair class value of the main table.

I can't imagine this being too complicated, but I'm at a complete loss on how to achieve this. Maybe I'm going at it the wrong way?

I sincerely appreciate any help on this. Thanks!

[ranman256]

You can show the continuous form, text or combo boxes on the header.
every time a box changes, the AFTERUPDATE event changes the filter.

sub cboBox_Afterupdate()
RunFilter
end sub

sub RunFilter()
Dim sWhere
   If not IsNull(cboBox) then sWhere = " and [field]='" & cboBox & "'"
   If not IsNull(txtBox) then sWhere = " and [field]='" & txtBox & "'"
SWhere = mid(sWhere,6).  'Remove 1st AND
me.Filter=sWhere 
me.FilterOn=true
End sub

[joecamel9166]

Thank you!
Please excuse my ignorance, but what would the reason for having the text boxes displayed in the header be?

Currently the text boxes are on the form bound to main units table. The model# is selected on the previous form, and I have a combo box on current form with values a, b, c(repair class) . If I used the afterupdate on the repair class combo box, would that work also?

Im not at work now, so I can't physically try your solution at the moment. I just want to get a good understanding of it.

[joecamel9166]

I still Cant figure this out. Maybe my relationships are not correct.
I compressed my DB and uploaded it to drop box. Here is the link. https://www.dropbox.com/s/l64t9qxq3s...x.com.url?dl=0
Don't mind the mock data that's in it.
Please any help on this would greatly appreciated.

[joecamel9166]

What I think I need on this is a statement like this on my quote form:

If Me.Repair_Class = "A" Then
Me.Estimate = ("modelID.ClassA")
End If

I just don't know the proper syntax to do this.