accessing data from an open query

[richardconers]

I have access xp. I want to obtain a value from a field of an open
query (let's say a query called MyQuery) using vba. I tried using
docmd.gotorecord which takes me to the record I want. I would like to
obtain the value of a field (let's say a field called CID).
However the code anumber =
screen.activedatasheet.Recordset.Fields("CID").Value gives the error
that there is no activedatasheet (since I'm running the vba from a
form, the query window is not the active window). How do I obtain the
value of the field CID in the open query MyQuery?

Thank you!!!

 

[Nikos Yannacopoulos]

Don't bother with opening the query at all! Just use a DLookup function
on the query.

Nikos

 

[richardconers]

Thank you for your reply but I didn't explain this fully enough. I
have an open query (MyQuery) that will be on a certain record (let's
say recnum 11), but I won't know which record it is on while the code
is running. I want to capture a value from that specific current record
of the open query. If I capture that value I know how to get a form to
move to that record in the form's recordset. So if i use Dlookup I
still need to know how to get dlookup to go to the current record of
the open query. that's why i tried to use
screen.activedatasheet.recordset.fields("CID") but I get an error
because the open query isn't active. What can I do?
Thanks!!!!

 

[M.L. Sco Scofield]

First make the query active with:

DoCmd.SelectObject acQuery, "qselYourQueryName"

Then you can make your form active again with:

Me.SetFocus

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[Nikos Yannacopoulos]

Richard,

You should not be working with queries to begin with! All user
interaction with data should be through forms. I do not know what you
are trying to do, so I cannot suggest a more suited way, but you can
certainly create a form (datasheet view) based on the query, which will
look the same as the query, and will allow you to reference the ID
control on the form and get the current record's value.

HTH,
Nikos