Updating multiple checkboxes (1 Viewer)

[Gaztech]

Hi,

I've come across an odd issue that isn't clear so I'd like input from someone to point me in the right direction if possible please. It's probably easy and I've missed something fundamental!

I want to design an unbound form that has around 60 rows of three fields. At this point I'm not connecting it to a database table.

On each row, one field is a text input, the next two are checkboxes like this:

<Field1> <Checkbox1> <Checkbox2>

Assuming I have 60 rows of this, I'm trying to find an easy way to populate each set of say, <Checkbox1> with True or False (via a button) but I need to code it.

I can easily do this one by one by addressing each checkbox field explicitly and using SetFocus and <FieldName> = False (or True). It works of course but it's messy and will create a huge amount of code for the 60 rows. I don't want to do that. I want to do it in a loop.

Each <Checkbox1> is called T_Inx Where x is the row number, so Row1 it's called T_In1, Row2 it's called T_In2 etc...

I'm trying to use a loop to pass these values but I have the syntax very wrong as I'm very new to Access.

Here's the general simplified idea of what I'm trying to do:

Dim T as Integer
Dim LBL as String
' Reset the string variable
LBL = ""
T=1
' Just do 5 rows for now
For T = 1 to 5
LBL = "T_In" & T
' This pieces together the field name to reference where to put the field value
' Now see below...
Exit For

This will effectively generate a string (LBL) of "T_INx" for each iteration where x is the value of integer T. So, on each run of the loop we get a different value of x: T_In1, T_In2, T_In3, T_In4, T_In5 etc. This works ok.

Now I want to pass a value of FALSE (or TRUE) to the relevant field (checkbox) each time the loop increments.

So first pass the string LBL is "T_In1" = False, pass 2 "T_In2" = False etc etc.

Form is called just "InputForm"

I'm trying to add code similar to below into the loop to actually populate the fields on each iteration:

Forms!InputForm!LBL.SetFocus
Forms!InputForm!LBL = False

or

LBL.SetFocus
LBL = False

' Now increment the loop and do it again.

However, the Forms!InputForm!LBL.Setfocus (or the direct method) is not correct syntax as it won't pick up the string variable "LBL" and insert it into the SetFocus command as this part of the code string needs to be an absolute value.

I know I'm giving it incorrect code (that is obvious) but "logically", it's what I want to do.

So how do I do this? Is there a different piece of code I can use to pick up the string variable and update the field one by one in the loop?

It seems an obvious workflow but I can't work out what I need to do to address and pass the values... The syntax is weird.

What do you think? I hope I've explained it clearly!

 

[Gaztech]

Oh, and I'm aware that there is a NEXT missing from the loop!

 

[theDBguy]

Hi. You could try any of the following syntax:

Forms!FormName(LBL)=False
or
Me.Controls(LBL)=False
or
Me(LBL)=False

 

[Uncle Gizmo]

You might find my Blog on Nifty Access useful:-

Loop Through a Set of Controls​

Loop Through a Set of Controls - Nifty Access

Loop Through a Set of Controls A common requirement is to check a set of controls for a particular value or condition. Here, I demonstrate two methods. The technique employed is what I call the "For Each Control in Me Controls" - it's a technique where you interrogate each control "in/on" the...

www.niftyaccess.com

You can download the sample file for free from here:- https://gumroad.com/l/loopcontrols

If you would like a free copy, then please contact me by private message and I will explain how you can get a free copy.

 

[plog]

... At this point I'm not connecting it to a database table.

That's an ominous statement. Are you not trying to connect it to a table because you think this simplifies things? It may not.

Will you eventually put all the data from those checkboxes into a table? If so, the answer to this issue may be different and even simplify things.

 

[Isaac]

Loop through the Controls Collection of the Form if the type of control is a checkbox then read the tag value make sure to call the checkbox one checkbox one in the tag. then check it

 

[Gaztech]

Hi. You could try any of the following syntax:

Forms!FormName(LBL)=False
or
Me.Controls(LBL)=False
or
Me(LBL)=False

YES! It now works fine.

I don't feel quite so stupid now. Any of these seem to work just fine.

I thought I was way off. Just adding some brackets sorted the issue. BIG thanks to all (as usual) .

 

[theDBguy]

