A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Cannot get code to work for API Save Dialog Box



 
 
Thread Tools Display Modes
  #11  
Old February 15th, 2006, 05:33 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Cannot get code to work for API Save Dialog Box

You will need to create a parameter query that will include only the data in
your sub form.

"Elleve" wrote:

I managed to get the open dialog box by this code:

Private Sub cmdExportSupportSchedule_Click()

Dim strFilter As String
Dim lngFlags As Long
Dim strDefaultDir As String
Dim strSaveFileName As String

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

'Get the File Name To Save
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If strSaveFileName = "" Then 'User Clicked CANCEL
Exit Sub
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrySubformUnionQuery1and2", strSaveFileName, True

End Sub

My problem now is that it is not exporting the correct information because
it is taking information from the query only. I want it to export only what
I have in the form w/subform currently. The main form is based on what is in
the listbox and the subform is based on qrySupportScheduleUnionqry1and2 that
is linked to the main form by four criterias of month, year, BU, and Affil.

How do I get only the information selected?


"Klatuu" wrote:

First thing is, Private Sub End Sub is not a module, it is a procedure.
There are two types of procedures. A Sub is called to execute code but does
not return any value to the calling procedure. You usually use a Sub for
events or for tasks that may be called from more than one place. Basically,
if you have a procedure you may call from more than one form or form multiple
places in a form, you should put that code in a Sub. If it is called from
more than one form or report, then it should be in a Standard module. If it
is only used by one form, then it can go in the General section of the form
module.

A Function is another type of procedure. It is used to perform some
evaluation or calculation and returns a value to the calling procedure. The
same rules as for Subs applys on how to use it .

A module is a collection of Subs and or Functions. Forms and reports can
have, but are not required to have modules. The code you put in your form
and report events are in the form or report module. A Standard module is a
collection of code that is usually called from forms, reports, or other
modules. For example, the code you downloaded for the API is a module. I
usually group procedures into modules that have something in common. I have
one module that is nothing more that date functions - Calculating the number
of working days between two dates, Finding the Friday of a given week, etc.

As to your code below. There are two things that need attention. First
there is a problem with this line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True

The next issue is declaring variables. Although not required, it is best to
establish the habit of always declaring and typing your variables. You can
always tell what data type my variables are by the prefix on the name. For
example var is for a Variant data type, str is for a String data type, lng is
Long Integer, etc. The reason you did not see the variable declarations the
code I sent is because they were declared at the module level because I need
them in more than this sub. Read up on varialbe Scoping.

So, add these lines right after the Sub statement:

Dim strFilter as String
Dim lngFlags as Long
Dim strDefaultDir as String
Dim varGetFileName as Variant

And, good luck!

frmSupportScheduleGLMainForm is a form name. What you want here is the name
of the table or query you are exporting.
"Elleve" wrote:

You're great in your feedback and speediness. I seem to be on the right
track now, I only have one more question regarding modules as I have never
used them before - how do you set up the module? Is it like private sub-end
sub?

By the way, here is the code I have for the event itself:

Private Sub cmdExportSupportSchedule_Click()
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY
'Get the File Name To Save
strDefaultDir = "c:/"
varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT,
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , "xls", varGetFileName,
"Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True
End Sub

Does this look like it would work?

"Klatuu" wrote:

Not stupid questions at all. See answers below in your post:

"Elleve" wrote:

Will I have to include the comboboxes in the coding? For your information,
No, what I sent was just an example. You can change it as necessary to use
the values you need for your situation. If they come from a list box, just
reference the column in the list box you need to use.

the listbox I mentioned last time consist of two columns that are contain a
relationship from a separate query. They are neccessary in order to pick up
the correct information. This is done by
=[Forms]![frmSupportScheduleMain]![List54].[column](0). Why can't I just
export what is at the current form to excel since this information is already
specified?
You can. This code does not do the export, it only returns a path and file
where you want to open or save the data. If you are exporting to Excel, you
would use varGetFileName as the File argument in your TransferSpreadsheet.


Also, as for the coding you provided me - where exactly do I input this?
Under the click event? Will I still need to keep the downloaded coding from
If you want this to happen as the result of a click event, then that would
be the place to put it.
You still need the downloaded code. It should be kept by itself in a
Standard module. Mine is named modCommonDialog. The sample I provided
calles the API routines in modCommonDialog.
the website as is or make changes to it. I apologize if I ask stupid
Do Not make changes to it. Calling APIs is very powerful, but also somewhat
dangerous. If you make changes in the downloaded code, you could get some
very weird unexpected results.

API stands for Application Program Interface. What you are doing is making
calls to DLL's in the Windows operating system. If you pass bad values, bad
things can happen. Don't let this discourage you. The alternative is an
ActiveX control. ActiveX controls are not that easy to deal with. This API
is very useful. My current application uses it about 20 different places.

questions, but that coding just got me lost.


"Klatuu" wrote:

First, don't put anything in the module you downloaded. It could cause
problems. Here is an example of how I use that exact same API module:

'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

strCurrMonth = Me.cboPeriod.Column(1)
strCurrYear = Me.txtCurrYear
'Get the File Name To Save
strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
varGetFileName = "Vought Invoice " & strCurrMonth & " " & strCurrYear &
".xls"

varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, _
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , _
"xls", varGetFileName, "Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If


"Elleve" wrote:

I have an access database (2000) where I want the user to click on a button
to save the current form (with subform) to excel. This event will open a
dialog box so the user will be able to save.

I have seen several links to http://www.mvps.org/access/api/api0001.htm when
creating dialog box to save. However, I cannot get this code to work. What
am I doing wrong?

I copied the code exactly like shown on the website and then started making
my changes to make it work. I did not understand how to put in my own
coding, if this should be a substitute for testit or my own "on click" or
function. Here is what I did by deleting the function testit():

Private Sub cmdTest_Click()
Dim strFilter As String
Dim strSaveFileName As String
strFilter = ahtAddFilterItem(mystrFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
End Sub

I keep getting the error message: "only comments may appear after end sub,
end function or end property."

Ads
  #12  
Old February 15th, 2006, 05:41 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Cannot get code to work for API Save Dialog Box

THANKS!!! You've been a great help.

"Klatuu" wrote:

You will need to create a parameter query that will include only the data in
your sub form.

"Elleve" wrote:

I managed to get the open dialog box by this code:

Private Sub cmdExportSupportSchedule_Click()

Dim strFilter As String
Dim lngFlags As Long
Dim strDefaultDir As String
Dim strSaveFileName As String

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

'Get the File Name To Save
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If strSaveFileName = "" Then 'User Clicked CANCEL
Exit Sub
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrySubformUnionQuery1and2", strSaveFileName, True

End Sub

My problem now is that it is not exporting the correct information because
it is taking information from the query only. I want it to export only what
I have in the form w/subform currently. The main form is based on what is in
the listbox and the subform is based on qrySupportScheduleUnionqry1and2 that
is linked to the main form by four criterias of month, year, BU, and Affil.

How do I get only the information selected?


"Klatuu" wrote:

First thing is, Private Sub End Sub is not a module, it is a procedure.
There are two types of procedures. A Sub is called to execute code but does
not return any value to the calling procedure. You usually use a Sub for
events or for tasks that may be called from more than one place. Basically,
if you have a procedure you may call from more than one form or form multiple
places in a form, you should put that code in a Sub. If it is called from
more than one form or report, then it should be in a Standard module. If it
is only used by one form, then it can go in the General section of the form
module.

A Function is another type of procedure. It is used to perform some
evaluation or calculation and returns a value to the calling procedure. The
same rules as for Subs applys on how to use it .

A module is a collection of Subs and or Functions. Forms and reports can
have, but are not required to have modules. The code you put in your form
and report events are in the form or report module. A Standard module is a
collection of code that is usually called from forms, reports, or other
modules. For example, the code you downloaded for the API is a module. I
usually group procedures into modules that have something in common. I have
one module that is nothing more that date functions - Calculating the number
of working days between two dates, Finding the Friday of a given week, etc.

As to your code below. There are two things that need attention. First
there is a problem with this line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True

The next issue is declaring variables. Although not required, it is best to
establish the habit of always declaring and typing your variables. You can
always tell what data type my variables are by the prefix on the name. For
example var is for a Variant data type, str is for a String data type, lng is
Long Integer, etc. The reason you did not see the variable declarations the
code I sent is because they were declared at the module level because I need
them in more than this sub. Read up on varialbe Scoping.

So, add these lines right after the Sub statement:

Dim strFilter as String
Dim lngFlags as Long
Dim strDefaultDir as String
Dim varGetFileName as Variant

And, good luck!

frmSupportScheduleGLMainForm is a form name. What you want here is the name
of the table or query you are exporting.
"Elleve" wrote:

You're great in your feedback and speediness. I seem to be on the right
track now, I only have one more question regarding modules as I have never
used them before - how do you set up the module? Is it like private sub-end
sub?

By the way, here is the code I have for the event itself:

Private Sub cmdExportSupportSchedule_Click()
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY
'Get the File Name To Save
strDefaultDir = "c:/"
varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT,
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , "xls", varGetFileName,
"Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True
End Sub

Does this look like it would work?

"Klatuu" wrote:

Not stupid questions at all. See answers below in your post:

"Elleve" wrote:

Will I have to include the comboboxes in the coding? For your information,
No, what I sent was just an example. You can change it as necessary to use
the values you need for your situation. If they come from a list box, just
reference the column in the list box you need to use.

the listbox I mentioned last time consist of two columns that are contain a
relationship from a separate query. They are neccessary in order to pick up
the correct information. This is done by
=[Forms]![frmSupportScheduleMain]![List54].[column](0). Why can't I just
export what is at the current form to excel since this information is already
specified?
You can. This code does not do the export, it only returns a path and file
where you want to open or save the data. If you are exporting to Excel, you
would use varGetFileName as the File argument in your TransferSpreadsheet.


Also, as for the coding you provided me - where exactly do I input this?
Under the click event? Will I still need to keep the downloaded coding from
If you want this to happen as the result of a click event, then that would
be the place to put it.
You still need the downloaded code. It should be kept by itself in a
Standard module. Mine is named modCommonDialog. The sample I provided
calles the API routines in modCommonDialog.
the website as is or make changes to it. I apologize if I ask stupid
Do Not make changes to it. Calling APIs is very powerful, but also somewhat
dangerous. If you make changes in the downloaded code, you could get some
very weird unexpected results.

API stands for Application Program Interface. What you are doing is making
calls to DLL's in the Windows operating system. If you pass bad values, bad
things can happen. Don't let this discourage you. The alternative is an
ActiveX control. ActiveX controls are not that easy to deal with. This API
is very useful. My current application uses it about 20 different places.

questions, but that coding just got me lost.


"Klatuu" wrote:

First, don't put anything in the module you downloaded. It could cause
problems. Here is an example of how I use that exact same API module:

'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

strCurrMonth = Me.cboPeriod.Column(1)
strCurrYear = Me.txtCurrYear
'Get the File Name To Save
strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
varGetFileName = "Vought Invoice " & strCurrMonth & " " & strCurrYear &
".xls"

varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, _
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , _
"xls", varGetFileName, "Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If


"Elleve" wrote:

I have an access database (2000) where I want the user to click on a button
to save the current form (with subform) to excel. This event will open a
dialog box so the user will be able to save.

I have seen several links to http://www.mvps.org/access/api/api0001.htm when
creating dialog box to save. However, I cannot get this code to work. What
am I doing wrong?

I copied the code exactly like shown on the website and then started making
my changes to make it work. I did not understand how to put in my own
coding, if this should be a substitute for testit or my own "on click" or
function. Here is what I did by deleting the function testit():

Private Sub cmdTest_Click()
Dim strFilter As String
Dim strSaveFileName As String
strFilter = ahtAddFilterItem(mystrFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
End Sub

I keep getting the error message: "only comments may appear after end sub,
end function or end property."

  #13  
Old February 15th, 2006, 10:56 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Cannot get code to work for API Save Dialog Box

That seemed to be more difficult than I thought. The reason is that the
subform is based on a union query (of two other queries). I tried putting
parameters in both the underlying queries, but I got into trouble. I need
four parameters to get the information needed, two comboboxes and two from a
listbox. As for the comboboxes I put in the following that seems to work:
[Month]=[Forms]!frmName]![comboMonth]
[Year]=[Forms]!frmName]![comboYear]

The listbox creates the problem:
[Unit]=[Forms]![frmName]![listbox].column(0)
[Affil]]=[Forms]![frmName]![listbox].column(1)

The error message says "undefined function listbox..."

I tried putting this code into the expression builder when clicking the
button to open the form/subform itself, but that gave me the same error
message....

What do you suggest I do?

"Klatuu" wrote:

You will need to create a parameter query that will include only the data in
your sub form.

"Elleve" wrote:

I managed to get the open dialog box by this code:

Private Sub cmdExportSupportSchedule_Click()

Dim strFilter As String
Dim lngFlags As Long
Dim strDefaultDir As String
Dim strSaveFileName As String

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

'Get the File Name To Save
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If strSaveFileName = "" Then 'User Clicked CANCEL
Exit Sub
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrySubformUnionQuery1and2", strSaveFileName, True

End Sub

My problem now is that it is not exporting the correct information because
it is taking information from the query only. I want it to export only what
I have in the form w/subform currently. The main form is based on what is in
the listbox and the subform is based on qrySupportScheduleUnionqry1and2 that
is linked to the main form by four criterias of month, year, BU, and Affil.

How do I get only the information selected?


"Klatuu" wrote:

First thing is, Private Sub End Sub is not a module, it is a procedure.
There are two types of procedures. A Sub is called to execute code but does
not return any value to the calling procedure. You usually use a Sub for
events or for tasks that may be called from more than one place. Basically,
if you have a procedure you may call from more than one form or form multiple
places in a form, you should put that code in a Sub. If it is called from
more than one form or report, then it should be in a Standard module. If it
is only used by one form, then it can go in the General section of the form
module.

A Function is another type of procedure. It is used to perform some
evaluation or calculation and returns a value to the calling procedure. The
same rules as for Subs applys on how to use it .

A module is a collection of Subs and or Functions. Forms and reports can
have, but are not required to have modules. The code you put in your form
and report events are in the form or report module. A Standard module is a
collection of code that is usually called from forms, reports, or other
modules. For example, the code you downloaded for the API is a module. I
usually group procedures into modules that have something in common. I have
one module that is nothing more that date functions - Calculating the number
of working days between two dates, Finding the Friday of a given week, etc.

As to your code below. There are two things that need attention. First
there is a problem with this line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True

The next issue is declaring variables. Although not required, it is best to
establish the habit of always declaring and typing your variables. You can
always tell what data type my variables are by the prefix on the name. For
example var is for a Variant data type, str is for a String data type, lng is
Long Integer, etc. The reason you did not see the variable declarations the
code I sent is because they were declared at the module level because I need
them in more than this sub. Read up on varialbe Scoping.

So, add these lines right after the Sub statement:

Dim strFilter as String
Dim lngFlags as Long
Dim strDefaultDir as String
Dim varGetFileName as Variant

And, good luck!

frmSupportScheduleGLMainForm is a form name. What you want here is the name
of the table or query you are exporting.
"Elleve" wrote:

You're great in your feedback and speediness. I seem to be on the right
track now, I only have one more question regarding modules as I have never
used them before - how do you set up the module? Is it like private sub-end
sub?

By the way, here is the code I have for the event itself:

Private Sub cmdExportSupportSchedule_Click()
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY
'Get the File Name To Save
strDefaultDir = "c:/"
varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT,
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , "xls", varGetFileName,
"Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True
End Sub

Does this look like it would work?

"Klatuu" wrote:

Not stupid questions at all. See answers below in your post:

"Elleve" wrote:

Will I have to include the comboboxes in the coding? For your information,
No, what I sent was just an example. You can change it as necessary to use
the values you need for your situation. If they come from a list box, just
reference the column in the list box you need to use.

the listbox I mentioned last time consist of two columns that are contain a
relationship from a separate query. They are neccessary in order to pick up
the correct information. This is done by
=[Forms]![frmSupportScheduleMain]![List54].[column](0). Why can't I just
export what is at the current form to excel since this information is already
specified?
You can. This code does not do the export, it only returns a path and file
where you want to open or save the data. If you are exporting to Excel, you
would use varGetFileName as the File argument in your TransferSpreadsheet.


Also, as for the coding you provided me - where exactly do I input this?
Under the click event? Will I still need to keep the downloaded coding from
If you want this to happen as the result of a click event, then that would
be the place to put it.
You still need the downloaded code. It should be kept by itself in a
Standard module. Mine is named modCommonDialog. The sample I provided
calles the API routines in modCommonDialog.
the website as is or make changes to it. I apologize if I ask stupid
Do Not make changes to it. Calling APIs is very powerful, but also somewhat
dangerous. If you make changes in the downloaded code, you could get some
very weird unexpected results.

API stands for Application Program Interface. What you are doing is making
calls to DLL's in the Windows operating system. If you pass bad values, bad
things can happen. Don't let this discourage you. The alternative is an
ActiveX control. ActiveX controls are not that easy to deal with. This API
is very useful. My current application uses it about 20 different places.

questions, but that coding just got me lost.


"Klatuu" wrote:

First, don't put anything in the module you downloaded. It could cause
problems. Here is an example of how I use that exact same API module:

'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

strCurrMonth = Me.cboPeriod.Column(1)
strCurrYear = Me.txtCurrYear
'Get the File Name To Save
strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
varGetFileName = "Vought Invoice " & strCurrMonth & " " & strCurrYear &
".xls"

varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, _
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , _
"xls", varGetFileName, "Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If


"Elleve" wrote:

I have an access database (2000) where I want the user to click on a button
to save the current form (with subform) to excel. This event will open a
dialog box so the user will be able to save.

