two questions on exporting from a form

[pat67]

First one: Is there a way to pick more than one criteria when
filtering a form? My form right now contains combo boxes for
parameters and operators and text boxes for criteria. Example, i can
select PO from the first box, = from the second box, and then say type
in 12345 in the criteria box. This will filter for PO 12345 only. My
question is there a way to select multiple POs?


I am trying to then export the filtered results to Excel. I can't use
the query behind the form as it will export all of the data. I am
currently exporting a report to Excel, it works but the formatting
isn't what I am looking for. What i need is to be able to create a
query on the fly and then export that one. That is where i am stuck.
the code I have for exporting the report is this

Private Sub Send_to_Excel_Click()
'Debug.Print globalstrWhere

DoCmd.OpenReport "rptSE16", acViewPreview

With Reports!rptSE16
.Filter = globalStrWhere
.FilterOn = True
End With

DoCmd.OutputTo acOutputReport, "rptSE16", "*.xls", , -1, , ,
acExportQualityPrint

DoCmd.Close acReport, "rptSE16"

End Sub


Any ideas would be helpful.

Thanks

 

[John W. Vinson]

First... I'd suggest rephrasing the nature of the problem. Data is not stored
in Forms, it's stored in Tables; any exporting should be done using a Query
based on the Table containing the data, not from a Form.

First one: Is there a way to pick more than one criteria when
filtering a form? My form right now contains combo boxes for
parameters and operators and text boxes for criteria. Example, i can
select PO from the first box, = from the second box, and then say type
in 12345 in the criteria box. This will filter for PO 12345 only. My
question is there a way to select multiple POs?

Only (easily) by constructing the entire SQL view of the query. You can use
SQL like

[PO] IN (12345, 12493, 12652)

to select multiple values. But to do so you'll need to actually construct the
SQL including the IN() expression. I'm not sure what code you're using now so
I don't know how much you'll need to change it.

I am trying to then export the filtered results to Excel. I can't use
the query behind the form as it will export all of the data.

So use a better query, constructed on demand, rather than trying to filter the
form. The form is just an unnecessary extra middleman.

I am
currently exporting a report to Excel, it works but the formatting
isn't what I am looking for. What i need is to be able to create a
query on the fly and then export that one. That is where i am stuck.
the code I have for exporting the report is this

Private Sub Send_to_Excel_Click()
'Debug.Print globalstrWhere

DoCmd.OpenReport "rptSE16", acViewPreview

With Reports!rptSE16
.Filter = globalStrWhere
.FilterOn = True
End With

DoCmd.OutputTo acOutputReport, "rptSE16", "*.xls", , -1, , ,
acExportQualityPrint

DoCmd.Close acReport, "rptSE16"

End Sub

Look at the VBA help for TransferSpreadsheet to see how to export from a
Query.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[pat67]

First... I'd suggest rephrasing the nature of the problem. Data is not stored
in Forms, it's stored in Tables; any exporting should be done using a Query
based on the Table containing the data, not from a Form.

First one: Is there a way to pick more than one criteria when
filtering a form? My form right now contains combo boxes for
parameters and operators and text boxes for criteria. Example, i can
select PO from the first box, = from the second box, and then say type
in 12345 in the criteria box. This will filter for PO 12345 only. My
question is there a way to select multiple POs?

Only (easily) by constructing the entire SQL view of the query. You can use
SQL like

[PO] IN (12345, 12493, 12652)

to select multiple values. But to do so you'll need to actually constructthe
SQL including the IN() expression. I'm not sure what code you're using now so
I don't know how much you'll need to change it.

I am trying to then export the filtered results to Excel. I can't use
the query behind the form as it will export all of the data.

So use a better query, constructed on demand, rather than trying to filter the
form. The form is just an unnecessary extra middleman.

I am
currently exporting a report to Excel, it works but the formatting
isn't what I am looking for. What i need is to be able to create a
query on the fly and then export that one. That is where i am stuck.
the code I have for exporting the report is this

Private Sub Send_to_Excel_Click()
'Debug.Print globalstrWhere

DoCmd.OpenReport "rptSE16", acViewPreview

With Reports!rptSE16
  .Filter = globalStrWhere
  .FilterOn = True
End With

DoCmd.OutputTo acOutputReport, "rptSE16", "*.xls", , -1, , ,
acExportQualityPrint

DoCmd.Close acReport, "rptSE16"

End Sub

Look at the VBA help for TransferSpreadsheet to see how to export from a
Query.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

the issue is the db is used by many people and i only want them to be
able to view the forms and filter for what they need. I don't want
anyone having access to the queries to be able to change any. maybe i
didn't say it clearly. i know the form is based on a qry or tbl. what
i need to do is create a qry from the filter with a new name and be
able to export that query.

I know how to filter and export from queries.

the way th db is set up there is this. users open and see the
switchboard and nothing else. they can then choose either "Standard
Reports", which is a few queries i created that they can download to
whatever they like, excel, word adobe. The second button is "Ad Hoc
Reports" which is a group of forms in which they can filter for
something other than what is on standard. In each of these forms they
can filter from several different fields and the export a report to
adobe, word or excel. What i am trying to do is export the filtered
query behind the form to excel instead of a report to excel

 

[John W. Vinson]

the issue is the db is used by many people and i only want them to be
able to view the forms and filter for what they need. I don't want
anyone having access to the queries to be able to change any. maybe i
didn't say it clearly. i know the form is based on a qry or tbl. what
i need to do is create a qry from the filter with a new name and be
able to export that query.

I know how to filter and export from queries.

the way th db is set up there is this. users open and see the
switchboard and nothing else. they can then choose either "Standard
Reports", which is a few queries i created that they can download to
whatever they like, excel, word adobe. The second button is "Ad Hoc
Reports" which is a group of forms in which they can filter for
something other than what is on standard. In each of these forms they
can filter from several different fields and the export a report to
adobe, word or excel. What i am trying to do is export the filtered
query behind the form to excel instead of a report to excel

Well, if you can build a Filter string for a form, you can use that string as
the Where clause of a query, and use that query for the export. That's
certainly how I'd do it.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com