Access - SQL Statement that uses variables

[Guerra67]

I am new to this so I am not sure if I am asking this correctly.
I have a form that has multiple toggle buttons. I would like to be able to do the following when the toggle button is clicked:

• set the date
• set the user
• lock both fields so that the information is not lost

Because there are multiple toggle buttons I would like to be able to use variables.
Here is a sample of what i have. Hopefully someone can point me in the right direction.



Private Sub CONTRACTTgl_Click()
CONTRACTDATE = Now()
CONTRACTEMP = CurrentUser
CONTRACT = "Collected"
Me.CONTRACTDATE.Locked = True
Me.CONTRACTEMP.Locked = True
End Sub


Private Sub COPYSSTgl_Click()
COPYSSDATE = Now()
COPYSSEMP = CurrentUser
COPYSS = "Collected"
Me.COPYSSDATE.Locked = True
Me.COPYSSEMP.Locked = True
End Sub


Private Sub COPYUTILITYTgl_Click()
COPYUTILITYDATE = Now()
COPYUTILITYEMP = CurrentUser
COPYUTILITY = "Collected"
Me.COPYUTILITYDATE.Locked = True
Me.COPYUTILITYEMP.Locked = True
End Sub

[thebigthing313]

I'm not entirely sure what you mean here. Your title says SQL Statement, but none of these functions have anything to do with SQL (not in an apparent way at least).

Are you trying to condense three separate subs into one? As in, write one sub that handles CONTRACT, COPYSS, COPYUTILITY on clicks?

[Guerra67]

My apologies.
Yes, I would like to consolidate the code as much as possible as the form has 30 toggle buttons and I don't want to have to keep copying and pasting the code.

[thebigthing313]

Ok, there are a few options here, but first: for each sub, before you lock, you are setting these variables:

COPYUTILITYDATE = Now()
COPYUTILITYEMP = CurrentUser
COPYUTILITY = "Collected"

Where are these variables declared? Or rather, what are you doing with these variables?

[Guerra67]

I am trying to capture the date and time that the toggle button is clicked.
I have not declared the variables.
copyutilitydate is the field name of a text box (date)
copyutilityemp is the field name of a text box (user)
copyutility is the toggle button

[thebigthing313]

Ok so first things first, you are actually setting variables, and not the textboxes. And the toggle button can only be set to True/False, not a string value. But aside from that, here are some ideas:

1) The easiest way would be to write a common sub takes an argument and uses that to determine which fields to affect. However, this means that you would still need to write one line of code for each toggle button's On Click event.

2) You can create an event-sink class using a class module. This is more complicated, but you can use this as a reference: http://stackoverflow.com/questions/1...s-on-userforms

If you need more help implementing either idea, don't hesitate to ask.

[ssanfu]

What is supposed to happen (or what do you want to happen) if you click the toggle a 2nd time?

[June7]

A toggle button is usually used to set a Yes/No, Off/On, True/False entity such as a Yes/No type field or to set the value of an option group control.

Seems a normal command button would be adequate to just set the value of a field and then lock the field.

If you want to allow the user to unlock the fields, then toggle button would be appropriate. But if user is allowed to change the values at will, why bother locking to begin with?

The textboxes can be locked in their setup and code can still populate the fields they are bound to.

[orange]

Good question Steve. I wondered the same (mistake/changed their mind..). I'd really like to hear about the big picture - where is this being/planned to be used? Why a toggle button? Is there business description in simple terms? Is there a data model?