I'm relatively new to VBA but learning quickly. While building a form with many fields, I came across something that is not quite an issue but bugs me because I know there is some way for it to be far more efficient.
I will present a small example. Consider the form "frmExampleSheet" which holds 10 fields (holders: H1, H2, H3, and so on). Say those 10 fields currently have data in them, and I build a simple clear button that will wipe the data.
I would have something like this:
Forms![frmExampleSheet].[H1] = ""
Forms![frmExampleSheet].[H2] = ""
Forms![frmExampleSheet].[H3] = ""
And so on...
The efficiency issue comes with this being 10 (or far more) lines long. I am looking for a way to somehow embed the counter into the number value of the field, so that I can just loop through it. Something like this:
'So this loop would clear H1, H2, H3, ..., H20
For i = 1 To 20
Forms![frmExampleSheet].[H[i]] = ""
Next i
I know this is not the correct syntax because I've tried it and it yelled at me. I have also attempted something like .[H & i] and .["H" & i], etc. There's got to be a way to do it I'm just not finding it and I'm hoping someone can give me a hand with the syntax.
Thanks for your time and consideration.
Edit [ Solved ]:
This syntax worked for me:
For i = 1 To 20
Forms!frmSetupSheet("H" & i) = NULL
Next i
This cannot be done with VBA variables. What you are really doing is dynamically building a reference to a field or control name.
I never allow empty string in fields, I prefer Null. If code is behind frmExampleSheet, can use Me alias. Use parens instead of brackets around field name construction.
Consider:
'So this loop would clear H1, H2, H3, ..., H20
For i = 1 To 20
Me("H" & i) = Null
Next i
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments