Setting the ControlSource of a form from a variable in VB

[Mexar]

My programming grammar might be a little off but I have a pretty good
idea of what I need to convey.

I am making an Access database with a form that pops up and lets you
choose to select an existing exercise or a new one. I want to focus on
the new exercise selection for now.

When selecting the new exercise a textbox appears where you enter the
new exercise name. This is passed and used to create a table and a
form from a template table and form. What I need to do is change the
control source of the new form when it is created to the new table.
Below is what I have:

Public Sub Form_Load
Dim ExerciseName As String
Dim NewExerciseName As String
Dim ExerciseTable As String
Dim ExerciseForm As String
End Sub

Private Sub btnEnter_Click()
If txtNewExercise.Visible = True Then
NewExerciseName = txtNewExercise.Text

DoCmd.CopyObject "", "tbl" & NewExerciseName, acTable,
"tblTemplate"

Dim db As DAO.Database
Dim rs As DAO.Recordset

**Below creates an entry into a table for later use**
Set db = CurrentDb
Set rs = db.OpenRecordset("tblExercises")
rs.AddNew
rs.Fields("Exercise_Name") = Me.txtNewExercise
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

....

DoCmd.CopyObject "", "frm" & NewExerciseName, acForm,
"frmEntryTemplate"
ExerciseTable = "tbl" & NewExerciseName
ExerciseForm = "frm" & NewExerciseName
DoCmd.OpenForm ExerciseForm, acNormal, "", "", acAdd,
acNormal

**From here I am lost, what I would like to do is make
ExerciseTable the ControlSource for ExerciseForm, but I am having
trouble using the variable names to set the ControlSource.**

[paii, Ron]

"Mexar" <me***@cox.netwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

My programming grammar might be a little off but I have a pretty good
idea of what I need to convey.

I am making an Access database with a form that pops up and lets you
choose to select an existing exercise or a new one. I want to focus on
the new exercise selection for now.

When selecting the new exercise a textbox appears where you enter the
new exercise name. This is passed and used to create a table and a
form from a template table and form. What I need to do is change the
control source of the new form when it is created to the new table.
Below is what I have:

Public Sub Form_Load
Dim ExerciseName As String
Dim NewExerciseName As String
Dim ExerciseTable As String
Dim ExerciseForm As String
End Sub

Private Sub btnEnter_Click()
If txtNewExercise.Visible = True Then
NewExerciseName = txtNewExercise.Text

DoCmd.CopyObject "", "tbl" & NewExerciseName, acTable,
"tblTemplate"

Dim db As DAO.Database
Dim rs As DAO.Recordset

**Below creates an entry into a table for later use**
Set db = CurrentDb
Set rs = db.OpenRecordset("tblExercises")
rs.AddNew
rs.Fields("Exercise_Name") = Me.txtNewExercise
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

....

DoCmd.CopyObject "", "frm" & NewExerciseName, acForm,
"frmEntryTemplate"
ExerciseTable = "tbl" & NewExerciseName
ExerciseForm = "frm" & NewExerciseName
DoCmd.OpenForm ExerciseForm, acNormal, "", "", acAdd,
acNormal

**From here I am lost, what I would like to do is make
ExerciseTable the ControlSource for ExerciseForm, but I am having
trouble using the variable names to set the ControlSource.**

I would think you would need to open the form in edit mode not acNormal.

But why would you create a new table and report for each exercise? You added
the new exercise to the tblExercises table. Open the standard form filtered
to the new exercise.