Seeing controls (1 Viewer)

kirkm · Dec 12, 2017

The following won't work, but I wonder how it should be changed to work ?

Code:

Dim ctl As Control
For Each ctl In Form_Mysubform.Controls
Debug.Print ctl.ControlType, ctl.Value, ctl.Name
Next

What I'm attempting to get working is, the form is Datasheet mode and there are a number of text boxes... I need to know if the current one is the last (in range Texbox1 to Textbox50). If I can get the above working my plan was to loop back, until the text is not "". Unless a better method exists?
Thanks.

moke123 · Dec 12, 2017

Debug.Print ctl.ControlType & "," & ctl.Value & "," & ctl.Name

not sure I understand the rest of your question. can you explain it some more.

kirkm · Dec 12, 2017

Tahnks Moke, I have sorted the print part of it now. The rest was defining which text boxes I want to read the value from.

From ctl can I get an index number for the control(s) to use ? This would be easier to work with than it's name.

moke123 · Dec 12, 2017

Whats the purpose of this exercise? I'm confused because your referring to text boxes and data sheets. I dont use or like datasheets and i dont think they work the way I'm thinking you want to use them.

Pat Hartman · Dec 12, 2017

We still have no idea what controls you want to read a value from. It is hard enough to debug code when we know what it is supposed to do. It verges on the impossible when we don't know what you expect. The code you posted looks like it is supposed to print three properties for each control of the form. You seem to have gotten past the original problem.

Please post your current code and what you want to do and what the code is doing.

kirkm · Dec 12, 2017

I haven't got any code yet. Still at the planning stage There's a bunch of text boxes, to read a value from but their names are a bit unwieldy. Is there an index available (from the name) to use in a loop?

Galaxiom · Dec 12, 2017

kirkm said:
There's a bunch of text boxes, to read a value from but their names are a bit unwieldy. Is there an index available (from the name) to use in a loop?

There are many options.

Use a naming pattern for the controls and return part of it with Left(), Right() or Mid().

Use the control's Tag property to hold any string you like.

Return their associated Label caption with

Code:

Me.controlname.Controls(0).Caption

BTW Not all controls have a Value property (Labels, boxes etc don't) so you would hit an error with your code. You need to test for the ControlType and select only those with a Value property before referring to it.

kirkm · Dec 12, 2017

Thanks Galaxion. Is there a reverse of

? Forms.MyForm.Controls(100).name

i.e I know the name and want to know the number e.g. 100 ?

arnelgp · Dec 12, 2017

you can write code on the form's Current event to test:

Private Sub Form_Current()
if Screen. ActiveControl. Name="Textbox50" then
msgbox "@ textbox50"
end if

Galaxiom · Dec 12, 2017

kirkm said:
Is there a reverse of

? Forms.MyForm.Controls(100).name

i.e I know the name and want to know the number e.g. 100 ?

Not as far as I know. It would require an incrementing loop by index through the collection comparing the Name property to the search term.

You wouldn't use it anyway because there is no control of the index by the developer. It might change if the database was compacted or the form were exported to a new database. (This is done from time to time to clear out junk.)

What you are asking to do suggests that you have structural issues in your data.

kirkm · Dec 12, 2017

I don't know what a structural issue is but I think you're having a go at me !
Screen. ActiveControl. Name may help ID the control clicked... look, as a last resort I'll try to explain what I;m after
Text box 1 to xxx exist. Some have values. TBox y is clicked and I want to know if it is the last text box in range y-xxx that contains a value.

My logic says to loop from xxx backwards, stopping at the first one <> "" - is it y or not.

It bugs me as this is pretty simple (too simple?) stuff... but I cannot see how to loop without an index and with only textbox names to iterate.

Galaxiom · Dec 12, 2017

kirkm said:
I don't know what a structural issue is but I think you're having a go at me !

You have a lot of controls and you care about which is the last one with data in it. Maybe you have a really good reason but it tickles my instincts for denormalized structures.

but I cannot see how to loop without an index and with only textbox names to iterate.

Use a naming pattern.

Name the controls mycontrol01, mycontrol02 .... etc

Code:

LastIndex = CInt(Right(Screen.ActiveControl.Name, 2))
 
For n = LastIndex To 1 Step -1
 
x = Me.Controls("mycontrol" & Format(n,"00"))
do stuff
 
Next

MarkK · Dec 12, 2017

It's still not clear to me what this thread is about. What is your purpose? Why do you need to loop thru all the controls on a form?

A very common mistake people make is to bring spreadsheet thinking to a database, and create a row with multiple values in it. Then, in order to process those multiple values, said people are tempted to write code, as you seem to be doing, to loop thru all the fields in the row. This is commonly a mistake, and it's what Galaxiom is talking about when he says, "structural issues."

I agree with Galaxiom that what you are trying to do is symptomatic of incorrect table design. You shouldn't need to loop thru all the fields. You should be doing operations on rows instead.

hth
Mark

kirkm · Dec 12, 2017

This is a spreadsheet, except it uses Access tables in datasheet mode. The reason is some actions Access does best. Then it's exported to Excel.
I inherited the control names and it would be a major job to change them.
Thanks to anegpl tip about Screen. ActiveControl as I'm getting there.

Called from text box after update event/

Code:

Dim i As Long, xx As String
For i = 194 To 1 Step -1
    xx = NoTH(i)
    If Nz(Form_Mysubform.Controls(xx)) > "" Then
        If Screen.ActiveControl.Name = xx Then
            'update calculated date, if necessary
            Exit For
        End If
    End If
Next

Noth is a function that converts the loop counter to the control name.

If Screen.ActiveControl value is the last in the row, then the date that corresponds to should be in another control. If not, it makes it so. This means as a new text box is populated that date will update automatically.

Whether this is 'structurally unsound' or some kind of mistake I would argue against. It does what it's meant to do and this is just one very small part of it.

Anyway, I appreciate the help and ideas from you all. Cheers ! (And LOL I should have known how to do this without asking!)

Pat Hartman · Dec 12, 2017

Check this very carefully. Who knows what order the controls are presented in? You may need to adjust the tab order if there are any anomalies.

Also, Please DO NOT start multiple threads on the same topic.

Seeing controls (1 Viewer)

kirkm

Registered User.

moke123

AWF VIP

kirkm

Registered User.

moke123

AWF VIP

Pat Hartman

Super Moderator

kirkm

Registered User.

Galaxiom

Super Moderator

kirkm

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Galaxiom

Super Moderator

kirkm

Registered User.

Galaxiom

Super Moderator

MarkK

bit cruncher

kirkm

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread