Update Field Based on Criteria from 2 Fields (1 Viewer)

[Cirrus]

Hello All,
Database Background: I have a database with two tables. The tables are linked together via a common field. The tables are then joined in a query that feeds a form.

What I'm Trying To Do: After updating a field in table 1 in the form, I want a field in table 2 to be auto updated based on two criteria. One of the criteria is the field that was just updated in table 1 and the other criteria is the existing value in the field I want to update in table 2. The field in table 1 is a YES/NO Field. The field in Table 2 is a text field based on dropdown values. If the field in table 1 is True (or checked) AND if the field in table 2 has a value of "None", I want it to update the field in table 2 to "Hatchery Cohort" (which is a value in the dropdown). Otherwise, I don't want the field in table 2 to update.

The Problem: The IF statements that I have tried in code builder will not work when trying to evaluate the values from the two separate tables.

This code works but only meets the first criteria:

Private Sub CaptureCohort_AfterUpdate()
If Me.CaptureCohort = True Then
        Me.LTRecapIdentifier = "Hatchery Cohort"
End If
End Sub

When I try to include the second criteria nothing happens (the field is not updated even when both criteria are met):

Private Sub CaptureCohort_AfterUpdate()
If Me.CaptureCohort = True Then
    If Me.LTRecapIdentifier = "None" Then
        Me.LTRecapIdentifier = "Hatchery Cohort"
    End If
End If
End Sub

I have searched numerous sites and tried different variations on the code but I can't seem to find the right syntax. Any help would be greatly appreciated but please keep in mind that I do not know alot about VBA or programing languages in general so keeping the code as simple as possible would be preferred.

 

[pbaldy]

Since you say it's a dropdown, I'd wonder if the value is different than what's shown. What is the rowsource of the combo? Have you set a breakpoint and determined what the value in the combo is? Or use this:

http://www.baldyweb.com/ImmediateWindow.htm

with

Debug.Print Me.LTRecapIdentifier

 

[pbaldy]

Oh, and I'd simply with

If Me.CaptureCohort = True And Me.LTRecapIdentifier = "None" Then

 

[Cirrus]

Hi Paul - I did try that syntax as well and it did not work either.

 

[pbaldy]

Your original syntax is fine, so the problem is the value held in that second control. Did you try the thoughts in post 2?

 

[Cirrus]

Hi Again Paul - the debug identified the problem for me. The dropdown is storing the ID value rather than the text value so instead of having "None" in the field it has "7" (number 7 in the dropdown). I'll be able to solve the issue now and I'll implement the simpler code as well as that should now work. Thanks so much for your help!

 

[pbaldy]

Happy to help!