How can I get data from an Access query into VBA? 3

[Hanss]

I would really appreciate any help with this.

Hans
Zurich, Switzerland

I have a query in an Access 2002 database named: "files" with the following fields:

- "fileinfo" (contains file names and locations: (eg. c:\file1.pdf&quot

- "id" (contains a number)

Question: How can I get the data from the "fileinfo" field of my query into the code? Note that depending on the number of records in my query the number of files being sent to the VBA code for merging will vary!

Here is the code:

Public Function merge(strfilename3 As String)
Dim objMerge As MergePDFClass
Dim arrMerge() As String

Set objMerge = New MergePDFClass 'instantiate the class
ReDim arrMerge(4) 'zero base array, 0 and 1 are the elements

arrMerge(0) = c:\file1.pdf
arrMerge(1) = c:\file2.pdf
arrMerge(2) = c:\file3.pdf
arrMerge(3) = c:\file4.pdf
arrMerge(4) = c:\file5.pdf

With objMerge
.DeleteOriginals = False
.ShowMessages = 1
.MergePDFs strfilename3, arrMerge()
End With
Set objMerge = Nothing

End Function

 

[WildHare]

Probably the easiest way to do this is use the query to populate a TABLE with your values - in other words, change it to a MAKE TABLE query, rather than a SELECT query.

Then read from the table in a loop with your code, each time retrieving the next record's "FileInfo" value and and shoving that into your strFileName variable.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[hwkranger]

To pull a value from a query do the following:

dim myrecord as recordset
dim myquery as querydef
dim db as database

'*** this part is not required, but it does establish the definition of your query ***
set db = currentdb()
set myquery = db.querydefs("Your queryname&quot
with myquery
.sql = "your SQL def" '**defines your query**
end with
set myquery = nothing
'**** End of part that is not required ***


Dim objMerge As MergePDFClass
Dim arrMerge() As String
dim i as integer 'holder
dim j as integer 'counter

Set objMerge = New MergePDFClass 'instantiate the class

set myrecord = db.openrecordset("your query name&quot
with myrecord
if .recordcount then
.movefirst
.movelast
i = .recordcount
ReDim arrMerge(i)
.movefirst
j = 1
do until myrecord.eof
arrmerge(j) = !fileinfo
j = j+1
loop
end if
end with

With objMerge
.DeleteOriginals = False
.ShowMessages = 1
.MergePDFs strfilename3, arrMerge()
End With
Set objMerge = Nothing
set myrecord = nothing

Hope this helps


Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[Hanss]

Thank you SO much for your ideas and code. I have been fiddling with the code you sent me. It does actually access my query! BUT I have been getting some error messages namely an "out of range" error for this line:

arrMerge(j) = !FileInfo


Debugger says that j has a value of 5. The query brings up 4 records. The value should however be 3. Always one less than the number of records brought up by the query. Any ideas on how to get j to display the correct value???

kind regards
Hans

 

[scriverb]

This section of the code needs to be adjusted:

do until myrecord.eof
arrmerge(j) = !fileinfo
j = j+1
loop
Change to

do until j > i
arrmerge(j) = !fileinfo
j = j+1
loop

I think this should get you going.

Bob Scriver

 

[Hanss]

Thank you! The whole script now works. No error messages. There is however still one problem:

If my query brings up 5 file names like this:

c:\file1.pdf
c:\file2.pdf
c:\file3.pdf
c:\file4.pdf
c:\file5.pdf

The code takes the first file name and duplicates to make:

c:\file1.pdf
c:\file1.pdf
c:\file1.pdf
c:\file1.pdf
c:\file1.pdf

In other words the script should produce an array that looks like this:

arrMerge(0) = c:\file1.pdf
arrMerge(1) = c:\file2.pdf
arrMerge(2) = c:\file3.pdf
arrMerge(3) = c:\file4.pdf
arrMerge(4) = c:\file5.pdf

But now produces an array that looks like this:

arrMerge(0) = c:\file1.pdf
arrMerge(1) = c:\file1.pdf
arrMerge(2) = c:\file1.pdf
arrMerge(3) = c:\file1.pdf
arrMerge(4) = c:\file1.pdf

Any ideas how to correct this??

Many thanks
Hans

PS Here is what the whole code now looks like:

Public Function swissmerge3()
Dim myrecord As Recordset
Dim myquery As QueryDef
Dim db As Database

'*** this part is not required, but it does establish the definition of your query ***
Set db = CurrentDb()
Set myquery = db.QueryDefs("filetest&quot
With myquery
.SQL = "SELECT swissupload.fileinfo FROM swissupload ORDER BY swissupload.fileinfo DESC" '**defines your query**
End With
Set myquery = Nothing
'**** End of part that is not required ***


Dim objMerge As MergePDFClass
Dim arrMerge() As String
Dim i As Integer 'holder
Dim j As Integer 'counter

Set objMerge = New MergePDFClass 'instantiate the class

Set myrecord = db.OpenRecordset("filetest&quot
With myrecord
If .RecordCount Then
.MoveFirst
.MoveLast
i = .RecordCount
ReDim arrMerge(i)
.MoveFirst
j = 1
Do Until j > i
arrMerge(j) = !FileInfo
j = j + 1
Loop
End If
End With

With objMerge
.DeleteOriginals = False
.ShowMessages = 1
.MergePDFs "C:\content\target.pdf", arrMerge()
End With
Set objMerge = Nothing
Set myrecord = Nothing
End Function

 

[scriverb]

This isn't my code but it looks like the same loop needs some adjustment:

.MoveFirst
j = 1
Do Until j > i
arrMerge(j) = !FileInfo
j = j + 1
.MoveNext
Loop

This loop needs to move through each record in the recordset and so the Red line of code needs to be added to do this.

Let me know if this does it for you.
Bob Scriver

 

[hwkranger]

Sorry about that.. if you look at the formatting of the text, i just cranked it from head... it was a friday - thank you Scriverb for pointing out my errors

FYI - I don't think you need the J > I line - what I forgot was the movenext. Logically speaking J SHOULD never be greater than I, so that messes up your loop.

You want the loop to end when you hit the bottom of your queue... Really sorry about the .movenext bit. Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[hwkranger]

So, essentially what you want is:

do until myrecord.eof '** this means when you hit the bottom to STOP!
arrmerge(j) = !fileinfo
j = j+1
.MoveNext
Loop

j should never be greater than i.... my apologies again.. bad bad me.. Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[scriverb]

hwkranger: Welcome back. Wasn't trying to step on toes or take over but he needed response and the weekend and all.

I added the J > 1 to give the loop a way to exit. Without the .MoveNext it was the only way to facilitate getting out. After more review and his posting it became apparent that you wanted to exit at .eof and yes that is the way it should be done.

I know all about pseudo code. Bang it out and hope you got it right. Been there and done that.

Type to/with your soon. Bob Scriver

 

[hwkranger]

Scriverb,

This forum is for helping, thank you for pointing out my error . Hehe, I'll pay more attention in the future to what I post. I've learned alot in these forums, and it's people like you that make the learning experience positive

Hanss - I really hope that everything works for you... and the corrections that Scriverb and I posted should fix all your errors.

Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[Hanss]

It works great!!! I really want to THANK YOU for all your help! I still have one question regarding the query:

This works fine:

Set myquery = db.QueryDefs("filetest&quot
With myquery
.SQL = "SELECT swissupload.fileinfo FROM swissupload" '**defines your query**

But if I add to my SQL:

.SQL = "SELECT swissupload.fileinfo, swisscontainer.id
FROM swissupload LEFT JOIN swisscontainer ON swissupload.container = swisscontainer.id
WHERE (((swisscontainer.id)=[Forms]![swisscontainer]![id]));"

I get this error message:

Run-Time error 3061, 1 Parameter was expected but not enought were provided.

Debugger highlights this line:

Set myrecord = db.OpenRecordset("filetest&quot

Do you have any thoughts on this??

Many thanks
Hans

This is the full code:

Public Function forum()
Dim myrecord As Recordset
Dim myquery As QueryDef
Dim db As Database

'*** this part is not required, but it does establish the definition of your query ***
Set db = CurrentDb()
Set myquery = db.QueryDefs("filetest&quot
With myquery
.SQL = "SELECT swissupload.fileinfo FROM swissupload ORDER BY swissupload.fileinfo DESC" '**defines your query**
End With
Set myquery = Nothing
'**** End of part that is not required ***


Dim objMerge As MergePDFClass
Dim arrMerge() As String
Dim i As Integer 'holder
Dim j As Integer 'counter

Set objMerge = New MergePDFClass 'instantiate the class

Set myrecord = db.OpenRecordset("filetest&quot
With myrecord
If .RecordCount Then
.MoveFirst
.MoveLast
i = .RecordCount
ReDim arrMerge(i)
.MoveFirst
j = 1
Do Until j > i
arrMerge(j) = !FileInfo
j = j + 1
.MoveNext
Loop
End If
End With

With objMerge
.DeleteOriginals = False
.ShowMessages = 1
.MergePDFs "C:\content\target.pdf", arrMerge()
End With
Set objMerge = Nothing
Set myrecord = Nothing
End Function

 

[scriverb]

First of all is "filetest" a query in your database. Just the name sounds more like a table. If it were a table then it wouldn't be found in the QueryDefs collection. Probably not.
Set myrecord = db.OpenRecordset("filetest&quot
I would update your Dim statements with DAO designations.
Dim myrecord As DAO.Recordset
Dim db As DAO.Database

Other than that I am going to have to look this over for a while to see what can be seen. Nothing else at this point.




Bob Scriver

 

[Hanss]

Hi would like to thank all of you for your help! Everything works now. I managed to solve the query problem by following wildhare's advise of using a query to make a table and then use the "filetest" query in my code to access the data from the temporary table. Being that I know very little about VBA I use the macro funtions in msaccess to make the table and then delete it after the VBA function is finished.

Thanks Again!
Hans