Macro to Output current data only to Word (Not a Merge)

[applevacross]

Access 2000 WinXP Pro

Good afternoon all, I'm looking for your expert knowledge.

I've created a form which is pretty simple. Each time a request for hardware of software comes in through the intranet, I enter that data into the database form. I created a macro "submit" button which will output the data entered into word via associated db report. However, every record comes up into the word file instead of just the one record I want. I'd like to make the report dependent on a query which will allow only that newly entered record to output to word. Does anybody know the criteria I will need to enter?

SQL:

SELECT [Date], [Ticket Number], [Staff Requesting Purchase], [Title of Requesor], [Reason of Request], [Quantity], [Description], [Part Number], [Price], [Shipping Cost], [Total]
FROM [Applevac_Inv_DB_Hardware/Software];

Thanks to All in advance,

 

[OnTheFly]

I am unclear how you are getting the data to the Word document. You reference a db report. Are you creating a report in Access and then exporting it to word. Do you have a word document tha is a merge document connected to the Access Table or the SQL?

In regard to the criteria for the SQL you should have a primary key established on the table you are entering the data into. To limit to that new record you would have to add a Where statement to your SQL using the primary key = to the record you wish to select's primary key.


Hope this helps.

OnTheFly

 

[applevacross]

Hi OnTheFly, Here it is

The form is a data entry form, after all data is entered The users will hit the submit button [macro].

The macro is Actions
Output(data goes to report which will output the data to a word doc it creates locally]
Close (closes the form to save the new data entered)
OpenForm (Reopens the form to enter new record)

I'm able to get the data to go to the word doc, but it enters all of the records contained in the master table. I only want the word doc to receive the data for the one update each time a new requeste is entered. Would I want to set this where statement up in the macro code? If so where should I put it? Thank you for your help.

This is the macro {event procedure) on click:

Private Sub Command29_Click()
On Error GoTo Err_Command29_Click

Dim stDocName As String

stDocName = "Transfer"
DoCmd.RunMacro stDocName





Exit_Command29_Click:
Exit Sub

Err_Command29_Click:
MsgBox Err.Description
Resume Exit_Command29_Click

End Sub

 

[OnTheFly]

I don't work a lot with Macros but from looking at it I have these questions...

What are the parameters for Object Type and Object Format?

If you are outputing based on a report you can make the data source of the report filter by the ID for the record displayed in the form by adding something like

[Forms]![FormName]![IDFieldName]

in the criteria of the record source of the report.

However, you will probably have to refresh the form to commit the record to the table before you do the Output Action. Otherwise, it may not find the record in the table.

Hope this helps.

OnTheFly

 

[applevacross]

Bingo! Thank you very much for your help. I greatly appreciate it.