Link to home
Start Free TrialLog in
Avatar of Jenkins
JenkinsFlag 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
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

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
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<<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.
Avatar of JanChanalytics
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.