why isn't the parameter seen?

  • Thread starter Thread starter javablood
  • Start date Start date
J

javablood

I have a program in VBA in which I tried to run an SQL for which two
parameters (text) are entered by calling for them from the 'criteria' field.
I also want to use those same parameters as the front part of the name of a
file to save. However, no matter what I do (Public, Dim, etc) the portion of
the code that uses the parameters for the file name does not 'see' those
parameters. I have also tried forms to capture the parameters but have had
no luck there either? What am I doing wrong?

TIA
 
Here is the code. It is called from a form. Thanks

Option Compare Database
Public Month_of_invoice As String
Public Year_of_invoice As String


Private Sub Command33_Click()
Select Case Frame0.Value


Case Is = 2
' This creates MS Excel files from the monthly budget data
' for the Denver database only, which operates in MS Access 2007

Dim strDefaultDir As String
Dim strFilter As String
Dim strInputFileName As String
Dim SQL As String

DoCmd.SetWarnings False ' turn warnings off

SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], [All
Invoices].[Year invoice paid], [All Invoices].[Sent to HSRC?]" & _
"HAVING ((([All Invoices].[Month invoice paid])=[Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])= [Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

DoCmd.RunSQL SQL

' The following allows the user to save the Excel file to the directory
chosen by the user:
'Set up default path and file
strDefaultDir = "C:\Documents and Settings\My Documents\"

'Set up default file
strDefaultFileName = Month_of_invoice & Year_of_invoice & " monthly
expenses.xls"

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")

'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_OVERWRITEPROMPT

'Call the Open File Dialog
varGetFileName = ahtCommonFileOpenSave( _
Openfile:=False, _
InitialDir:=strDefaultDir, _
Filter:=strFilter, _
FileName:=strDefaultFileName, _
Flags:=lngFlags, _
DialogTitle:="Save Report")

If varGetFileName <> "" Then

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Monthly costs tracking", varGetFileName, True

End If

DoCmd.SetWarnings True ' turn warnings on

Msg2 = MsgBox("Your Excel file " & strDefaultFileName & " has been
created. Click OK to continue", vbInformation + vbOKOnly, "Excel File
Created")

Case Is = 3
DoCmd.Close acForm, "Monthly Expenses Summary"
DoCmd.OpenForm "Main", acNormal

End Select

End Sub
 
What are Month_of_invoice & Year_of_invoice?
Are they fields in the All Invoices table, controls on your form, or
variables? If they are variables, where are they defined?
--
Dave Hargis, Microsoft Access MVP


javablood said:
Here is the code. It is called from a form. Thanks

Option Compare Database
Public Month_of_invoice As String
Public Year_of_invoice As String


Private Sub Command33_Click()
Select Case Frame0.Value


Case Is = 2
' This creates MS Excel files from the monthly budget data
' for the Denver database only, which operates in MS Access 2007

Dim strDefaultDir As String
Dim strFilter As String
Dim strInputFileName As String
Dim SQL As String

DoCmd.SetWarnings False ' turn warnings off

SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], [All
Invoices].[Year invoice paid], [All Invoices].[Sent to HSRC?]" & _
"HAVING ((([All Invoices].[Month invoice paid])=[Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])= [Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

DoCmd.RunSQL SQL

' The following allows the user to save the Excel file to the directory
chosen by the user:
'Set up default path and file
strDefaultDir = "C:\Documents and Settings\My Documents\"

'Set up default file
strDefaultFileName = Month_of_invoice & Year_of_invoice & " monthly
expenses.xls"

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")

'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_OVERWRITEPROMPT

'Call the Open File Dialog
varGetFileName = ahtCommonFileOpenSave( _
Openfile:=False, _
InitialDir:=strDefaultDir, _
Filter:=strFilter, _
FileName:=strDefaultFileName, _
Flags:=lngFlags, _
DialogTitle:="Save Report")

If varGetFileName <> "" Then

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Monthly costs tracking", varGetFileName, True

