Multi Selection

[Stemdriller]

Hi All

Is there a quicker way to do the following

A form has 20 Text Boxes on, all enabled. But if a certain user logs in I want to disable 10.

The way I would currently program is something like, the On Current of the form to say something like

If User = "John Smith" then txt1.enabled = False
Or something like that but I would have to do it 20 times and there are a few other forms like this.

I'm thinking using 'Case'

Any suggestions?

Thanks

Gareth

 

[boblarson]

Put the word dAble on the tag property of the controls you want to disable. Then, also create a table to put users you want it disabled for in there, or if that would be the norm then those who should have them all. And you could use:

Dim ctl As Control
Dim rst As DAO.Recordset ' am assuming if they are in there then it should be disabled
 
Set rst = "Select * From TableNameHere Where UserID = " & Chr(34) & VBA.Environ("username") & Chr(34)
If rst.RecordCount > 0 Then
   For Each ctl In Me.Controls
       If ctl.Tag = "dAble" Then
          ctl.Disabled = True
       End If
   Next
End If
 
rst.Close
Set rst = Nothing

 

[VilaRestal]

You can also refer to controls with numbers in their names in a sort of pseudo-control array:

Dim i as integer
For i = 1 to 20
[INDENT]Me.Controls("txt" & i).Enabled = False
[/INDENT]Next i

Would disable controls txt1, txt2, txt3 ... txt20

More usefully that way, you can modify their properties based on calculations involving i:

Dim i as integer
For i = 1 to 10
[INDENT]Me.Controls("txt" & (i * 2)).Enabled = False
[/INDENT]Next i

Would disable even controls (txt2, txt4, txt6, txt8 ... txt20)

 

[boblarson]

You can also refer to controls with numbers in their names in a sort of pseudo-control array:

Dim i as integer
For i = 1 to 20
[INDENT]Me.Controls("txt" & i).Enabled = False
[/INDENT]Next i

Would disable controls txt1, txt2, txt3 ... txt20

More usefully that way, you can modify their properties based on calculations involving i:

Dim i as integer
For i = 1 to 10
[INDENT]Me.Controls("txt" & (i * 2)).Enabled = False
[/INDENT]Next i

Would disable even controls (txt2, txt4, txt6, txt8 ... txt20)

One major drawback to naming controls like that is that you lose friendly names which can help you so much more in other places when you are looking for something. You don't need to remember which is bound to which field when you have friendly names. I would NOT suggest naming controls like txt01, txt02, etc. unless ABSOLUTELY NECESSARY.

 

[VilaRestal]

Absolutely, but there are times when a number in the name is absolutely appropriate.

To give an example, I've recently had to make a Timesheet system. Each day is a subform showing entries for that date broken down into periods.

So, each subform is named subDay1, subDay2, subDay3 ... subDay7 for the week (to name it subDayMon would be inappropriate because some people have different start days for a week) and within each subform: controls for each period of the day (15 minute blocks) to be precise are named with a number sequence txt1, txt2 ... txt48

Code can then cycle through all 7 days and all 48 time periods without having to name each control individually, which would be a nightmare:

Dim iDay, iPeriod as Integer
For iDay = 1 to 7
[INDENT]With Me.Controls("subDay" & iDay).Form 
[INDENT]For iPeriod = 1 to 48
[INDENT]With .Controls("txt" & iPeriod)
[INDENT]If SomeFunction(iDay, iPeriod) = True Then 
[INDENT].Value = Null
.Enabled = False
[/INDENT]End If
[/INDENT]End With
[/INDENT]Next iPeriod
[/INDENT]End With
[/INDENT]Next iDay

To do it anyother way would make the code a hundred times longer.

Control arrays are extremely useful things in programming languages that allow them. That Access can 'emulate' them in this way can be a huge timesaver.

 

[boblarson]

And how about a way which you can have the same functionality but have friendly names? If you have to name something to give it the txt01 name, then why not just have a friendly name and put 01 into its tag? Then you can use the similar codeyou have with some slight modifications. I'm not saying that the example you gave is not good. But I am saying it is RARE and you can have your cake and eat it too, if you just choose to do so.

 

[VilaRestal]

