Macro to open forms, also need to update a query

[wwiSports]

I have a form "Fulfillment". On the form I have a command button "cmdPatchReport" with a Macro on the OnClick event to open the PatchReport. When this button is clicked, not only would I like it to run an update querry to update the ItemShipDate to a date that is provided. (In other words I would like a modal to pop-up asking for the ShipDate, then run the update querry so that all of the records have that ShipDate)

Should I put this information in VBA on the Unload event of the Report?

Beth

 

[Guest_imported]

Hi Beth,
I have done this in a way. Modify it to suit your needs.

I have used a function called setDate() in a module to provide the date, that is to be set, to the query. I have typed setDate() in the Update To row under the ShippingDate column.

In a module I have put the following function:

Public Function setDate()
setDate=Me.Text1
End Sub

In the actual form ('Fulfillment' in your case), I have added a hidden text box, text1.

Add the following code to the Click event of your cmdPatchReport button:

Private Sub cmdPatchReport_Click()
Me.Text19 = InputBox("Enter the shipping date", "Shipping date?", date)
DoCmd.OpenQuery "Query9"
DoCmd.OpenReport "PatchReport", acViewPreview

End Sub

Now you are prompted for a date and the Update query ('Query9' in my case) is run after which the report is opened in the preview mode.

Hope this helps. Let me know.

 

[wwiSports]

Thank you! I just wanted to let you know that I used Macro's and used a RunSQL statement and it worked. Thank's again!
Beth