What am I doing wrong in this reference to the control?

[Vita]

Yes, I forgot that you were using an array and that I had mentioned that I probably would not for this.

I don't think I need it now that I have started doing it. I think I just need to set a few variables to keep track of some left positions.


Though what is your reasoning for not using an array? What method would you suggest?

[Vita]

Using a select statement seems to make it really messy. I originally wanted to do something like taking the control to the lefts left value and width and adding a number for a gap but the trouble I ran into is that the controls don't cycle in the order I want.
So instead I put that logic at the top of the for loop to run everytime. Seems to work fine but appears a bit messy. I have an idea of making an array to put the controls in the order I want but that doesn't feel like a smart idea to me, it feels like a rather tedious method.

[moke123]

Huh, Interesting. I haven't heard of or read about dictionaries. Is this a native object?

Its part of microsoft scripting runtime.

The code I posted is late bound and you do not get intellisense.
If you set a reference in your VBE to microsoft scripting runtime then it can be early bound and include intellisense.

Attachment 50410

for early bound you would use the new keyword

dim dict as dictionary

set dict = new scripting.Dictionary

The part that is getting messy is the actual positioning since different controls are different sizes and in different places. Is there perhaps a better way to do what I am doing?

I rarely need to move controls around but in one instance I created a class for a date and time picker and coded it so that the picker was positioned under the requisite textbox control. There were many
things to consider such as whether its a pop up form or regular form, header height, form border type, scroll bars, etc. There are a lot of factors involved I never thought of.

[Micron]

Maybe I've missed part of the entire goal because when I went looking for that comment here I can't find it. Methinks it was in another forum in this past week and was about visibility of textboxes.

If I was concerned about order my control names would start with the same text and end with a different number. Then I would string together the text and number in a loop. Since I'd know that this concerns x number of controls, I don't need the control count either. If I had batches to deal with, I'd probably use a batch identifier, perhaps like txtA1 to distinguish from txtB1. Those names are just an example of creating batches and don't tell you much about the control source. Such names would not be a concern in something like a custom calendar picker where 42 textboxes are all for dates and wouldn't be bound anyway.

If I was only concerned about visibility then I'd use the tag property in the controls of concern and loop over the controls, setting visibility only where that tag value is found.

[Vita]

Not always, if you are sloppy.

See this thread and the answer in page 2.
https://eileenslounge.com/viewtopic....39787&start=20

All because someone did not use Option Explicit.

so if you forget to use option explicit and forget to dimension a variable then set will default to a variant and wont actually store the object but will store the value of the object?
Does this mean that you can do something like

 Set Test = 1574

to initialize and assign in the same row of code if you don't mind it being a variant? if yes, is this only available without Option Explicit?
other wise I was correct?

Its part of microsoft scripting runtime.

The code I posted is late bound and you do not get intellisense.
If you set a reference in your VBE to microsoft scripting runtime then it can be early bound and include intellisense.

Attachment 50410

for early bound you would use the new keyword

dim dict as dictionary

set dict = new scripting.Dictionary

I rarely need to move controls around but in one instance I created a class for a date and time picker and coded it so that the picker was positioned under the requisite textbox control. There were many
things to consider such as whether its a pop up form or regular form, header height, form border type, scroll bars, etc. There are a lot of factors involved I never thought of.

Had to look up what early bound and late bound meant so thanks for teaching me that.
So it isn't actually part of a default new project. I haven't dabbled in any other references (besides DAO and ADO) because I don't really know what any of them are for.
Are there any other useful references I should look into? I'm not sure what else Microsoft Scripting Runtime includes but I suppose I will look into it now.

While I like the dictionary object (from the little bit I understood of it without reading its use page) I don't know if I want to add a whole reference just for this one thing. Perhaps if I were to use dictionary elsewhere too.

[Welshgasman]

Well MajP over at AWF reckons that a variant will subclass and still point to an object.
However I ran the same logic on my code and it did not update the record.
Only if the variable was dimmed as a field would it update the record.

