What is the VB code for looking up a value in a table?

[Jonathan Austin]

My situation is, if the value of textbox = any of the value in a table column then certain results will occur. I just want to know what the code would be for that.

Private Sub TextboxA_AfterUpdate()
If TextboxA = (value from table column) Then
TextboxB.Forecolor = vbBlack
TextboxB.Text = "Signed In"
End If
End Sub

My apologies for any confusion, just want to know the code that looks up value from a table.

[MikeTheBike]

Hi Jonathan

I think a little more info on your progrmming environment might help, but if its Access then try the VLookup() function (Access help will give you the necessary info).

Post back if this doesn't help, or you have move specific questions

HTH


MTB

[munkee]

You can use DLookup I expect

General syntax is DLookup(express ion, domain,[Criteria])

So for some vba code you can use:

    Dim SDate As Date

    SDate = DLookup("DateSentColumn", "tblOrders", "OrdID = 444")

[Jonathan Austin]

Hi Jonathan

I think a little more info on your progrmming environment might help, but if its Access then try the VLookup() function (Access help will give you the necessary info).

Post back if this doesn't help, or you have move specific questions

HTH


MTB

The program is Access 07. Basically this is what I have and need. I have a textbox (TextboxA), if I enter a certain value, it looks up in the table in the certain field, if it matches then I setfocus to TextboxB and the textbox will read, "SIGNED IN @ (timestamp)". And so what your saying I should use VLookup() or something else?

[Jonathan Austin]

Sorry, forgot to also mention. I will be using a barcode scanner, but right now I don't have the barcode reader, BUT using the keyboard should be the same effect. If the number entered (in the textbox) matches the table column then the rest of the code kicks in. Hopefully everything I mentioned explained it a little bit better.

[NeoPa]

I think Mike meant DLookup() for Access or VLookup() for Excel (See Using VLookUp in Excel to Link to Excel 'Tables').

Chris has given the basic outlines of how to use it. I suggest you use Context-Sensitive Help for the full details.

Context-Sensitive Help
In Office programs generally, there is a very easy way to get help on a particular item, be it a procedure, an object, or even an object property. Simply put the cursor on the word you want help for (or select the word) then press F1. This brings up the help page relevant to the word or selected text.

[MikeTheBike]

I think Mike meant DLookup() for Access or VLookup() for Excel (See Using VLookUp in Excel to Link to Excel 'Tables').

Chris has given the basic outlines of how to use it. I suggest you use Context-Sensitive Help for the full details.

Context-Sensitive Help
In Office programs generally, there is a very easy way to get help on a particular item, be it a procedure, an object, or even an object property. Simply put the cursor on the word you want help for (or select the word) then press F1. This brings up the help page relevant to the word or selected text.

Thanks Neopa, my brain definitly said DLookup() but my fingers thought otherwise!?

MTB

[NeoPa]

Don't tell anyone ;), but I've done exactly the same myself in the past :D

[Jonathan Austin]

Just want to make sure on this, though I have been looking into the DLookup() function, I just wanted to touch basis on it. My end result that I'm looking for is, if I enter a value into an unbound textbox, and if that value in the textbox matches the value in the column in a table, then (whatever code that I want to use afterwards.)

So I should be using DLookup() as everyone is suggesting? If so how do I use it exactly, I got somewhat the function of it, but don't know how to use it exactly.

Private Sub TextboxA_AfterUpdate()
If TextboxA = DLookup() Then
'TextboxA is Unbound
TextBoxB.Text = "This would be the end result if the value of TextBoxA matches the value of the column table."
End If
End Sub

[mseo]

My situation is, if the value of textbox = any of the value in a table column then certain results will occur. I just want to know what the code would be for that.

Private Sub TextboxA_AfterUpdate()
If TextboxA = (value from table column) Then
TextboxB.Forecolor = vbBlack
TextboxB.Text = "Signed In"
End If
End Sub

My apologies for any confusion, just want to know the code that looks up value from a table.

hi,
here's an example about how to use dlookup a value in a field, try this but change the names of the table, field and control name in the code
I supposed that the datatype of your field is a text

Private Sub textboxname_BeforeUpdate(Cancel As Integer)
   Dim bytes As Variant
   bytes= DLookup("[fieldname in the table]", _
                    "table name", _
                    "[fieldname in the table] = '" & Me.textbox control name& "'")
  If Not IsNull(bytes) Then
     ' write a code for doing whatever you want 
   End If
End Sub

again use your names

P.S you can use the same syntax within afterupdate trigger
hope this helps

[NeoPa]

You're looking for something on the lines of :

Private Sub TextBoxA_AfterUpdate()
    If DLookup("[YourField]", _
               "[YourTable]", _
               "[YourField]='" & Me.TextBoxA & "'") > "" Then
        'TextboxA is Unbound
        TextBoxB = "This would be the end result if the value of TextBoxA matches the value of the column table."
    End If
End Sub

[munkee]

Might be useful to note that all of the domain functions have been said to run quite slowly. So if the performance of your form seems to have been affected adversley it might be worth having a google for ELookup. Exact same expression really but re written to not be so sluggish from what I have read.

[NeoPa]

While that's true Chris, it's one of those things that should rarely, if ever, cause a problem.

If it's used in such a way to cause any sort of noticeable delay, then the code design is almost certainly poor. Generally it's run after an operator has intervened in some way, and compared to operator times the fraction of a second of difference that it makes is hard to notice. If it's used in such a way as to be called a number of times within such a window then the performance degradation may be noticeable. In such a situation though, I'd focus my attention on the code design rather than the Domain Aggregate functions.

[mseo]

Might be useful to note that all of the domain functions have been said to run quite slowly. So if the performance of your form seems to have been affected adversley it might be worth having a google for ELookup. Exact same expression really but re written to not be so sluggish from what I have read.

hi, Munkee
what you said is true in some cases
but DLookup is completely appropriate for looking up a single value from a table. you can Use it safely. the only problem with DLookup is that it couldn't distinguish between a Null and a zero-length string.

[NeoPa]

mseo: The only problem with DLOOKUP is that it can't distinguish between a Null and a zero-length string.

I've never seen any evidence of that MSEO. In what way have you found there to be a problem? Is this a case of not handling the returned result correctly perhaps?