Absolutely, I totally agree if the controls are of a different 'type' of field. First Name, Last Name, Date of Birth, etc then naming the controls with numbers is silly.
But if it is for the purposes of a looping through a set of controls all of the same 'type' (so would have the same descriptive name anyway but with just a number to distinguish them apart) then doing it with the controls name and telling access to do things to just those controls is a lot more efficient than looping through every control and checking its tag.
Wouldn't you have to loop through every control every pass of the iteration to find which control had that number as a tag?

For iDay = 1 to 7

For Each ctl In Me.Controls

If ctl.Tag = iDay Then ctl.Disabled = True
​

Next ctl
​

Next iDay

That would be horribly inefficent (and more code).

 

[boblarson]

That would be horribly inefficent (and more code).

With a maximum of 755 controls on a form in the form's lifetime, the time difference to scan all of them as opposed to just the ones you have named, would in no way be distinguishable. But yes, technically it isn't as efficient but it would solve a two part problem IF, as you say they have different data in them. And if not, then an array based format of naming is completely fine. For example, a Calendar form. I don't name each text box something other than txt01, txt02, txt03, etc. because it would be ludicrous.

So the ideal is to know what you have and when each makes sense. In this case, I'm throwing a guess out there that my response makes sense because it isn't necessarily just text boxes that are to be disabled. Of course the OP didn't say so we are left to speculate. And so with some more information it would probably help to determine which would be the better solution in this case. Agreed?

 

[VilaRestal]

Yes, definitely. In the case of the OP both your points are probably true: Naming the controls txt1, txt2, etc is bad practice and tags would be a quick and easy way to do it.

I would recommend people play around with the pseudo-control array though as, I find, there are plenty of times when there are sequences of fields that, at design time, you don't care or don't know what they're called (they could have captions assigned to values in records in a lookup table that's liable to change) and so naming them, for example lblTextField1, lblTextField2, etc and txtTextField1, txtTextField2 is not only fine but all you can do and allows you to loop through them and process their properties based on this 'index'.

There are many situations I've found that extremely useful and the code is very neat, compact and reusable, which can quickly become more advantages than having descriptive names for controls.

It's not as good as proper control arrays as per VB 6 (a feature removed in VB.NET for some bizarre reason known only to Microsoft) as every control still has its own individual set of events but that can be reduced to a one line call to a generic 'handler' with the 'index' of the control passed as the argument.

 

[Stemdriller]

Bob

Thanks for the code example, it looks just the job and I can now use your procedure elsewhere in the database too, saving me loads of time.

However, can't seem to get the code right on the 'Set rst' line below, keeps coming up Object required. I've tried various alternatives but alas.

The table is tblUsers, of which a field is called Group, and each user is either a 'user' or 'administrator'

Private Sub Form_Current()
Dim ctl As Control
Dim rst As DAO.Recordset ' am assuming if they are in there then it should be disabled

Set rst = "Select tblUsers where Group = " & Chr(34) & VBA.Environ("user")
If rst.RecordCount > 0 Then
For Each ctl In Me.Controls
If ctl.Tag = "dAble" Then
ctl.Disabled = True
End If
Next
End If


rst.Close
Set rst = Nothing

End Sub

VilaRestal - Thanks also for your input, when I stated txt1 that was only an example, each field has a unique name with no numbers in.

Regards
Gareth

 

[gemma-the-husky]

Set rst = "Select tblUsers where Group = " & Chr(34) & VBA.Environ("username")

note that Bob said username, not user.
Also note that "group" may or may not be correct for you.

environ("username") returns the windows login name for the current user.

 

[Stemdriller]

Set rst = "Select tblUsers where Group = " & Chr(34) & VBA.Environ("username")

note that Bob said username, not user.
Also note that "group" may or may not be correct for you.

environ("username") returns the windows login name for the current user.

But in tblUsers, there is a field called 'Group', of which the options are either 'user' or 'administrator', so I figured the code was querying that.

So if the user was in the 'User' field then the controls specified could be disabled.

Thanks for responding.

GW

 

[VilaRestal]

If it's saying object required I'm guessing you don't have DAO referenced

Also, setting a recordset to a string is a new one on me. I've not tried that. The 'usual' way would be something like:

Set rst = CurrentDb.OpenRecordset("Select tblUsers where Group = " & Chr(34) & VBA.Environ("username") & Chr(34))