I have seen several links to http://www.mvps.org/access/api/api0001.htm when
creating dialog box to save. However, I cannot get this code to work. What
am I doing wrong?

I copied the code exactly like shown on the website and then started making
my changes to make it work. I did not understand how to put in my own
coding, if this should be a substitute for testit or my own "on click" or
function. Here is what I did by deleting the function testit():

Private Sub cmdTest_Click()
Dim strFilter As String
Dim strSaveFileName As String
strFilter = ahtAddFilterItem(mystrFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
End Sub

I keep getting the error message: "only comments may appear after end sub,
end function or end property."

  #14  
Old February 16th, 2006, 12:01 AM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Cannot get code to work for API Save Dialog Box

I assume that you are not really naming your listbox listbox. Don't use
Access reserved named to name your objects, but I can't say that is the
problem.

Is this line actually in your criteria?
[Affil]]=[Forms]![frmName]![listbox].column(1)
Seems it would throw a syntax error in the query builder.
I have not used a listbox as a query parameter, but I have used combo boxes
as parameters in queries that are tied to union queries, so that part should
be good.


"Elleve" wrote:

That seemed to be more difficult than I thought. The reason is that the
subform is based on a union query (of two other queries). I tried putting
parameters in both the underlying queries, but I got into trouble. I need
four parameters to get the information needed, two comboboxes and two from a
listbox. As for the comboboxes I put in the following that seems to work:
[Month]=[Forms]!frmName]![comboMonth]
[Year]=[Forms]!frmName]![comboYear]

The listbox creates the problem:
[Unit]=[Forms]![frmName]![listbox].column(0)
[Affil]]=[Forms]![frmName]![listbox].column(1)

The error message says "undefined function listbox..."

I tried putting this code into the expression builder when clicking the
button to open the form/subform itself, but that gave me the same error
message....

What do you suggest I do?

"Klatuu" wrote:

You will need to create a parameter query that will include only the data in
your sub form.

"Elleve" wrote:

I managed to get the open dialog box by this code:

Private Sub cmdExportSupportSchedule_Click()

Dim strFilter As String
Dim lngFlags As Long
Dim strDefaultDir As String
Dim strSaveFileName As String

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

'Get the File Name To Save
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If strSaveFileName = "" Then 'User Clicked CANCEL
Exit Sub
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrySubformUnionQuery1and2", strSaveFileName, True

End Sub

My problem now is that it is not exporting the correct information because
it is taking information from the query only. I want it to export only what
I have in the form w/subform currently. The main form is based on what is in
the listbox and the subform is based on qrySupportScheduleUnionqry1and2 that
is linked to the main form by four criterias of month, year, BU, and Affil.

How do I get only the information selected?


"Klatuu" wrote:

First thing is, Private Sub End Sub is not a module, it is a procedure.
There are two types of procedures. A Sub is called to execute code but does
not return any value to the calling procedure. You usually use a Sub for
events or for tasks that may be called from more than one place. Basically,
if you have a procedure you may call from more than one form or form multiple
places in a form, you should put that code in a Sub. If it is called from
more than one form or report, then it should be in a Standard module. If it
is only used by one form, then it can go in the General section of the form
module.

A Function is another type of procedure. It is used to perform some
evaluation or calculation and returns a value to the calling procedure. The
same rules as for Subs applys on how to use it .

A module is a collection of Subs and or Functions. Forms and reports can
have, but are not required to have modules. The code you put in your form
and report events are in the form or report module. A Standard module is a
collection of code that is usually called from forms, reports, or other
modules. For example, the code you downloaded for the API is a module. I
usually group procedures into modules that have something in common. I have
one module that is nothing more that date functions - Calculating the number
of working days between two dates, Finding the Friday of a given week, etc.

As to your code below. There are two things that need attention. First
there is a problem with this line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True

The next issue is declaring variables. Although not required, it is best to
establish the habit of always declaring and typing your variables. You can
always tell what data type my variables are by the prefix on the name. For
example var is for a Variant data type, str is for a String data type, lng is
Long Integer, etc. The reason you did not see the variable declarations the
code I sent is because they were declared at the module level because I need
them in more than this sub. Read up on varialbe Scoping.

So, add these lines right after the Sub statement:

Dim strFilter as String
Dim lngFlags as Long
Dim strDefaultDir as String
Dim varGetFileName as Variant

And, good luck!

frmSupportScheduleGLMainForm is a form name. What you want here is the name
of the table or query you are exporting.
"Elleve" wrote:

You're great in your feedback and speediness. I seem to be on the right
track now, I only have one more question regarding modules as I have never
used them before - how do you set up the module? Is it like private sub-end
sub?

By the way, here is the code I have for the event itself:

Private Sub cmdExportSupportSchedule_Click()
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY
'Get the File Name To Save
strDefaultDir = "c:/"
varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT,
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , "xls", varGetFileName,
"Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True
End Sub

Does this look like it would work?

"Klatuu" wrote:

Not stupid questions at all. See answers below in your post:

"Elleve" wrote:

Will I have to include the comboboxes in the coding? For your information,
No, what I sent was just an example. You can change it as necessary to use
the values you need for your situation. If they come from a list box, just
reference the column in the list box you need to use.

the listbox I mentioned last time consist of two columns that are contain a
relationship from a separate query. They are neccessary in order to pick up
the correct information. This is done by
=[Forms]![frmSupportScheduleMain]![List54].[column](0). Why can't I just
export what is at the current form to excel since this information is already
specified?
You can. This code does not do the export, it only returns a path and file
where you want to open or save the data. If you are exporting to Excel, you
would use varGetFileName as the File argument in your TransferSpreadsheet.


Also, as for the coding you provided me - where exactly do I input this?
Under the click event? Will I still need to keep the downloaded coding from
If you want this to happen as the result of a click event, then that would
be the place to put it.
You still need the downloaded code. It should be kept by itself in a
Standard module. Mine is named modCommonDialog. The sample I provided
calles the API routines in modCommonDialog.
the website as is or make changes to it. I apologize if I ask stupid
Do Not make changes to it. Calling APIs is very powerful, but also somewhat
dangerous. If you make changes in the downloaded code, you could get some
very weird unexpected results.

API stands for Application Program Interface. What you are doing is making
calls to DLL's in the Windows operating system. If you pass bad values, bad
things can happen. Don't let this discourage you. The alternative is an
ActiveX control. ActiveX controls are not that easy to deal with. This API
is very useful. My current application uses it about 20 different places.

questions, but that coding just got me lost.


"Klatuu" wrote:

First, don't put anything in the module you downloaded. It could cause
problems. Here is an example of how I use that exact same API module:

'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

strCurrMonth = Me.cboPeriod.Column(1)
strCurrYear = Me.txtCurrYear
'Get the File Name To Save
strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
varGetFileName = "Vought Invoice " & strCurrMonth & " " & strCurrYear &
".xls"

varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, _
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , _
"xls", varGetFileName, "Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If


"Elleve" wrote:

I have an access database (2000) where I want the user to click on a button
to save the current form (with subform) to excel. This event will open a
dialog box so the user will be able to save.

I have seen several links to http://www.mvps.org/access/api/api0001.htm when
creating dialog box to save. However, I cannot get this code to work. What
am I doing wrong?

I copied the code exactly like shown on the website and then started making
my changes to make it work. I did not understand how to put in my own
coding, if this should be a substitute for testit or my own "on click" or
function. Here is what I did by deleting the function testit():

Private Sub cmdTest_Click()
Dim strFilter As String
Dim strSaveFileName As String
strFilter = ahtAddFilterItem(mystrFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
End Sub

I keep getting the error message: "only comments may appear after end sub,
end function or end property."

  #15  
Old February 16th, 2006, 12:14 AM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Cannot get code to work for API Save Dialog Box

The listbox is called listname so that should not be the problem. Yes, the
line in my criteria is: [Affil]=[Forms]![frmName]![listName].column(1). Why
would that cause a problem? I would like to keep my listbox as I will only
show about 18 relationships every time and having comboboxes would confuse
the user more than it would do good.

Any ideas of how to make it work?

"Klatuu" wrote:

I assume that you are not really naming your listbox listbox. Don't use
Access reserved named to name your objects, but I can't say that is the
problem.

Is this line actually in your criteria?
[Affil]]=[Forms]![frmName]![listbox].column(1)
Seems it would throw a syntax error in the query builder.
I have not used a listbox as a query parameter, but I have used combo boxes
as parameters in queries that are tied to union queries, so that part should
be good.


"Elleve" wrote:

That seemed to be more difficult than I thought. The reason is that the
subform is based on a union query (of two other queries). I tried putting
parameters in both the underlying queries, but I got into trouble. I need
four parameters to get the information needed, two comboboxes and two from a
listbox. As for the comboboxes I put in the following that seems to work:
[Month]=[Forms]!frmName]![comboMonth]
[Year]=[Forms]!frmName]![comboYear]

The listbox creates the problem:
[Unit]=[Forms]![frmName]![listbox].column(0)
[Affil]]=[Forms]![frmName]![listbox].column(1)