End If

DoCmd.SetWarnings True ' turn warnings on

Msg2 = MsgBox("Your Excel file " & strDefaultFileName & " has been
created. Click OK to continue", vbInformation + vbOKOnly, "Excel File
Created")

Case Is = 3
DoCmd.Close acForm, "Monthly Expenses Summary"
DoCmd.OpenForm "Main", acNormal

End Select

End Sub

--
javablood


Klatuu said:
Post the code, please. Can't help troubleshoot something we can't see.
Click to expand...
Click to expand...
 
these are the input parameters that the user enters when prompted from the
query. they are used to capture the data from the table for the fields
"Month invoice paid" and "Year invoice paid" I have them listed as Public at
the beginning, is that not correct?
--
javablood


Klatuu said:
What are Month_of_invoice & Year_of_invoice?
Are they fields in the All Invoices table, controls on your form, or
variables? If they are variables, where are they defined?
--
Dave Hargis, Microsoft Access MVP


javablood said:
Here is the code. It is called from a form. Thanks

Option Compare Database
Public Month_of_invoice As String
Public Year_of_invoice As String


Private Sub Command33_Click()
Select Case Frame0.Value


Case Is = 2
' This creates MS Excel files from the monthly budget data
' for the Denver database only, which operates in MS Access 2007

Dim strDefaultDir As String
Dim strFilter As String
Dim strInputFileName As String
Dim SQL As String

DoCmd.SetWarnings False ' turn warnings off

SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], [All
Invoices].[Year invoice paid], [All Invoices].[Sent to HSRC?]" & _
"HAVING ((([All Invoices].[Month invoice paid])=[Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])= [Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

DoCmd.RunSQL SQL

' The following allows the user to save the Excel file to the directory
chosen by the user:
'Set up default path and file
strDefaultDir = "C:\Documents and Settings\My Documents\"

'Set up default file
strDefaultFileName = Month_of_invoice & Year_of_invoice & " monthly
expenses.xls"

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")

'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_OVERWRITEPROMPT

'Call the Open File Dialog
varGetFileName = ahtCommonFileOpenSave( _
Openfile:=False, _
InitialDir:=strDefaultDir, _
Filter:=strFilter, _
FileName:=strDefaultFileName, _
Flags:=lngFlags, _
DialogTitle:="Save Report")

If varGetFileName <> "" Then

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Monthly costs tracking", varGetFileName, True

End If

DoCmd.SetWarnings True ' turn warnings on

Msg2 = MsgBox("Your Excel file " & strDefaultFileName & " has been
created. Click OK to continue", vbInformation + vbOKOnly, "Excel File
Created")

Case Is = 3
DoCmd.Close acForm, "Monthly Expenses Summary"
DoCmd.OpenForm "Main", acNormal

End Select

End Sub

--
javablood


Klatuu said:
Post the code, please. Can't help troubleshoot something we can't see.
--
Dave Hargis, Microsoft Access MVP


:

I have a program in VBA in which I tried to run an SQL for which two
parameters (text) are entered by calling for them from the 'criteria' field.
I also want to use those same parameters as the front part of the name of a
file to save. However, no matter what I do (Public, Dim, etc) the portion of
the code that uses the parameters for the file name does not 'see' those
parameters. I have also tried forms to capture the parameters but have had
no luck there either? What am I doing wrong?

TIA
Click to expand...
Click to expand...
Click to expand...
 
Your VBA code cannot see query parameters and a query can't see variables.
The better design would be to put two text box controls on your form and
have the use enter the values there. That way, both the query and VBA can
reference them.

In your query, you would use the fully qualified name of the control:
"HAVING ((([All Invoices].[Month invoice
paid])=[Forms]![MyFormName]![Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])=
[Forms]![MyFormName]![Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

And in your VBA code use:

strDefaultFileName = Me.Month_of_invoice & Me.Year_of_invoice & " monthly
expenses.xls"

Just another hint. I would use Year then month in the name. It is a
sorting issue. When a user is looking for a specific file name, it will sort
better:

As you wrote it:

012007
012008
022007
022008
....
122007
122008

As I Suggest:
200701
200702
.....
200712
200801
200802
....
200812

--
Dave Hargis, Microsoft Access MVP


javablood said:
these are the input parameters that the user enters when prompted from the
query. they are used to capture the data from the table for the fields
"Month invoice paid" and "Year invoice paid" I have them listed as Public at
the beginning, is that not correct?
--
javablood


Klatuu said:
What are Month_of_invoice & Year_of_invoice?
Are they fields in the All Invoices table, controls on your form, or
variables? If they are variables, where are they defined?
--
Dave Hargis, Microsoft Access MVP


javablood said:
Here is the code. It is called from a form. Thanks

Option Compare Database
Public Month_of_invoice As String
Public Year_of_invoice As String


Private Sub Command33_Click()
Select Case Frame0.Value


Case Is = 2
' This creates MS Excel files from the monthly budget data
' for the Denver database only, which operates in MS Access 2007

Dim strDefaultDir As String
Dim strFilter As String
Dim strInputFileName As String
Dim SQL As String

DoCmd.SetWarnings False ' turn warnings off

SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], [All
Invoices].[Year invoice paid], [All Invoices].[Sent to HSRC?]" & _
"HAVING ((([All Invoices].[Month invoice paid])=[Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])= [Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

