Access Query help

dbfromnewjersey
dbfromnewjersey used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
actually, a better way to do that might be to leave the text controls controlsource blank and in the afterupdate event of the combo, simply do:

 Me.<text box control name>=Dlookup("[<field to be returned>]","<query name>")

Not quite sure what your ultimate goal is, so that may be a better approach.

 and if the data is already part of the combo, you can grab it woith the column property:

Me.<text box control name>=Me.<my combo name>.Column(x)

 where x is the column of the combo that contains the data.

Jim.

Author

Commented:
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.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

I hate using Dlookups as they are quite sluggish.

I also don't like saving information in Queries linked to the form.
I would recommend a much faster way of doing things.

This will not be effected by the volumes of data that you will get.
And also does away with the need for 3 queries.

Also using only the update on Combo box 3 is not really correct.
It should work regardless of which combo has been played with
try this out

Private Sub ComboBox1_AfterUpdate()
    UpdateTotal
End Sub

Private Sub ComboBox2_AfterUpdate()
    UpdateTotal
End Sub

Private Sub ComboBox3_AfterUpdate()
    UpdateTotal
End Sub

Sub UpdateTotal()
Dim rst As DAO.Recordset
    Dim dblTotal As Double
    
    'Me.Current_Balance_sub.Requery
    Set rst = CurrentDb.OpenRecordset("SELECT Sum(MasterTable.OpeningBalance) AS CB FROM MasterTable WHERE (((MasterTable.AccountNumber) = '" & ComboBox1 & "') And ((Mid([MasterTable].[Series], 1, 1)) = Mid('" & ComboBox2 & "', 1, 1)) And ((MasterTable.SubSeries) = '" & ComboBox3 & "'))", dbOpenSnapshot, dbReadOnly)
    If Not rst.EOF Then
        dblTotal = Nz(rst(0), 0)
    End If
    Set rst = CurrentDb.OpenRecordset("SELECT Sum(-[TransactionAmount]) AS CB FROM TransactionsTable WHERE (((TransactionsTable.AccountNumber)='" & ComboBox1 & "') AND ((Mid([TransactionsTable].[Series],1,1))=Mid('" & ComboBox2 & "',1,1)) AND ((TransactionsTable.SubSeries)='" & ComboBox3 & "'))", dbOpenSnapshot, dbReadOnly)
    If Not rst.EOF Then
        dblTotal = dblTotal + Nz(rst(0), 0)
    End If
    Me.txtGetResult = dblTotal
End Sub

Open in new window

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<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.
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial