Pulling query result in VBA (1 Viewer)

[JDeezy]

I am trying to figure out how to pull data from query results in VBA but i have been unsuccessful so far. I have a form where I enter a client number and date and it pulls a report and saves it automatically.

The only problem is the save path includes [Client#] & " " & [ClientName]. The only way around this I have found is to enter the entire Client name and only pull the first 9 digits to pull all my querys that run off this form. I do have a query that runs off this form and pulls the Client Name with relationship to the client number I have entered.

This is the code I have so far.

Private Sub printreport_Click()
Dim blret As Boolean
Dim Filter As String
Dim strClient As String
Dim strDate As String
Dim stdispname As String
Dim stdocname As String
Dim stsumname As String

strClient = txtClient
strDate = txtDate
stdocname = "Results Weekly"
stdispname = "Results Wk Dispute"
stsumname = "Results Weekly - Master"
'Pulls Weekly Reports for client folder
blret = ConvertReportToPDF(stdocname, , "Q:\ClientReports\" & strClient & "\" & strDate & "Weekly.pdf", False, False, 150, "", "", 0, 0, 0)
'Pulls Weekly Reports for cooler e-mail folder
blret = ConvertReportToPDF(stdocname, , "Q:\ClientReports\Cooler Email Weekly\" & Left(strClient, 9) & "Weekly.pdf", False, False, 150, "", "", 0, 0, 0)
'Pulls Weekly Sum Reports for cooler e-mail folder
blret = ConvertReportToPDF(stsumname, , "Q:\ClientReports\Cooler Email Weekly\" & Left(strClient, 9) & "WeeklySummary.pdf", False, False, 150, "", "", 0, 0, 0)
'Pulls Weekly Sum Reports for client folder
blret = ConvertReportToPDF(stsumname, , "Q:\ClientReports\" & strClient & "\" & strDate & "WeeklySummary.pdf", False, False, 150, "", "", 0, 0, 0)
'Pulls Dispute Reports for Client Folder
blret = ConvertReportToPDF(stdispname, , "Q:\ClientReports\" & strClient & "\" & strDate & "Dispute.pdf", False, False, 150, "", "", 0, 0, 0)
'Pull Dispute Report for Cooler e-mail folder
blret = ConvertReportToPDF(stdispname, , "Q:\ClientReports\Cooler Email Wk Dispute\" & Left(strClient, 9) & "Dispute.pdf", False, False, 150, "", "", 0, 0, 0)
End Sub

 

[vbaInet]

Your query isn't clear. Could you please elaborate?

 

[JDeezy]

I figured out a way to do it. My query includes client number and client name. I just used the DLookup command. I need a VBA book lol. I had no idea that command existed.

But I have run into another problem. I need to have the code below loop through the query that has the client number and name and run the code once for each client number. Or in the very least figure out a way to make the listbox I created clickable so when you highlight the client number it runs the code for that client number. I have attached a DB with some dll files (I am using lebans reportstopdf code.) to give you a little better idea of what I am dealing with.

 

[vbaInet]

Well, I've not used Lebhan's code before so that won't help.

To loop through, use a DAO recordset. Here's a link to get you started:

http://allenbrowne.com/ser-29.html

 

[JDeezy]

Ok, I figured out how to loop it using a recordset but since I did and got rid of my text box. I now have to place that record set as a query criteria but for some reason its not working. But the loop actually works perfectly. Any suggestions?

 

[vbaInet]

You don't use a recordset as a parameter in a query. Let's see your code.

Also, explain what you're trying to do.

 

[JDeezy]

Here is the code I have as of now.

Private Sub cmdYesandDiv_Click()
'Yes and Div
Dim blret As Boolean
Dim Filter As String
Dim strDate As String
Dim stdispname As String
Dim stdocname As String
Dim stsumname As String
Dim stdivname As String
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("Result Active Client List", dbOpenDynaset)
strDate = txtDate
stdocname = "Results Weekly (Auto)"
stdispname = "Results Wk Dispute (Auto)"
stsumname = "Results Weekly - Master (Auto)"
stdivname = "Results Weekly - Rpt Division (Auto)"
Do While Not rs.EOF
    If rs![Wkly Sum] = "Yes & Div" Then
      
'Pulls Weekly Reports for client folder
blret = ConvertReportToPDF(stdocname, , "Q:\ClientReports\" & rs![Master #] & " " & rs![Master Name] & "\" & strDate & "Weekly.pdf", False, False, 150, "", "", 0, 0, 0)
'Pulls Weekly Reports for cooler e-mail folder
blret = ConvertReportToPDF(stdocname, , "Q:\ClientReports\Cooler Email Weekly\" & rs![Master #] & "Weekly.pdf", False, False, 150, "", "", 0, 0, 0)
'Pulls Weekly Sum Reports for cooler e-mail folder
blret = ConvertReportToPDF(stsumname, , "Q:\ClientReports\Cooler Email Weekly\" & rs![Master #] & "WeeklySummary.pdf", False, False, 150, "", "", 0, 0, 0)
'Pulls Weekly Sum Reports for client folder
blret = ConvertReportToPDF(stsumname, , "Q:\ClientReports\" & rs![Master #] & " " & rs![Master Name] & "\" & strDate & "WeeklySummary.pdf", False, False, 150, "", "", 0, 0, 0)
'Pulls Dispute Reports for Client Folder
blret = ConvertReportToPDF(stdispname, , "Q:\ClientReports\" & rs![Master #] & " " & rs![Master Name] & "\" & strDate & "Dispute.pdf", False, False, 150, "", "", 0, 0, 0)
'Pull Dispute Report for Cooler e-mail folder
blret = ConvertReportToPDF(stdispname, , "Q:\ClientReports\Cooler Email Wk Dispute\" & rs![Master #] & "Dispute.pdf", False, False, 150, "", "", 0, 0, 0)
'Pull Div Report for Client Folder
blret = ConvertReportToPDF(stdivname, , "Q:\ClientReports\" & rs![Master #] & " " & rs![Master Name] & "\" & strDate & "WeeklyDivision.pdf", False, False, 150, "", "", 0, 0, 0)
'Pull Div Report for Cooler e-mail folder
blret = ConvertReportToPDF(stdispname, , "Q:\ClientReports\Cooler Email Wk Dispute\" & rs![Master #] & "WeeklyDivision.pdf", False, False, 150, "", "", 0, 0, 0)
 
End If
 rs.MoveNext
Loop
End Sub

What I need is someway to reference the client number from the query based on what the recordset is pulling at that time. I have no idea on how to do this without some type of textbox.

 

[vbaInet]

What I need is someway to reference the client number from the query based on what the recordset is pulling at that time. I have no idea on how to do this without some type of textbox.

What do you mean by referencing the Client from the recordset? I can see you are referencing the [Wkly Sum] field. Whatever fields are available in the recordset can be called.

 

[JDeezy]

Basically I have a report that pulls from the query but what I had before was the txtBox in the query criteria so whatever client number you enter pulls the corresponding report. Now that I got rid of the txtBox I dont have a way to put the corresponding client number for that report in the query criteria so the report comes up blank.

And I tried making a string from the recordset and even pulling it straight from the record set but like you said you can't use a recordset as a parameter in a query so I am pretty stuck.

 

[JDeezy]

Opps sorry the fields in the query are [Master #] and [Master Name] would be the client number and client name. Sorry about that.

 

[JDeezy]

Wow. Nevermind I figured it out. I mean its kind of a work around but i really thought outside the box. I just set a text box as my recordset and then called the txtBox from the query. Which works out even better because now the text box shows exactly what report is running.

I do appreciate all of your help up to this point though. Thanks so much!

 

[vbaInet]

No problemo. Ensure that the textbox is read-only.

Glad that's sorted.