DoCmd.RunSQL SQL

' The following allows the user to save the Excel file to the directory
chosen by the user:
'Set up default path and file
strDefaultDir = "C:\Documents and Settings\My Documents\"

'Set up default file
strDefaultFileName = Month_of_invoice & Year_of_invoice & " monthly
expenses.xls"

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")

'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_OVERWRITEPROMPT

'Call the Open File Dialog
varGetFileName = ahtCommonFileOpenSave( _
Openfile:=False, _
InitialDir:=strDefaultDir, _
Filter:=strFilter, _
FileName:=strDefaultFileName, _
Flags:=lngFlags, _
DialogTitle:="Save Report")

If varGetFileName <> "" Then

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Monthly costs tracking", varGetFileName, True

End If

DoCmd.SetWarnings True ' turn warnings on

Msg2 = MsgBox("Your Excel file " & strDefaultFileName & " has been
created. Click OK to continue", vbInformation + vbOKOnly, "Excel File
Created")

Case Is = 3
DoCmd.Close acForm, "Monthly Expenses Summary"
DoCmd.OpenForm "Main", acNormal

End Select

End Sub

--
javablood


:

Post the code, please. Can't help troubleshoot something we can't see.
--
Dave Hargis, Microsoft Access MVP


:

I have a program in VBA in which I tried to run an SQL for which two
parameters (text) are entered by calling for them from the 'criteria' field.
I also want to use those same parameters as the front part of the name of a
file to save. However, no matter what I do (Public, Dim, etc) the portion of
the code that uses the parameters for the file name does not 'see' those
parameters. I have also tried forms to capture the parameters but have had
no luck there either? What am I doing wrong?

TIA
Click to expand...
Click to expand...
Click to expand...
 
I wll try that and let you know. Thanks for the hint on the reversal, it is
better for sorting and I will implement that.

thanks
--
javablood


Klatuu said:
Your VBA code cannot see query parameters and a query can't see variables.
The better design would be to put two text box controls on your form and
have the use enter the values there. That way, both the query and VBA can
reference them.

In your query, you would use the fully qualified name of the control:
"HAVING ((([All Invoices].[Month invoice
paid])=[Forms]![MyFormName]![Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])=
[Forms]![MyFormName]![Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

And in your VBA code use:

strDefaultFileName = Me.Month_of_invoice & Me.Year_of_invoice & " monthly
expenses.xls"

Just another hint. I would use Year then month in the name. It is a
sorting issue. When a user is looking for a specific file name, it will sort
better:

As you wrote it:

012007
012008
022007
022008
...
122007
122008

As I Suggest:
200701
200702
....
200712
200801
200802
...
200812

--
Dave Hargis, Microsoft Access MVP


javablood said:
these are the input parameters that the user enters when prompted from the
query. they are used to capture the data from the table for the fields
"Month invoice paid" and "Year invoice paid" I have them listed as Public at
the beginning, is that not correct?
--
javablood


Klatuu said:
What are Month_of_invoice & Year_of_invoice?
Are they fields in the All Invoices table, controls on your form, or
variables? If they are variables, where are they defined?
--
Dave Hargis, Microsoft Access MVP


:

Here is the code. It is called from a form. Thanks

Option Compare Database
Public Month_of_invoice As String
Public Year_of_invoice As String


Private Sub Command33_Click()
Select Case Frame0.Value


Case Is = 2
' This creates MS Excel files from the monthly budget data
' for the Denver database only, which operates in MS Access 2007

Dim strDefaultDir As String
Dim strFilter As String
Dim strInputFileName As String
Dim SQL As String

DoCmd.SetWarnings False ' turn warnings off

SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], [All
Invoices].[Year invoice paid], [All Invoices].[Sent to HSRC?]" & _
"HAVING ((([All Invoices].[Month invoice paid])=[Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])= [Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

