Full Version: The order of controls in a form
UtterAccess Forums > Microsoft® Access > Access Forms
I have a form of checkboxes
that I have put into order and there are more than 20
and I can see more in the future being added.
If course I put the checkboxes in alphabetical order,
and the taborder is in alphabetical order,
But when I need to check the checkboxes for checks,
it checks them in the order they were added.
For each ctrl in Control
make this happen in alphabetical order
Next ctrl

Is there a way to get that resorted?
That would be the simplest way for me to accomplish what I want
otherwise I have to do an alphabetical sort of the information for the checkboxes
and I'm no where near that level.
Sonia shrug.gif
If you really need them in some kind of order, I would suggest building a "controls array", or the closest equivalent Access offers - name them chk1, chk2, chk3.... then loop them
dim idx as long
for idx=1 to N
  debug.print me("chk" & idx).value
next idx
Jack Cowley
If you have 20 checkboxes in a table similar to this:
MyTableID (PK and auto)
then your have repeating groups in our table and your table is not propely normalized. You will need normalize your data before you go any further. There are a number of articles here at UA on Normalization so I would suggest you search the archives if you are unsure how to handle this.
These checkboxes are unique
and this is the only table that the information is in
so I pretty sure it's normalized OK.
tried to copy the checkboxes again
in the hopes that the new checkboxes would be in order
but they are in the order they are copied in.
Odon't want to alphabetically sort in VBA.
Jack Cowley
If your table has 20 checkboxes for each record I can pretty well guarantee that it is not normalized. You should have 20 records, one for each checkbox, not one record with 20 checkboxes. As an example here is how you might set up a survey with 20 questions with any number of possible answers for each question:
QuestionID (PK and auto)
AnswerID (PK and auto)
RespondentsID (PK and auto)
ResponsesID (PK and auto)
RespondentID (FK)
QuestionID (FK)
AnswerID (FK)
In the above example if there are 5 possible answsers to question 1 there will be five records (answers) in tblAnswers for that question, of which the user can select one, which will be saved in tblResponses.
I am not sure what you are doing with your 20 checkboxes so the above may or may not be a possible solution...
Thanks for trying to knead some information into my mind
(Thousands have tried...)
You are right if I only needed one checkbox checked.
And I think this is different, because sometimes I need one checkbox checked
and sometimes I need 5 checked.
Most times it's between 1 and 5
and sometimes it is none.
The answer I am looking for from those checkboxes
will show nothing
For one item
or any combination of the 20 items
including all
(which could just simply be an all choice).
So if that needs to be normalized
then I don't understand normalization.
I thought normalizing was
making sure that you don't repeat the same information
in record after record.
But it still doesn't answer my question
of can we set up which order the checkboxes are checked in VBA.
do I understand normalization correctly
or is it just in my mind?
and can we change the order the checkboxes
are checked or not?
I know what I know and I don't what I don't. confused.gif
It would help if you gave some examples of your data, but I agree with Jack, it sounds like you are not normalized. While normalization is the process of reducing redundant data its not quite that simple. As Jack mentioned it appears thast you have a repeating group. This is a situation where you have a singl data type that can have multiple values. An example of this might be color of an item of clothing. Rather then have checkboxes in each record for each color, you would have a subtable with fields for Clothing ID and Color. That appears to be the situation with your database.
That you should have is a subform on your main form, where you can select the values represented by the checkboxes you use now. Instead of checkboxes you would use a combobox where youi select a value. From that sturcute it would be very easy to sort in order and check which have been selected. You would only have records in the child table where a value exists.
Jack Cowley
My solution of a separate record for each check box is correct. If there were 500 possibilities that a person can select from then you would have a table with 500 records. Try to imagine it this way... A form where the user selects their name (or whatever) and then in a subform there is a list of 20 records, each with a check box. They select 1 or 2 or 5 or any combination of items that they want. It is a simple as that...
oes that make it easier to visualize?
PS. Doing it as I suggest will allow you to have the 20 or more records in any order that you like....
These selections that are made
are not on the main form.
The main form only gets
the selections made
on a form with checkboxes.
need to get these selections to the main form
in alphabetical order
(for the sake of order).
And I am trying to do that
on this pop-up form.
So am I normalized here?
Jack Cowley
Sonia -
You table is not normalized. You should not have the checkboxes in your table "tProductForm". You need three tables something like this:
ProductFormID (PK and auto)
ProductTypesID (PK and auto)
ProductFormTypesID (PK and auto)
ProductFormID (FK)
ProductTypesID (FK)
"tblProductTypes" will have Bar as the first record, Tube as the second, Forgings as the third, etc. You can put them in any order you like. The "tblPrductFormTypes" would be a subform on your form "fProductSelection" where you select the Bar, Tube or whatever.
I have to leave in about 10 minutes, but I will be back around noon Pacific Time and I will rework your db then, if you like... Let me know what you would like to do...
Thank you, thank you.
Oneed the time to look at what you have suggested
and see how to work it
and if it works the way I need it to.
I will let you know. Have a good (lunch)
too early there for lunch, must be breakfast!.
Jack Cowley
Drat! I had a sample db to send you but I lost it when I tried to save it. I am late so I must dash and I will send you a sample db when I return.
Jack Cowley
Sonia -
ttached is a sample db that is one way to do what you want to do. In the subform you select the types of forms that you want to go with the product selected on the main form. When the db opens you will see two items in the subform. You can add more by clicking in the empty record.
There are other ways to display the forms that are available, but this was the fastest and easiest to implement so you can see what I am talking about. Check out relationships as well as the new tables.
Thank you for doing that for me.
Ocan see what you mean now.
But I can't see this working with my happy little crew of engineers.
Because they are just as liable to input
Rods & Bars
Has they are Bars & Rods.
Then I still have to alphabetize.
And moving forward,
I have the same problem with Specification Standards (another field)
that I have with these product forms.
I think Psychologically
if they have a pop up form
and they check the boxes that apply
and that information gets concatenated into the field
everything will be as it should be.
(That is the way it is in the Excel field
in the current database they are using)
Otherwise, if they are adding to the concatenated field
by clicking on a subform bar
(which by the way would require training (some don't know to click on the sidebar)),
it takes longer
(because that requires two clicks
- one for the bar and one for the form)
and just doesn't work the way they work
(or maybe it's just the way I work)
I will have to think about this and see how to incorporate it.
Once again I thank you for taking the time
to work that out and show me what you meant.
Like I said I have another field
that the same kind of thing is happening in.
So thanks again.
Food for thought.
Jack Cowley
Sonia -
You should NOT concatenate data into a single field. All fields in a database should be atomic (only one piece of data). I did notice concatenation but I did not realize that is what you were doing and I am still not sure just what your database is supposed to do.
What I showed you with the sample db is only one way to skin the Access cat. You can have popup forms or whatever you like, but before you even go there you need to get your data normalized. I strongly suggest that you create the tables that you need and THEN you can start to think about your forms. Also, Access is not like Excel and you should not approach Access as you would an Excel spreadsheet.
If I can be of assistance with your tables just let me know, but you really need to get them right before you go any further...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.