Avatar of Jenkins
Jenkins
Flag for United States of America asked on

Access Query help

I've attached an Access database. When you open Form1, you'll see 3 comboboxes, a textbox and a subform.  The After Update event of combobox3 causes a query to run that populates the subform. I want the query to populate the textbox instead of the subform. How do I do it?  Thanks.
TEST1-1.mdb
Microsoft Access

Avatar of undefined
Last Comment
JanChanalytics

8/22/2022 - Mon
Jim Dettman (EE MVE)

Simplest method is to set the text controls controlsource property to:

=Dlookup("[<field to be returned>]","<query name>")

 and in the Afterupdate,

 Me.<text box control name>.Requery

Note that Dlookup is OK for one or two controls, but if your going to populate more controls from the same record, you're going to want to open a recordset on the query and push the data into each of the controls from the AfterUpdate event.

Jim.
SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jenkins

ASKER
OK. I got the DLookup method you suggested working but there's a slight delay in its returning a result.   Is there a way to get the result into the textbox faster? It's a very small database.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Dettman (EE MVE)

<<I hate using Dlookups as they are quite sluggish.>>

 This is actually a bit of myth.  Dlookup() and the other domain functions can be as fast as or faster in some cases then other methods.

  They often get a bad rep because they are used in places where they never were meant to be used (like a query).

  In fact I bet if you checked DLookup() it would be faster then the code you posted as your using CurrentDB() in each statement rather then holding a reference to it.

 Of course if your getting more then a field or two it will always be faster to open a recordset and fetch the record, at which point you'll have access to all the fields, but there are situations where a DLookup() is not a bad thing.



Jim.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
JanChanalytics

Bet mine is lots faster !  Lots and lots faster with or without the CurrentDBs.

I tried yours and it was not exactly nippy, mine was instantaneous.

CurrentDb is quick and elegant and unless you want to make changes to the table structure it will be the most reliable method.

Dlookups are simple and quick to code, so I like them, but they are also slow, thats why I wouldn't recommend using them here.