Lookup Table Not Working? (1 Viewer)

[Lilly420]

Hello,

I have created a lookup table before but with a Yes-No box and it works great but am having a difficult time trying to recreate this for another field and have no idea what I am doing wrong and would be grateful for any help.

I have a form for input and feeds directly into my "Audit Detail" table. On the form, there is a combo box that points to the "Type of Field Work" table and lists all the types of fieldwork with their ID. The user selects the fieldwork type from the combo box, for example "Participation" is ID 20, and then once it is selected, the ID that is associated with that type is stored in the "Audit Detail" table in the "Type_of_Field_Work_ID" field (number field).

So what I am trying to do is if the user selects "Participation" which is ID 20, then I want to fill in another field on my form which is called " NoofExamsPerAuditID" (number field) also in the "Audit Detail" table with 0.7 and all other types with a 1. So I created a lookup table called "tFWScope" and put in the ID of 20 and called that ScopeAutoValue and then I selected the "Type_of_Field_Work_ID" field on the form and created the below code on the AfterUpdate Event but it does not work, I can't get past the Me.Type_of_Field_Work_ID…I get a message that reads "Compile error" "Method or Data member not found" and it highlights that part of the code. I checked spelling and all looks correct. Not sure if there is a better way to accomplish what I need?

Private Sub cmbScope_AfterUpdate()
If IsNull(DLookup("ScopeAutoValue", "tFWScope", "ScopeAutoValue = " & Me.Type_of_Field_Work_ID)) Then
Me.NoofExamsPerAuditID = 1
Else
Me.NoofExamsPerAuditID = .7
End If
End Sub


Any help is so greatly appreciated. Thank you.
Lilly

 

[MarkK]

1) Well, you are using the cmbScope's AfterUpdate event, but you never use cmbScope.Value. I would expect you to use the new value somewhere.
2) Also, you do a lookup, but you never use the looked up value.

If a change to one field should push a change to some other field based on a lookup, i would expect to see something more like...

Private Sub cmbScope_AfterUpdate()
   Me.DependsOn = DLookup("LookedUpValue", "tFWScope", "ScopeAutoValue = " & Me.cmbScope)
End Sub

 

[Lilly420]

Thank you for the help. I renamed my combo box to the field name and it worked, so thank you very much for pointing me in the right direction.