Referencing a query field

[rcrobman]

It seems to me that you used to be able to reference a query field much the same way as you reference a form field i.e. Forms!Formname!Formfield and I use it like:

something = Forms!Formname!Formfield

However when I try to do it with a query Query!Queryname!Queryfield(that has been opened) such as below

Email = Query![Tech Support Survey Notice]!Email

All I get is an error message that:



"Qualifier must be a collection"

Unfortunately I cannot find any reference on how to address a query field directly so I keep going around in circles.

Anyone know why this doesn't work?

Thanks

[pbaldy]

To my knowledge you've never been able to get the value like that (I'm assuming the query is not the record source of the form). You either need to open a recordset on the query or use a DLookup().

[rcrobman]

Well I have been working on Access for some time and this might have been something a long time ago but sure doesn't work now.
Thanks for the reply

So perhaps if I explain what I am trying to accomplish in steps:
1) Look at a table to figure out how many emails need to be sent (DCount)
2) Open a query that loads the appropriate data for the email into the query
3) Reference the query line by line (Do While using the info from step 1 ) using Gotorecord to then populate a SendObject command with the email address etc
4) Decrement the email count by 1 and start again.

I know that using a recordset would probably be a more elegant way to do this but I can never get them to work - every time I try to do the definitions I get errors that I can't get around so end up reverting to trying to "extract" the data using forms/queries etc.
The recordset problem is fiurther complicated by the fact that all of the tables are linked from a SQL Server back end DB.

I am sure there are better ways to do this but I want to keep everything inside of Access (as opposed to creating an email in Publisher for example).
Thanks for any help provided!

[pbaldy]

Well, that would definitely be the hard way. I'd recommend working out the recordset kinks, as that would be the cleanest way to go. Here's the shell of a recordset loop:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT * FROM ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF
    'do your thing here
    rs.MoveNext
  Loop

  set rs = nothing
  set db = nothing

In some versions you'd have to make sure the MS DAO reference was set in Tools/References but in 2007 it should work "out-of-the-box". If you're emailing a report, there's a lot of good info here:

Emailing a different report to each recipient

[rcrobman]

It took me a couple of days to get around to it but thanks to your help I got the recordset working properly. I don't know why your code worked but the sample code I was using from MSoft didn't but at this point it doesn't matter!
Thanks for the great help!
Truly appeciated!

[pbaldy]

Happy to help!