473,320 Members | 2,020 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub TextboxA_AfterUpdate()
  2. If TextboxA = (value from table column) Then
  3. TextboxB.Forecolor = vbBlack
  4. TextboxB.Text = "Signed In"
  5. End If
  6. End Sub
My apologies for any confusion, just want to know the code that looks up value from a table.
Jul 24 '10 #1
21 2300
MikeTheBike
639 Expert 512MB
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
Jul 24 '10 #2
munkee
374 256MB
You can use DLookup I expect

General syntax is DLookup(expression, domain,[Criteria])

So for some vba code you can use:

Expand|Select|Wrap|Line Numbers
  1.     Dim SDate As Date
  2.  
  3.     SDate = DLookup("DateSentColumn", "tblOrders", "OrdID = 444")
  4.  
  5.  
Jul 24 '10 #3
@MikeTheBike
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?
Jul 24 '10 #4
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.
Jul 24 '10 #5
NeoPa
32,556 Expert Mod 16PB
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.
Jul 24 '10 #6
MikeTheBike
639 Expert 512MB
@NeoPa
Thanks Neopa, my brain definitly said DLookup() but my fingers thought otherwise!?

MTB
Jul 24 '10 #7
NeoPa
32,556 Expert Mod 16PB
Don't tell anyone ;), but I've done exactly the same myself in the past :D
Jul 24 '10 #8
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub TextboxA_AfterUpdate()
  2. If TextboxA = DLookup() Then
  3. 'TextboxA is Unbound
  4. TextBoxB.Text = "This would be the end result if the value of TextBoxA matches the value of the column table."
  5. End If
  6. End Sub
Jul 24 '10 #9
mseo
181 100+
@Jonathan Austin
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub textboxname_BeforeUpdate(Cancel As Integer)
  2.    Dim bytes As Variant
  3.    bytes= DLookup("[fieldname in the table]", _
  4.                     "table name", _
  5.                     "[fieldname in the table] = '" & Me.textbox control name& "'")
  6.   If Not IsNull(bytes) Then
  7.      ' write a code for doing whatever you want 
  8.    End If
  9. End Sub
again use your names

P.S you can use the same syntax within afterupdate trigger
hope this helps
Jul 24 '10 #10
NeoPa
32,556 Expert Mod 16PB
You're looking for something on the lines of :
Expand|Select|Wrap|Line Numbers
  1. Private Sub TextBoxA_AfterUpdate()
  2.     If DLookup("[YourField]", _
  3.                "[YourTable]", _
  4.                "[YourField]='" & Me.TextBoxA & "'") > "" Then
  5.         'TextboxA is Unbound
  6.         TextBoxB = "This would be the end result if the value of TextBoxA matches the value of the column table."
  7.     End If
  8. End Sub
Jul 24 '10 #11
munkee
374 256MB
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.
Jul 24 '10 #12
NeoPa
32,556 Expert Mod 16PB
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.
Jul 24 '10 #13
mseo
181 100+
@munkee
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.
Jul 24 '10 #14
NeoPa
32,556 Expert Mod 16PB
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?
Jul 24 '10 #15
mseo
181 100+
@NeoPa
hi,
thanks for your comment
this code, I have used it many times and it works fine, without any delay
Jul 24 '10 #16
mseo
181 100+
ok, Neopa,I am completely agree with you and munkee
I am wrong
I need to know, what's the difference between Null and Zero-lenght string?
Jul 25 '10 #17
munkee
374 256MB
null has no value whereas zero length has been assigned a "blank" value
Jul 25 '10 #18
mseo
181 100+
@NeoPa
so why would I use something like this with datatype number fields:
Expand|Select|Wrap|Line Numbers
  1. Nz(DLookup("[fieldname in the table]", _ 
  2.            "table name", _ 
  3.            "[fieldname in the table] = " & Me.textbox control name& ""))
Jul 25 '10 #19
NeoPa
32,556 Expert Mod 16PB
mseo: I need to know, what's the difference between Null and Zero-lenght string?
Null is not a value as such at all. It is an indicator of an absence of a value.

An empty string, on the other hand, is a string value that simply has no characters in it. Strings can be of any length from 0 characters upwards, to the upper limit. Of the VBA variable types (excluding any object types), only Variant can possibly hold a Null, as standard variables are designed to hold values of a specific type only and Null is none of those.

I hope this clarifies things for you somewhat :)
Jul 25 '10 #20
NeoPa
32,556 Expert Mod 16PB
mseo: so why would I use something like this with datatype number fields:
Expand|Select|Wrap|Line Numbers
  1. Nz(DLookup("[fieldname in the table]", _ 
  2.            "table name", _ 
  3.            "[fieldname in the table] = " & Me.textbox control name& ""))
Good question. That's code that cannot possibly compile.

If you are asking why the use of Nz() for a number field then that's a different story.

First of all, the Nz() is applied to the DLookup() call and not the field. If you check the Help system for DLookup() you'll see it returns a Null if nothing is found.

Secondly, fields are not VBA variables. They are designed to have the possibility of containing Nulls. This is configurable in the design of your table fields, but the possibility is there.
Jul 25 '10 #21
NeoPa
32,556 Expert Mod 16PB
A new, and unrelated question was asked in this thread by the OP which has now been moved to Clear Error Message from TextBox. This is against our rules and I'd ask that you create new threads for your questions please.
Jul 25 '10 #22

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: P.Hill | last post by:
I have this simple bit of code that when you check a checkbox and it displays or hides two other fields (well actually two parts of the document with two different IDs. Pretty straight forward......
3
by: MLH | last post by:
What is the value of X immediately after declaring the variable? I thought its value was Null. However, I am unable to assign a value of Null to X... X = Null produces an error. I don't...
9
by: Mike L | last post by:
I tried a sample of code in MSDN magazine, but now I'm stuck. What code has the best performance to populate a Data Grid with a SP? Below is the code I have, which might be completing the wrong...
0
by: VM | last post by:
I have a pretty big application made in C but I only want to view the code that's being executed when a button in a toolbar is clicked. Since I didn't write the program, is there an easy way to...
1
by: Mateusz Rajca | last post by:
Hello, What code should I write to hide the taskbar? Mateusz
1
by: Pete Smith | last post by:
I am making the animated text inside the table cell data. The animation is done using Javascript. Here is the code looks like.. <table><tr><td><script>Java Script code which does animation of...
3
by: NEWSGROUPS | last post by:
Is there any way to find out what database object or table was corrupt after a repair has run in Access 2000? If I can find this out I may find out why the corruption is happening. Any help would...
1
by: parvathiganesan | last post by:
what is a virtual table in plsql?
0
by: 2Barter.net | last post by:
From: 2Barter's Google.Org <chasingcowtomusic@gmail.com> Date: Jul 24, 2007 8:15 PM Subject: Big Money foe New Orleans " Blessings "http://mail.google.com/mail/?...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.