Form field names as variables?

**Stuart McCall** · Jun 27 '08, 07:25 PM

Re: Form field names as variables?

"ManningFan " <manningfan@gma il.comwrote in message
news:6da5d3c2-544c-46a3-ae9a-dfdd24715577@t5 4g2000hsg.googl egroups.com...

Got a little problem here...
>
I have a combo box (Combo5) which needs to make a different field
visible depending on the choice made.
Standard code for what I'm doing is as such:
>
If Me.Combo5.Value = "2040" Then
Me.OLEOneVal.Vi sible = True
Me.OLETwoVal.Vi sible = False
Me.OLEThreeVal. Visible = False
Me.OLEFourVal.V isible = False
Elseif Me.Combo5.Value = "2041" Then
Me.OLEAetnaTrad itional.Visible = False
Me.OLEPrincipal Financial.Visib le = True
Me.OLEThreeVal. Visible = False
Me.OLEFourVal.V isible = False
Elseif Me.Combo5.Value = "2042" Then
Me.OLEAetnaTrad itional.Visible = False
Me.OLEPrincipal Financial.Visib le = False
Me.OLEThreeVal. Visible = True
Me.OLEFourVal.V isible = False
Elseif Me.Combo5.Value = "2043" Then
Me.OLEAetnaTrad itional.Visible = False
Me.OLEPrincipal Financial.Visib le = False
Me.OLEThreeVal. Visible = False
Me.OLEFourVal.V isible = True
End If
>
As you can see, each true value sets a different field to visible.
>
I'd like to do this a little cleaner if possible, to allow for future
expansion, so I'm trying to use variables. I have a table (tblAudits)
with an audit number, audit name and the corresponding field I want
visible on the form. I'm having no luck. My "air code" (which
doesn't work syntactically) goes:
>
Set db = CurrentDb
Set rec = db.OpenRecordse t("Select * from tblAudits")
>
'Loop through all possible values
Do While rec.EOF = False
'Get the name of the field from the table
OLED = rec(2).Value
'If the audit number from the table equals the audit number from the
combo box...
If Int(rec(0)) = Int(Me.Combo5.V alue) Then
Me.OLED.Value = False
Else
Me.OLED.Value = True
End If
rec.MoveNext
Loop
>
>
Any ideas?

I think it would be a mistake to do what you're proposing. It will obscure
what is happening when you come back to this code in say a year's time. What
you're already doing is simple and efficient and most of all is easy to
understand. You could however optimise a bit. You could use a select case
statement instead of if...elseif. That way the combo's value only needs to
be obtained once, rather than 4 times. Also you could persist the reference
to the form (Me). Something like this:

With Me
Select Case .Combo5.Value
Case "2040"
.OLEOneVal.Visi ble = True
.OLETwoVal.Visi ble = False
.OLEThreeVal.Vi sible = False
.OLEFourVal.Vis ible = False
Case "2041"
.OLEAetnaTradit ional.Visible = False
.OLEPrincipalFi nancial.Visible = True
.OLEThreeVal.Vi sible = False
.OLEFourVal.Vis ible = False
Case "2042"
.OLEAetnaTradit ional.Visible = False
.OLEPrincipalFi nancial.Visible = False
.OLEThreeVal.Vi sible = True
.OLEFourVal.Vis ible = False
Case "2043"
.OLEAetnaTradit ional.Visible = False
.OLEPrincipalFi nancial.Visible = False
.OLEThreeVal.Vi sible = False
.OLEFourVal.Vis ible = True
End Select
End With

**mrelam@yahoo.com** · Jun 27 '08, 07:25 PM

Re: Form field names as variables?

On May 27, 12:38 pm, "Stuart McCall" <smcc...@myunre albox.comwrote:

"ManningFan " <manning...@gma il.comwrote in message
>
news:6da5d3c2-544c-46a3-ae9a-dfdd24715577@t5 4g2000hsg.googl egroups.com...
>
>
>
>
>

Got a little problem here...

>

I have a combo box (Combo5) which needs to make a different field
visible depending on the choice made.
Standard code for what I'm doing is as such:

>

If Me.Combo5.Value = "2040" Then
Me.OLEOneVal.Vi sible = True
Me.OLETwoVal.Vi sible = False
Me.OLEThreeVal. Visible = False
Me.OLEFourVal.V isible = False
Elseif Me.Combo5.Value = "2041" Then
Me.OLEAetnaTrad itional.Visible = False
Me.OLEPrincipal Financial.Visib le = True
Me.OLEThreeVal. Visible = False
Me.OLEFourVal.V isible = False
Elseif Me.Combo5.Value = "2042" Then
Me.OLEAetnaTrad itional.Visible = False
Me.OLEPrincipal Financial.Visib le = False
Me.OLEThreeVal. Visible = True
Me.OLEFourVal.V isible = False
Elseif Me.Combo5.Value = "2043" Then
Me.OLEAetnaTrad itional.Visible = False
Me.OLEPrincipal Financial.Visib le = False
Me.OLEThreeVal. Visible = False
Me.OLEFourVal.V isible = True
End If

>

As you can see, each true value sets a different field to visible.

>

I'd like to do this a little cleaner if possible, to allow for future
expansion, so I'm trying to use variables. I have a table (tblAudits)
with an audit number, audit name and the corresponding field I want
visible on the form. I'm having no luck. My "air code" (which
doesn't work syntactically) goes:

>

Set db = CurrentDb
Set rec = db.OpenRecordse t("Select * from tblAudits")

>

'Loop through all possible values
Do While rec.EOF = False
'Get the name of the field from the table
OLED = rec(2).Value
'If the audit number from the table equals the audit number from the
combo box...
If Int(rec(0)) = Int(Me.Combo5.V alue) Then
Me.OLED.Value = False
Else
Me.OLED.Value = True
End If
rec.MoveNext
Loop

>

Any ideas?

>
I think it would be a mistake to do what you're proposing. It will obscure
what is happening when you come back to this code in say a year's time. What
you're already doing is simple and efficient and most of all is easy to
understand. You could however optimise a bit. You could use a select case
statement instead of if...elseif. That way the combo's value only needs to
be obtained once, rather than 4 times. Also you could persist the reference
to the form (Me). Something like this:
>
With Me
Select Case .Combo5.Value
Case "2040"
.OLEOneVal.Visi ble = True
.OLETwoVal.Visi ble = False
.OLEThreeVal.Vi sible = False
.OLEFourVal.Vis ible = False
Case "2041"
.OLEAetnaTradit ional.Visible = False
.OLEPrincipalFi nancial.Visible = True
.OLEThreeVal.Vi sible = False
.OLEFourVal.Vis ible = False
Case "2042"
.OLEAetnaTradit ional.Visible = False
.OLEPrincipalFi nancial.Visible = False
.OLEThreeVal.Vi sible = True
.OLEFourVal.Vis ible = False
Case "2043"
.OLEAetnaTradit ional.Visible = False
.OLEPrincipalFi nancial.Visible = False
.OLEThreeVal.Vi sible = False
.OLEFourVal.Vis ible = True
End Select
End With- Hide quoted text -
>
- Show quoted text -

I would use boolean expressions, like this:

Me.OLEOneVal.Vi sible = (Me.Combo5.Valu e = "2040")
Me.OLETwoVal.Vi sible = (Me.Combo5.Valu e = "2041")
Me.OLEThreeVal. Visible = (Me.Combo5.Valu e = "2042")
Me.OLEFourVal.V isible = (Me.Combo5.Valu e = "2043")

**Salad** · Jun 27 '08, 07:25 PM

Re: Form field names as variables?

ManningFan wrote:

Got a little problem here...
>
I have a combo box (Combo5) which needs to make a different field
visible depending on the choice made.
Standard code for what I'm doing is as such:
>
If Me.Combo5.Value = "2040" Then
Me.OLEOneVal.Vi sible = True
Me.OLETwoVal.Vi sible = False
Me.OLEThreeVal. Visible = False
Me.OLEFourVal.V isible = False
Elseif Me.Combo5.Value = "2041" Then
Me.OLEAetnaTrad itional.Visible = False
Me.OLEPrincipal Financial.Visib le = True
Me.OLEThreeVal. Visible = False
Me.OLEFourVal.V isible = False
Elseif Me.Combo5.Value = "2042" Then
Me.OLEAetnaTrad itional.Visible = False
Me.OLEPrincipal Financial.Visib le = False
Me.OLEThreeVal. Visible = True
Me.OLEFourVal.V isible = False
Elseif Me.Combo5.Value = "2043" Then
Me.OLEAetnaTrad itional.Visible = False
Me.OLEPrincipal Financial.Visib le = False
Me.OLEThreeVal. Visible = False
Me.OLEFourVal.V isible = True
End If
>
As you can see, each true value sets a different field to visible.

Maybe set all Visibles to false then SelectCase as Stuart mentioned and
set to True as needed.

**ManningFan** · Jun 27 '08, 07:25 PM

Re: Form field names as variables?

I got it...

All I needed to do was replace
Me.OLED.Value = False

with
Me.Controls(OLE D).Value = False

**hbinc** · Jun 27 '08, 07:25 PM

Re: Form field names as variables?

On May 27, 11:06 pm, ManningFan <manning...@gma il.comwrote:

I got it...
>
All I needed to do was replace
Me.OLED.Value = False
>
with
Me.Controls(OLE D).Value = False

Shorter: Me(OLED) = False

**Albert D. Kallal** · Jun 27 '08, 07:25 PM

Re: Form field names as variables?

Build a table with your valeus...

dim rstC as dao.RecordSet
dim strSql as string

strSql = "select * from myTable where id = " & me.combo5
set rstC = currentdb.OpenR eocrdSet(strSql )

with rstc do
if rstc.ReocrdCoun t 0 then
Me.OLEOneVal.Vi sible = !OneValue
Me.OLETwoVal.Vi sible = !TwoValue
Me.OLEThreeVal. Visible = !ThreeValue
Me.OLEFourVal.V isible = !FourValue
end if
end if
rstc.Close

just create a table with the pk of the value..and then add the 4 fieelds.
You be able to add new values over time without having to modify the code...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

Form field names as variables?

Form field names as variables?

Comment

Comment

Comment

Comment

Comment

Comment