Convert a variable into lines of code

[Pan]

I have captured the following variable, which will change depending upon the value(s) a user chooses from a list box that contains a list of fields to display on a form:

'Forms!frm1!txt1.Visible = True
Forms!frm1!txt2.Visible = True
Forms!frm1!txt5.Visible = True'

Now this variable needs to be processed as lines of code.

Can anyone help?

 

[KenHigg]

You may try the example:

For intX = 1 To 6
Forms("frmMainForm")("txt" & intX) = "Blank"
Next intX

In the following post:

http://www.access-programmers.co.uk/forums/showthread.php?t=61539


???
kh

 

[Pan]

Thank you for your reply.
Perhaps I should add some background information:
An unbound form has a list box where you can select from a list of fields. When you click ‘Ok” on this form, it opens a query-bound form (frm1) in datasheet view that contains all the possible fields from the query (and the list box). How can I hide all the fields on this form except those that the user selected on the first form?
I used a loop from the first form to put together the variable:

'Forms!frm1!txt1.Visible = True
Forms!frm1!txt2.Visible = True
Forms!frm1!txt5.Visible = True'

What I need VB to do now is to run the variable as a procedure.
If I'm not being clear (or if I'm just being dense), please let me know and I'll be happy to try to clarify this further.
Thanks again for your reply.

 

[john471]

Try passing OpenArgs to form

Pan,

This may work for you.

Pass an OpenArgs argument in your open form statement, with a delimeted list of the fields the user has selected.

In the target form's Load event, parse the OpenArgs, and show the columns specified in the OpenArgs, and hide the others.

On the selection Form, for the Click event of the OK button...
Private Sub cmdOK_Click()
Dim szOpenArgs As String
Dim varItem As Variant
Dim ctl As Control

Set ctl = Me.lstFieldSelector

If ctl.ItemsSelected.Count > 0 Then
szOpenArgs = "/"
For Each varItem In ctl.ItemsSelected
szOpenArgs = szOpenArgs & ctl.ItemData(varItem) & "/"
Next varItem
End If

Set ctl = Nothing

'Open the form passing the delimeted string housing the required column selection
DoCmd.OpenForm "frmMyDisplayForm", acFormDS, , , _
acFormPropertySettings, acWindowNormal, szOpenArgs

'Close the form used for the selection process (me)
DoCmd.Close acForm, Me.Name
End Sub


Then in the Load event of the target form...

Private Sub Form_Load()
'if none specified, or no OpenArgs supplied, then don't hide any (show all)
If Len(Nz(Me.OpenArgs)) > 0 Then
ManageColumn "/fruit/", Me.txtFruit
ManageColumn "/weekday/", Me.txtWeekday
ManageColumn "/relationship/", Me.txtRelationship
ManageColumn "/colour/", Me.txtColour
ManageColumn "/pet/", Me.txtPet
Else
Me.txtFruit.ColumnHidden = False
Me.txtWeekday.ColumnHidden = False
Me.txtRelationship.ColumnHidden = False
Me.txtColour.ColumnHidden = False
Me.txtPet.ColumnHidden = False
End If
End Sub

Private Sub ManageColumn(szSearchFor As String, ctl As Control)
If Not InStr(1, Me.OpenArgs, szSearchFor, vbTextCompare) > 0 Then
ctl.ColumnHidden = True
Else
ctl.ColumnHidden = False
End If
End Sub

Here's my P.O.C DB (access 97)

Hope This Helps

Regards

John.

 

[Pan]

Thank you.

John,
Once I weaved it in with the rest of my code (SQL-building loop, etc.), it works perfectly.
Wish I could buy you lunch.

Good on you.

Pan

 

[john471]

Pan,

Glad it worked.

I wish you could buy me lunch too !! !!

I had a bit of fun doing it. Enjoy.

John.