CODEWITHSUNDEEP
ms-accessvbams-access-2013
Erfan Gholamian
Erfan Gholamian

Reputation: 225

retrieving data from a query inside a form

I have a form in MS access which contains information about customers of a store (It's directly connected to the table because I want to be able to edit fields). I want to have a field inside the form which contains information calculated in a query in the form of (ID, Value) How is it possible?

Upvotes: 0

Views: 89

Answers (2)

Parsa Keshavarz
Parsa Keshavarz

Reputation: 26

Derive data using either VBA or Macro

Upvotes: 1

MoondogsMaDawg
MoondogsMaDawg

Reputation: 1714

Since your form is bound, and the field in your query is not a field in the bound table, you can simply add an unbound control to your form. You can use a textbox control, draw on form, and it should default to an unbound control. To check, select the new control and confirm that its control source property is blank.

I am assuming that your query already has form references that are filtering the results based on which record you have open on the form. If not, you will want to put in the ID field's criteria of your query: [Forms]![MyForm]![MyBoundIDControlNameOnForm]

In your form's On Current event. Open the code builder and type this into the On Current event: Me!txtMyNewControlName = DLOOKUP("[Value]", "MyQueryName")

DLOOKUP has a third argument that allows you to add criteria. I am assuming that your query only returns a single row with the form reference criteria, so there is no need to tell DLOOKUP which row in the query results to return.

Upvotes: 0

Related Questions