code to run query from a form

[Guest]

Hello everyone...

i asked this question before but i can't solve my problem yet...

i have the following data in a query

Id Date Name Quantity Price Total Discount NTotal
1 10-Sep-04 ABC 5 10 =10x5 10%
=Total*(1-Discount)
2 15-Sep-04 XYZ 10 15 =10x15 15%
=Total*(1-Discount)


I have a form to fill the data to this query. What i need is a code that
will be assigned to a button, so when the user presses that button the
program should ask start date and end date, then select the records that
match those dates, and then export those records to excel (only the fields
ID, Date, Name and NTotal)

I know it's complicated, but i have been consulting several books, and i
haven't beenable to solve this for 3 weeks.... HEEEELP!!!

Thaks...

 

[Shaun Beane]

As always, there are a couple ways of doing this. To accomplish most of
this without code ...

The first step is to setup the query to use the form's text boxes (i am
assuming you created text boxes for the date prompts) as the criteria for
the start date and end date fields. This is relatively easy, open the query
in design view, then add the syntax
[forms]![yourformnamehere]![nameofyourstartdatetextbox] to the criteria for
start date and end date. This tells the query to use whatever has been
typed into the text boxes on the form as criteria. One downfall of doing it
this way is that the form will need to be open any time you run that query.

The next step would be to create a command button on the form to export the
query to excel. Use the docmd.transferspreadsheet:

docmd.TransferSpreadsheet acExport,,"yourqueryname","c:\file.xls",true

Hope this gets you started ... I've made assumptions that you are fairly
comfortable with Access.

 

[Allen Browne]

1. Add a text box to your form, and set these properties:
Name StartDate
Format Short Date
and another text box set to:
Name EndDate
Format Short Date

2. In your query, in the Criteria row under the Date column, enter:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
replacing "Form1" with the name of your form.

3. Add a command button to your form to perform the export. In its Click
event, create the code or macro that uses the TransferSpreadsheet action to
export this query.

Assuming that the form is open and has dates filled in, the query will read
its dates from the form, and the export will be limited to the date range.

BTW, if you really do have a table with fields called "Date" and "Name",
consider renaming them. Date is a reserved word in code (for the system
date), so sooner or later this will bite you. Likewise, nearly everything in
Access has a Name property: for examle, Forms.Forms1.Name will probably
return the name of the form instead of the value of the Name field.

 

[Guest]

Thanks a looooooottt!!!!

You ended my 2 week agony.....

1. Add a text box to your form, and set these properties:
Name StartDate
Format Short Date
and another text box set to:
Name EndDate
Format Short Date

2. In your query, in the Criteria row under the Date column, enter:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
replacing "Form1" with the name of your form.

3. Add a command button to your form to perform the export. In its Click
event, create the code or macro that uses the TransferSpreadsheet action to
export this query.

Assuming that the form is open and has dates filled in, the query will read
its dates from the form, and the export will be limited to the date range.

BTW, if you really do have a table with fields called "Date" and "Name",
consider renaming them. Date is a reserved word in code (for the system
date), so sooner or later this will bite you. Likewise, nearly everything in
Access has a Name property: for examle, Forms.Forms1.Name will probably
return the name of the form instead of the value of the Name field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello everyone...

i asked this question before but i can't solve my problem yet...

i have the following data in a query

Id Date Name Quantity Price Total Discount
NTotal
1 10-Sep-04 ABC 5 10 =10x5 10%
=Total*(1-Discount)
2 15-Sep-04 XYZ 10 15 =10x15 15%
=Total*(1-Discount)


I have a form to fill the data to this query. What i need is a code that
will be assigned to a button, so when the user presses that button the
program should ask start date and end date, then select the records that
match those dates, and then export those records to excel (only the fields
ID, Date, Name and NTotal)

I know it's complicated, but i have been consulting several books, and i
haven't beenable to solve this for 3 weeks.... HEEEELP!!!

Thaks...