How to use a variable to get a controls value.

Hello All,
I am attempting to use the variable "AccAllow " which is populated by
opening a recordset. I am using AccAllowed to refer to a control on
the active form (the Switchboard) but I am having difficulty with the
syntax.
AccessAllowed is a text field in the Switchboard items table. I am
trying to modify the HandleButtonClick function in the Switchboard
form. The AccessAllowed field is the name of one of many hidden text
controls on the Switchboard form. This control's value is either a 1
or 0 not "1" or "0"

Could someone please help.? I am not sure what I should be declaring
the AccAllow variable as.

Dim dbs As Database
Dim rst As Recordset
On Error GoTo HandleButtonClick_Err
Dim AccAllow As Variant

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Switchboard Items", dbOpenDynaset)

rst.FindFirst "[SwitchboardID]=" & Me![SwitchboardID] & " AND
[ItemNumber]=" & intBtn
AccAllow = "Me!" & rst![AccessAllowed]

Select Case rst![Command]

' Go to another switchboard.
Case conCmdGotoSwitchboard

If AccAllow = 1 Then
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rst!
[Argument]
Else
MsgBox "you don't have access to this function"
Exit Function
End If



Cheers Kevin

Re: How to use a variable to get a controls value.

Kev wrote:

> Hello All,
> I am attempting to use the variable "AccAllow " which is populated by
> opening a recordset. I am using AccAllowed to refer to a control on
> the active form (the Switchboard) but I am having difficulty with the
> syntax.
> AccessAllowed is a text field in the Switchboard items table. I am
> trying to modify the HandleButtonClick function in the Switchboard
> form. The AccessAllowed field is the name of one of many hidden text
> controls on the Switchboard form. This control's value is either a 1
> or 0 not "1" or "0"
>
> Could someone please help.? I am not sure what I should be declaring
> the AccAllow variable as.
>
> Dim dbs As Database
> Dim rst As Recordset
> On Error GoTo HandleButtonClick_Err
> Dim AccAllow As Variant
>
> Set dbs = CurrentDb()
> Set rst = dbs.OpenRecordset("Switchboard Items", dbOpenDynaset)
>
> rst.FindFirst "[SwitchboardID]=" & Me![SwitchboardID] & " AND
> [ItemNumber]=" & intBtn
> AccAllow = "Me!" & rst![AccessAllowed]
>
> Select Case rst![Command]
>
> ' Go to another switchboard.
> Case conCmdGotoSwitchboard
>
> If AccAllow = 1 Then
> Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rst!
> [Argument]
> Else
> MsgBox "you don't have access to this function"
> Exit Function
> End If
>
>
>
> Cheers Kevin

My recommendation would be to scrap your switchboard and make your own.
From the database window, click on forms, New/Design. Now I'd drop in
a few command buttons (then follow the wizard for the command button) to
open a form or report, change the caption, then go to the next command
button.

If you're at the stage where you are attempting to modify the
Switchboard code you are at the next stage where you can create your own
switchboard form.

LightenUp
http://www.youtube.com/watch?v=TOzpGRQACg8

Re: How to use a variable to get a controls value.

"Salad" wrote

> My recommendation would be to scrap your switchboard
> and make your own. From the database window, click on
> forms, New/Design. Now I'd drop in a few command buttons
> (then follow the wizard for the command button) to open
> a form or report, change the caption, then go to the next
> command button.
>
> If you're at the stage where you are attempting to modify
> the Switchboard code you are at the next stage where you
> can create your own switchboard form.

I heartily endorse Salad's advice... but would go even farther -- never use
the Switchboard Manager to create your application's control and navigation
features. It is a "complex solution to a simple problem" involving forms,
code, and a table that must interact together; to modify any of the three
components, you need to be a competent developer and to have put in far more
time studying the output of the Switchboard Manager than any user/developer
would put in, if they were thinking rationally. If you have not, it is easy
to interfere with the interactions, and "break" your Switchboard; even if
you have, and forget, and go back to modify with the Switchboard Manager,
your carefully-crafted code may be overwritten, or may be unexpected by the
Switchboard Manager, and cause it to not work as expected.

The simple solution, of course, (whether you are a novice end-user or an
advanced developer) is to create a Switchboard using unbound Forms with
Command Buttons, some of which may open subsequent Forms. For a simple
navigation structure, all necessary code will be generated by the Command
Button Wizard; for a more complex navigation structure, the Controls and VBA
Code or Macros you have to supply are still far, far simpler than the
(flaming) hoops through which you have to jump to modify a Switchboard
Manager-created Switchboard to accomplish the same purpose.

Switchboard Manager is abbreviated "SM", which is also the abbreviation for
Sado-Masochism. Mere coincidence? I think NOT.

Larry Linson
Microsoft Office Access MVP

Re: How to use a variable to get a controls value.

OK thanks for this Salad and Larry,

If I do create my own switchboard and if I wanted to use a variable to
use in a Me! statement to

refer to a control on said form. How would I do this? My syntax isn't
working. This was my main

question here.

Discussion:

I customised the Switchboard Form and SwitchBoard Items table as I
didn't think about an

alternative - I just went ahead but you are probably right, I should
have started afresh.
I feel that what I have done is working well for me. It doesn't seem
that bad to keep going - but

I take your points. I never use the Switchboard Manager wizard. I do
all my changes with the form

and table directly.

I have my back end in SQLServer on a separate server and my Front end
on a shared network drive.

Many of my forms require information on the users database access
rights ( which forms they can

open / view) and department rights (when a form is opened, which
departments can they view). I

thought instead of keeping this information in a table on the shared
server and having to pull

this across each time, I would keep their access and department
information in hidden controls

(text boxes) on the switchboard form - Clunky huh!! This is done as
part of the log on process.
Each form or switchboard has an access code assigned via the
AccessAllowed field in the

Switchboard Items table. This code corresponds to one of the hidden
text boxes on the SB form.

When the user clicks a control on the form to enter the form or SB,
not only does the recordset get the command info but the AccessAllowed
field as well. I was wanting to use this AccessAllowed code to get the
corresponding hidden controls' value from the SB form.

This sounds so 'long way around' now that I have written it in black
and (well black not white).

Is there an easier way to achieve this?

Re: How to use a variable to get a controls value.

For anyone interested, the solution was simple - Bobby Heid came up
with the answer

I needed to replace this line

AccAllow = "Me!" & rst![AccessAllowed]


with this line

AccAllow = me(rst![AccessAllowed])

Regards
Kevin