UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Passing Parameter To A Make Table Query, Access 2016    
 
   
cooksc
post Sep 9 2018, 03:24 PM
Post#1



Posts: 30
Joined: 30-April 10



Is it possible to loop through a recordset and pass a single variable (supplier name) as a parameter to a query within the loop that makes a table? The table will then be used to export an Excel file. Thank you in advance and I'm new to VBA so I apologies it I haven't used the correct terms. Any help is greatly appreciated.
Go to the top of the page
 
theDBguy
post Sep 9 2018, 03:30 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,257
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Pretty sure it’s possible but need to make sure we understand your needs properly. Can you post some mock up data to show us what you got now and then show us what you want in the end? Thanks!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cooksc
post Sep 9 2018, 04:36 PM
Post#3



Posts: 30
Joined: 30-April 10



Thanks for the reply. Conceptually this is what I am trying to do...........

Send an email to a recipient with an Excel attachment.
I have the code working to send the email and a single attachment. The attachment is hard coded though.
What I'm trying to do through by looping through a recordset is pass a variable/parameter to a make table query and then from there export the data as an Excel file which will then become the attachment.
I've tried exporting directly to Excel without creating a table, but can't seemed to make that work.
I'll post the code tomorrow that I have.

Once again, Thanks!!
Go to the top of the page
 
cooksc
post Sep 10 2018, 11:54 AM
Post#4



Posts: 30
Joined: 30-April 10



There is an Access query called "SuppDocXLS_qry" that has a parameter [SUPP_CD]
I need to programmatically pass a value for [SUPP_CD] when executing "SuppDocXLS_qry"


********************************************************************************
****
Sub CreateExcelFileWithQueryParameter()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SuppID As String
Dim FileName As String
Dim todayDate As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("SendSuppDocs", dbOpenDynaset)

'For each SELL_ORG_ID in the recordset...
Do Until rs.EOF
SuppID = rs![SELL_ORG_ID]

'Generate file name & location
FileName = "c:\Temp\" & SuppID & "_" & ".xlsx"

DoCmd.SetParameter "SUPP_CD", SuppID 'This line doesn't seem to work
DoCmd.OpenQuery "SuppDocXLS_qry", acViewNormal, acReadOnly

'Create Excel document in FileName
'(is there another way to create an excel file from a query?)
DoCmd.OutputTo acOutputQuery, , "ExcelWorkbook(*.xlsx)", FileName, _
False, "", , acExportQualityPrint
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub


Thanks in advance for any help
Go to the top of the page
 
theDBguy
post Sep 10 2018, 12:06 PM
Post#5


Access Wiki and Forums Moderator
Posts: 73,257
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Check out the QueryDef object. It has a parameter property.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Sep 10 2018, 12:14 PM
Post#6



Posts: 5,518
Joined: 11-November 10
From: SoCal, USA


frankly, i don't like working with QueryDefs if i can avoid it - my bad when it comes to working with both VBA and SQL statements, i'm sure. but since that's where i'm at, i usually build a custom function to retrieve a value from a global variable, and call the custom function as the criteria in my query. the value of the global variable can be set from each record while iterating through a VBA recordset, and then the query output to Excel. to see it if works, just do a quick test by hardcoding a criteria value in the function, and outputting the query which calls the function in its' WHERE clause.

also, since you're using A2016, you have TempVars (sp?) available. i've never used A2016, but i think you could skip the custom function and just use the TempVar as criteria in the query; if that works, you can just set the value of the TempVar while iterating through the recordset.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
cooksc
post Sep 11 2018, 06:13 AM
Post#7



Posts: 30
Joined: 30-April 10



Tina.........could you provide some sample code showing how to use tempvars to call parameters? Thanks in advance
Go to the top of the page
 
JonSmith
post Sep 11 2018, 06:18 AM
Post#8



Posts: 3,831
Joined: 19-October 10



Why do you need a temp table for your export? Why not just use a query?
Go to the top of the page
 
tina t
post Sep 11 2018, 11:59 AM
Post#9



Posts: 5,518
Joined: 11-November 10
From: SoCal, USA


QUOTE
Tina.........could you provide some sample code showing how to use tempvars to call parameters?

no, hon, i've never used them, only read about them in threads here in the forums. and i don't know what you mean by "call parameters". my understanding of query parameters is that it refers to references used in criteria (the WHERE clause), rather than hard-coded criteria values. i assume that you could refer to a TempVar in query criteria the same way you'd refer to it anywhere else. have you read up on TempVars in Access Help?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
theDBguy
post Sep 11 2018, 12:06 PM
Post#10


Access Wiki and Forums Moderator
Posts: 73,257
Joined: 19-June 07
From: SunnySandyEggo


We do have a Wiki article on TempVars. See if it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cooksc
post Sep 11 2018, 01:27 PM
Post#11



Posts: 30
Joined: 30-April 10



The code below shows the code with QueryDef

The code runs until it hits here: DoCmd.OutputTo acOutputQuery, "SuppDocXLS_qry", "ExcelWorkbook(*.xlsx)", FileName, _
False, "", , acExportQualityPrint

Then a dialog box from the query "SuppDocXLS_qry" appears.
How do I pass a parameter programmatically to the query?

**********************************************************************
Sub useQueryDefObject()
Dim qdf As QueryDef
Dim rs As Recordset
Dim SuppID As String
Dim FileName As String

Set qdf = CurrentDb.QueryDefs("SuppDocXLS_qry")
Set rs = CurrentDb.OpenRecordset("SendSuppDocs", dbOpenDynaset)

'For each SELL_ORG_ID in the recordset...
Do Until rs.EOF
SuppID = rs![SELL_ORG_ID]
FileName = "c:\Temp\" & SuppID & "_" & ".xlsx"

qdf.Parameters("SUPP_CD") = SuppID
qdf.OpenRecordset

'Create Excel document in FileName location
DoCmd.OutputTo acOutputQuery, "SuppDocXLS_qry", "ExcelWorkbook(*.xlsx)", FileName, _
False, "", , acExportQualityPrint
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing
Set qdf = Nothing
End Sub
This post has been edited by cooksc: Sep 11 2018, 01:31 PM
Go to the top of the page
 
cooksc
post Sep 11 2018, 01:40 PM
Post#12



Posts: 30
Joined: 30-April 10



Really appreciated the replies and hanging in there with me to get some resolution to this issue
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th September 2018 - 12:57 AM