By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,385 Members | 847 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,385 IT Pros & Developers. It's quick & easy.

Can I pass parameters from a macro to query?

P: n/a
Hi - I have 3 access queries which I run via 1 macro.

Each of the queries now requires 2 parameters when they the run. The
parameters are start and end dates.

I have built the parameters into the queries but on running the macro
I have to enter each of the dates 3 times - once for each query.

Is there an easy way to ask for the parameters once and for these to
be passed onto each query?

TIA
Mar 9 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:9i********************************@4ax.com...
Hi - I have 3 access queries which I run via 1 macro.

Each of the queries now requires 2 parameters when they the run. The
parameters are start and end dates.

I have built the parameters into the queries but on running the macro
I have to enter each of the dates 3 times - once for each query.

Is there an easy way to ask for the parameters once and for these to
be passed onto each query?

TIA


The standard way to do this is to have a form open with controls on it to
hold the values. For example, if you had txtStartDate and txtEndDate as
textboxes on a form, then the query could take as parameters:
Forms!MyOpenForm.txtStartDate and Forms!MyOpenForm.txtEndDate

Of course, there may be a better way to do what you are doing, but we don't
have any details. After all, you might be running action queries where it
would be more appropriate to do the whole thing in vba code with the three
actions all wrapped in a transaction.


Mar 9 '06 #2

P: n/a
Hi - my knowledge of Access and VBA is very sketchy so please bear
with me.

The way that I have set this up is via a switchboard. You click on an
option and it runs code to delete 3 files if they already exist and
then runs 3 queries to export 3 excel files.

This was fine until the parameters were required as an additional
requirement.
On Thu, 9 Mar 2006 12:02:38 +0000 (UTC), "Anthony England"
<ae******@oops.co.uk> wrote:
"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:9i********************************@4ax.com.. .
Hi - I have 3 access queries which I run via 1 macro.

Each of the queries now requires 2 parameters when they the run. The
parameters are start and end dates.

I have built the parameters into the queries but on running the macro
I have to enter each of the dates 3 times - once for each query.

Is there an easy way to ask for the parameters once and for these to
be passed onto each query?

TIA


The standard way to do this is to have a form open with controls on it to
hold the values. For example, if you had txtStartDate and txtEndDate as
textboxes on a form, then the query could take as parameters:
Forms!MyOpenForm.txtStartDate and Forms!MyOpenForm.txtEndDate

Of course, there may be a better way to do what you are doing, but we don't
have any details. After all, you might be running action queries where it
would be more appropriate to do the whole thing in vba code with the three
actions all wrapped in a transaction.


Mar 9 '06 #3

P: n/a

Then again I may be able to do it like this
http://groups.google.co.uk/group/mic...d979a1a68945e9

On Thu, 09 Mar 2006 12:11:08 GMT, skinnybloke
<th**************@yahoo.co.uk> wrote:
Hi - my knowledge of Access and VBA is very sketchy so please bear
with me.

The way that I have set this up is via a switchboard. You click on an
option and it runs code to delete 3 files if they already exist and
then runs 3 queries to export 3 excel files.

This was fine until the parameters were required as an additional
requirement.
On Thu, 9 Mar 2006 12:02:38 +0000 (UTC), "Anthony England"
<ae******@oops.co.uk> wrote:
"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:9i********************************@4ax.com. ..
Hi - I have 3 access queries which I run via 1 macro.

Each of the queries now requires 2 parameters when they the run. The
parameters are start and end dates.

I have built the parameters into the queries but on running the macro
I have to enter each of the dates 3 times - once for each query.

Is there an easy way to ask for the parameters once and for these to
be passed onto each query?

TIA


The standard way to do this is to have a form open with controls on it to
hold the values. For example, if you had txtStartDate and txtEndDate as
textboxes on a form, then the query could take as parameters:
Forms!MyOpenForm.txtStartDate and Forms!MyOpenForm.txtEndDate

Of course, there may be a better way to do what you are doing, but we don't
have any details. After all, you might be running action queries where it
would be more appropriate to do the whole thing in vba code with the three
actions all wrapped in a transaction.


Mar 9 '06 #4

P: n/a
I would put this all in one vba routine. This is a fairly short bit of
code, so if you let us know the parameters (e.g. is it start and end dates)
I could show you an idea of how to do it - sometime this afternoon.

"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:lj********************************@4ax.com...
Hi - my knowledge of Access and VBA is very sketchy so please bear
with me.

The way that I have set this up is via a switchboard. You click on an
option and it runs code to delete 3 files if they already exist and
then runs 3 queries to export 3 excel files.

This was fine until the parameters were required as an additional
requirement.
On Thu, 9 Mar 2006 12:02:38 +0000 (UTC), "Anthony England"
<ae******@oops.co.uk> wrote:
"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:9i********************************@4ax.com. ..
Hi - I have 3 access queries which I run via 1 macro.

