Getting a query value into a module? 2

[vanillapod]

Okay i've tried long enough, i have a query that groups on a year value and outputs only the year. How can i get these values into a module? I want to use each value retruned by the query to be used by the module to check for a directory called that, e.g. 1999 and if it isn't found then create it...... creating dir's is easy how do i get the query values into my module though???

Cheers

 

[JimHorton]

Vanilla,
You could pass the value into a function...ie. in the Field: row of the query grid, just put your function name with the year value ie:
X: Myfunction(Year([mydatefield]))
This assumes your function accepts this argument and expects to do this one-by-one.

If you want to do it all in the function calling the function just once, then you can open a recordset based on this query, and use the field in question in the logic in a loop through all records.

--Jim

 

[vanillapod]

Hey Jim,
Could you give me a bit more help with the calling the function just once method???
Thanks a lot

 

[vanillapod]

Hey Jim,
Could you give me a bit more help with the calling the function just once method??? I've never done that before....
Thanks a lot

 

[JimHorton]

vanilla,
All you'd need to do is create the query, then in a module open a recordsest based on the querydef, ie:
dim qd as querydef, rst as recordset
set qd = currentdb.querydefs("queryname&quot
set rst = qd.openrecordset(dbopensnapshot) 'snapshot would be default anyway
do until rst.eof
'the directory verify/create code goes here based on rst!X
loop
--Jim

 

[GGleason]

Vanillapod,

Suppose you had a query by the name of qryMine with a field called MyYear. The variable would be saved to strField.

Within the module, you could do the following:

Set dbs = OpenDatabase(CurrentDb.Name)
Set rstQuery = dbs.OpenRecordset(qryMine)

rstQuery.MoveLast
x = rstQuery.RecordCount
rstQuery.MoveFirst

For i = 1 To x
strField = rstQuery.Fields("MyYear&quot
‘ data manipulation goes here
rstQuery.MoveNext
Next i

rstQuery.Close
dbs.Close

HTH,
GGleason

 

[GGleason]

Vanillapod,

A minor correction to the code (quotes around query name, unless you have the query as a variable, then it has no quotes):


Set dbs = OpenDatabase(CurrentDb.Name)
Set rstQuery = dbs.OpenRecordset(”qryMine”)

rstQuery.MoveLast
x = rstQuery.RecordCount
rstQuery.MoveFirst

For i = 1 To x
strField = rstQuery.Fields("MyYear&quot
‘ data manipulation goes here
rstQuery.MoveNext
Next i

rstQuery.Close
dbs.Close


Thanks,
GGleason

 

[vanillapod]

Cheers for all the responses, I'll go do it now!!!
Thanks again

 

[vanillapod]

Okay here's the code i've been using... cheers to everyone above! However when i try and run the module i get an error saying "Compile Error: Method or Data Member Not Found" any ideas?

Dim dbs As Database
Dim rstQuery As QueryDef
Dim QueryName
QueryName = "UPLOADSYears"
Set dbs = OpenDatabase(CurrentDb.name)
Set rstQuery = dbs.OpenRecordset(QueryName)

rstQuery.MoveLast
x = rstQuery.RecordCount
rstQuery.MoveFirst

For i = 1 To x
strField = rstQuery.Fields("MyYear&quot
‘ data manipulation goes here
rstQuery.MoveNext
Next i

rstQuery.Close
dbs.Close

 

[GGleason]

vanillapod,

When you are viewing the module, go to Tools->References. Make sure the following references are checked:

Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.5 Object Library

I don't know if this is the problem, but I would check it.

HTH,
GGLeason

 

[vanillapod]

Hey GGleason cheers for the quick response...
Yep there all there, this is typical the last part of my database to get working and the most troublesome.....

 

[GGleason]

vanillapod,

Here is a minor correction to one of the Dim statements:

Dim rstQuery As Recordset

You also may want to add this entire line:

Dim rstQuery As Recordset, i As Long, x As Long, strField As String


HTH,
GGleason

 

[vanillapod]

Oh yeah, its a recordset isn't it! Just tried it and it works fine, thank you sooooooo much....