How do you modify a control by using a string formula?

[BrettM]

I have a need to modify a control on a form by using a string formula. I was trying to use Eval() but have now discovered that this doesn't do what I want. Here is my code...

Public Sub AllowComboEdit()
 
    frmName = Screen.ActiveForm.Name
    PublicName1 = Screen.ActiveControl.Name
    PublicName2 = (Left(PublicName1, Len(PublicName1) - 2)) & Val(Right(PublicName1, 2) + 1)
 
    If Val(Right(PublicName2, 2)) < 99 Then
        PublicVar = "Forms!" & frmName & "." & PublicName2 & ".Visible= True"
        Eval (PublicVar)
        ' end result should be Forms!Timesheet.Log102.Visible = True
    End If
 
End Sub

I am trying to see if a control (Log101) has something in it. If it does then make Log102 visible. I know this is a simple task using the "On Change" property but this is only part of what is needed to do and there are 100 controls. I need a global sub that can simply be called and do what is required with a single line entry for the "On Change" - in this case it just says "AllowComboEdit" and the task should be done.

Can anyone help me with what should be used instead of Eval()?

Regards Brett

 

[wazz]

Public Sub AllowComboEdit()
 
    frmName = Screen.ActiveForm.Name
    PublicName1 = Screen.ActiveControl.Name
    PublicName2 = (Left(PublicName1, Len(PublicName1) - 2)) & Val(Right(PublicName1, 2) + 1)
 
    If Val(Right(PublicName2, 2)) < 99 Then
        [COLOR="Red"]bln[/COLOR]PublicVar = "Forms!" & frmName & "[COLOR="Red"]![/COLOR]" & PublicName2 & ".Visible"  [COLOR="Red"]'remove = True[/COLOR]
        [COLOR="Red"]If[/COLOR] Eval (PublicVar) [COLOR="Red"]= True/False Then
        etc.[/COLOR] 
        ' end result should be Forms!Timesheet[COLOR="Red"]![/COLOR]Log102.Visible = True
    End If
 
End Sub

 

[CyberLynx]

Like this:

Forms(frmName).Controls(PublicName2).Visible = True

.

 

[wazz]

this might be better:

- get your form name as you are;
- looks like you've got your control name already (PublicName2);

Dim blnVis As Boolean
blnVis = Forms(frmName)(ctlName).Visible

then you don't need eval. just:
if Forms(frmName)(ctlName).Visible Then
...

 

[wazz]

oops. sry cyberlynx i didn't check before posting back. you beat me to it.

 

[namliam]

Having some 200 controls, that smells like something that needs rethinking/redesigning.

 

[wazz]

Having some 200 controls, that smells like something that needs rethinking/redesigning.

thought it best not to go there just yet...

btw, since we're not going there, if you trust your controls to always be in the same place, with same data, etc. maybe you could set the tab indexes in order and loop through them all?

 

[BrettM]

Whoooohhhh.... Lots of replies with this one. Thanks guys.

namliam... 200 controls??? Actually there are a max of 70 main controls on the sheet (plus some other non important ones). It is a timesheet with the ability to log time b4 morning tea, b4 lunch, b4 arvo tea, before knockoff, and for overtime. 10 controls per day x 7 days. No need to redesign anything. Hey, you assisted in getting the time control working for me. It works fantastically well now. Thanks for that.

Wazz... I am already using a 2 dimensional array to control the names. It was one of the reasons I went down the route of using generic (albeit complex) public coding so that I don't have to do lots of extra stuff in the "On Change" and "Got Focus" control options. You are correct in suggesting that this would make life easier... it really does. Not too sure about the boolean bits though. I used CyberLynx's suggestion and simply replaced the Eval(stuff) with "Forms(frmName).Controls(PublicName2).Visible = True" and it worked like a treat. I am actually setting everything to visible on the form now and just enabling data entry where applicable but the principle is still the same.

CyberLynx... Thanks for the tip. You know what it is like when your brain gets stuck in one loop and the solution is in another. Thank God for forums such as this one... and to people like all of you who are prepared to assist another programmer in the wee small hours when the rest of the world is sleeping. (Sydney time is currently 00:20).

Thanks guys, you are all fantastic and all of your scales have been weighted in your favour.

 

[namliam]

10 controls per day x 7 days.

Still smells like a redesign, atleast there is a good chance you could do all this in 1 related table with just the 10 controls/Columns.
Or even 1 related table with just 4 columns.
ID/Period/Day/Amount

 

[BrettM]

namliam,

How does "ID/Period/Day/Amount" give me Login/Logout x 5 times (possible) in one day with one table but showing a full week on the page (70 controls)? I am already using one table with data stored as (week value) to give me the start date and (log10) thru to (log 79) for the weeks login/logout times.

Doing it on a daily basis just decreases the width (to 11) but increases length (x7) of the table and there still has to be 70 controls available on the form.

Historical records must be kept for all entries.

 

[namliam]

Yes normalization means less columns but more rows... The point tho is... If you have 70 columns for 7 days... nobody works all timeframes 7 days per week, so you get empty fields.

Normalizing your DB is to prevent just that, empty fields.
Normalizing also (unfortunatly) means that a lot of times things are harder to make, like this... The ideal optimization would be to have 1 record per timeframe, to limit the recuring events.
After all it is all dependant upon the timeframe is it not??

Then again it is going to be much harder to get the forms looking nice and all, where as with just 70 controls/columns it is easy to make up the form exactly as you want it to be.

The best thing is to have it somewhere in the middle... So maybe for your case this is best. But in a regular design phase you try to prevent stuff like:
Period1
Period2
Period3
Period4
Or
Phone1
Phone2
Phone3
etc.

Stuff like that is 'not accoording to normalization' but sometimes practical for design.

Please note, I am not just trying to annoy you, just saying... well... there may be other ways that you didnt think of when designing things.

 

[BrettM]

Thanks namliam,

I do appreciate your input and find it stimulating, not critical.

The issue with this case is that I am needing to get the staff to fill in the forms online and stop using the horrible paper forms they currently use (and abuse with you don't want to know what). They also only work 5 days a week but the operation is 7 days so all staff rotate shifts. Best practice is totally out the window here. That's why I am taking the time to make a "one stop shop" for the routines and keep the individual control programming down to a bare minimum.

It is still taking a massive toll on my brain so I do appreciate every little bit of help you give .