CODEWITHSUNDEEP
ms-accessvba
Georg Windhaber
Georg Windhaber

Reputation: 45

Dynamic Query in a MS-Access form

I have a form in Microsoft Access (2016) where I have a dropdown list named KID. When the dropdown list looses Focus I want to run a query that would look like this:

SELECT max(Cnt)
FROM Table
WHERE KID = ValueOfDropdownList

The result of this query should be usable in a variable in the VB Code of the Event.

Unfortuantly I am restricted to Access at the Moment. Am I even using Access the right way (Choose from dropdown in form -> Event -> SQL Query in VB -> Display in form) or are there better ways to do that?

Thanks for your help in advance!

Upvotes: 0

Views: 2000

Answers (3)

iDevlop
iDevlop

Reputation: 25262

FYI there is another option. As the data source of your combo,
use a query returning 2 columns, like select KID, max(cnt) from table1 group by KID.
Then in your text box, retrieve your value using =combo1.columns(1).
For this to work you need to specify in the combo properties that it has 2 columns. In the formula, don't forget that those combo columns are 0 based.
This is the most efficient solution when you need to retrieve several values linked to the combo (and you don't have millions of records).

Upvotes: 0

Minty
Minty

Reputation: 1626

Firstly I would use the AfterUpdate event of the combo box, otherwise it will fire even if someone just tabs through the control.

Secondly if this is just to set a variable you would probably be better to use a Domain function in this case a DMax() 

iVariable = DMax("Cnt","Table","[KID] = " & Me.YourComboControlName )

This assumes KID is a number field. If KID is text you would need to delimit the criteria like this; 

 iVariable = DMax("Cnt","Table","[KID] = '" & Me.YourComboControlName & "'" )

Lastly if you want this to update as you scroll through records you would need to add the same code to the OnCurrent event of the form. That fires when the record changes.

If it is for display purposes you can't use the same technique on a continuous form if this is setting an Unbound control, you would need to pull this in to the underlying forms recordset.

Upvotes: 2

Gustav
Gustav

Reputation: 55831

You can use DMax:

YourVariable = DMax("[Cnt]", "[Table]", "[KID] = " & Me!DropdownListName.Value & "")

In case KID is text, apply quotes:

YourVariable = DMax("[Cnt]", "[Table]", "[KID] = '" & Me!DropdownListName.Value & "'")

Upvotes: 0

Related Questions