The error message says "undefined function listbox..."

I tried putting this code into the expression builder when clicking the
button to open the form/subform itself, but that gave me the same error
message....

What do you suggest I do?

"Klatuu" wrote:

You will need to create a parameter query that will include only the data in
your sub form.

"Elleve" wrote:

I managed to get the open dialog box by this code:

Private Sub cmdExportSupportSchedule_Click()

Dim strFilter As String
Dim lngFlags As Long
Dim strDefaultDir As String
Dim strSaveFileName As String

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

'Get the File Name To Save
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If strSaveFileName = "" Then 'User Clicked CANCEL
Exit Sub
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrySubformUnionQuery1and2", strSaveFileName, True

End Sub

My problem now is that it is not exporting the correct information because
it is taking information from the query only. I want it to export only what
I have in the form w/subform currently. The main form is based on what is in
the listbox and the subform is based on qrySupportScheduleUnionqry1and2 that
is linked to the main form by four criterias of month, year, BU, and Affil.

How do I get only the information selected?


"Klatuu" wrote:

First thing is, Private Sub End Sub is not a module, it is a procedure.
There are two types of procedures. A Sub is called to execute code but does
not return any value to the calling procedure. You usually use a Sub for
events or for tasks that may be called from more than one place. Basically,
if you have a procedure you may call from more than one form or form multiple
places in a form, you should put that code in a Sub. If it is called from
more than one form or report, then it should be in a Standard module. If it
is only used by one form, then it can go in the General section of the form
module.

A Function is another type of procedure. It is used to perform some
evaluation or calculation and returns a value to the calling procedure. The
same rules as for Subs applys on how to use it .

A module is a collection of Subs and or Functions. Forms and reports can
have, but are not required to have modules. The code you put in your form
and report events are in the form or report module. A Standard module is a
collection of code that is usually called from forms, reports, or other
modules. For example, the code you downloaded for the API is a module. I
usually group procedures into modules that have something in common. I have
one module that is nothing more that date functions - Calculating the number
of working days between two dates, Finding the Friday of a given week, etc.

As to your code below. There are two things that need attention. First
there is a problem with this line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True

The next issue is declaring variables. Although not required, it is best to
establish the habit of always declaring and typing your variables. You can
always tell what data type my variables are by the prefix on the name. For
example var is for a Variant data type, str is for a String data type, lng is
Long Integer, etc. The reason you did not see the variable declarations the
code I sent is because they were declared at the module level because I need
them in more than this sub. Read up on varialbe Scoping.

So, add these lines right after the Sub statement:

Dim strFilter as String
Dim lngFlags as Long
Dim strDefaultDir as String
Dim varGetFileName as Variant

And, good luck!

frmSupportScheduleGLMainForm is a form name. What you want here is the name
of the table or query you are exporting.
"Elleve" wrote:

You're great in your feedback and speediness. I seem to be on the right
track now, I only have one more question regarding modules as I have never
used them before - how do you set up the module? Is it like private sub-end
sub?

By the way, here is the code I have for the event itself:

Private Sub cmdExportSupportSchedule_Click()
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY
'Get the File Name To Save
strDefaultDir = "c:/"
varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT,
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , "xls", varGetFileName,
"Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True
End Sub

Does this look like it would work?

"Klatuu" wrote:

Not stupid questions at all. See answers below in your post:

"Elleve" wrote:

Will I have to include the comboboxes in the coding? For your information,
No, what I sent was just an example. You can change it as necessary to use
the values you need for your situation. If they come from a list box, just
reference the column in the list box you need to use.

the listbox I mentioned last time consist of two columns that are contain a
relationship from a separate query. They are neccessary in order to pick up
the correct information. This is done by
=[Forms]![frmSupportScheduleMain]![List54].[column](0). Why can't I just
export what is at the current form to excel since this information is already
specified?
You can. This code does not do the export, it only returns a path and file
where you want to open or save the data. If you are exporting to Excel, you
would use varGetFileName as the File argument in your TransferSpreadsheet.


Also, as for the coding you provided me - where exactly do I input this?
Under the click event? Will I still need to keep the downloaded coding from
If you want this to happen as the result of a click event, then that would
be the place to put it.
You still need the downloaded code. It should be kept by itself in a
Standard module. Mine is named modCommonDialog. The sample I provided
calles the API routines in modCommonDialog.
the website as is or make changes to it. I apologize if I ask stupid
Do Not make changes to it. Calling APIs is very powerful, but also somewhat
dangerous. If you make changes in the downloaded code, you could get some
very weird unexpected results.

API stands for Application Program Interface. What you are doing is making
calls to DLL's in the Windows operating system. If you pass bad values, bad
things can happen. Don't let this discourage you. The alternative is an
ActiveX control. ActiveX controls are not that easy to deal with. This API
is very useful. My current application uses it about 20 different places.

questions, but that coding just got me lost.


"Klatuu" wrote:

First, don't put anything in the module you downloaded. It could cause
problems. Here is an example of how I use that exact same API module:

'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

strCurrMonth = Me.cboPeriod.Column(1)
strCurrYear = Me.txtCurrYear
'Get the File Name To Save
strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
varGetFileName = "Vought Invoice " & strCurrMonth & " " & strCurrYear &
".xls"

varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, _
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , _
"xls", varGetFileName, "Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If


"Elleve" wrote:

I have an access database (2000) where I want the user to click on a button
to save the current form (with subform) to excel. This event will open a
dialog box so the user will be able to save.

I have seen several links to http://www.mvps.org/access/api/api0001.htm when
creating dialog box to save. However, I cannot get this code to work. What
am I doing wrong?

I copied the code exactly like shown on the website and then started making
my changes to make it work. I did not understand how to put in my own
coding, if this should be a substitute for testit or my own "on click" or
function. Here is what I did by deleting the function testit():

Private Sub cmdTest_Click()
Dim strFilter As String
Dim strSaveFileName As String
strFilter = ahtAddFilterItem(mystrFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
End Sub

I keep getting the error message: "only comments may appear after end sub,
end function or end property."

  #16  
Old February 16th, 2006, 12:28 AM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Cannot get code to work for API Save Dialog Box

Instead of exporting the form, is it possible to do a printscreen of the form
and output it to word/excel/etc? I want the output to be exactly like on the
form, which contains total amount as well.

"Klatuu" wrote:

I assume that you are not really naming your listbox listbox. Don't use
Access reserved named to name your objects, but I can't say that is the
problem.

Is this line actually in your criteria?
[Affil]]=[Forms]![frmName]![listbox].column(1)
Seems it would throw a syntax error in the query builder.
I have not used a listbox as a query parameter, but I have used combo boxes
as parameters in queries that are tied to union queries, so that part should
be good.


"Elleve" wrote:

That seemed to be more difficult than I thought. The reason is that the
subform is based on a union query (of two other queries). I tried putting
parameters in both the underlying queries, but I got into trouble. I need
four parameters to get the information needed, two comboboxes and two from a
listbox. As for the comboboxes I put in the following that seems to work:
[Month]=[Forms]!frmName]![comboMonth]
[Year]=[Forms]!frmName]![comboYear]

The listbox creates the problem:
[Unit]=[Forms]![frmName]![listbox].column(0)
[Affil]]=[Forms]![frmName]![listbox].column(1)

The error message says "undefined function listbox..."

I tried putting this code into the expression builder when clicking the
button to open the form/subform itself, but that gave me the same error
message....

What do you suggest I do?

"Klatuu" wrote:

You will need to create a parameter query that will include only the data in
your sub form.

"Elleve" wrote:

I managed to get the open dialog box by this code:

Private Sub cmdExportSupportSchedule_Click()

Dim strFilter As String
Dim lngFlags As Long
Dim strDefaultDir As String
Dim strSaveFileName As String

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

'Get the File Name To Save
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If strSaveFileName = "" Then 'User Clicked CANCEL
Exit Sub
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrySubformUnionQuery1and2", strSaveFileName, True

End Sub

My problem now is that it is not exporting the correct information because
it is taking information from the query only. I want it to export only what
I have in the form w/subform currently. The main form is based on what is in
the listbox and the subform is based on qrySupportScheduleUnionqry1and2 that
is linked to the main form by four criterias of month, year, BU, and Affil.

How do I get only the information selected?


"Klatuu" wrote:

First thing is, Private Sub End Sub is not a module, it is a procedure.
There are two types of procedures. A Sub is called to execute code but does
not return any value to the calling procedure. You usually use a Sub for
events or for tasks that may be called from more than one place. Basically,
if you have a procedure you may call from more than one form or form multiple
places in a form, you should put that code in a Sub. If it is called from
more than one form or report, then it should be in a Standard module. If it
is only used by one form, then it can go in the General section of the form
module.

A Function is another type of procedure. It is used to perform some
evaluation or calculation and returns a value to the calling procedure. The
same rules as for Subs applys on how to use it .

