How do I Set RecordSource Property Using Visual Basic? 1

[margoo]

Setting up an Emergency Messaging Database for FUTURE Emergency Events. Namely Hurricanes. With a form I prompt end-user to fill out Event Type, Name, Year, Month, then concatenate fields [FullName]. Okay button runs a Visual Basic module that copies a MasterTable, and calls it the concatenated field, copies two Master Forms (fill out/ print) and names them the concatenated field, and copies a report, and names it the concatenated field. IE: Hurricane Tony 2006 08

How do I set the Record Source for these new objects???


The concatenated field is defined as a string: NewEvent

Dim strNewEvent As String
strNewEvent = Forms!PromptEventName!FullName


DoCmd.CopyObject "", strNewEvent, acTable, "MasterTable"
DoCmd.CopyObject "", strNewEvent, acForm, "MasterForm"
Forms!strNewEvent.RecordSource = strNewEvent

(doesn't work .. says it can't find form strNewEvent)

So thinking I may need to use an existing form name I thought I would change the record source of the MasterForm before copying it, then change it back later:

DoCmd.CopyObject "", strNewEvent, acTable, "MasterTable"
Forms!MasterForm.RecordSource = strNewEvent
DoCmd.CopyObject "", strNewEvent, acForm, "MasterForm"

(error message says it can't find MasterForm, or form is not open, or module not compiling. Love the specificity of these error messages. Does form have to be open to change recordSource Property?? So I first try to open Masterform (DoCmd.OPen), error says it can't find it. I have definitly spelled the form correctly.

Any advice at all? Need to get this done by 10:00am tomorrow .. or maybe by 1:30 if I can push them off.

MArgoo

 

[TheAceMan1]

How are ya margoo . . . . .

First, remove the quotations from the DoCmd.CopyObject lines. I don't remember right off if a null string [blue]""[/blue] represents the destination DB, but to be sure, remove them.

At the same time, I don't believe form/table names can be the same (I could be wrong . . . soon as I find out I'll let ya know). In either case this is a bad programming habit. You can keep the name [blue]NewEvent[/blue], just preappend with [blue]tbl & frm[/blue] to differentiate approiately. And yes! . . . . the form has to be open! There's also an improper use of the Bang Operator [purple]![/purple] in [blue]Forms[purple]![/purple]strNewEvent.RecordSource = strNewEvent[/blue]. So your origional code should be:
Should be:

[blue]   Dim tblNewEvent As String, frmNewEvent As String
    
   [purple][b]tblNewEvent[/b][/purple] = "tbl" & Forms!PromptEventName!FullName
   [purple][b]frmNewEvent[/b][/purple] = "frm" & Forms!PromptEventName!FullName    

   DoCmd.CopyObject "", [purple][b]tblNewEvent[/b][/purple], acTable, "MasterTable"
   DoCmd.CopyObject "", [purple][b]frmNewEvent[/b][/purple], acForm, "MasterForm"
   DoCmd.OpenForm [purple][b]frmNewEvent[/b][/purple], acNormal
   Forms([purple][b]frmNewEvent[/b][/purple]).RecordSource = [purple][b]tblNewEvent[/b][/purple][/blue]

Give it a whirl & let me know . . . . .

See Ya! . . . . . .

 

[scking]

Just a short note but the prefixes 'tbl' and 'frm' are generally reserved for table and form objects, not strings. I know of no developer that wouldn't have a double-take to try to determine what those variable names were supposed to be. You could use strNewEventTable or strNewEventForm which would clearly represent the type of the variable. Margoo had the correct variable naming, and since it is exactly the same for form an table there is no need for two variables.

Also, it is good practice to verify user accepted input as in a PromptEventName!FullName value before actually trying to copy it into a table. What happens on a collision.

It appears that the user is prompted for a Event Type, Name, Year, Month in separate controls on the PromptEventName (frmPromptEventName) form and they are then concatenated into a control named FullName (txtFullName). When the user clicks the OK button the code copies the MasterTable into the same database and renames it to the name of the event. Then it sets the RecordSource of the current MasterForm to the new table name and then copies the MasterForm effectively creating a clone of the form. The result is multiple tables containing the same data with different names connected to multiple forms with nothing but different RecordSource values. It appears as if MasterForm is used only as a template to create other forms that reference the same table data with different table names. It is unclear what is actually the requirements. It is totally unnecessary to have multiple forms with only the RecordSource property values different as these can be set at runtime.