DoCmd.RunSQL SQL

' The following allows the user to save the Excel file to the directory
chosen by the user:
'Set up default path and file
strDefaultDir = "C:\Documents and Settings\My Documents\"

'Set up default file
strDefaultFileName = Month_of_invoice & Year_of_invoice & " monthly
expenses.xls"

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")

'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_OVERWRITEPROMPT

'Call the Open File Dialog
varGetFileName = ahtCommonFileOpenSave( _
Openfile:=False, _
InitialDir:=strDefaultDir, _
Filter:=strFilter, _
FileName:=strDefaultFileName, _
Flags:=lngFlags, _
DialogTitle:="Save Report")

If varGetFileName <> "" Then

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Monthly costs tracking", varGetFileName, True

End If

DoCmd.SetWarnings True ' turn warnings on

Msg2 = MsgBox("Your Excel file " & strDefaultFileName & " has been
created. Click OK to continue", vbInformation + vbOKOnly, "Excel File
Created")

Case Is = 3
DoCmd.Close acForm, "Monthly Expenses Summary"
DoCmd.OpenForm "Main", acNormal

End Select

End Sub

--
javablood


:

Post the code, please. Can't help troubleshoot something we can't see.
--
Dave Hargis, Microsoft Access MVP


:

I have a program in VBA in which I tried to run an SQL for which two
parameters (text) are entered by calling for them from the 'criteria' field.
I also want to use those same parameters as the front part of the name of a
file to save. However, no matter what I do (Public, Dim, etc) the portion of
the code that uses the parameters for the file name does not 'see' those
parameters. I have also tried forms to capture the parameters but have had
no luck there either? What am I doing wrong?

TIA
Click to expand...
Click to expand...
Click to expand...
 
Okay, post back if you have any problems getting it to work.
--
Dave Hargis, Microsoft Access MVP


javablood said:
I wll try that and let you know. Thanks for the hint on the reversal, it is
better for sorting and I will implement that.

thanks
--
javablood


Klatuu said:
Your VBA code cannot see query parameters and a query can't see variables.
The better design would be to put two text box controls on your form and
have the use enter the values there. That way, both the query and VBA can
reference them.

In your query, you would use the fully qualified name of the control:
"HAVING ((([All Invoices].[Month invoice
paid])=[Forms]![MyFormName]![Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])=
[Forms]![MyFormName]![Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

And in your VBA code use:

strDefaultFileName = Me.Month_of_invoice & Me.Year_of_invoice & " monthly
expenses.xls"

Just another hint. I would use Year then month in the name. It is a
sorting issue. When a user is looking for a specific file name, it will sort
better:

As you wrote it:

012007
012008
022007
022008
...
122007
122008

As I Suggest:
200701
200702
....
200712
200801
200802
...
200812

--
Dave Hargis, Microsoft Access MVP


javablood said:
these are the input parameters that the user enters when prompted from the
query. they are used to capture the data from the table for the fields
"Month invoice paid" and "Year invoice paid" I have them listed as Public at
the beginning, is that not correct?
--
javablood


:

What are Month_of_invoice & Year_of_invoice?
Are they fields in the All Invoices table, controls on your form, or
variables? If they are variables, where are they defined?
--
Dave Hargis, Microsoft Access MVP


:

Here is the code. It is called from a form. Thanks

Option Compare Database
Public Month_of_invoice As String
Public Year_of_invoice As String


Private Sub Command33_Click()
Select Case Frame0.Value


Case Is = 2
' This creates MS Excel files from the monthly budget data
' for the Denver database only, which operates in MS Access 2007

Dim strDefaultDir As String
Dim strFilter As String
Dim strInputFileName As String
Dim SQL As String

DoCmd.SetWarnings False ' turn warnings off

SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], [All
Invoices].[Year invoice paid], [All Invoices].[Sent to HSRC?]" & _
"HAVING ((([All Invoices].[Month invoice paid])=[Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])= [Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