A module is a collection of Subs and or Functions. Forms and reports can
have, but are not required to have modules. The code you put in your form
and report events are in the form or report module. A Standard module is a
collection of code that is usually called from forms, reports, or other
modules. For example, the code you downloaded for the API is a module. I
usually group procedures into modules that have something in common. I have
one module that is nothing more that date functions - Calculating the number
of working days between two dates, Finding the Friday of a given week, etc.

As to your code below. There are two things that need attention. First
there is a problem with this line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True

The next issue is declaring variables. Although not required, it is best to
establish the habit of always declaring and typing your variables. You can
always tell what data type my variables are by the prefix on the name. For
example var is for a Variant data type, str is for a String data type, lng is
Long Integer, etc. The reason you did not see the variable declarations the
code I sent is because they were declared at the module level because I need
them in more than this sub. Read up on varialbe Scoping.

So, add these lines right after the Sub statement:

Dim strFilter as String
Dim lngFlags as Long
Dim strDefaultDir as String
Dim varGetFileName as Variant

And, good luck!

frmSupportScheduleGLMainForm is a form name. What you want here is the name
of the table or query you are exporting.
"Elleve" wrote:

You're great in your feedback and speediness. I seem to be on the right
track now, I only have one more question regarding modules as I have never
used them before - how do you set up the module? Is it like private sub-end
sub?

By the way, here is the code I have for the event itself:

Private Sub cmdExportSupportSchedule_Click()
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY
'Get the File Name To Save
strDefaultDir = "c:/"
varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT,
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , "xls", varGetFileName,
"Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True
End Sub

Does this look like it would work?

"Klatuu" wrote:

Not stupid questions at all. See answers below in your post:

"Elleve" wrote:

Will I have to include the comboboxes in the coding? For your information,
No, what I sent was just an example. You can change it as necessary to use
the values you need for your situation. If they come from a list box, just
reference the column in the list box you need to use.

the listbox I mentioned last time consist of two columns that are contain a
relationship from a separate query. They are neccessary in order to pick up
the correct information. This is done by
=[Forms]![frmSupportScheduleMain]![List54].[column](0). Why can't I just
export what is at the current form to excel since this information is already
specified?
You can. This code does not do the export, it only returns a path and file
where you want to open or save the data. If you are exporting to Excel, you
would use varGetFileName as the File argument in your TransferSpreadsheet.


Also, as for the coding you provided me - where exactly do I input this?
Under the click event? Will I still need to keep the downloaded coding from
If you want this to happen as the result of a click event, then that would
be the place to put it.
You still need the downloaded code. It should be kept by itself in a
Standard module. Mine is named modCommonDialog. The sample I provided
calles the API routines in modCommonDialog.
the website as is or make changes to it. I apologize if I ask stupid
Do Not make changes to it. Calling APIs is very powerful, but also somewhat
dangerous. If you make changes in the downloaded code, you could get some
very weird unexpected results.

API stands for Application Program Interface. What you are doing is making
calls to DLL's in the Windows operating system. If you pass bad values, bad
things can happen. Don't let this discourage you. The alternative is an
ActiveX control. ActiveX controls are not that easy to deal with. This API
is very useful. My current application uses it about 20 different places.

questions, but that coding just got me lost.


"Klatuu" wrote:

First, don't put anything in the module you downloaded. It could cause
problems. Here is an example of how I use that exact same API module:

'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

strCurrMonth = Me.cboPeriod.Column(1)
strCurrYear = Me.txtCurrYear
'Get the File Name To Save
strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
varGetFileName = "Vought Invoice " & strCurrMonth & " " & strCurrYear &
".xls"

varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, _
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , _
"xls", varGetFileName, "Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If


"Elleve" wrote:

I have an access database (2000) where I want the user to click on a button
to save the current form (with subform) to excel. This event will open a
dialog box so the user will be able to save.

I have seen several links to http://www.mvps.org/access/api/api0001.htm when
creating dialog box to save. However, I cannot get this code to work. What
am I doing wrong?

I copied the code exactly like shown on the website and then started making
my changes to make it work. I did not understand how to put in my own
coding, if this should be a substitute for testit or my own "on click" or
function. Here is what I did by deleting the function testit():

Private Sub cmdTest_Click()
Dim strFilter As String
Dim strSaveFileName As String
strFilter = ahtAddFilterItem(mystrFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
End Sub

I keep getting the error message: "only comments may appear after end sub,
end function or end property."

  #17  
Old February 16th, 2006, 03:11 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Cannot get code to work for API Save Dialog Box

If using the list box in a query criteria is a problem, there is an easy way
to address that problem. You can use user defined functions in queries. So,
write a function that will return one of the columns based on a parameter you
pass it. Then use that function in your query. It would be something like
this:

Public Function GetListColumn(lngCol as Long) as String
GetListColumn = Forms!MyFormName!ListName.Column(lngCol)
End Function


"Elleve" wrote:

Instead of exporting the form, is it possible to do a printscreen of the form
and output it to word/excel/etc? I want the output to be exactly like on the
form, which contains total amount as well.

"Klatuu" wrote:

I assume that you are not really naming your listbox listbox. Don't use
Access reserved named to name your objects, but I can't say that is the
problem.

Is this line actually in your criteria?
[Affil]]=[Forms]![frmName]![listbox].column(1)
Seems it would throw a syntax error in the query builder.
I have not used a listbox as a query parameter, but I have used combo boxes
as parameters in queries that are tied to union queries, so that part should
be good.


"Elleve" wrote:

That seemed to be more difficult than I thought. The reason is that the
subform is based on a union query (of two other queries). I tried putting
parameters in both the underlying queries, but I got into trouble. I need
four parameters to get the information needed, two comboboxes and two from a
listbox. As for the comboboxes I put in the following that seems to work:
[Month]=[Forms]!frmName]![comboMonth]
[Year]=[Forms]!frmName]![comboYear]

The listbox creates the problem:
[Unit]=[Forms]![frmName]![listbox].column(0)
[Affil]]=[Forms]![frmName]![listbox].column(1)

The error message says "undefined function listbox..."

I tried putting this code into the expression builder when clicking the
button to open the form/subform itself, but that gave me the same error
message....

What do you suggest I do?

"Klatuu" wrote:

You will need to create a parameter query that will include only the data in
your sub form.

"Elleve" wrote:

I managed to get the open dialog box by this code:

Private Sub cmdExportSupportSchedule_Click()

Dim strFilter As String
Dim lngFlags As Long
Dim strDefaultDir As String
Dim strSaveFileName As String

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

'Get the File Name To Save
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If strSaveFileName = "" Then 'User Clicked CANCEL
Exit Sub
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrySubformUnionQuery1and2", strSaveFileName, True

End Sub

My problem now is that it is not exporting the correct information because
it is taking information from the query only. I want it to export only what
I have in the form w/subform currently. The main form is based on what is in
the listbox and the subform is based on qrySupportScheduleUnionqry1and2 that
is linked to the main form by four criterias of month, year, BU, and Affil.

How do I get only the information selected?


"Klatuu" wrote:

First thing is, Private Sub End Sub is not a module, it is a procedure.
There are two types of procedures. A Sub is called to execute code but does
not return any value to the calling procedure. You usually use a Sub for
events or for tasks that may be called from more than one place. Basically,
if you have a procedure you may call from more than one form or form multiple
places in a form, you should put that code in a Sub. If it is called from
more than one form or report, then it should be in a Standard module. If it
is only used by one form, then it can go in the General section of the form
module.

A Function is another type of procedure. It is used to perform some
evaluation or calculation and returns a value to the calling procedure. The
same rules as for Subs applys on how to use it .

A module is a collection of Subs and or Functions. Forms and reports can
have, but are not required to have modules. The code you put in your form
and report events are in the form or report module. A Standard module is a
collection of code that is usually called from forms, reports, or other
modules. For example, the code you downloaded for the API is a module. I
usually group procedures into modules that have something in common. I have
one module that is nothing more that date functions - Calculating the number
of working days between two dates, Finding the Friday of a given week, etc.

As to your code below. There are two things that need attention. First
there is a problem with this line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True

The next issue is declaring variables. Although not required, it is best to
establish the habit of always declaring and typing your variables. You can
always tell what data type my variables are by the prefix on the name. For
example var is for a Variant data type, str is for a String data type, lng is
Long Integer, etc. The reason you did not see the variable declarations the
code I sent is because they were declared at the module level because I need
them in more than this sub. Read up on varialbe Scoping.

So, add these lines right after the Sub statement:

Dim strFilter as String
Dim lngFlags as Long
Dim strDefaultDir as String
Dim varGetFileName as Variant

And, good luck!

frmSupportScheduleGLMainForm is a form name. What you want here is the name
of the table or query you are exporting.
"Elleve" wrote:

You're great in your feedback and speediness. I seem to be on the right
track now, I only have one more question regarding modules as I have never
used them before - how do you set up the module? Is it like private sub-end
sub?

By the way, here is the code I have for the event itself:

Private Sub cmdExportSupportSchedule_Click()
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY
'Get the File Name To Save
strDefaultDir = "c:/"
varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT,
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , "xls", varGetFileName,
"Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True
End Sub

Does this look like it would work?

"Klatuu" wrote:

Not stupid questions at all. See answers below in your post:

"Elleve" wrote:

Will I have to include the comboboxes in the coding? For your information,
No, what I sent was just an example. You can change it as necessary to use
the values you need for your situation. If they come from a list box, just
reference the column in the list box you need to use.

the listbox I mentioned last time consist of two columns that are contain a
relationship from a separate query. They are neccessary in order to pick up
the correct information. This is done by
=[Forms]![frmSupportScheduleMain]![List54].[column](0). Why can't I just
export what is at the current form to excel since this information is already
specified?
You can. This code does not do the export, it only returns a path and file
where you want to open or save the data. If you are exporting to Excel, you
would use varGetFileName as the File argument in your TransferSpreadsheet.


Also, as for the coding you provided me - where exactly do I input this?
Under the click event? Will I still need to keep the downloaded coding from
If you want this to happen as the result of a click event, then that would
be the place to put it.
You still need the downloaded code. It should be kept by itself in a
Standard module. Mine is named modCommonDialog. The sample I provided
calles the API routines in modCommonDialog.
the website as is or make changes to it. I apologize if I ask stupid
Do Not make changes to it. Calling APIs is very powerful, but also somewhat
dangerous. If you make changes in the downloaded code, you could get some
very weird unexpected results.

API stands for Application Program Interface. What you are doing is making
calls to DLL's in the Windows operating system. If you pass bad values, bad
things can happen. Don't let this discourage you. The alternative is an
ActiveX control. ActiveX controls are not that easy to deal with. This API
is very useful. My current application uses it about 20 different places.

questions, but that coding just got me lost.


"Klatuu" wrote:

First, don't put anything in the module you downloaded. It could cause
problems. Here is an example of how I use that exact same API module:

'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

strCurrMonth = Me.cboPeriod.Column(1)
strCurrYear = Me.txtCurrYear
'Get the File Name To Save
strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
varGetFileName = "Vought Invoice " & strCurrMonth & " " & strCurrYear &
".xls"

varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, _
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , _
"xls", varGetFileName, "Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If


"Elleve" wrote:

I have an access database (2000) where I want the user to click on a button
to save the current form (with subform) to excel. This event will open a
dialog box so the user will be able to save.

I have seen several links to http://www.mvps.org/access/api/api0001.htm when
creating dialog box to save. However, I cannot get this code to work. What
am I doing wrong?

I copied the code exactly like shown on the website and then started making
my changes to make it work. I did not understand how to put in my own
coding, if this should be a substitute for testit or my own "on click" or
function. Here is what I did by deleting the function testit():

Private Sub cmdTest_Click()
Dim strFilter As String
Dim strSaveFileName As String
strFilter = ahtAddFilterItem(mystrFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
End Sub

I keep getting the error message: "only comments may appear after end sub,
end function or end property."

  #18  
Old February 16th, 2006, 04:52 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Cannot get code to work for API Save Dialog Box

Also, how do I get the output to be formatted correctly?

"Klatuu" wrote:

If using the list box in a query criteria is a problem, there is an easy way
to address that problem. You can use user defined functions in queries. So,
write a function that will return one of the columns based on a parameter you
pass it. Then use that function in your query. It would be something like
this:

Public Function GetListColumn(lngCol as Long) as String
GetListColumn = Forms!MyFormName!ListName.Column(lngCol)
End Function


"Elleve" wrote:

Instead of exporting the form, is it possible to do a printscreen of the form
and output it to word/excel/etc? I want the output to be exactly like on the
form, which contains total amount as well.

"Klatuu" wrote:

I assume that you are not really naming your listbox listbox. Don't use
Access reserved named to name your objects, but I can't say that is the
problem.

Is this line actually in your criteria?
[Affil]]=[Forms]![frmName]![listbox].column(1)
Seems it would throw a syntax error in the query builder.
I have not used a listbox as a query parameter, but I have used combo boxes
as parameters in queries that are tied to union queries, so that part should
be good.


"Elleve" wrote:

That seemed to be more difficult than I thought. The reason is that the
subform is based on a union query (of two other queries). I tried putting
parameters in both the underlying queries, but I got into trouble. I need
four parameters to get the information needed, two comboboxes and two from a
listbox. As for the comboboxes I put in the following that seems to work:
[Month]=[Forms]!frmName]![comboMonth]
[Year]=[Forms]!frmName]![comboYear]

The listbox creates the problem:
[Unit]=[Forms]![frmName]![listbox].column(0)
[Affil]]=[Forms]![frmName]![listbox].column(1)

The error message says "undefined function listbox..."

I tried putting this code into the expression builder when clicking the
button to open the form/subform itself, but that gave me the same error
message....

What do you suggest I do?

"Klatuu" wrote:

You will need to create a parameter query that will include only the data in
your sub form.

"Elleve" wrote:

I managed to get the open dialog box by this code:

Private Sub cmdExportSupportSchedule_Click()

Dim strFilter As String
Dim lngFlags As Long
Dim strDefaultDir As String
Dim strSaveFileName As String

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

'Get the File Name To Save
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If strSaveFileName = "" Then 'User Clicked CANCEL
Exit Sub
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrySubformUnionQuery1and2", strSaveFileName, True

End Sub

My problem now is that it is not exporting the correct information because
it is taking information from the query only. I want it to export only what
I have in the form w/subform currently. The main form is based on what is in
the listbox and the subform is based on qrySupportScheduleUnionqry1and2 that
is linked to the main form by four criterias of month, year, BU, and Affil.

How do I get only the information selected?


"Klatuu" wrote:

First thing is, Private Sub End Sub is not a module, it is a procedure.
There are two types of procedures. A Sub is called to execute code but does
not return any value to the calling procedure. You usually use a Sub for
events or for tasks that may be called from more than one place. Basically,
if you have a procedure you may call from more than one form or form multiple
places in a form, you should put that code in a Sub. If it is called from
more than one form or report, then it should be in a Standard module. If it
is only used by one form, then it can go in the General section of the form
module.

A Function is another type of procedure. It is used to perform some
evaluation or calculation and returns a value to the calling procedure. The
same rules as for Subs applys on how to use it .

A module is a collection of Subs and or Functions. Forms and reports can
have, but are not required to have modules. The code you put in your form
and report events are in the form or report module. A Standard module is a
collection of code that is usually called from forms, reports, or other
modules. For example, the code you downloaded for the API is a module. I
usually group procedures into modules that have something in common. I have
one module that is nothing more that date functions - Calculating the number
of working days between two dates, Finding the Friday of a given week, etc.

As to your code below. There are two things that need attention. First
there is a problem with this line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True

The next issue is declaring variables. Although not required, it is best to
establish the habit of always declaring and typing your variables. You can
always tell what data type my variables are by the prefix on the name. For
example var is for a Variant data type, str is for a String data type, lng is
Long Integer, etc. The reason you did not see the variable declarations the
code I sent is because they were declared at the module level because I need
them in more than this sub. Read up on varialbe Scoping.

So, add these lines right after the Sub statement:

Dim strFilter as String
Dim lngFlags as Long
Dim strDefaultDir as String
Dim varGetFileName as Variant

And, good luck!

frmSupportScheduleGLMainForm is a form name. What you want here is the name
of the table or query you are exporting.
"Elleve" wrote:

You're great in your feedback and speediness. I seem to be on the right
track now, I only have one more question regarding modules as I have never
used them before - how do you set up the module? Is it like private sub-end
sub?

By the way, here is the code I have for the event itself:

Private Sub cmdExportSupportSchedule_Click()
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY
'Get the File Name To Save
strDefaultDir = "c:/"
varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT,
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , "xls", varGetFileName,
"Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True
End Sub

Does this look like it would work?

"Klatuu" wrote:

Not stupid questions at all. See answers below in your post:

"Elleve" wrote:

Will I have to include the comboboxes in the coding? For your information,
No, what I sent was just an example. You can change it as necessary to use
the values you need for your situation. If they come from a list box, just
reference the column in the list box you need to use.

the listbox I mentioned last time consist of two columns that are contain a
relationship from a separate query. They are neccessary in order to pick up
the correct information. This is done by
=[Forms]![frmSupportScheduleMain]![List54].[column](0). Why can't I just
export what is at the current form to excel since this information is already
specified?
You can. This code does not do the export, it only returns a path and file
where you want to open or save the data. If you are exporting to Excel, you
would use varGetFileName as the File argument in your TransferSpreadsheet.


Also, as for the coding you provided me - where exactly do I input this?
Under the click event? Will I still need to keep the downloaded coding from
If you want this to happen as the result of a click event, then that would
be the place to put it.
You still need the downloaded code. It should be kept by itself in a
Standard module. Mine is named modCommonDialog. The sample I provided
calles the API routines in modCommonDialog.
the website as is or make changes to it. I apologize if I ask stupid
Do Not make changes to it. Calling APIs is very powerful, but also somewhat
dangerous. If you make changes in the downloaded code, you could get some
very weird unexpected results.

API stands for Application Program Interface. What you are doing is making
calls to DLL's in the Windows operating system. If you pass bad values, bad
things can happen. Don't let this discourage you. The alternative is an
ActiveX control. ActiveX controls are not that easy to deal with. This API
is very useful. My current application uses it about 20 different places.

questions, but that coding just got me lost.


"Klatuu" wrote:

First, don't put anything in the module you downloaded. It could cause
problems. Here is an example of how I use that exact same API module:

'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

strCurrMonth = Me.cboPeriod.Column(1)
strCurrYear = Me.txtCurrYear
'Get the File Name To Save
strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
varGetFileName = "Vought Invoice " & strCurrMonth & " " & strCurrYear &
".xls"

varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, _
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , _
"xls", varGetFileName, "Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If


"Elleve" wrote:

I have an access database (2000) where I want the user to click on a button
to save the current form (with subform) to excel. This event will open a
dialog box so the user will be able to save.

I have seen several links to http://www.mvps.org/access/api/api0001.htm when
creating dialog box to save. However, I cannot get this code to work. What
am I doing wrong?

I copied the code exactly like shown on the website and then started making
my changes to make it work. I did not understand how to put in my own
coding, if this should be a substitute for testit or my own "on click" or
function. Here is what I did by deleting the function testit():

Private Sub cmdTest_Click()
Dim strFilter As String
Dim strSaveFileName As String
strFilter = ahtAddFilterItem(mystrFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
End Sub

I keep getting the error message: "only comments may appear after end sub,
end function or end property."

  #19  
Old February 16th, 2006, 04:52 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Cannot get code to work for API Save Dialog Box

That worked perfectly! I exported the necessary information. Is there a way
I can include the total amount in a line under the records without having the
user do this? I have four columns that should each be totalled.


"Klatuu" wrote:

If using the list box in a query criteria is a problem, there is an easy way
to address that problem. You can use user defined functions in queries. So,
write a function that will return one of the columns based on a parameter you
pass it. Then use that function in your query. It would be something like
this:

Public Function GetListColumn(lngCol as Long) as String
GetListColumn = Forms!MyFormName!ListName.Column(lngCol)
End Function


"Elleve" wrote:

Instead of exporting the form, is it possible to do a printscreen of the form
and output it to word/excel/etc? I want the output to be exactly like on the
form, which contains total amount as well.

"Klatuu" wrote:

I assume that you are not really naming your listbox listbox. Don't use
Access reserved named to name your objects, but I can't say that is the
problem.

Is this line actually in your criteria?
[Affil]]=[Forms]![frmName]![listbox].column(1)
Seems it would throw a syntax error in the query builder.
I have not used a listbox as a query parameter, but I have used combo boxes
as parameters in queries that are tied to union queries, so that part should
be good.


"Elleve" wrote:

That seemed to be more difficult than I thought. The reason is that the
subform is based on a union query (of two other queries). I tried putting
parameters in both the underlying queries, but I got into trouble. I need
four parameters to get the information needed, two comboboxes and two from a
listbox. As for the comboboxes I put in the following that seems to work:
[Month]=[Forms]!frmName]![comboMonth]
[Year]=[Forms]!frmName]![comboYear]

The listbox creates the problem:
[Unit]=[Forms]![frmName]![listbox].column(0)
[Affil]]=[Forms]![frmName]![listbox].column(1)

The error message says "undefined function listbox..."

I tried putting this code into the expression builder when clicking the
button to open the form/subform itself, but that gave me the same error
message....

What do you suggest I do?

"Klatuu" wrote:

You will need to create a parameter query that will include only the data in
your sub form.

"Elleve" wrote:

I managed to get the open dialog box by this code:

Private Sub cmdExportSupportSchedule_Click()

Dim strFilter As String
Dim lngFlags As Long
Dim strDefaultDir As String
Dim strSaveFileName As String

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

'Get the File Name To Save
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If strSaveFileName = "" Then 'User Clicked CANCEL
Exit Sub
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrySubformUnionQuery1and2", strSaveFileName, True

End Sub

My problem now is that it is not exporting the correct information because
it is taking information from the query only. I want it to export only what
I have in the form w/subform currently. The main form is based on what is in
the listbox and the subform is based on qrySupportScheduleUnionqry1and2 that
is linked to the main form by four criterias of month, year, BU, and Affil.

How do I get only the information selected?


"Klatuu" wrote:

First thing is, Private Sub End Sub is not a module, it is a procedure.
There are two types of procedures. A Sub is called to execute code but does
not return any value to the calling procedure. You usually use a Sub for
events or for tasks that may be called from more than one place. Basically,
if you have a procedure you may call from more than one form or form multiple
places in a form, you should put that code in a Sub. If it is called from
more than one form or report, then it should be in a Standard module. If it
is only used by one form, then it can go in the General section of the form
module.

A Function is another type of procedure. It is used to perform some
evaluation or calculation and returns a value to the calling procedure. The
same rules as for Subs applys on how to use it .

A module is a collection of Subs and or Functions. Forms and reports can
have, but are not required to have modules. The code you put in your form
and report events are in the form or report module. A Standard module is a
collection of code that is usually called from forms, reports, or other
modules. For example, the code you downloaded for the API is a module. I
usually group procedures into modules that have something in common. I have
one module that is nothing more that date functions - Calculating the number
of working days between two dates, Finding the Friday of a given week, etc.

As to your code below. There are two things that need attention. First
there is a problem with this line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True

The next issue is declaring variables. Although not required, it is best to
establish the habit of always declaring and typing your variables. You can
always tell what data type my variables are by the prefix on the name. For
example var is for a Variant data type, str is for a String data type, lng is
Long Integer, etc. The reason you did not see the variable declarations the
code I sent is because they were declared at the module level because I need
them in more than this sub. Read up on varialbe Scoping.

So, add these lines right after the Sub statement:

Dim strFilter as String
Dim lngFlags as Long
Dim strDefaultDir as String
Dim varGetFileName as Variant

And, good luck!

frmSupportScheduleGLMainForm is a form name. What you want here is the name
of the table or query you are exporting.
"Elleve" wrote:

You're great in your feedback and speediness. I seem to be on the right
track now, I only have one more question regarding modules as I have never
used them before - how do you set up the module? Is it like private sub-end
sub?

By the way, here is the code I have for the event itself:

Private Sub cmdExportSupportSchedule_Click()
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY
'Get the File Name To Save
strDefaultDir = "c:/"
varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT,
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , "xls", varGetFileName,
"Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True
End Sub

Does this look like it would work?

"Klatuu" wrote:

Not stupid questions at all. See answers below in your post:

"Elleve" wrote:

Will I have to include the comboboxes in the coding? For your information,
No, what I sent was just an example. You can change it as necessary to use
the values you need for your situation. If they come from a list box, just
reference the column in the list box you need to use.

the listbox I mentioned last time consist of two columns that are contain a
relationship from a separate query. They are neccessary in order to pick up
the correct information. This is done by
=[Forms]![frmSupportScheduleMain]![List54].[column](0). Why can't I just
export what is at the current form to excel since this information is already
specified?
You can. This code does not do the export, it only returns a path and file
where you want to open or save the data. If you are exporting to Excel, you
would use varGetFileName as the File argument in your TransferSpreadsheet.


Also, as for the coding you provided me - where exactly do I input this?
Under the click event? Will I still need to keep the downloaded coding from
If you want this to happen as the result of a click event, then that would
be the place to put it.
You still need the downloaded code. It should be kept by itself in a
Standard module. Mine is named modCommonDialog. The sample I provided
calles the API routines in modCommonDialog.
the website as is or make changes to it. I apologize if I ask stupid
Do Not make changes to it. Calling APIs is very powerful, but also somewhat
dangerous. If you make changes in the downloaded code, you could get some
very weird unexpected results.

API stands for Application Program Interface. What you are doing is making
calls to DLL's in the Windows operating system. If you pass bad values, bad
things can happen. Don't let this discourage you. The alternative is an
ActiveX control. ActiveX controls are not that easy to deal with. This API
is very useful. My current application uses it about 20 different places.

questions, but that coding just got me lost.


"Klatuu" wrote:

First, don't put anything in the module you downloaded. It could cause
problems. Here is an example of how I use that exact same API module:

'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

strCurrMonth = Me.cboPeriod.Column(1)
strCurrYear = Me.txtCurrYear
'Get the File Name To Save
strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
varGetFileName = "Vought Invoice " & strCurrMonth & " " & strCurrYear &
".xls"

varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, _
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , _
"xls", varGetFileName, "Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If


"Elleve" wrote:

I have an access database (2000) where I want the user to click on a button
to save the current form (with subform) to excel. This event will open a
dialog box so the user will be able to save.

I have seen several links to http://www.mvps.org/access/api/api0001.htm when
creating dialog box to save. However, I cannot get this code to work. What
am I doing wrong?

I copied the code exactly like shown on the website and then started making
my changes to make it work. I did not understand how to put in my own
coding, if this should be a substitute for testit or my own "on click" or
function. Here is what I did by deleting the function testit():

Private Sub cmdTest_Click()
Dim strFilter As String
Dim strSaveFileName As String
strFilter = ahtAddFilterItem(mystrFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
End Sub

I keep getting the error message: "only comments may appear after end sub,
end function or end property."

  #20  
Old February 16th, 2006, 05:17 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Cannot get code to work for API Save Dialog Box

Another thing just came up... The fourth column in the subform is basically
the same as the third column, the only difference is that I put in some extra
criteria if else that is calculated in the query. However I want the user to
be allowed to change this amount. For each line in the subform the user can
edit the amount based on his/her judgment. I set the default value to the
fourth column, but this made each row in the subform change to the same
amount entered. I want to save this individual amount instead of what was
there before (replace) and calculate the new total for that specific
form/subform. Later I will use each total from about 18 records in a form
that calculates some of these totals. Is this possible to do?

"Klatuu" wrote:

If using the list box in a query criteria is a problem, there is an easy way
to address that problem. You can use user defined functions in queries. So,
write a function that will return one of the columns based on a parameter you
pass it. Then use that function in your query. It would be something like
this:

Public Function GetListColumn(lngCol as Long) as String
GetListColumn = Forms!MyFormName!ListName.Column(lngCol)
End Function


"Elleve" wrote:

Instead of exporting the form, is it possible to do a printscreen of the form
and output it to word/excel/etc? I want the output to be exactly like on the
form, which contains total amount as well.

"Klatuu" wrote:

I assume that you are not really naming your listbox listbox. Don't use
Access reserved named to name your objects, but I can't say that is the
problem.

Is this line actually in your criteria?
[Affil]]=[Forms]![frmName]![listbox].column(1)
Seems it would throw a syntax error in the query builder.
I have not used a listbox as a query parameter, but I have used combo boxes
as parameters in queries that are tied to union queries, so that part should
be good.


"Elleve" wrote:

That seemed to be more difficult than I thought. The reason is that the
subform is based on a union query (of two other queries). I tried putting
parameters in both the underlying queries, but I got into trouble. I need
four parameters to get the information needed, two comboboxes and two from a
listbox. As for the comboboxes I put in the following that seems to work:
[Month]=[Forms]!frmName]![comboMonth]
[Year]=[Forms]!frmName]![comboYear]

The listbox creates the problem:
[Unit]=[Forms]![frmName]![listbox].column(0)
[Affil]]=[Forms]![frmName]![listbox].column(1)

The error message says "undefined function listbox..."

I tried putting this code into the expression builder when clicking the
button to open the form/subform itself, but that gave me the same error
message....

What do you suggest I do?

"Klatuu" wrote:

You will need to create a parameter query that will include only the data in
your sub form.

"Elleve" wrote:

I managed to get the open dialog box by this code:

Private Sub cmdExportSupportSchedule_Click()

Dim strFilter As String
Dim lngFlags As Long
Dim strDefaultDir As String
Dim strSaveFileName As String

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

'Get the File Name To Save
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If strSaveFileName = "" Then 'User Clicked CANCEL
Exit Sub
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrySubformUnionQuery1and2", strSaveFileName, True

End Sub

My problem now is that it is not exporting the correct information because
it is taking information from the query only. I want it to export only what
I have in the form w/subform currently. The main form is based on what is in
the listbox and the subform is based on qrySupportScheduleUnionqry1and2 that
is linked to the main form by four criterias of month, year, BU, and Affil.

How do I get only the information selected?


"Klatuu" wrote:

First thing is, Private Sub End Sub is not a module, it is a procedure.
There are two types of procedures. A Sub is called to execute code but does
not return any value to the calling procedure. You usually use a Sub for
events or for tasks that may be called from more than one place. Basically,
if you have a procedure you may call from more than one form or form multiple
places in a form, you should put that code in a Sub. If it is called from
more than one form or report, then it should be in a Standard module. If it
is only used by one form, then it can go in the General section of the form
module.

A Function is another type of procedure. It is used to perform some
evaluation or calculation and returns a value to the calling procedure. The
same rules as for Subs applys on how to use it .

A module is a collection of Subs and or Functions. Forms and reports can
have, but are not required to have modules. The code you put in your form
and report events are in the form or report module. A Standard module is a
collection of code that is usually called from forms, reports, or other
modules. For example, the code you downloaded for the API is a module. I
usually group procedures into modules that have something in common. I have
one module that is nothing more that date functions - Calculating the number
of working days between two dates, Finding the Friday of a given week, etc.

As to your code below. There are two things that need attention. First
there is a problem with this line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True

The next issue is declaring variables. Although not required, it is best to
establish the habit of always declaring and typing your variables. You can
always tell what data type my variables are by the prefix on the name. For
example var is for a Variant data type, str is for a String data type, lng is
Long Integer, etc. The reason you did not see the variable declarations the
code I sent is because they were declared at the module level because I need
them in more than this sub. Read up on varialbe Scoping.

So, add these lines right after the Sub statement:

Dim strFilter as String
Dim lngFlags as Long
Dim strDefaultDir as String
Dim varGetFileName as Variant

And, good luck!

frmSupportScheduleGLMainForm is a form name. What you want here is the name
of the table or query you are exporting.
"Elleve" wrote:

You're great in your feedback and speediness. I seem to be on the right
track now, I only have one more question regarding modules as I have never
used them before - how do you set up the module? Is it like private sub-end
sub?

By the way, here is the code I have for the event itself:

Private Sub cmdExportSupportSchedule_Click()
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY
'Get the File Name To Save
strDefaultDir = "c:/"
varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT,
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , "xls", varGetFileName,
"Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"frmSupportScheduleGLMainForm", varGetFileName, True
End Sub

Does this look like it would work?

"Klatuu" wrote:

Not stupid questions at all. See answers below in your post:

"Elleve" wrote:

Will I have to include the comboboxes in the coding? For your information,
No, what I sent was just an example. You can change it as necessary to use
the values you need for your situation. If they come from a list box, just
reference the column in the list box you need to use.

the listbox I mentioned last time consist of two columns that are contain a
relationship from a separate query. They are neccessary in order to pick up
the correct information. This is done by
=[Forms]![frmSupportScheduleMain]![List54].[column](0). Why can't I just
export what is at the current form to excel since this information is already
specified?
You can. This code does not do the export, it only returns a path and file
where you want to open or save the data. If you are exporting to Excel, you
would use varGetFileName as the File argument in your TransferSpreadsheet.


Also, as for the coding you provided me - where exactly do I input this?
Under the click event? Will I still need to keep the downloaded coding from
If you want this to happen as the result of a click event, then that would
be the place to put it.
You still need the downloaded code. It should be kept by itself in a
Standard module. Mine is named modCommonDialog. The sample I provided
calles the API routines in modCommonDialog.
the website as is or make changes to it. I apologize if I ask stupid
Do Not make changes to it. Calling APIs is very powerful, but also somewhat
dangerous. If you make changes in the downloaded code, you could get some
very weird unexpected results.

API stands for Application Program Interface. What you are doing is making
calls to DLL's in the Windows operating system. If you pass bad values, bad
things can happen. Don't let this discourage you. The alternative is an
ActiveX control. ActiveX controls are not that easy to deal with. This API
is very useful. My current application uses it about 20 different places.

questions, but that coding just got me lost.


"Klatuu" wrote:

First, don't put anything in the module you downloaded. It could cause
problems. Here is an example of how I use that exact same API module:

'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

strCurrMonth = Me.cboPeriod.Column(1)
strCurrYear = Me.txtCurrYear
'Get the File Name To Save
strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
varGetFileName = "Vought Invoice " & strCurrMonth & " " & strCurrYear &
".xls"

varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, _
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , _
"xls", varGetFileName, "Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If


"Elleve" wrote:

I have an access database (2000) where I want the user to click on a button
to save the current form (with subform) to excel. This event will open a
dialog box so the user will be able to save.

I have seen several links to http://www.mvps.org/access/api/api0001.htm when
creating dialog box to save. However, I cannot get this code to work. What
am I doing wrong?

I copied the code exactly like shown on the website and then started making
my changes to make it work. I did not understand how to put in my own
coding, if this should be a substitute for testit or my own "on click" or
function. Here is what I did by deleting the function testit():

Private Sub cmdTest_Click()
Dim strFilter As String
Dim strSaveFileName As String
strFilter = ahtAddFilterItem(mystrFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
End Sub

I keep getting the error message: "only comments may appear after end sub,
end function or end property."

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Visio Shortcuts [email protected] Visio 1 December 29th, 2006 12:28 AM
Save work automatically Ramon Niese General Discussions 2 November 7th, 2005 05:59 PM
Make Change Case in Excel a format rather than formula Kevin Worksheet Functions 1 March 18th, 2005 09:53 PM
Open File and Save As don't work David Evans Powerpoint 8 June 4th, 2004 04:25 PM
Two versions again-language issue Otto Setup, Installing & Configuration 3 May 28th, 2004 04:57 AM


All times are GMT +1. The time now is 09:55 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.