SO it might be down to versions. When the O/P corrected what to me is a huge mistake, then he reckoned his code worked as he expected it to.

Try it for yourself.
Just run on a test table without Option Explicit and commenting out the fld variable Dim statement.

Sub UpdateRst()
Dim db As DAO.Database
Dim fld As DAO.Field
Dim rst As DAO.Recordset


Set db = CurrentDb
Set rst = db.OpenRecordset("Select Description from TestTransactionsDelete")


Set fld = rst!Description


Do Until rst.EOF
    rst.MoveLast
    rst.Edit
    fld = "Test Description 4"
    rst.Update
    rst.MoveNext
Loop
Set fld = Nothing
Set rst = Nothing
Set db = Nothing
End Sub

Edit: If you use the .Value property of the non dimmed variable it will then update the recordset.

[Vita]

Maybe I've missed part of the entire goal because when I went looking for that comment here I can't find it. Methinks it was in another forum in this past week and was about visibility of textboxes.

If I was concerned about order my control names would start with the same text and end with a different number. Then I would string together the text and number in a loop. Since I'd know that this concerns x number of controls, I don't need the control count either. If I had batches to deal with, I'd probably use a batch identifier, perhaps like txtA1 to distinguish from txtB1. Those names are just an example of creating batches and don't tell you much about the control source. Such names would not be a concern in something like a custom calendar picker where 42 textboxes are all for dates and wouldn't be bound anyway.

If I was only concerned about visibility then I'd use the tag property in the controls of concern and loop over the controls, setting visibility only where that tag value is found.

My goal is just to make it so controls resize with the form. I saw other people have projects that do it but I wanted to make my own and learn by doing.
At the current point I can't imagine how a modular one works since I am typing in all the control names myself. I want to try though because if I don't then I won't learn!

[moke123]

Have a look at Colins articles for some guidance https://isladogs.co.uk/automatic-for...g-1/index.html

So it isn't actually part of a default new project.

Yes and No. It's there and available, its just not checked by default.

I'm not sure what else Microsoft Scripting Runtime includes but I suppose I will look into it now.

FileSystemObject is also part of the scripting runtime.
this page says excel but it is the same for access https://analystcave.com/vba-filesyst...-fso-in-excel/

I don't know if I want to add a whole reference just for this one thing. Perhaps if I were to use dictionary elsewhere too.

It doesn't matter really if you add the reference or not. It's there for you to use. Without adding the reference you still can use it by late binding using CreateObject().
The advantage of adding the reference(early binding) is that you get intellisense. The downside is if you use early binding and someone else goes to use your code and they don't have the reference checked they will get an error. Most of the time I will develop with the reference checked so I have intellisense and after it works correctly I will switch to late binding so it will work no matter what. That's why the code I posted was late bound, so if you tried it, it would work for you no matter what.

Are there any other useful references I should look into?

Microsoft Office xx.x Object Library.




As Gasman says you should always have option explicit declared in every module.
In the VBE go to Tools > Options and on the Editor tab check off "Require Variable Declaration".
That will add option explicit to your modules by default going forward. *** You will have to add it manually to all existing modules.***

[Micron]

The downside is if you use early binding and someone else goes to use your code and they don't have the reference checked they will get an error.

Are you sure about that? AFAIK, the problem usually happens when the version is different enough that the library is not in the same place or it is altogether missing (sometimes IT doesn't do a full install). Once it's checked, it's checked for everybody - just maybe not found?

[moke123]

The downside is if you use early binding and someone else goes to use your code and they don't have the reference checked they will get an error.

Gotcha, Maybe a little misleading. Note the word code. If you were to copy and paste the sample code into a module, not open a sample database for example.

That's why the code I posted was late bound, so if you tried it, it would work for you no matter what.

Agree the scenario you posted will error too. It was a long day. I was going to go into the quirks with late binding and object variables too but I just didn't have it in me.

Edit: Timing is everything. After posting above I went to look at the new posts and this was first in the list https://www.accessforums.net/showthread.php?t=88291