DoCmd.RunSQL SQL

' The following allows the user to save the Excel file to the directory
chosen by the user:
'Set up default path and file
strDefaultDir = "C:\Documents and Settings\My Documents\"

'Set up default file
strDefaultFileName = Month_of_invoice & Year_of_invoice & " monthly
expenses.xls"

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")

'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_OVERWRITEPROMPT

'Call the Open File Dialog
varGetFileName = ahtCommonFileOpenSave( _
Openfile:=False, _
InitialDir:=strDefaultDir, _
Filter:=strFilter, _
FileName:=strDefaultFileName, _
Flags:=lngFlags, _
DialogTitle:="Save Report")

If varGetFileName <> "" Then

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Monthly costs tracking", varGetFileName, True

End If

DoCmd.SetWarnings True ' turn warnings on

Msg2 = MsgBox("Your Excel file " & strDefaultFileName & " has been
created. Click OK to continue", vbInformation + vbOKOnly, "Excel File
Created")

Case Is = 3
DoCmd.Close acForm, "Monthly Expenses Summary"
DoCmd.OpenForm "Main", acNormal

End Select

End Sub

--
javablood


:

Post the code, please. Can't help troubleshoot something we can't see.
--
Dave Hargis, Microsoft Access MVP


:

I have a program in VBA in which I tried to run an SQL for which two
parameters (text) are entered by calling for them from the 'criteria' field.
I also want to use those same parameters as the front part of the name of a
file to save. However, no matter what I do (Public, Dim, etc) the portion of
the code that uses the parameters for the file name does not 'see' those
parameters. I have also tried forms to capture the parameters but have had
no luck there either? What am I doing wrong?

TIA
Click to expand...
Click to expand...
Click to expand...
 
Ok I am doing something wrong. I put the text boxes on the same form from
which I call the code, is that correct? Then, should I create the text boxes
similar to the "Create a form that collects parameters" under Access help?
--
javablood


Klatuu said:
Your VBA code cannot see query parameters and a query can't see variables.
The better design would be to put two text box controls on your form and
have the use enter the values there. That way, both the query and VBA can
reference them.

In your query, you would use the fully qualified name of the control:
"HAVING ((([All Invoices].[Month invoice
paid])=[Forms]![MyFormName]![Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])=
[Forms]![MyFormName]![Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

And in your VBA code use:

strDefaultFileName = Me.Month_of_invoice & Me.Year_of_invoice & " monthly
expenses.xls"

Just another hint. I would use Year then month in the name. It is a
sorting issue. When a user is looking for a specific file name, it will sort
better:

As you wrote it:

012007
012008
022007
022008
...
122007
122008

As I Suggest:
200701
200702
....
200712
200801
200802
...
200812

--
Dave Hargis, Microsoft Access MVP


javablood said:
these are the input parameters that the user enters when prompted from the
query. they are used to capture the data from the table for the fields
"Month invoice paid" and "Year invoice paid" I have them listed as Public at
the beginning, is that not correct?
--
javablood


Klatuu said:
What are Month_of_invoice & Year_of_invoice?
Are they fields in the All Invoices table, controls on your form, or
variables? If they are variables, where are they defined?
--
Dave Hargis, Microsoft Access MVP


:

Here is the code. It is called from a form. Thanks

Option Compare Database
Public Month_of_invoice As String
Public Year_of_invoice As String


Private Sub Command33_Click()
Select Case Frame0.Value


Case Is = 2
' This creates MS Excel files from the monthly budget data
' for the Denver database only, which operates in MS Access 2007

Dim strDefaultDir As String
Dim strFilter As String
Dim strInputFileName As String
Dim SQL As String

DoCmd.SetWarnings False ' turn warnings off

SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], [All
Invoices].[Year invoice paid], [All Invoices].[Sent to HSRC?]" & _
"HAVING ((([All Invoices].[Month invoice paid])=[Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])= [Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