And maybe try User field rather than Group:

Set rst = CurrentDb.OpenRecordset("Select tblUsers where User = " & Chr(34) & VBA.Environ("username") & Chr(34))

Note, either way you also need closing quotation marks (Chr(34))

 

[Stemdriller]

If it's saying object required I'm guessing you don't have DAO referenced

Also, setting a recordset to a string is a new one on me. I've not tried that. The 'usual' way would be something like:

Set rst = CurrentDb.OpenRecordset("Select tblUsers where Group = " & Chr(34) & VBA.Environ("username") & Chr(34))

And maybe try User field rather than Group:

Set rst = CurrentDb.OpenRecordset("Select tblUsers where User = " & Chr(34) & VBA.Environ("username") & Chr(34))

Note, either way you also need closing quotation marks (Chr(34))

I don't want to use the VBA.Environ I need to use the data in tblUsers
I have amended to

Set rst = CurrentDb.OpenRecordset("Select tblUsers where Group = " & ("User"))

Getting Run-time error 3075
Syntax error (missing operator) in query expressions 'tblUsers where Group = User'

 

[gemma-the-husky]

just looking again, you actually need a closing chr(34)

Set rst = "Select tblUsers where Group = " & Chr(34) & VBA.Environ("username") & chr(34)

OK - the environ call will give you the logged in user name - in my case on my PC it shows "Dave" (generally it's what you see in the top of the box, when you click the windows "start" button).

This may or may not be what you have in the GROUP field of your table. If not, the recordset will return no items, and in Bob's example the disable loop will not be executed.

 

[VilaRestal]

OK that'll be because

A) you do need quotation marks around a string in an sql statement and B) because Group = User is nonsense (like Apple = Pear). Groups and Users are different things.
It will need to resolve to something like:
Where Group = 'Guests' (comparing Groups to a Group)
or
Where User = 'Bob' (comparing User to a User)

Depending on your requirements

But either way there must be some way of knowing who the user is?!

Is there a custom logon system? If so, presumably that makes a note of the current user's username in a global variable?

If not then how do you know who the user is except by VBA.Environ.

However you discover the current user, you would then use that as your right side of the condition (enclosed in quotation marks - its a string).

But I suspect you don't need to open a recordset at all.

Based on the OP the line would be:

If VBA.Environ("user") = "John Smith" Then

or with a global userID

If userID = "John Smith" Then

 

[Stemdriller]

OK that'll be because

A) you do need quotation marks around a string in an sql statement and B) because Group = User is nonsense (like Apple = Pear). Groups and Users are different things.
It will need to resolve to something like:
Where Group = 'Guests' (comparing Groups to a Group)
or
Where User = 'Bob' (comparing User to a User)

Depending on your requirements

But either way there must be some way of knowing who the user is?!

Is there a custom logon system? If so, presumably that makes a note of the current user's username in a global variable?

If not then how do you know who the user is except by VBA.Environ.

However you discover the current user, you would then use that as your right side of the condition (enclosed in quotation marks - its a string).

But I suspect you don't need to open a recordset at all.

Based on the OP the line would be:

If VBA.Environ("user") = "John Smith" Then

or with a global userID

If userID = "John Smith" Then

Yes there is a Custom login system, a Public Variable knows which group the user is a member of, in this case either 'Administrator' or 'User'

 

[Stemdriller]

Yes there is a Custom login system, a Public Variable knows which group the user is a member of, in this case either 'Administrator' or 'User'

OK, a degree of success

Private Sub Form_Current()
Dim ctl As Control
Dim rst As DAO.Recordset ' am assuming if they are in there then it should be disabled

'Set rst = "Select tblGroup where Group = " & Chr(34) & VBA.Environ("username") & Chr(34)
If ControlsAdmin = "User" Then
For Each ctl In Me.Controls
If ctl.Tag = "dAble" Then
ctl.???
End If
Next
End If


'rst.Close
'Set rst = Nothing

End Sub

It doesn't like the ctl.Disabled?? Any suggestions

 

[VilaRestal]

ctl.Enabled = False

 

[Stemdriller]

Yes ctl.disabled - I should've spotted that one DOH!!

Many thanks Bob, Dave and VilaRestal it's now working like I want, and saving me so so much time.

Many Many Thanks again

Gareth