Please bear with me. I am still new to Access VBA.

I have here a second textbox (txtLOS) that should get the correct value of the field (LOS) during afterupdate of the first textbox (txtTicket) as long as the first textbox value (txtTicket) matches the ID of the expected field (LOS) from table TBL_2025.

Table Name is TBL_2025

LOS Values are string such as "Ruby", "Diamond", "Emerald", etc.

TBL_2025 [ID] is an autonumber.

So this is my first textbox value (txtTicket):

I used this code below:

Private Sub txtTicket_AfterUpdate()
        Dim tryLOS As Variant
        tryLOS = Nz(DLookup("[LOS]", "[TBL_2025]", "[ID] = " & [txtTicket]))
        txtLOS.ControlSource = tryLOS
End Sub

After update, it should be giving "Ruby" for LOS as per table above.

However, it is giving me this result below:

I tried this, too:

txtLOS.ControlSource = DLookup("[LOS]", "[TBL_2025]", "[ID] = '" & [txtTicket] & "'")

It is giving me this result:

I tried removing the apostrophes:

txtLOS.ControlSource = DLookup("[LOS]", "[TBL_2025]", "[ID] = " & [txtTicket] & "")
txtLOS.ControlSource = IIf(IsNull([txtTicket]), "", DLookup("[LOS]", "[TBL_2025]", "[ID]=" & [txtTicket] & ""))

It showed this again:

I tried these below:

txtLOS.ControlSource = DLookup("[LOS]", "[TBL_2025]", ID = "[txtTicket]")
txtLOS.ControlSource = DLookup("[LOS]", "[TBL_2025]", [ID] = "[txtTicket]")
txtLOS.ControlSource = DLookup("[LOS]", "[TBL_2025]", "[ID]" = "[txtTicket]")

It returned me:

What is wrong with my lines above.

Your help is greatly appreciated.

Please bear with me. I am still new to Access VBA.

I have here a second textbox (txtLOS) that should get the correct value of the field (LOS) during afterupdate of the first textbox (txtTicket) as long as the first textbox value (txtTicket) matches the ID of the expected field (LOS) from table TBL_2025.

Table Name is TBL_2025

LOS Values are string such as "Ruby", "Diamond", "Emerald", etc.

TBL_2025 [ID] is an autonumber.

So this is my first textbox value (txtTicket):

I used this code below:

Private Sub txtTicket_AfterUpdate()
        Dim tryLOS As Variant
        tryLOS = Nz(DLookup("[LOS]", "[TBL_2025]", "[ID] = " & [txtTicket]))
        txtLOS.ControlSource = tryLOS
End Sub

After update, it should be giving "Ruby" for LOS as per table above.

However, it is giving me this result below:

I tried this, too:

txtLOS.ControlSource = DLookup("[LOS]", "[TBL_2025]", "[ID] = '" & [txtTicket] & "'")

It is giving me this result:

I tried removing the apostrophes:

txtLOS.ControlSource = DLookup("[LOS]", "[TBL_2025]", "[ID] = " & [txtTicket] & "")
txtLOS.ControlSource = IIf(IsNull([txtTicket]), "", DLookup("[LOS]", "[TBL_2025]", "[ID]=" & [txtTicket] & ""))

It showed this again:

I tried these below:

txtLOS.ControlSource = DLookup("[LOS]", "[TBL_2025]", ID = "[txtTicket]")
txtLOS.ControlSource = DLookup("[LOS]", "[TBL_2025]", [ID] = "[txtTicket]")
txtLOS.ControlSource = DLookup("[LOS]", "[TBL_2025]", "[ID]" = "[txtTicket]")

It returned me:

What is wrong with my lines above.

Your help is greatly appreciated.

• vba
• ms-access

• Why would the ID of TBL_2025 be the same as ticket ID? Is ticket ID also an autonumber field? It makes no sense for these values to be equal. – June7 Commented Feb 8 at 18:19
• @June7 so when user enters any number in text field that matches the autonumber ID, then it should display its stone based on LOS column – Shiela Commented Feb 13 at 14:27
• I thought txtTicket was ID from TBL_Orders, not TBL_2025. Did you see my comment in your other thread stackoverflow/questions/79407893/…? In light of that, this question still makes no sense. – June7 Commented Feb 13 at 18:13
• @June7 i really do apologize for the confusion. some of the tables are named differently and used for testing – Shiela Commented Feb 15 at 15:32

1 Answer 1

Don't use the ControlSource property but the Value

txtLOS.Value = tryLOS

this will set it to the string.