Using a Vari for an Object name

[jstutz]

Hope someone can help... I'm about to go insane! Here's the scoop...

I've got a subform that appears on several parent forms. The subform shows the name and contact infomation of an individual related to the parent record. The parent record has a field called Contact_ID which is what the subform uses to populate itself. I've written some code that will hopefully allow the user to open up a Contact search screen, select a new or different contact name than what is already displayed and then update the Contact_ID field on the parent record, thus changing it's associated contact.

Here's what I've got. First, a public varaible called strForm stores the name of the current active form when the user clicks the "Change Contact" button so the database knows which parent form it's supposed to change. After storing the name of the parent form, Access opens a "Contact Selection" form the user uses to select the name of the contact they want to associate with the record. They then select a button named btnSelectContact that runs the following code run:

---------------------------
Private Sub btnSelectContact_Click()
Dim strFormName As string

strFormName = "Forms!" & strForm & "!Contact_ID"
debug.print strFormName

strFormName = Me.subfrmContact!Contact_ID

DoCmd.Close acForm, "frmContactSelect"
strForm = Null

End Sub
---------------------------

strFormName is the var that I'm using to build the name of the text box that needs to receive the new ContactID number (ie. the Contact_ID textbox on the parent form). The debug.print command is printing the proper name of the textbox I need to update, so I know I'm getting that far. An example of a name it returns would be forms!frmLocationMaint!Contact_ID. If I change:

strFormName = Me.subfrmContact!Contact_ID
TO
forms!frmLocationMaint!Contact_ID = Me.subfrmContact!Contact_ID

...the code works no problem, that is it changes the value of Contact_ID on the parent form. If I leave it as "strFormName = Me.subfrmContact!Contact_ID", Access sets strFormName = to the value of Me.subfrmContact!Contact_ID. Once I stopped to think about it, I understood that the code is doing exactly what's it's supposed to do.

So the question is... What do I do to tell access to change the value of the textbox strFormName respresents and NOT change the value of strFormName itself?

I've tried a lot of different methods to get this to work, from creating strFormName as an TextBox object and trying to set it's value with a "With strFormName" statement, but none seem to be correct. Can someone PLEASE point me in the right direction!!! I'd be VERY greatful!

Thanks in advance and hope this makes sense. Please email me if I need to clarify anything.

js

 

[Jack Cowley]

Hmmm. I am a bit confused by what you are trying to do. Do you have a one to many relationship (One Parent record, Many Child (Contacts) records) between your tables? If so then you can't change the ContactID in the Parent record. You either add a new Contact to the Contact table using the exising ContactID, edit the Contact information or you delete a Contact from the Contact table.

 

[DALeffler]

I’m pretty sure you need to code something along these lines:

Dim strMainFrm, strSubFrm as String
strMainFrm = “MainFrmCmbxNme” 'just the control name, not "Form!FormName!.." etc.
strSubFrm = “SubFrmCmbxNme” 'just the name

If Me(strMainFrm) = Forms!frmMainFrmName!frmSubFrmName.Form!(strSubFrm) Then
Me!txtbxCompRes = “Equal”
Else
Me!txtbxCompRes = “Not Equal”
End If

Hth,

Doug.

[This message has been edited by DALeffler (edited 07-13-2001).]

 

[jstutz]

Thank you both for the replies... I'm grateful you waded through the short novel I posted. Unfortunately, I don't think I explained myself very well. Let me see if I can clarify.

First, to answer Jack's question, each parent record can have only one contact associated with it, but a contact can be associated with more than one record.

Let's say I have 3 forms, FormA, FormB & FormC. Although FormA & B are populated from different tables, both use the same subform, SubFrmD. Both FormA & FormB have a field called ContactID, which links SubFrmD to either A or B. Finally, FormC let's you browse through a list of names from a table with a primary key field called ContactID.

On Forms A or B, to change the which contact is associated with the active record, all I have to do is change the ContactID number for the given record. So, from FormC, if I select the contact name I want to associate with the current record on formA, on FormC I would have code that looked like:

