Need to Set Text Box Value to Query Result

bconner

Registered User.
Local time
Today, 05:44
Joined
Dec 22, 2008
Messages
183
I have a Form with a Text Box and I would like to have the value of the text box be a field from a query. Basically when a Report name is selected in a Combo Box I would like for the report Description to show up in the Text Box....
 
You can't display the result of a query in a textbox, you'll have to use the DLookup() function. In the AfterUpdate event of the combobox

Code:
Me.Description = DLookup("DescriptionFieldName", "QueryOrTableName", "[ReportName] = '" & Me.ComboboxName & "'")
 
Thank you very much.....
 
There is really no need for the AfterUpdate code ... you an just set the control source of a text box to the expression ...

= DLookup("DescriptionFieldName", "QueryOrTableName", "[ReportName] = '" & [ComboboxName] & "'")

But ... there is even a BETTER way... set your rowsource of the combo box to return the DescriptionFieldName, then set your ColumnCount to one more than it already is, then set the width of the DescriptionFieldName column to 0 if you wish to hide it from the user.

Next is the text box you wish to show the description of the selected item, set the Control Source to ...

=[ComboBoxName].[Column](indexnumber)

Where indexnumber is the column index number with the left most being 0. So if you want the second column, you would set the indexnumber to 1, the third column would be index number 2, etc ...

By doing this, you don't have the delay or overhead of the of the DLookup().
 
You're talking about three methods, all of which work, each one using one line of code! How do you figure any of the three ways are the "best?"
 
I certainly would not use dlookup unless i had to - VERY slow.
Allen Browne's elookup replacement is much better.

Evan
 
>> I certainly would not use dlookup unless i had to - VERY slow. <<

I have put my foot in my mouth with that kind of statement too. It is a very generalized statement that I have found not to be true in MANY more cases than I care to admit. Basically, the domain aggregate functions are, in many situations FASTER, the Allen's code.

-- Here is a clip of a previous thread on the speed of Domain Aggregate functions that applies here too --

The "secret" to fast DA's is proper indexing and utilizing those indexed fields as your criteria for the DA. I used to beleive that the DA's were slow also, even through my own testing (in A97) they were slower than opening recordsets, however, as my knowledge base increased, AND participating in the thread above, I have changed my view on the DA's, and now do NOT shun them! They are quite useful, and simple! I would encourage you to do side by side comparisons on between some recordset "lookups" and the DA's to see which is faster, you might be surprized!

It is important to note, however, that the DA's are NOT ALWAYS faster! ... Like if you have an "Always Open" recordset ... you are probably better off searching that recordset, instead of using a DA to, retrieve a value.

--- End Clip ----

Also, here are a few links to read on your own that have speed data in them ...

http://www.utteraccess.com/forums/showflat.php?Number=1097439&fpart=all#Post1097439 {Note in this thread, my initial post was in defense of "custom" domain ag. functions}

http://www.utteraccess.com/forums/showflat.php?Number=1216828&fpart=all#Post1216828 {This thread has some code and a description of when I have used custom domain ag. functions ... twice as fast as the standard DA}

Ultimately my table designs utilize the stuff the standard Domain Aggregates like, and thus I use the DA functions as I need to (not exhorbitantly, but truly as needed). If I think they are "slow" I will give the custom solution a go.
 
>> You're talking about three methods, all of which work, each one using one line of code! <<

I suggested 2 methods ... and NEITHER of them uses code! ... :)

The .Column method is best simply because it opens a the table (recordset) ONCE to get the data in order to poplate the combo, so the [Combo].[Column](index) is simply referencing a value that is already in memory and "right there" ready for you to grab.

The DLookup() solution is OK, but EVERY time the expression is evaluated (ie: navigation or a forced refresh, DLookup() will open the table, find the value, and close the table. The .Column method has already retreived all the data, so why force access to spend the time to open, retrieve, close again.

Your code based solution is the "heaviest" of the options simply because of the call to VBA, in addition to the DLookup() overhead of open, retrieve, close. If you like code, thats fine, but I would suggest that, even in code, the .Column method is used.

EDITS ADDED>>>
I just noticed that you are from Richmond,VA ... I now work in Purcellville, VA (Northern part of VA, west by north west of DC) ...
 
I'm sorry, but I've been hearing this %^& about how slow DLookUp() is for so long, and the fact is you're talking about times so short as not to be measurable in any real-world application, considering today's processors' speeds and running a single or even two DLookUps when loading a record. I wouldn't run the function twenty times a record, but people who go to the trouble of opening and searching a recordset in order to avoid running a single DLookUp() are just being silly. The problem is that someone writes that a function is slow, and a decade later everyone is still repeating the comment, disregarding the fact that the latest technology has made the entire point moot.

Allen's replacement was, I have no doubt, great, when it was written, and it can still do some things that DLookUp() can't. But it, too has a number of problems, as he himself admits, including the fact that it can return garbage at times and may require rewriting if used in a query!

We'll just have to agree to disagree on this.

Just as an aside, I do miss Allen's presence on the various forums/newsgroups these days. I had occasion to email him a month or two ago, on something unrelated to Access, and while he's supporting old clients and doing a little training, he's primarily working as the director of a Christian Leadership College in his hometown of Perth.

Late Note: Sorry, Brent! I had to leave in the middle of writing this for half an hour to settle the menagerie in for the night, and didn't realized you'd been back twice. Yeah, I once spent a week in Purcellville one day! But that was a long time ago! Guess like everything in NorVA it's grown!

Take care!
 
Last edited:
missinglinq ...

I assume your response was for evan ... since we pretty much agree on the use of DLookup(), but even with speed and technology, I like to be efficient, so .. as stated above .. I have been in scenarios in which DLookup() was just plain slow (SQL Server back end, 3 million rows of data) ... so I used my custom version which forced SQL Server to do the lookup on the server level. The custom solution blew the doors off DLookup().

... Basically, choose the right thing for the job at hand ...
 
Perhaps I'm in the dark ages, but I used a hi-res timer to compare dLookup and eLookup and seemed to find that eLookup was consistantly 5 to 10x faster. Does this mean, perhaps, that I have problems with my indexes?

I'm certainly not attached in stone to one method over another.... I just learn from what I read.

Thank you,
Evan
 
>> Does this mean, perhaps, that I have problems with my indexes? <<\

Not necessarily ... you may be looking up info on a non-indexed column, and in those cases, a custom solution may edge out the inherent functions.

Example:

DLookup("SomeField", "SomeTable", "SomeIndexedField = SomeValue")

Will most likely be faster than ...

eLookup("SomeField", "SomeTable", "SomeIndexedField = SomeValue")

... However ....

DLookup("SomeField", "SomeTable", "SomeNonIndexedField = SomeValue")

Will possibly be slower than ...

eLookup("SomeField", "SomeTable", "SomeNonIndexedField = SomeValue")

.....

But ... that is a generalization and I have seen non-indexed DLookups() outperform the custom solutions as well... So, I have taken the approach that I will use DLookup() if I need to. If that portion of the app is hinderingly slow, I will give the custom solution a go, especially if the back end is NOT JET|ACE, and is a transactional database server (ie: Oracle or SQL Server). In those cases, I often will use a custom solution, like the code I linked to above, in order to force the db server to process the request.
 
Thanks for the details. I will keep it in mind and perhaps give dLookup another chance.

Evan
 

Users who are viewing this thread

Back
Top Bottom