If you're using a saved query, you'll need
'Create an object pointing to the query
Set qry = CurrentDb.QueryDefs([name of query])
'Set any parameters used in the query here, they're 0-based so
'if you have 5 parameters, you would reference each as
'0 for the first, 1 for the second, 2 for the third, etc.
qry.Parameters(0) = [parameter Value]
'Create an object pointing to the query's recordset, when
'working with a query, .OpenRecordset does not require a sql
'statement since it pulls it from the qry object
Set rs = qry.OpenRecordset(dbOpenForwardOnly)
and of course
rs.close
qry.close
Set rs = Nothing
Set qry = Nothing
I was going to include this yesterday, but since I don't work with
QueryDef's that often I could ad it off the top of my head and didn't
have time to look it up as I was headed out.
ryan_eng said:
Thanks for all the help guys.
I put in the code suggested and I get the following error:
Run-Time Error '3061': Too few parameters. Expected 1.
Code stops at this line:
Set rs = db.OpenRecordset(sql_1, dbOpenForwardOnly)
sql_1 is simply a SQL string I define earlier in the code.
Whats my next step?
RYAN
:
Sorry the qry. should be db. my bad, I edited the post without changing
the code.
Yes. [sql_string] can be either a formal SQL statement or the name of a
table as such a variable that contains either will work as well.
To snag the data use rs.Fields([fieldName]) its also possible to use
rs![fieldName] however I've recently been having problems with that syntax.
ex
Debug.Print rs.Fields([fieldName])
-or-
lngRunningTotal = lngRunningTotal + rs.Fields([fieldName])
-or-
objword.ActiveDocument.Tables(4).Cells(Row,Col).Range.InsertAfter Text:=
rs.Fields([fieldName])
ryan_eng wrote:
I stand corrected. Ok, this time I'll make sure I do everything by the book.
SO, I would appreciate a couple of clarifications pretty please...
In this line of code:
"Set rs = qry.OpenRecordset([SQL Statement], dbOpenForwardOnly)"
if I create an SQL string and store it as a variable, say sql_string, would
the statement read:
"Set rs = qry.OpenRecordset([sql_string], dbOpenForwardOnly)" OR
"Set rs = qry.OpenRecordset(sql_string, dbOpenForwardOnly)"
Second question.
In this While loop:
"While NOT rs.EOF
[Record-level processing here]
rs.MoveNext
wend"
How would I snag the contents of one field from that record such that it
ends up in the word document table: ie
objword.ActiveDocument.Tables(4).Cells(Row,Col).Range.InsertAfter Text:= XXXX
Thanks dudes!
RY
:
That makes no sense at all!
DAO comes from the Access program itself: nothing to do with shared drives.
As long as you've got Access properly installed on your workstation, you
should be able to use DAO.
This, of course, assumes that your application is split into a front-end
(containing the queries, forms, reports, macros and modules) and a back-end
(containing the tables). Only the back-end should be on the server: each
user should have his/her own copy of the front-end, preferably on their hard
drive. If that's not your situation, you should make that happen first.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Unfortunately I haven't been able to get DAO stuff to work due to some
issue
with our shared drive. I was hoping for a non-DAO solution.
Any other ideas? Thanks anyways
:
The generic code will look something like this..
Dim db as DAO.Database
Dim rs as DAO.RecordSet
Set db = CurrentDB()
Set rs = qry.OpenRecordset([SQL Statement], dbOpenForwardOnly)
While NOT rs.EOF
[Record-level processing here]
rs.MoveNext
wend
rs.close
Set rs = nothing
Set db = nothing
....I have not worked with queryDefs in code that much as such I can
only
point you to Access HELP under QUERYDEF's for the specifics.
ryan_eng wrote:
Hi all,
I have a macro that so far opens a word template and creates tables at
bookmark locations. Now I want to fill the tables with data from saved
queries but I don't know how to code it.
I want my code to look something like this...
With objword.ActiveDocument.Tables(4)
record_no = 1
For Row = 5 To (instl_dwg_count+5)
.Rows(Row,1).Range.InsertAfter Text:= [Dwg_No] of record
# 1
.Rows(Row,2).Range.InsertAfter Text:= [Rev] of record # 1
....etc
record_no = record_no + 1
Next Row
End With
instl_Dwg_count is the number of records in the saved query determined
earlier in the code using a Dcount function.
SO, I need the lookup function to use record_no as a criteria such
that in
the end I have a table filled with all the records from the query.
Any help is much appreciated.
Thanks
RY