Forms!FormB.!contactID = me.contactID

OR... to to set/change the association for FormB it would look like:

Forms!FormB.!contactID = me.contactID

What I'm trying to do is figure out a way to write the code so that if FormC is launched from FormA, the db knows to change the value of contactID on FormA. Likewise, if Form C is launched from FormB, the value of ContactID that is selected from the list on FromC is used to update the value of ContactID on FormB. So instead of using:

Forms!FormA.!contactID = me.contactID
OR...
Forms!FormB.!contactID = me.contactID

I'm trying to use something like:

strFormName = me.contactID

Where strFormName = the name of the form that FormC was launched from.

If I'm still making no sense, I won't be surprised... Feel free to tell me so!

I won't be offeded. I would have probably figured this out by now except that I'm out of town and I don't have any of my reference books w/ me. To make matters worse, the laptop I took w/ me doesn't have the VBA help files installed and I don't have access to the installation disks. I'm up S--- creek w/out a paddle so to speak. I'll be home soon though, but I'm kinda stuck until I can get this worked out and was hoping to get some more work in on the db before then.

Thanks again in advance!

js

 

[Pat Hartman]

Maybe I'm missing something but wouldn't it be easier to use a combobox to select a contact. That way you wouldn't need subformC.

 

[DALeffler]

Hi js,

What I'm getting from you is the need to be able to set a controls "value" on a form or sub form or a sub-sub form using variables where ever VB code would normally require a field name or a form name - is that right?

If so, here's what worked for me:

What I did was create three simple forms: Form1, Form2, and Form3. Each form has one control - a label.

The labels on respective forms are named LblFrm1, LblFrm2, Lblfrm3.

Now I've placed Form3 onto Form2 as a subform. That subform control on Form2 is named "Form3". And likewise, I've placed Form2 as a subform control onto Form1 as a subform control named "Form2". Now we have nested subforms displayed when Form1 is opened.

I've added a command button to Form1 that when clicked changes the caption property of the sub and sub-sub label caption properties to a value set with a VB statement. And it's done using VB variables for the form and control names as well.

That's what I think you're after.

Here's the code for Form1 Command1_Click:

Private Sub Command1_Click()
Dim strSubFrmCntlNameonFrm1, strLblCntlNameonFrm2 As String
Dim strSubFrmCntlNameonFrm2, strLblCntlNameonFrm3 As String

strSubFrmCntlNameonFrm1 = "Form2"
strLblCntlNameonFrm2 = "LblFrm2"
strSubFrmCntlNameonFrm2 = "Form3"
strLblCntlNameonFrm3 = "LblFrm3"

Me(strSubFrmCntlNameonFrm1).Form(strLblCntlNameonFrm2).Caption = "SubFrmTxtChange"
Me(strSubFrmCntlNameonFrm1).Form(strSubFrmCntlNameonFrm2).Form(strLblCntlNameonFrm3).Caption = "SubSubFrmTxtChange"

End Sub

You want a small demo? Let me know -

Doug.

PS: This works in '97, does it work in 2000?

[This message has been edited by DALeffler (edited 07-15-2001).]

 

[jstutz]

Thanks for your reply! Everyone's suggestions got me to where I needed to be!

My problem was two fold. First, I didn't have my variable set up right. It needs to be:
"Public frmFormName as Form"

Second, I was setting the value improperly. I was using:
"frmFormName = Screen.ActiveForm"

I needed to use:
"Set frmFormName = Screen.ActiveForm"

Once frmFormName was declared and set properly, I could use it to set the value of a control, a form property, etc. For example, if I set frmFormName = FormA (from FormA run "Set frmFormName = Screen.ActiveForm) and from FormB run:

"frmFormName!Contact_ID = me.Contact_ID"

I will set the Contact_ID control on FormA = to Contact_ID on FormB.

Anyway, thanks again for everyone's diligence! Hope I can return the favor someday!

js