How do I set Record Source for a Report using Visual Basic?

[margoo]

Developing an Emergency Messaging Database for Natural Diasters. Namely Hurricanes. Collecting Type of Diaster, Name, Year and Month from a Prompt form. Concantenating these four fields to name Tables, forms, reports. When end-user clicks the Okay button on prompt form (called CreateTable), the following code runs. I'm having name appended to EVENTS table for menu selction menu . First time experience naming objects from end-user input. The report recordsource statement is not working. Any ideas?



Private Sub CreateTable_Click()
On Error GoTo CreateTable_Click_Err

Dim strNewEvent As String
Dim tblNewEvent As String
Dim frmNewEvent As String
Dim pfrmNewEvent As String
Dim rptNewEvent As String
Dim Rpt As Report


strNewEvent = Forms!PromptEventName!FullName
tblNewEvent = "tbl" & Forms!PromptEventName!FullName
frmNewEvent = "frm" & Forms!PromptEventName!FullName
pfrmNewEvent = "pfrm" & Forms!PromptEventName!FullName
rptNewEvent = "rpt" & Forms!PromptEventName!FullName



DoCmd.RunCommand acCmdRefreshPage
DoCmd.OpenForm "EVENTS", acNormal, "", "", acAdd, acNormal
DoCmd.GoToControl "EVENTS"
Forms!EVENTS!EVENTS = strNewEvent
DoCmd.Close acForm, "Events", acSaveYes


DoCmd.CopyObject "", tblNewEvent, acTable, "MasterTable"
DoCmd.CopyObject "", frmNewEvent, acForm, "MasterForm"
DoCmd.OpenForm frmNewEvent, acDesign
Forms(frmNewEvent).RecordSource = tblNewEvent
DoCmd.Close acForm, (frmNewEvent), acSaveYes




DoCmd.CopyObject "", pfrmNewEvent, acForm, "pMasterForm"
DoCmd.OpenForm pfrmNewEvent, acDesign
Forms(pfrmNewEvent).RecordSource = tblNewEvent
DoCmd.Close acForm, (pfrmNewEvent), acSaveYes


DoCmd.CopyObject "", rptNewEvent, acReport, "MasterReport"
DoCmd.OpenReport rptNewEvent, acViewDesign
(this is not working
Rpt(rptNewEvent).RecordSource = tblNewEvent
DoCmd.Close acReport, (rptNewEvent), acSaveYes




CreateTable_Click_Exit:
Exit Sub

CreateTable_Click_Err:
MsgBox Error$
Resume CreateTable_Click_Exit

End Sub

 

[RoyVidar]

Must confess I'm not entirely sure on the complete process you're doing, but to refer to a report, which name is stored in a variable, try:

[tt]Reports(rptNewEvent).RecordSource = tblNewEvent[/tt]

Roy-Vidar

 

[margoo]

Got it, Thanks! I was forgetting to make the word "Reports "plural. Thanks so much. margoo