Dynamic Form Reference

[mikemaki]

I'm stumped and spinning my wheels like so many Milwaukee drivers after yesterday's snowfall. I have a series of forms named Survey1, Survey2, Survey3, etc. I also have a form for recording notes. When I close the notes form it is meant to post the notes into a hidden memo field on the survey form I am working with. I would like to keep the number of notes forms to one. I would like to refer to the survey form dynamically. Currently I am trying something like:

Dim SurveyForm As Form

Set SurveyForm = "frmSurvey" & Forms!frmMainScreen!SurveyDID

Forms!SurveyForm!MEMO1 = Forms!frmFreeText!Text0

I keep receiving an 'object required' error. Has anyone done anything like this? Can it be done? How?

 

[llkhoutx]

Text0 is obviously Null. A Null cannot be stored in a bound memo field.

 

[mikemaki]

Actually, I do populate the field before rumming this code.

 

[llkhoutx]

Set SurveyForm = "frmSurvey" & Forms!frmMainScreen!SurveyDID

is invalid Access VBA code. A form is expected,

"frmSurvey" & Forms!frmMainScreen!SurveyDID

is not a form.

Try

Forms!SurveyForm.SourceObject = "Survey1"

or

Forms!SurveyForm.SourceObject = "Survey2"

or

Forms!SurveyForm.SourceObject = "Survey3"

as appropriate.

Note that Surveyform is referenced forms!SurveyForm!CtlName regardless of the SourceObject, that is, Survey1, Survey2, or Survey3.

 

[mikemaki]

This still didn't work because SrveyForm does not exist in the Forms collection. I really don't want to create another form. It is not the direction I intended to go. Basically I wanted to know if there is a way to use a variable in a Forms statement ie Forms!<Variable>!MEMO1. But due to time constraints, I am giving up this quest.