Search code examples
vbams-access

How to get the correct value for textbox control source Access VBA after update of another textbox?

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.

imgTBL

So this is my first textbox value (txtTicket):

firsttxtbox

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:

imgLOS

I tried this, too:

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

It is giving me this result:

datamsmatch

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:

imgLOS

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:

imgInvalid

What is wrong with my lines above.

Your help is greatly appreciated.

Solution

  • Don't use the ControlSource property but the Value

    txtLOS.Value = tryLOS

    this will set it to the string.