Placing a figure from a query in a textbox 3

[IANGRAND]

I have created a query in access and need to show the figure output from the query in a textbox. Ive tried several methods but each one mearly recalls the entire query, whereas i require only to view the figure in the textbox.

Does anyone know how to place a figure from a query in a textbox

Regards

Ian

 

[KenReay]

Hi

Create a recordset from the query

Dim Db as DAO.Database
Dim rs as DAO.Recordset
Dim qdf As DAO.QueryDef
Set Db = CurrentDb()
Set qdf = db.QueryDefs("YourQueryNamehere&quot
Set rs = qdf.OpenRecordset

txtBox = rs!TheRelevantColumnName

Set qdf = Nothing
Set Rs = nothing
set db = nothing

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

You will need a reference to the DAO library, if you do not already have one

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ITMannie]

Hi

I think it's also worth mentioning the Domain Aggregate Functions that are available in Access. In particular, the DLookup function would do here.

The code would be:

txtValue = DLookup("[FieldName]", "QueryName&quot

or you could just set the control source to

=DLookup("[FieldName]", "QueryName&quot

Cheers

Iain

 

[IANGRAND]

Cheers ITMannie,

Did the job.

Ian

 

[gotnomoney]

Thanks of much ITMannie, just what I needed.

Gary

 

[pbbriggs]

Ken, if you're still out there (or if anyone else knows the answer)...

I wanted to implement Ken's solution to this problem (bringing a field from a query into a text box on a form whose record source is a different table). I have already tried using DLookup, and it works -- sort of -- but is very slow and does not seem to work consistently on my "continuous forms" style form.

I wasn't sure where to put the code Ken mentioned, and then how to actually get the value of rs!MyColumnName into the text box. Is the "txtBox" line actually part of the code, or what I should enter into the control source of the text box itself? I tried putting the code into the OnOpen event of the form, and then tried entering various things as the control source of the text box ("=txtBox", "rs!MyColumnName", etc.) and keep getting the #NAME? error. Clearly, I am just not quite sure what to do w/the code.

I have read several times that the recordset method is better (faster) than the DLookup, so I would like to use it, but am sort of at a loss as to how to get this to work properly (FYI, linking the table to the query in an underlying query is not an option here).

Thanks for any help.
Patricia

 

[KenReay]

Hi

To set the value from the recordset into the textbox

txtBox = rs!TheRelevantColumnName

where txtBox is the name of the textbox control, and Rs!TheRelevantColumnName is the name of teh column within the recordset (Rs)

It is not possible to give a hard and fast rule as to where to put this code, it depends on what you are doing, with a form the OnCurrent Event is often a good bet, with a report the OnFormat Event is often a good choice



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pbbriggs]

Wow, obviously I had been working too many hours straight on this project yesterday, because I kept reading the "txtBox =rs!..." line, thinking, "hmmm, he never defined the variable txtBox..." Doh! This sounds very logical. Will give it a try when I get to work. Have been having a lot of trouble getting DLookup to work quickly enough - and it also seems to have quirks when the recordset gets large. So, I will give it a try, leaving the control source for the text box initially empty but then setting it this way via the code. Thanks, Ken!

 

[pbbriggs]

Just an FYI for future readers of this thread, I don't believe this code will work for a text box on a continuos form (unless someone knows something I don't). It seems to just pull the first value of the field and populates all rows of the continuous form with that value.