Store field names in a table and then use them in VBA

[Todd]

I have an unusual need for my database. I need to store form field names
inside a table. Let's say I have tblFields and one field name is
txtFieldName. I'm going to type in the field names of several checkbox
fields found on form frmPrintApps into txtFieldName. For example, the
values might be chkDocument1, chkDocument2, chkDocument3, etc. All of the
names of the checkbox fields found on my form will be stored into
txtFieldName.

Now, I want to be able to use txtFieldName in my code but how do I call on
it as a field instead of just text? For example, I want to call on it like
this:

If Me!chkDocument1 = True Then ... 'Print the document


Instead, however, I want to call on it from the txtFieldName. Of course, I
can't do this:
If txtFieldName = True Then ... 'Print the document
because that looks at the text in the record rather than realizing the text
is referring to a field name.


The end result I'm looking for is to loop through all my checkboxes on a
form and for each one that is checked, the code will open a corresponding
document. I sure hope you followed that because I'm almost confusing myself
trying to explain what I want to do.

Thanks!!!!

 

[Graham Mandeno]

Hi Todd

You can always refer to a member of a collection using a string expression
in parentheses.

For example:

Me!chkDocument1

is equivalent to:

Me("chkDocument1")

which is equivalent to:

strControlName = "chkDocument1"
Me(strControlName )

 

[GeoffG]

Hi Todd,

Why store the Checkbox names in a table?

It seems you could perhaps:
1. loop through all the controls on the form;
2. see if the current control is a CheckBox;
3. see if it's selected;
4. if it is, open the appropriate document.

Here's some sample code to illustrate what I mean:


Private Sub cmdOpenDocuments_Click()

' This is the event procedure for the
' click event of a Command Button on the form.

Dim objCTRL As Access.Control

' Loop through all controls on the form:
For Each objCTRL In Me.Controls

' See if the current control is a CheckBox:
If objCTRL.ControlType = acCheckBox Then

' See if the CheckBox is selected:
If objCTRL.Value = True Then

' Depending on the name of the CheckBox,
' open the appropriate document:
Select Case objCTRL.Name
Case "chkDocument1"
Call OpenMyDocument("C:\MyTemp1.txt")
Case "chkDocument2"
Call OpenMyDocument("C:\MyTemp2.txt")
Case "chkDocument3"
Call OpenMyDocument("C:\MyTemp3.txt")
Case "chkDocument4"
Call OpenMyDocument("C:\MyTemp4.txt")
Case Else
' Do nothing.
End Select

End If
End If
Next

End Sub

Private Sub OpenMyDocument(strPathName As String)

' IN:
' strPathName is the path and name of
' the file that's to be opened.

Dim lngTaskID As Long

' This example opens a text file in Notepad.
lngTaskID = Shell("Notepad " & strPathName, vbNormalFocus)

End Sub


Regards
Geoff

 

[Todd]

Actually, my original hope was to use a system exactly like you mentioned.
Unfortunately, there are soooo many fields, that the "select case" was going
to get very lengthy. Using the table instead isn't a perfect system, but I
think it'll help me organize the code a little better. I also have a few
plans with the table in other parts of my database. Thanks!

 

[Todd]

Very cool! I didn't know you could do that. Thanks for your help, Graham!

Hi Todd

You can always refer to a member of a collection using a string expression
in parentheses.

For example:

Me!chkDocument1

is equivalent to:

Me("chkDocument1")

which is equivalent to:

strControlName = "chkDocument1"
Me(strControlName )


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have an unusual need for my database. I need to store form field names
inside a table. Let's say I have tblFields and one field name is
txtFieldName. I'm going to type in the field names of several checkbox
fields found on form frmPrintApps into txtFieldName. For example, the
values might be chkDocument1, chkDocument2, chkDocument3, etc. All of the
names of the checkbox fields found on my form will be stored into
txtFieldName.

Now, I want to be able to use txtFieldName in my code but how do I call
on it as a field instead of just text? For example, I want to call on it
like this:

If Me!chkDocument1 = True Then ... 'Print the document


Instead, however, I want to call on it from the txtFieldName. Of course,
I can't do this:
If txtFieldName = True Then ... 'Print the document
because that looks at the text in the record rather than realizing the
text is referring to a field name.


The end result I'm looking for is to loop through all my checkboxes on a
form and for each one that is checked, the code will open a corresponding
document. I sure hope you followed that because I'm almost confusing
myself trying to explain what I want to do.

Thanks!!!!