YES! It now works fine.

I don't feel quite so stupid now. Any of these seem to work just fine.

I thought I was way off. Just adding some brackets sorted the issue. BIG thanks to all (as usual) .

Hi. Glad we could assist. Good luck with your project.

 

[Pat Hartman]

I want to design an unbound form that has around 60 rows of three fields. At this point I'm not connecting it to a database table.

I hope this is just an intellectual exercise because it makes no sense when you have bound forms and queries available to you. Maybe you get paid by the hour and this method was the least efficient you could find.

I'm pretty sure this would have been easier in excel and could have been done with functions rather than code.

The other experts were very helpful and you seem to be happy. Peace be with you.

 

[gemma-the-husky]

I struggle to understand how you could have something so structured, that would be unbound.
Surely it's easier to (semi) bind the data.

This reminds ne of the Switchboard system in Access (I preferred the A97/A2003 version to be honest)

You have a table of switchboard options, allowing (say) 60 options per page
You need some structure. So you have on your form 60 option buttons, 60 text fields, 60 check boxes, called option1 thru 60, textfield1 thru 60, checkbox1 thru 60 etc, and lots of click events. The click events are coded for the field number, so option button 1 gets HandleButtonClick(1), button 2 gets HandleButtonClick(2), and so on, so there is only one button click handler.

Then there is a function in the current event of the switchboard called
FillFormOptions() that loads all of the options for the current menu page into the relevant fields on the form.

simply

for x from 1 thru 60
    me.controls("textfield" & x)=whatever
    me.controls("optionbutton" & x)=whatever
next

 

[Pat Hartman]

I used the 2003 version of the switchboard with a couple of modifications untill two years ago when I wrote my own that used a subform instead of buttons.

 

[gemma-the-husky]

I used the 2003 version of the switchboard with a couple of modifications untill two years ago when I wrote my own that used a subform instead of buttons.

I never got on with the way the switchboard changed after A2003. I modified my switchboard to have 25 buttons, 3 columns of 8 and a "return". That way, I could group options into blocks with a bit of space between them, I just couldn't see how you could do that after they changed it.

 

[Uncle Gizmo]

I used the 2003 version of the switchboard

The early version of the Microsoft switchboard builder, the VBA version as opposed to the macro version, is a fascinating piece of code.

The only problem with it, is the interface for building the switchboards, it is, shall we say not the best!

I was threatening to do it for years and a couple of years ago I did it! I build my own own interface, based on the original Microsoft Access VBA.

Nifty Switchboard Builder - Nifty Access

Nifty Switchboard Builder One of the best add-in features of Microsoft Access is the Switchboard. It's been around as long as I can remember! However it's a bitch to use, the interface is just well, bad... I have used the Switchboard many times, but I avoid the interface, I edit directly in the...

www.niftyaccess.com

Access World Forum members (AWF) are welcome to a free copy of the "Nifty Switchboard Builder".

Contact me for details of how to get a free copy.

 

[gemma-the-husky]

@Uncle Gizmo

Yes, I never used the switchboard builder tool. I just edited the switchboard items table directly. It's dead easy.

 

[Gaztech]

The early version of the Microsoft switchboard builder, the VBA version as opposed to the macro version, is a fascinating piece of code.

The only problem with it, is the interface for building the switchboards, it is, shall we say not the best!

I was threatening to do it for years and a couple of years ago I did it! I build my own own interface, based on the original Microsoft Access VBA.

Nifty Switchboard Builder - Nifty Access

Nifty Switchboard Builder One of the best add-in features of Microsoft Access is the Switchboard. It's been around as long as I can remember! However it's a bitch to use, the interface is just well, bad... I have used the Switchboard many times, but I avoid the interface, I edit directly in the...

www.niftyaccess.com

Access World Forum members (AWF) are welcome to a free copy of the "Nifty Switchboard Builder".

Contact me for details of how to get a free copy.

Hi Gizmo,
I would be very interested in your switchboard builder! It would answer quite a few questions I have about coding up my existing forms.

 

[Pat Hartman]

I forgot to post mine earlier. It also includes security tied to the switchboard.

 

[Uncle Gizmo]

Hi Gizmo,
I would be very interested in your switchboard builder!

I've sent you a PM....