Access VBA - Looping with counter in variable name

Question

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] = Null
Forms![frmExampleSheet].[H2] = Null
Forms![frmExampleSheet].[H3] = Null

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, though I know this is not the correct syntax:

'So this loop would clear H1, H2, H3, ..., H20
For i = 1 To 20
    Forms![frmExampleSheet].[H[i]] = Null
Next i

I have also attempted something like .[H & i] and .["H" & i], etc. There's got to be a way to do it.

Edit #2 [ Related Inquiry ]:

Would it be possible to do something similar to the above code, but with a SQL injection? For instance, when updating a single record I use this code which works:

'Update Program Number
strSql = "UPDATE tblSheetLog " _
       & "SET tblSheetLog.[Prog_No] = '" & Me.Prog_No & "' WHERE tblSheetLog.[Part_No] = '" & Me.Part_Number & "'"
CurrentDb.Execute strSql

But when I try to combine the two formats, say if I have a lot of similar fields (consider H1-H20 again), I do this code but it does not work:

For i = 1 To 20
    strSql = "UPDATE tblSheetLog " _
           & "SET tblSheetLog("H" & i) = '" & Me("H" & i) & "' WHERE tblSheetLog.[Part_No] = '" & Me.Part_Number & "'"
    CurrentDb.Execute strSql
Next i

For reference, I have also tried:

"SET tblSheetLog.["H" & i] = '"

Is something like this possible or should I just do them one by one?

June7 · Accepted Answer · 2021-02-01T20:04:32.503

0

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

edited Feb 01 '21 at 20:04

answered Feb 01 '21 at 19:57

June7

19,874
8
24
34

Thank you for your response! I gave your suggestion a try and got a compile error "Invalid use of Me keyword." My form is just designed through Access form design tools. It is called through event code, and holds event code, but otherwise don't think there is much code behind it. – CosmicJTL Feb 01 '21 at 20:29
So where did you place this code? If it is not behind form - why not? Try full form path reference: `Forms!frmExampleSheet("H" & i)`. Both work for me. – June7 Feb 01 '21 at 21:13
In the form I have the "clear" button which calls the code from a module currently just called Module 1. Also, that worked for me. Losing the brackets from "frmExampleSheet" and using parenths instead of brackets around "H" & i. Thank you very much! I knew it was something simple I just wasn't landing on the right syntax. – CosmicJTL Feb 01 '21 at 21:21
Unless you modify Module1 code to be more generic instead of referencing a specific form, might as well put code behind form. – June7 Feb 01 '21 at 21:47
Thank you for the pointers, I will keep that in mind. I moved it behind the form. I made a second edit with a related question, if you feel up to it maybe you can help me out again. Your assistance is appreciated. – CosmicJTL Feb 05 '21 at 19:44
Should be another posting, not another question in this one. However, AFAIK, cannot do that - field cannot be dynamic within the string. Would have to be built outside the literal string and concatenated same as the parameter reference. – June7 Feb 05 '21 at 20:00
OK, thank you! My apologies, I didn't want to bloat the board but will keep in mind for next time. Take care! – CosmicJTL Feb 05 '21 at 20:11
I've never used embedded parameters, but on review, it might be possible with the field. Would still have to construct the name outside the SQL string and set the parameter. Not sure there is any real advantage. Review this about using parameters in Access VBA https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access. Try it and see what happens. – June7 Feb 06 '21 at 04:20

Access VBA - Looping with counter in variable name

1 Answers1