How to rename controls to give number consistency for a loop

[chrisjames25]

HI

I have been working on a form and am trying to create a form that has 200 textboxes with an associated label and another textbox alongside it.

As i add these to the form at present i am manually chaging the names to be what i require as what i want is the first 200 textboxes to be called Text1-200
the labels to be Lbl1-200 and the other textbox to be called Txt1-200

Obviously this is very painful so i thought i should just check there is no shortcut to do this renaming process.

I know normally controls should be named with more menaing but i plan to use these with a loop to perform various actions so need the simplicity of names

Cheers

 

[CJ_London]

you can use createcontrol to create controls on your form and name them accordingly

see this link

https://msdn.microsoft.com/en-us/vb...rol-method-access?f=255&MSPPError=-2147217396

 

[Mark_]

ChrisJames25,

Does your underlying record have 400 fields? Just wondering why you need so many text boxes.

IIRC, there is a limit to the number of controls on a form and I believe it is not high. If you've had to delete some controls previously you may run into issues.

 

[Minty]

The control number limit I seem to remember is around 700 (712 rings a bell)

 

[MarkK]

Obviously this is very painful so i thought i should just check there is no shortcut to do this renaming process.

You can create a form and add controls to it programmatically. Check out the CreateForm and CreateControl methods of the Access.Application object.
Cheers,
Mark

 

[chrisjames25]

Thanks for respsones. Reason why so many responses is form a view recent threeads on continuous forms etc i have decided for what i want to achieve it will probably be better as one form that i can then use as a subform.

MINTY - quick question this fomr will fall below the number of controls you stated but when added to a paretn form wont. Does a subform count as one control in the parent or do all the controls in a subform count?

Basically my concept is (and this may now sound familiar) is that i overlay the label transparent on a text box and use the double click event to then input a number into the adjacent text box to replicate my exisiting stock system.

I appreciate there are easier ways of typing the order out but my pickers on site are not very computer literate and have never really used a keyboard so i have tried to make there end be completely reliant on a mouse.

Image attached of form trying to create.

I can then run a loop through to see which textboxes are not null or 0 and append them to the table with the corresponding other textbox variety value which i can get as the textbox names will corrspond,. THats the plan anyhow.

 

[Uncle Gizmo]

Image attached of form trying to create.

No Image attached !!!

 

[chrisjames25]

Doh. Now attached (hopefully)

 

[MarkK]

What is the workflow here? I think that is what matters, and what is not that clear in your posts. OK, your users are not sophisticated, but then providing them with a UI that is buggy or excessively complicated is not a service either, and what you are working on here is not going to be easy to implement in Access.

Maybe it is worth backing up a little, and describing your overall objective, and seeing if there is a simpler way to accomplish that underlying task that avoids hundreds of cryptically named controls.

hth
Mark

 

[jdraw]

What will a user do with the form shown in mix.jpg???
I agree with MarkK --a little business context would be helpful.

 

[chrisjames25]

OK i will try my best to explain. I have attached another image which shows the whole current form on different software.

THe concept is across the top you have your customers and down the side you have the products you sell.

We sell various different types of plants. When a customer orders a product they will for exmaple order a box of climbing plants and it is our discretion what climbing plants to put in. Generally 18 different varities to a box.

Orders come in at late notice and due to how many plants can fit on a trolley we get up 90 boxes at a time for each customer. We call these 90 boxes 1 mix.

SO the form as is at the moment is showing a mix for a customer. The idea is the picker double clicks whatever varieties they are going to include in that particular mix. THe counters above the mix let the employee know how manyt plants they have laready selected for that mix and how many are outstanding.

Once an order finally comes in the order is added to system and the mixs are then allocated against the orders.

In relaity it is not that complicated but putting into words isnt the simplest of htings so hopefully i have done ok.

Any follow up questions please ask. Once i get a microphone ordered i will try do a video to show you exactly what it currently does.

 

[chrisjames25]

image attached

 

[MarkK]

So it's a selection tool? And is it the case that the selected items are the ones with numbers on the right? If so, in Access, I would put the selected items in their own list as a vertical subform. For the other items, make a single vertical subform that allows selection, but not multi-columned as appears in the image. The difficulty in Access is that multi-columned display of the same level of information.

Taking those steps, if that is a workable solution, would be vastly simpler in Access than what you are talking about doing.

Mark

 

[chrisjames25]

Yep in essence it is a selection tool.

With the ability for someone to come and change the number of plants selected at any time if for example a problem or disease was found to be on the plants selected.

THe bit on the right of the image is what i believe you are suggesting i do as a subform. Am i right?

I will have a play with your suggestions and see how it comes out.

 

[MarkK]

Do everything as a subform, but yes, the list of items selected is one subform, a single vertical list as a continuous form or datasheet. Then the list of items to select also, another subform and single vertical list, but with a scroll bar, and also a substring search textbox, so a user can type a few characters, and the list is filtered to only show products that have those few characters in their name.

Making these changes in your approach vastly simplifies your investment in creating textboxes, down from like 400 to around 6 or so. You lose a couple columns in the selection tool, but you save a ton of headaches.

Mark

 

[Pat Hartman]

You would actually have more success using a report in report view. You can define it to have multiple columns and if you open it in report view, it will respond to click events so you can update a table or use it for drilling down by opening a more detailed form or report..

Minimal coding and no control limits. Will probably take less than a half hour.

 

[Mark_]

chrisjames25,

I may have a solution to your issue, but I would need to know; What is the structure of the underlying data you are working with/updating?

I.e. does one record hold a name for a product but another the quantity or are they in the same? Where does the default to decrement come from?

Also, for your end users, is the five across format best or do they need fewer columns? If more products are added does it expand vertically or horizontally?

Finally, do you need the ability to include blank spaces as in your original?

I know you are trying to replicate what is there, have you thought of ways to improve upon what they are using?