DoCmd.RunSQL SQL

' The following allows the user to save the Excel file to the directory
chosen by the user:
'Set up default path and file
strDefaultDir = "C:\Documents and Settings\My Documents\"

'Set up default file
strDefaultFileName = Month_of_invoice & Year_of_invoice & " monthly
expenses.xls"

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")

'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_OVERWRITEPROMPT

'Call the Open File Dialog
varGetFileName = ahtCommonFileOpenSave( _
Openfile:=False, _
InitialDir:=strDefaultDir, _
Filter:=strFilter, _
FileName:=strDefaultFileName, _
Flags:=lngFlags, _
DialogTitle:="Save Report")

If varGetFileName <> "" Then

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Monthly costs tracking", varGetFileName, True

End If

DoCmd.SetWarnings True ' turn warnings on

Msg2 = MsgBox("Your Excel file " & strDefaultFileName & " has been
created. Click OK to continue", vbInformation + vbOKOnly, "Excel File
Created")

Case Is = 3
DoCmd.Close acForm, "Monthly Expenses Summary"
DoCmd.OpenForm "Main", acNormal

End Select

End Sub

--
javablood


:

Post the code, please. Can't help troubleshoot something we can't see.
--
Dave Hargis, Microsoft Access MVP


:

I have a program in VBA in which I tried to run an SQL for which two
parameters (text) are entered by calling for them from the 'criteria' field.
I also want to use those same parameters as the front part of the name of a
file to save. However, no matter what I do (Public, Dim, etc) the portion of
the code that uses the parameters for the file name does not 'see' those
parameters. I have also tried forms to capture the parameters but have had
no luck there either? What am I doing wrong?

TIA
Click to expand...
Click to expand...
Click to expand...
 
Yes, the text boxes should go on the same form. You don't need to use a
Create a form..." wizard. You already have a form. Just add the text boxes
to the form. Be sure the parameters in your query have the same name as the
name of the text boxes. I just used your parameter prompts as names as an
example.
--
Dave Hargis, Microsoft Access MVP


javablood said:
Ok I am doing something wrong. I put the text boxes on the same form from
which I call the code, is that correct? Then, should I create the text boxes
similar to the "Create a form that collects parameters" under Access help?
--
javablood


Klatuu said:
Your VBA code cannot see query parameters and a query can't see variables.
The better design would be to put two text box controls on your form and
have the use enter the values there. That way, both the query and VBA can
reference them.

In your query, you would use the fully qualified name of the control:
"HAVING ((([All Invoices].[Month invoice
paid])=[Forms]![MyFormName]![Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])=
[Forms]![MyFormName]![Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

And in your VBA code use:

strDefaultFileName = Me.Month_of_invoice & Me.Year_of_invoice & " monthly
expenses.xls"

Just another hint. I would use Year then month in the name. It is a
sorting issue. When a user is looking for a specific file name, it will sort
better:

As you wrote it:

012007
012008
022007
022008
...
122007
122008

As I Suggest:
200701
200702
....
200712
200801
200802
...
200812

--
Dave Hargis, Microsoft Access MVP


javablood said:
these are the input parameters that the user enters when prompted from the
query. they are used to capture the data from the table for the fields
"Month invoice paid" and "Year invoice paid" I have them listed as Public at
the beginning, is that not correct?
--
javablood


:

What are Month_of_invoice & Year_of_invoice?
Are they fields in the All Invoices table, controls on your form, or
variables? If they are variables, where are they defined?
--
Dave Hargis, Microsoft Access MVP


:

Here is the code. It is called from a form. Thanks

Option Compare Database
Public Month_of_invoice As String
Public Year_of_invoice As String


Private Sub Command33_Click()
Select Case Frame0.Value


Case Is = 2
' This creates MS Excel files from the monthly budget data
' for the Denver database only, which operates in MS Access 2007

Dim strDefaultDir As String
Dim strFilter As String
Dim strInputFileName As String
Dim SQL As String

DoCmd.SetWarnings False ' turn warnings off

SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], [All
Invoices].[Year invoice paid], [All Invoices].[Sent to HSRC?]" & _
"HAVING ((([All Invoices].[Month invoice paid])=[Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])= [Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

DoCmd.RunSQL SQL

' The following allows the user to save the Excel file to the directory
chosen by the user:
'Set up default path and file
strDefaultDir = "C:\Documents and Settings\My Documents\"

'Set up default file
strDefaultFileName = Month_of_invoice & Year_of_invoice & " monthly
expenses.xls"

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")

'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_OVERWRITEPROMPT

'Call the Open File Dialog
varGetFileName = ahtCommonFileOpenSave( _
Openfile:=False, _
InitialDir:=strDefaultDir, _
Filter:=strFilter, _
FileName:=strDefaultFileName, _
Flags:=lngFlags, _
DialogTitle:="Save Report")

If varGetFileName <> "" Then

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Monthly costs tracking", varGetFileName, True

End If

DoCmd.SetWarnings True ' turn warnings on

Msg2 = MsgBox("Your Excel file " & strDefaultFileName & " has been
created. Click OK to continue", vbInformation + vbOKOnly, "Excel File
Created")

Case Is = 3
DoCmd.Close acForm, "Monthly Expenses Summary"
DoCmd.OpenForm "Main", acNormal

End Select

End Sub

--
javablood


:

Post the code, please. Can't help troubleshoot something we can't see.
--
Dave Hargis, Microsoft Access MVP


:

I have a program in VBA in which I tried to run an SQL for which two
parameters (text) are entered by calling for them from the 'criteria' field.
I also want to use those same parameters as the front part of the name of a
file to save. However, no matter what I do (Public, Dim, etc) the portion of
the code that uses the parameters for the file name does not 'see' those
parameters. I have also tried forms to capture the parameters but have had
no luck there either? What am I doing wrong?

TIA
Click to expand...
Click to expand...
Click to expand...
 
Dave,

My mistake. I had the control in the wrong place. It works!!! Thanks so
much! Guess I need to read up on parameters and variables - I thought they
were the same or similar.
--
javablood


Klatuu said:
Yes, the text boxes should go on the same form. You don't need to use a
Create a form..." wizard. You already have a form. Just add the text boxes
to the form. Be sure the parameters in your query have the same name as the
name of the text boxes. I just used your parameter prompts as names as an
example.
--
Dave Hargis, Microsoft Access MVP


javablood said:
Ok I am doing something wrong. I put the text boxes on the same form from
which I call the code, is that correct? Then, should I create the text boxes
similar to the "Create a form that collects parameters" under Access help?
--
javablood


Klatuu said:
Your VBA code cannot see query parameters and a query can't see variables.
The better design would be to put two text box controls on your form and
have the use enter the values there. That way, both the query and VBA can
reference them.

In your query, you would use the fully qualified name of the control:
"HAVING ((([All Invoices].[Month invoice
paid])=[Forms]![MyFormName]![Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])=
[Forms]![MyFormName]![Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

And in your VBA code use:

strDefaultFileName = Me.Month_of_invoice & Me.Year_of_invoice & " monthly
expenses.xls"

Just another hint. I would use Year then month in the name. It is a
sorting issue. When a user is looking for a specific file name, it will sort
better:

As you wrote it:

012007
012008
022007
022008
...
122007
122008

As I Suggest:
200701
200702
....
200712
200801
200802
...
200812

--
Dave Hargis, Microsoft Access MVP


:

these are the input parameters that the user enters when prompted from the
query. they are used to capture the data from the table for the fields
"Month invoice paid" and "Year invoice paid" I have them listed as Public at
the beginning, is that not correct?
--
javablood


:

What are Month_of_invoice & Year_of_invoice?
Are they fields in the All Invoices table, controls on your form, or
variables? If they are variables, where are they defined?
--
Dave Hargis, Microsoft Access MVP


:

Here is the code. It is called from a form. Thanks

Option Compare Database
Public Month_of_invoice As String
Public Year_of_invoice As String


Private Sub Command33_Click()
Select Case Frame0.Value


Case Is = 2
' This creates MS Excel files from the monthly budget data
' for the Denver database only, which operates in MS Access 2007

Dim strDefaultDir As String
Dim strFilter As String
Dim strInputFileName As String
Dim SQL As String

DoCmd.SetWarnings False ' turn warnings off

SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], [All
Invoices].[Year invoice paid], [All Invoices].[Sent to HSRC?]" & _
"HAVING ((([All Invoices].[Month invoice paid])=[Month_of_Invoice]) AND
(([All Invoices].[Year invoice paid])= [Year_of_Invoice]) AND (([All
Invoices].[Sent to HSRC?])=True));"

DoCmd.RunSQL SQL

' The following allows the user to save the Excel file to the directory
chosen by the user:
'Set up default path and file
strDefaultDir = "C:\Documents and Settings\My Documents\"

'Set up default file
strDefaultFileName = Month_of_invoice & Year_of_invoice & " monthly
expenses.xls"

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")

'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_OVERWRITEPROMPT

'Call the Open File Dialog
varGetFileName = ahtCommonFileOpenSave( _
Openfile:=False, _
InitialDir:=strDefaultDir, _
Filter:=strFilter, _
FileName:=strDefaultFileName, _
Flags:=lngFlags, _
DialogTitle:="Save Report")

If varGetFileName <> "" Then

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Monthly costs tracking", varGetFileName, True

End If

DoCmd.SetWarnings True ' turn warnings on

Msg2 = MsgBox("Your Excel file " & strDefaultFileName & " has been
created. Click OK to continue", vbInformation + vbOKOnly, "Excel File
Created")

Case Is = 3
DoCmd.Close acForm, "Monthly Expenses Summary"
DoCmd.OpenForm "Main", acNormal

End Select

End Sub

--
javablood


:

Post the code, please. Can't help troubleshoot something we can't see.
--
Dave Hargis, Microsoft Access MVP


:

I have a program in VBA in which I tried to run an SQL for which two
parameters (text) are entered by calling for them from the 'criteria' field.
I also want to use those same parameters as the front part of the name of a
file to save. However, no matter what I do (Public, Dim, etc) the portion of
the code that uses the parameters for the file name does not 'see' those
parameters. I have also tried forms to capture the parameters but have had
no luck there either? What am I doing wrong?

TIA
Click to expand...
Click to expand...
Click to expand...
 
Back
Top