Each of the queries now requires 2 parameters when they the run. The
parameters are start and end dates.

I have built the parameters into the queries but on running the macro
I have to enter each of the dates 3 times - once for each query.

Is there an easy way to ask for the parameters once and for these to
be passed onto each query?

TIA


The standard way to do this is to have a form open with controls on it to
hold the values. For example, if you had txtStartDate and txtEndDate as
textboxes on a form, then the query could take as parameters:
Forms!MyOpenForm.txtStartDate and Forms!MyOpenForm.txtEndDate

Of course, there may be a better way to do what you are doing, but we
don't
have any details. After all, you might be running action queries where it
would be more appropriate to do the whole thing in vba code with the three
actions all wrapped in a transaction.


Mar 9 '06 #5

P: n/a
That is exactly what I had suggested. Neverthless, you may still benefit
from seeing how others would do the whole routine in vba.
"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:vc********************************@4ax.com...

Then again I may be able to do it like this
http://groups.google.co.uk/group/mic...d979a1a68945e9

On Thu, 09 Mar 2006 12:11:08 GMT, skinnybloke
<th**************@yahoo.co.uk> wrote:
Hi - my knowledge of Access and VBA is very sketchy so please bear
with me.

The way that I have set this up is via a switchboard. You click on an
option and it runs code to delete 3 files if they already exist and
then runs 3 queries to export 3 excel files.

This was fine until the parameters were required as an additional
requirement.
On Thu, 9 Mar 2006 12:02:38 +0000 (UTC), "Anthony England"
<ae******@oops.co.uk> wrote:
"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:9i********************************@4ax.com ...
Hi - I have 3 access queries which I run via 1 macro.

Each of the queries now requires 2 parameters when they the run. The
parameters are start and end dates.

I have built the parameters into the queries but on running the macro
I have to enter each of the dates 3 times - once for each query.

Is there an easy way to ask for the parameters once and for these to
be passed onto each query?

TIA

The standard way to do this is to have a form open with controls on it to
hold the values. For example, if you had txtStartDate and txtEndDate as
textboxes on a form, then the query could take as parameters:
Forms!MyOpenForm.txtStartDate and Forms!MyOpenForm.txtEndDate

Of course, there may be a better way to do what you are doing, but we
don't
have any details. After all, you might be running action queries where
it
would be more appropriate to do the whole thing in vba code with the
three
actions all wrapped in a transaction.


Mar 9 '06 #6

P: n/a
Thanks for the offer of help - it really is appreciated.

The parameters are start date and end date. They are both in
yyyy/mm/dd format.
On Thu, 9 Mar 2006 12:23:22 +0000 (UTC), "Anthony England"
<ae******@oops.co.uk> wrote:
I would put this all in one vba routine. This is a fairly short bit of
code, so if you let us know the parameters (e.g. is it start and end dates)
I could show you an idea of how to do it - sometime this afternoon.

"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:lj********************************@4ax.com.. .
Hi - my knowledge of Access and VBA is very sketchy so please bear
with me.

The way that I have set this up is via a switchboard. You click on an
option and it runs code to delete 3 files if they already exist and
then runs 3 queries to export 3 excel files.

This was fine until the parameters were required as an additional
requirement.
On Thu, 9 Mar 2006 12:02:38 +0000 (UTC), "Anthony England"
<ae******@oops.co.uk> wrote:
"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:9i********************************@4ax.com ...
Hi - I have 3 access queries which I run via 1 macro.

Each of the queries now requires 2 parameters when they the run. The
parameters are start and end dates.

I have built the parameters into the queries but on running the macro
I have to enter each of the dates 3 times - once for each query.

Is there an easy way to ask for the parameters once and for these to
be passed onto each query?

TIA

The standard way to do this is to have a form open with controls on it to
hold the values. For example, if you had txtStartDate and txtEndDate as
textboxes on a form, then the query could take as parameters:
Forms!MyOpenForm.txtStartDate and Forms!MyOpenForm.txtEndDate

Of course, there may be a better way to do what you are doing, but we
don't
have any details. After all, you might be running action queries where it
would be more appropriate to do the whole thing in vba code with the three
actions all wrapped in a transaction.


Mar 9 '06 #7

P: n/a
"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:ls********************************@4ax.com...
Thanks for the offer of help - it really is appreciated.

The parameters are start date and end date. They are both in
yyyy/mm/dd format.

Here is a code example - I hope it doesn't look too complicated if you are
just starting off down the vba route.

It is based on a form with textboxes 'txtStartDate' and 'txtEndDate' and a
command button 'cmdExport' and it assumes you have an existing query named
'qryExport'. What happens here is that when you click the button, the code
looks at the two textboxes to get the parameters, then re-writes the query
before exporting that query as an Excel file.

