I have to selectively answer this.
Any alternative solution is much appreciated as long as it's faster than my current approach (query)
Bit twiddling takes more steps than directly accessing something, particularly since you have more than 32 of the things you need to twiddle. First you have to find the container field then you have to identify the correct masking position then you have to perform the Boolean operation to get the result. Or, you could just directly access it using the native storage element of the computer's addressing hardware - the byte. That is why I am saying the speed/space trade-off here favors using extra space to make for extra speed.
Are you suggesting to save a set of 155 character of ones and zeros, each character specified to a control name and parse the string to get the permission? something like 1100101101010101 and save it as a string?
Similar to that, anyway. You would have either a set of declared constants or a lookup table so that you might have tuples <"CheckBoxMarried", 13>, <"CheckBoxMale", 2>, etc. etc. so that you can run through a memory structure and find the position of the digit for that field. Parsing would strictly be: Find the name of the control in the table, get the number, decide what to do based on
Mid$(TheString, ThePosition, 1) returning 0 or 1. And of course, if you have this lookup table then you can have a form that uses the same information so that you can decide what everyone can or can't see and can set the records up initially.
We are working hard to add users to groups and as you explained control the previliges per groups. We are talking about 20 users and since each one is responsible for a specific job, we may end up to 17 or 18 roles (groups) that makes no much difference.
Forgive me if this comes across a bit harsh, but you need to work smarter, not harder. Look for commonalities. Find those things that EVERYONE sees and don't store flags for those cases. Only store flags for the things that are variable.
It is not mathematically possible to have that much variation in 20 users unless the roles are such that your 20 people only see an average of 7.75 controls out of 155. If they see more than that number then there MUST be some overlap in what they see. Identify the common, focus on controlling the differences. You can't possibly need flags for all 155 controls unless the roles are seriously constricted.
Is it different with what I'm doing now? And why you don't think this is right either?
It is different in that the correct way for Access is to have narrow but tall structures, whereas keeping a lot of flag data in a single record is thinking wide but short. Access efficiency comes about from allowing SQL to look at things vertically.
I don't think it is right based on my previous comments about looking for similarities and differences, and only controlling the differences. My earlier comments about "role" also play into this. I don't think you can possibly need 155 flags because I don't think there is that much non-overlap possible.
In essence, if everyone really is THAT separated in function, you might do better to just give them 20 different forms with just a couple of controls on each. Trying to manage a form with a myriad of possible control choices is nightmarish. You have to reduce the complexity and that is why I said "155 records per user, 1 for each control" is also not the right approach.
You are implementing a complex design - but from a mechanical standpoint, is that design the best for your problem?
One of the incredible strengths of this forum is not that we can answer technical questions. Even some of the schlumpy forums can do that much. We have enough people to give you
ideas you hadn't thought about. I appreciate that you are new to this arena, so don't have a lot of experience from which to draw designs and ideas. But we do. Step away from the details and ask folks for approaches at the design level. THEN ask for guidelines on how to implement those designs.