Prev: BETWEEN criteria not working correctly
Next: VBA Editor Erratic Behaviour
From: christianlott1 on 8 Jul 2010 17:06

How do I get past this problem, where I want to place a variable where
it's expecting the exact name:

strTable = "Query1"

Set rs = CurrentDb.QueryDefs!(strTable).OpenRecordset



(I really want to use this in a loop, so this is just a simple
example..)

thanks
From: Bob Barrows on 8 Jul 2010 17:19
christianlott1(a)yahoo.com wrote:
> How do I get past this problem, where I want to place a variable where
> it's expecting the exact name:
>
> strTable = "Query1"
>
> Set rs = CurrentDb.QueryDefs!(strTable).OpenRecordset
>
>
Get rid of the bang operator. This should work

Set rs = CurrentDb.QueryDefs(strTable).OpenRecordset

--
HTH,
Bob Barrows


From: christianlott1 on 8 Jul 2010 17:47
Thanks!
From: David W. Fenton on 9 Jul 2010 19:18
"Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> wrote in
news:i15fdo$q53$1(a)news.eternal-september.org:

> christianlott1(a)yahoo.com wrote:
>> How do I get past this problem, where I want to place a variable
>> where it's expecting the exact name:
>>
>> strTable = "Query1"
>>
>> Set rs = CurrentDb.QueryDefs!(strTable).OpenRecordset
>
> Get rid of the bang operator. This should work
>
> Set rs = CurrentDb.QueryDefs(strTable).OpenRecordset

Just to explain why, so it doesn't seem arbitrary:

The ! is a shortcut for using an object's default collection. When
you use Me!MyControl on a form or report, you're using the default
collection (in forms and reports it is a union of the controls and
fields collections). In a recordset, the default collection is the
Fields collection, so rs!NameOfAField is a way to directly refer to
a particular field.

An alternative way is:

rs.Fields("NameOfAField")

....which can be abbreviated:

rs("NameOfAField")

For the object collections (TableDefs, QueryDefs, etc.), you can use
the bang when you know the name:

CurrentDB.TableDefs!NameOfATable

....which is an equivalent to:

CurrentDB.TableDefs("NameOfATable")

....and when you have a variable, you can pass it instead of the
literal string:

CurrentDB.TableDefs(strTableName)

So, you can use a bang when you know the exact name, and the () for
items in the collection when you don't know it and need to use a
variable.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
 | 
Pages: 1
Prev: BETWEEN criteria not working correctly
Next: VBA Editor Erratic Behaviour