Obviously if you had multiple tasks with the same pair of dates, you could
just add more code without the user having to re-enter the dates.

You may also notice I don't ever try to delete the XL file, just warn if it
exists and let the export perform its default behaviour of overwriting the
old file.
Private Sub cmdExport_Click()

On Error GoTo Err_Handler

Dim dteStart As Date
Dim dteEnd As Date
Dim strSQL As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Const XL_PATH As String = "C:\Test.xls"
Const XL_QUERY As String = "qryExport"

If IsNull(Me.txtStartDate) Then
MsgBox "Enter a start date", vbInformation
Me.txtStartDate.SetFocus
Exit Sub
Else
dteStart = CDate(Me.txtStartDate)
End If
If IsNull(Me.txtEndDate) Then
MsgBox "Enter an end date", vbInformation
Me.txtEndDate.SetFocus
Exit Sub
Else
dteEnd = CDate(Me.txtEndDate)
End If

strSQL = "SELECT * FROM MyTable WHERE MyDate BETWEEN #" & _
Format(dteStart, "mm/dd/yyyy") & "# AND #" & _
Format(dteEnd, "mm/dd/yyyy") & "#"

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs(XL_QUERY)

qdf.SQL = strSQL

Set qdf = Nothing

Set dbs = Nothing
If Len(Dir(XL_PATH)) > 0 Then
If MsgBox("Overwrite existing file?", _
vbExclamation Or vbYesNoCancel, _
"Export Routine") <> vbYes Then

MsgBox "Export cancelled", _
vbExclamation, _
"Export Routine"
Exit Sub

End If
End If

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, XL_QUERY, XL_PATH

MsgBox "File Exported", vbInformation

Exit_Handler:

If Not qdf Is Nothing Then
Set qdf = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
Mar 9 '06 #8

P: n/a

"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:ls********************************@4ax.com...
Thanks for the offer of help - it really is appreciated.

The parameters are start date and end date. They are both in
yyyy/mm/dd format.

Here is a code example - I hope it doesn't look too complicated if you are
just starting off down the vba route.

It is based on a form with textboxes 'txtStartDate' and 'txtEndDate' and a
command button 'cmdExport' and it assumes you have an existing query named
'qryExport'. What happens here is that when you click the button, the code
looks at the two textboxes to get the parameters, then re-writes the query
before exporting that query as an Excel file.

Obviously if you had multiple tasks with the same pair of dates, you could
just add more code without the user having to re-enter the dates.

You may also notice I don't ever try to delete the XL file, just warn if it
exists and let the export perform its default behaviour of overwriting the
old file.
Private Sub cmdExport_Click()

On Error GoTo Err_Handler

Dim dteStart As Date
Dim dteEnd As Date
Dim strSQL As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Const XL_PATH As String = "C:\Test.xls"
Const XL_QUERY As String = "qryExport"

If IsNull(Me.txtStartDate) Then
MsgBox "Enter a start date", vbInformation
Me.txtStartDate.SetFocus
Exit Sub
Else
dteStart = CDate(Me.txtStartDate)
End If
If IsNull(Me.txtEndDate) Then
MsgBox "Enter an end date", vbInformation
Me.txtEndDate.SetFocus
Exit Sub
Else
dteEnd = CDate(Me.txtEndDate)
End If

strSQL = "SELECT * FROM MyTable WHERE MyDate BETWEEN #" & _
Format(dteStart, "mm/dd/yyyy") & "# AND #" & _
Format(dteEnd, "mm/dd/yyyy") & "#"

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs(XL_QUERY)

qdf.SQL = strSQL

Set qdf = Nothing

Set dbs = Nothing
If Len(Dir(XL_PATH)) > 0 Then
If MsgBox("Overwrite existing file?", _
vbExclamation Or vbYesNoCancel, _
"Export Routine") <> vbYes Then

MsgBox "Export cancelled", _
vbExclamation, _
"Export Routine"
Exit Sub

End If
End If

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, XL_QUERY, XL_PATH

MsgBox "File Exported", vbInformation

Exit_Handler:

If Not qdf Is Nothing Then
Set qdf = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
Mar 9 '06 #9

P: n/a
On Thu, 9 Mar 2006 15:10:37 +0000 (UTC), "Anthony England"
<ae******@oops.co.uk> wrote:

"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:ls********************************@4ax.com.. .
Thanks for the offer of help - it really is appreciated.

The parameters are start date and end date. They are both in
yyyy/mm/dd format.

Here is a code example - I hope it doesn't look too complicated if you are
just starting off down the vba route.


Hi Anthony - just got round to implementing this and it works a treat.

Thanks for taking the time to help.
Mar 27 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.