Form field names as variables?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ManningFan - 27 May 2008 17:13 GMT

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.Visible = True
Me.OLETwoVal.Visible = False
Me.OLEThreeVal.Visible = False
Me.OLEFourVal.Visible = False
Elseif Me.Combo5.Value = "2041" Then
Me.OLEAetnaTraditional.Visible = False
Me.OLEPrincipalFinancial.Visible = True
Me.OLEThreeVal.Visible = False
Me.OLEFourVal.Visible = False
Elseif Me.Combo5.Value = "2042" Then
Me.OLEAetnaTraditional.Visible = False
Me.OLEPrincipalFinancial.Visible = False
Me.OLEThreeVal.Visible = True
Me.OLEFourVal.Visible = False
Elseif Me.Combo5.Value = "2043" Then
Me.OLEAetnaTraditional.Visible = False
Me.OLEPrincipalFinancial.Visible = False
Me.OLEThreeVal.Visible = False
Me.OLEFourVal.Visible = 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.OpenRecordset("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.Value) Then
Me.OLED.Value = False
Else
Me.OLED.Value = True
End If
rec.MoveNext
Loop

Any ideas?

Reply to this Message

Stuart McCall - 27 May 2008 17:38 GMT

> Got a little problem here...
>
[quoted text clipped - 50 lines]
>
> 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.Visible = True
.OLETwoVal.Visible = False
.OLEThreeVal.Visible = False
.OLEFourVal.Visible = False
Case "2041"
.OLEAetnaTraditional.Visible = False
.OLEPrincipalFinancial.Visible = True
.OLEThreeVal.Visible = False
.OLEFourVal.Visible = False
Case "2042"
.OLEAetnaTraditional.Visible = False
.OLEPrincipalFinancial.Visible = False
.OLEThreeVal.Visible = True
.OLEFourVal.Visible = False
Case "2043"
.OLEAetnaTraditional.Visible = False
.OLEPrincipalFinancial.Visible = False
.OLEThreeVal.Visible = False
.OLEFourVal.Visible = True
End Select
End With

Reply to this Message

mrelam@yahoo.com - 27 May 2008 18:07 GMT

> > Got a little problem here...
>
[quoted text clipped - 85 lines]
>
> - Show quoted text -

I would use boolean expressions, like this:

Me.OLEOneVal.Visible = (Me.Combo5.Value = "2040")
Me.OLETwoVal.Visible = (Me.Combo5.Value = "2041")
Me.OLEThreeVal.Visible = (Me.Combo5.Value = "2042")
Me.OLEFourVal.Visible = (Me.Combo5.Value = "2043")

Reply to this Message

Salad - 27 May 2008 19:28 GMT

> Got a little problem here...
>
[quoted text clipped - 25 lines]
>
> 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.

Reply to this Message

ManningFan - 27 May 2008 22:06 GMT

I got it...

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

with
Me.Controls(OLED).Value = False

Reply to this Message

hbinc - 28 May 2008 09:03 GMT

> I got it...
>
[quoted text clipped - 3 lines]
> with
> Me.Controls(OLED).Value = False

Shorter: Me(OLED) = False

Reply to this Message

Albert D. Kallal - 28 May 2008 09:57 GMT

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.OpenReocrdSet(strSql)

with rstc do
if rstc.ReocrdCount > 0 then
Me.OLEOneVal.Visible = !OneValue
Me.OLETwoVal.Visible = !TwoValue
Me.OLEThreeVal.Visible = !ThreeValue
Me.OLEFourVal.Visible = !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...

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Reply to this Message