Solved How to access dynamically form recordsource fields (1 Viewer)

[boerbende]

Dear readers

I have in my form a recordsource containing several columns with nearly identical names
DR01, DR02, DR03 etc. How can I use this dynamically in VBA with a counter?

So when it is a field on the form, one can use

for counter = 1 to 10
ME(“DR0” & counter)
Next counter

But what analogy can I use when the data is not a form field but only part of the recordsource?

For single columns I use
me![DR01]

But is there a trick to use the counter with this as well?
ME![ DR0 & counter] does not work.

Many thanks

Ben

 

[GPGeorge]

Dear readers

I have in my form a recordsource containing several columns with nearly identical names
DR01, DR02, DR03 etc. How can I use this dynamically in VBA with a counter?

So when it is a field on the form, one can use

for counter = 1 to 10
ME(“DR0” & counter)
Next counter

But what analogy can I use when the data is not a form field but only part of the recordsource?

For single columns I use
me![DR01]

But is there a trick to use the counter with this as well?
ME![ DR0 & counter] does not work.

Many thanks

Ben

You probably don't want to hear this, but the REAL problem is an inappropriate table design. You are looking for clever VBA to compensate for that flawed table design.

Here is a series of blog articles explaining the problem and describing how to correct it.

 

[Uncle Gizmo]

several columns with nearly identical names
DR01, DR02, DR03

This is not the recommended way to use MS Access. You are more than likely to regret the format.

See my blog which explains the world of hurt this approach can lead to....

Excel in Access:-

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

 

[The_Doc_Man]

First, I will reinforce the comments made by others. This is the wrong way to use Access. You have a normalization problem. Look up the topic of "Normalization" on this forum, or "Database Normalization" if searching the general web. Having fields with a constant prefix and distinct from each other only through a numeric suffix is a symptom of "Repeating fields" and you don't want that to stick around. Uncle G's links on this subject are top-notch.

Second, having now soundly thrashed you with a wet noodle, I believe the syntax you want is something like:

Me.RecordsetClone.Fields("DR" & Format(number, "00") )

IF you use this approach, be aware that it is unstable since the recordset clone's record position can be moved for searching and therefore is not necessarily always synchronized with your form (though it usually is).

There is another approach, equally bad, in which you place & bind all of those DR fields on the form - but make them invisible. Then Me.Controls("DR" & the formatted number) will get you the desired value, too.

 

[MajP]

There is a pretty detailed discussion why this is.

Lets Kill the Me. vs Me! sticky or lets rewrite it

This discussion moved to clean thread https://www.access-programmers.co.uk/forums/threads/bang-vs-dot-me-and-default-properties.327749/

www.access-programmers.co.uk

See discussion on when you have to use the ! operator.

If you add a recordsource to a form at design time each field is added to the controls collection even if a control is not on the form. They are like pseudo controls. So assume you have a field ID. That field is treated as a control. This is why even if the field is not on the form you can type
me.ID or me!ID

If however you do not assign a recordsouce until runtime those pseudo controls do not exist as properties.
me.ID will not compile since it is not a property

me!ID will compile
The reason is the thing following the ! is treated as a runtime index.
Me.controls ("ID") will also compile
In both cases it is not referencing a property of a form, but passing an index to the controls collection


However the ! operator does not support variables or concatenation. So you cannot do something like
dim x as string
x = "ID"
me!X
you can do
me.controls(x) (FYI this is the same as Me(X))

With all of that said the following will work, so maybe you are doing something wrong

for counter = 1 to 10
  ME.controls(“DR0” & counter)
Next counter

I tested this to be sure. I created a blank form with no recordsource or controls. I assigned the recordsource at runtime and was able to return the values of the controls using the above syntax. I added "controls" for clarity, but not needed since it is the default property of a form.
Works for me see demo (Hit OK to run)

 

[MajP]

FYI. The other responders are correct. You database is not structured correctly. Anytime fields are numbered like that I can guarantee this is a SpreadSheet design.

 

[Pat Hartman]

The expert advice may be free but it is definitely not worthless. You are making a mistake with this design. Repeating groups belong in a separate child table. Show them using a subform.

 

[boerbende]

All, thank you very much for the tips. I solved it by adding the fields in the subform + showing them to the user. It has to do with one machine producing min 1 and max 4 items simultaniously, while the products are already entered in a subform and the database need to react with SPC on averages of the products. For this specific form in my frontend I prefered this solution in stead of a subform under a subform.