MS Access Database Macro

[rccoburn]

I have a database that has thousands of customer records. Right now I use a macro that prompts the user for the customer registration number and then exports the record to excel. It prompts the user prior to exporting every year. Is there any way to prompt the user once for the registration number then store the value in a variable that is reference in the criteria of the queries so it consecutively exports the records to excel. I want seperate excel sheets so I do not want to use a union query. Thanks.

[BradHodge]

I have a database that has thousands of customer records. Right now I use a macro that prompts the user for the customer registration number and then exports the record to excel. It prompts the user prior to exporting every year. Is there any way to prompt the user once for the registration number then store the value in a variable that is reference in the criteria of the queries so it consecutively exports the records to excel. I want seperate excel sheets so I do not want to use a union query. Thanks.

Can you expand a little bit on how your macro currently works? How are you getting it to print several different reports with a single year on each?

It should be a relatively simple thing to set up a variable that will hold your registration number (need to know what format the registration number is in (e.g. number, text, etc.)).

Brad.

[rccoburn]

Can you expand a little bit on how your macro currently works? How are you getting it to print several different reports with a single year on each?

It should be a relatively simple thing to set up a variable that will hold your registration number (need to know what format the registration number is in (e.g. number, text, etc.)).

Brad.

Here is the gist of it. I've expanded to show the action arguments. My query has [Please enter Reg #]in the criteria field of the column titled Registration #, which is what I am prompting the use to enter. I hope this helps.

Action

OutputTo

Object type query
Object name 2005 Detail (name of the query)
output format Microsoft Excel 97-2003 (*.xls)

OutputTo

Object type query
Object name 2006 Detail (name of the query)
output format Microsoft Excel 97-2003 (*.xls)

OutputTo

Object type query
Object name 2007 Detail (name of the query)
output format Microsoft Excel 97-2003 (*.xls)

[BradHodge]

Here is the gist of it. I've expanded to show the action arguments. My query has [Please enter Reg #]in the criteria field of the column titled Registration #, which is what I am prompting the use to enter. I hope this helps.

Action

OutputTo

Object type query
Object name 2005 Detail (name of the query)
output format Microsoft Excel 97-2003 (*.xls)

OutputTo

Object type query
Object name 2006 Detail (name of the query)
output format Microsoft Excel 97-2003 (*.xls)

OutputTo

Object type query
Object name 2007 Detail (name of the query)
output format Microsoft Excel 97-2003 (*.xls)

First, I would suggest taking the "Enter Reg#" criteria out of your Query.

Second, add an unbound txtBox to the form that your button is on (that lauches the output). You could call it RegNum or something like that.

Third, add criteria to your Query that references this new field (e.g. Forms![frmMAIN]![RegNum].

Fourth, add this code to your button...

Dim strRegNum as String
strRegNum = InputBox("Enter your Reg #")
 
Me.RegNum = strRegNum
 
DoCmd.OutputTo acOutputQuery, "2005 Detail", acFormatXLS
DoCmd.OutputTo acOutputQuery, "2006 Detail", acFormatXLS
DoCmd.OutputTo acOutputQuery, "2007 Detail", acFormatXLS
 

This should do what you are looking for. Let me know how it goes.

Brad.

[rccoburn]

First, I would suggest taking the "Enter Reg#" criteria out of your Query.

Second, add an unbound txtBox to the form that your button is on (that lauches the output). You could call it RegNum or something like that.

Third, add criteria to your Query that references this new field (e.g. Forms![frmMAIN]![RegNum].

Fourth, add this code to your button...

Dim strRegNum as String
strRegNum = InputBox("Enter your Reg #")
 
Me.RegNum = strRegNum
 
DoCmd.OutputTo acOutputQuery, "2005 Detail", acFormatXLS
DoCmd.OutputTo acOutputQuery, "2006 Detail", acFormatXLS
DoCmd.OutputTo acOutputQuery, "2007 Detail", acFormatXLS
 

This should do what you are looking for. Let me know how it goes.

Brad.

Thank Brad,

I was using a macro which if you go to the macro tab and double click on it will run. I'm not sure how to make a form. Can you explain? Thanks.

[BradHodge]

Thank Brad,

I was using a macro which if you go to the macro tab and double click on it will run. I'm not sure how to make a form. Can you explain? Thanks.

Sure. If you just go to the Forms tab instead. Select NEW and then Design view. When you get the blank slate, use the Toolbox and select the Command Button tool. Drag one onto your form. If the wizard button was also selected, it may try to do the rest of the button for you. Just hit Cancel.

Once you have the button on your form, it will likely say "Command 0" or something like that. Right click on the button and choose Properties. Go to the Other tab, and change it's Name to something that describes it (like "cmdDetailRpt").

Close the Properties box and then Right Click on the button again and choose Build Event... Code Builder. Between the Private Sub and End Sub statements, put the code that I suggested. Save your form.

Once it is saved, you will be able to go to the Forms tab and open it. Once open, click your new button, and it should do what you wanted.

Once you have it working, you ought to try fooling with the form to add other functions. You could turn the Wizard on (the top middle button on the ToolBox) and drag other buttons or controls onto your form. It will walk you through making them work, then you can look at the code that it came up with.

Good Luck,
Brad.

[rccoburn]

Sure. If you just go to the Forms tab instead. Select NEW and then Design view. When you get the blank slate, use the Toolbox and select the Command Button tool. Drag one onto your form. If the wizard button was also selected, it may try to do the rest of the button for you. Just hit Cancel.

Once you have the button on your form, it will likely say "Command 0" or something like that. Right click on the button and choose Properties. Go to the Other tab, and change it's Name to something that describes it (like "cmdDetailRpt").

Close the Properties box and then Right Click on the button again and choose Build Event... Code Builder. Between the Private Sub and End Sub statements, put the code that I suggested. Save your form.

Once it is saved, you will be able to go to the Forms tab and open it. Once open, click your new button, and it should do what you wanted.

Once you have it working, you ought to try fooling with the form to add other functions. You could turn the Wizard on (the top middle button on the ToolBox) and drag other buttons or controls onto your form. It will walk you through making them work, then you can look at the code that it came up with.

Good Luck,
Brad.

Thanks again. I am now getting a compile error method or data member not found. It highlights the statment Me.RegNum when this happens. Any ideas?

[BradHodge]

Thanks again. I am now getting a compile error method or data member not found. It highlights the statment Me.RegNum when this happens. Any ideas?

My apologies. You will also need to create a text field on your form (use the ToolBox again). This TextBox will be unbound and will not have anything listed as a Control Source in Properties. Make sure you name this TextBox "RegNum". When you click the button you created, it will ask you for the Reg Number and then will input what you type, into that Text Box. The queries will then draw the Reg Number from the text field on your form.

Brad.

[rccoburn]

My apologies. You will also need to create a text field on your form (use the ToolBox again). This TextBox will be unbound and will not have anything listed as a Control Source in Properties. Make sure you name this TextBox "RegNum". When you click the button you created, it will ask you for the Reg Number and then will input what you type, into that Text Box. The queries will then draw the Reg Number from the text field on your form.

Brad.

It works! Thanks. Lastly. Can I enter the RegNum in the text field and have it stored in the variable so I don't have to have a popup input box?

[BradHodge]

It works! Thanks. Lastly. Can I enter the RegNum in the text field and have it stored in the variable so I don't have to have a popup input box?

You could indeed. It just depends on your work flow. If you are doing multiple reg numbers in a sitting, it might be nice to have the Text Box empty out after each export (just add "Me.RegNum=Null" after DoCmd.OutputTo acOutputQuery, "2007 Detail", acFormatXLS).

Brad.

[BradHodge]

You could indeed. It just depends on your work flow. If you are doing multiple reg numbers in a sitting, it might be nice to have the Text Box empty out after each export (just add "Me.RegNum=Null" after DoCmd.OutputTo acOutputQuery, "2007 Detail", acFormatXLS).

Brad.

And... As I'm sure you figured out, you would delete

Dim strRegNum as String
strRegNum = InputBox("Enter your Reg #")
 
Me.RegNum = strRegNum

[rccoburn]

And... As I'm sure you figured out, you would delete

Dim strRegNum as String
strRegNum = InputBox("Enter your Reg #")
 
Me.RegNum = strRegNum

Everything works great. Thanks again for your help.