---------------------
scking@arinc.com
---------------------

 

[TheAceMan1]

How are ya scking . . . . .

[blue]Just a short note but the prefixes [purple]'tbl' and 'frm'[/purple] are [purple]generally reserved[/purple] for table and form objects, not strings[/blue]

Your not following whats happening! . . . . [blue]margoo[/blue] is concatenating an [blue]unique identifier[/blue] (as far as their purpose is concerned) [blue]used to name copied Table and Form objects[/blue]. This in itself [purple]prescribes unique names[/purple] for the [purple]objects![/purple] The code preappends [blue]tbl & frm[/blue] to the [purple]object[/purple] names for clarity.

[blue]The preappended text is not used for the string, its used in naming the actual objects![/blue]

[purple]So how does this violate the reservation you speak of?[/purple]

Only problem I see is an error I didn't pickup in prescribing the concatenated name in code. As [blue]margoo[/blue] uses spaces and as such I should be using brackets [purple][][/purple]. Making the code:

[blue]   Dim tblNewEvent As String, frmNewEvent As String
    
   tblNewEvent = "tbl" & Forms!PromptEventName!FullName
   frmNewEvent = "frm" & Forms!PromptEventName!FullName    

   DoCmd.CopyObject "", [purple][b][[/b][/purple]tblNewEvent[purple][b]][/b][/purple], acTable, "MasterTable"
   DoCmd.CopyObject "", [purple][b][[/b][/purple]frmNewEvent[purple][b]][/b][/purple], acForm, "MasterForm"
   DoCmd.OpenForm [purple][b][[/b][/purple]frmNewEvent[purple][b]][/b][/purple], acNormal
   Forms([purple][b][[/b][/purple]frmNewEvent[purple][b]][/b][/purple]).RecordSource = [purple][b][[/b][/purple]tblNewEvent[purple][b]][/b][/purple][/blue]

[blue]Margoo had the correct variable naming, and since it is exactly the same for form an table there is no need for two variables[/blue]

I havn't found it yet, but as I said before, I don't believe you can give table & form the exact same. In any case, how would you distinct one from the other? Besides the fact you could easily get into referencing problems.

[blue]Also, it is good practice to verify user accepted input as in a PromptEventName!FullName value before actually trying to copy it into a table.[/blue]

I can think of a number of other things that should be check, but this is [purple]Tek-Tips[/purple] not [purple]Tek-DoEverything[/purple], besides [blue]margoo[/blue] did not ask for that!

I can keep going, but if you really read it, You'll find you've been a little premature . . . . .

See Ya! . . . . . .

 

[margoo]

TheAceMan1:

IT WORKED! Thank you so much.

I know one cannot have queries the same name as tables ... but I thought form with same name was okay. However, my code was seizing Access, your code worked (except the form has to be open in design mode to change RecordSource).

But I'm not finished. (I gave end-user tables, forms, report for Hurricane Francis to buy myself some time. I have untill next Hurricane hits.)

I would also like to create a report from the MasterReport with name and record source of the concatenated field in prompt form. Apparently the procedure is different than forms. I can't do Report(rptNewEvent).RecordSource = tbleNewEvent. Looks like I have to define Report as a Report. So I defined Rpt as a Report, but still not working. They can live without this report, but it would be nice.

My bigger concern is how the end-users are going to open the various Event Forms once they are created. That is why I used the same name for the form as the table. As you can see in my code, I am opening a form based on the EVENTS table, called EVENTS and adding the new event. Then from the switchboard I wanted to have a menu item open a form that would have a combo box listing the EVENTS from this table. End-user would select an event, the Okay button would open the form that matched the name in the combo box. Now how am I going to do it? Is there a way to list forms and reports in a combo box? Is there some other way to list database objects for end user to select from?

Thanks so much for your time. I am so glad you people are out there! Here's what I have now:



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

 

[TheAceMan1]

margoo . . . . .

I have a number of other posts that require my promised attention (they've been on the back burner a little too long). It would be at least a day or two before I could critique your last post.

Rather than have you wait, post your latest question in the [blue]Microsoft: Access Reports Forum[/blue]. All the [blue]Report Gurus'[/blue] hang out there! When time permits I'll be sure to stop by there an check you've been taken of (the gurus' there are really good!).

See Ya! . . . . . .