General discussion


Access 2000 Macro

By pan_linda ·
I am a beginner macro writer trying to write a macro that will automate printing labels from data in an Access 2000 database. I know the steps required in doing it within Access .... Tools --> Office Links ---> Merge it with MS Word and when the Word window opens up, Tool --> Mail Merge ... etc. I have so far written up to the point where I use the RunCommand and command: "WordMailMerge", however, I am not able to bring the Word window into focus. It keeps the Access Database window in focus. Anyone with suggestions?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 2000 Macro

by RichTee In reply to Access 2000 Macro

You might find working directly in Access more efficient. It is quite easy to do with VB.

#1 Create a query that pulls the fields you want on your labels. (a parameter query will give you options to filter your records?I can provide additionalhelp if needed).

#2 Create a report that fits your mailing label requirements. Use the query you created in step 1 as the data source for this report. (name the report: rptMailLabels)

#3 Create an unbound form with a ?command button? (Actually--You can add this command button to any form you have) (Name the command button: cmdPrintLabels)

#4 Put the following code in the ?On Click? Event Procedure of the command button. (Do not duplicate the first and last line of the code, that is, the ?Private Sub?? line and the ?End Sub? line)

Private Sub cmdPrintLabels_Click()
Dim strDoc As String
On Error GoTo Err_cmdPrintLabels_Click

strDoc = "rptAllRecords_Straight"
DoCmd.OpenReport strDoc, acViewPreview
Exit Sub

Err_cmdPrintLabels _Click:
Resume Exit_cmdPrintLabels_Click

End Sub

Now when you want to print labels from your Access database, just click the commandbutton.

Hope this helps. Rich

Collapse -

Access 2000 Macro

by RichTee In reply to Access 2000 Macro

Important: Please CHANGE the following line in the code:
FROM: strDoc = "rptAllRecords_Straight"

TO: strDoc = "rptMailLabels"

Collapse -

Access 2000 Macro

by yatst In reply to Access 2000 Macro

This might be a little easier.

If you have already created the report that formats the labels. Try this.

Draw a command button on your form. Let the wizard walk you through the step to choose the report you want to print. In about 5 easy pick and click steps you will have automated the process.

However, if you really want Word to do this it might be better to create the macro there. You can do this by simply turning Macros on Go through the steps to create the labels using the MailMerge wizard then turn the Macro off. (Yes, you can connect to your access table or query from the Mail Merge wizard).

There is also an option to "Auto Start" in the macro arguments in Access when you use the "output to" option to send the report to Word.

Collapse -

Access 2000 Macro

by pan_linda In reply to Access 2000 Macro

Ultimately I went with something quick and dirty. Use the report wizard to create the report and the query associated with report. Users to modify the file Number range in the query and then double-click on the report to just bring in records in that range.

Related Discussions

Related Forums