Setting the ControlSource of a form using a VB Variable

[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.**

 

[Guest]

What you are doing is not a very good way to go. Why do you think you need a
new table and a new form for each exercise? Each exercise should only be a
record in a table and one form will handle all your exercises.

You really need to reconsider what you are doing. You are creating yourself
an absolute nightmare.