A dynamic table name

[yhgtbfk]

I have the following code

With rst
![year] = year
![Quarter] = Quarter
![Division] = Pert

But instead of ![Division], I want it to be dynamic so it reads from what is in a string.

ie

myString = "Division"

With rst
![year] = year
![Quarter] = Quarter
![" & myString & "] = Pert

This does not work. Can anybody provide syntax that does work?

Thank you

 

[WayneRyan]

y,

I never code without knowing the names, but I think that you can:

!rst.Fields(myString) = Pert

Wayne

 

[yhgtbfk]

y,

I never code without knowing the names, but I think that you can:

!rst.Fields(myString) = Pert

Wayne

I get an error message "Item not found in this collection"

 

[WayneRyan]

y,

Didn't mean to preface it with "rst": !rst.Fields(myString)

Don't need the rst (Using With)

Is myString set to "Division"?

Is Division a field in the table?

Does !.Fields("Division") work?

Wayne

 

[yhgtbfk]

y,

Didn't mean to preface it with "rst": !rst.Fields(myString)

Don't need the rst (Using With)

Is myString set to "Division"?

Is Division a field in the table?

Does !.Fields("Division") work?

Wayne

!.Fields("Division") or !.Fields(Division)
Expected: Identifier or Bracketed expression

!Fields(Division)
"Item not found in this collection"

What I am trying to do is the following.

I have a table that is year, quarter, division, label1, label2, label3

Now I have unbound textboxes (with the same names)

When I update a textbox, it writes that value to the table, then accumulates the same value and gives the total to a parent object.

Now I figured instead of doing the same code 20 times, I would pass the control through a function and do it that way.

On Error GoTo Err_AppMeetsNo_AfterUpdate

thename = "AppMeetsNo" 'Name of the textbox, also name of the field
updatedata (thename) 'Sends that name through to the function

Exit_AppMeetsNo_AfterUpdate:
Exit Sub
Err_AppMeetsNo_AfterUpdate:
MsgBox Err.Description
Resume Exit_AppMeetsNo_AfterUpdate



Function updatedata(thename)
On Error GoTo Err_updatedata

Pert = DLookup("Pertain", "miscindicators", "division = '" & Division & "' AND year = '" & year & "' AND quarter = '" & Quarter & "'")
If IsNull(Pert) = True Then
Pert = DLookup("Pertain", "program", "program = '" & Division & "'")
End If

If IsNumeric(Me.Controls(thename)) = False And Me.Controls(thename) <> "" And IsNull(Me.Controls(thename)) = False Then
Me.Controls(thename) = DLookup(thename, "miscindicators", "[Year] = '" & year & "' AND [Quarter] = '" & Quarter & "' AND [Division] = '" & Division & "'")
End If

check = DLookup("Division", "miscindicators", "Year = '" & year & "' And Quarter ='" & Quarter & "' AND Division = '" & Division & "'")
Set rst = CurrentDb.OpenRecordset("SELECT * FROM miscindicators WHERE Year = '" & year & "' And Quarter ='" & Quarter & "' AND Division = '" & Division & "';")
With rst
If IsNull(check) = True Then
.AddNew
Else
.Edit
End If
![year] = year
![Quarter] = Quarter
![Division] = Division
!Fields(thename) = Me.Controls(thename) '!!!HELP HERE!!!
![Pertain] = Pert
.Update
End With

Do While 1 = 1
Count = DLookup("sum(" & thename & ")", "miscindicators", "Year = '" & year & "' And Quarter ='" & Quarter & "' AND Pertain = '" & Pert & "' AND isNull(AppMeetsNo) = False")
Total = DLookup("count(" & thename & ")", "miscindicators", "Year = '" & year & "' And Quarter ='" & Quarter & "' AND Pertain = '" & Pert & "' AND isNull(AppMeetsNo) = False")

check = DLookup("Division", "miscindicators", "Year = '" & year & "' And Quarter ='" & Quarter & "' AND Division = '" & Pert & "'")
Set rst = CurrentDb.OpenRecordset("SELECT * FROM miscindicators WHERE Year = '" & year & "' And Quarter ='" & Quarter & "' AND Division = '" & Pert & "';")
With rst
If IsNull(check) = True Then
.AddNew
Else
.Edit
End If
![year] = year
![Quarter] = Quarter
![Division] = Pert
!Fields(thename) = Me.Controls(thename) '!!!HELP HERE!!!
pertainto = DLookup("pertain", "program", "program = '" & Pert & "'")
![Pertain] = pertainto
.Update
End With

Pert = DLookup("Pertain", "miscindicators", "division = '" & Pert & "' AND year = '" & year & "' AND quarter = '" & Quarter & "'")
If IsNull(Pert) = True Then
Pert = DLookup("Pertain", "program", "program = '" & Pert & "'")
End If

If IsNull(Pert) = True Or Pert = "None" Then
Exit Do
End If
Loop

Exit_updatedata:
Exit Function
Err_updatedata:
MsgBox Err.Description
Resume Exit_updatedata

 

[WayneRyan]

y,

Wow! That's a lot of code.

!Fields(thename) = Me.Controls(thename) '!!!HELP HERE!!!

you need !.Fields(thename) = Me.Controls(thename)

But I can't see the reason for not using a traditional Access
form with controls bound to a table/query.

All of those recordsets, DLookups, and DSums (You need DSum instead of DLookUp(Sum(.....

You can check to see that the name you're passing is really something like "Division",
it might be Text16 (Access generated name).

Other than that, your syntax looks OK, but I'd have to see this in person
and walk through the code.

Sure seems complicated though.

Wayne

 

[The_Doc_Man]

In general, the syntax for dynamically selecting a field requires you to have some extra code to handle the unavoidable cases where the field isn't in the collection you are probing. This occurs because Access is what is called a "sparse"-design database.

The idea of collection management is that whatever you are working with at the VBA level, odds are that if you didn't define it with an explicit Dim statement or equivalent and it ain't global, then it is part of a collection. Recordsets & tabledefs & querydefs are the most likely places where you would find a field name, and in fact all three include collections of fields. (Besides also being a collection of records based on those fields.)

Syntax to reference a named member of a collection of fields is

collection-name.fields("field-name") OR

collection-name.fields(variable-name) {where the field name is the only content of the variable name, and where the variable has been trimmed with the TRIM$ function or its equivalent}

Now, let's talk just a moment. What you are doing is legal. There are even times when it might be desirable. But be aware that your code overhead for error handling needs to be air-tight, 'cause you ARE going to find records in a table, query, or recordset for which that field appears to not exist. It is a side-effect of a system for which a null field isn't stored as a field marker with a null. It isn't stored AT ALL. Admittedly, you run into this more often when manipulating collections of properties, but it can happen elsewhere.

I get an error message "Item not found in this collection"

This is EXACTLY the message you will get when you play with dynamic naming. It doesn't necessarily mean that the field wasn't defined in the table or query. It might mean that it has never been defined in THAT PARTICULAR RECORD. I.e., you just found a null for that field, and Access is notoriously picky about null fields.

 

[yhgtbfk]

y,
you need !.Fields(thename) = Me.Controls(thename)

Thank you Wayne and Doc for all of your help.

When I type the above, I get a compile error. It highlights the decimal point between the ! and the word Fields and give the message:

Expected: Identifier or Bracketed expression

Visual Basic simply has a problem with that syntax.

This is what I am trying to achieve:

Each division (or program area) has a score. And what they do is feed it up to their "parent" in a heirarchy.

ie - hours spent in training.

4 programs get the figures of 10, 15, 8 and 20.

That means the total for the whole business is 53. But it is not that easy:

What if one of the programs have 2 children?

They got 5 and 5, which equals the 10 said above.

so if you change one of the 5s to a 7:

The total for division 1 is now 12 (5 + 7). The total for the overview is now 55.

What the first part of the code does is first to add the figure into the hours spent in training for the division being edited, and second to use a loop to change the total of its parent, search for the parent of the parent and change its total etc...

Now rather than writing this series of codes 20 times, I thought I would make a function that could do it automatically regardless of what the field was.

Whether it is hours spent in training, or hours spent in meetings, the code is going to be the same, it is the name of the table in the filed that is going to be different.

The textboxes are named identical to the fields in the table. Thats why I get no errors with the code like

Me.Controls(thename) = DLookup(thename, "miscindicators", "[Year] = '" & year & "' AND [Quarter] = '" & Quarter & "' AND [Division] = '" & Division & "'")

Which basically says set the textbox with the same name as the field name to the value of the field name in the table.

But I cant get it to work for putting the value in the table.

 

[WayneRyan]

y,

What's your table structure.

You don't really want to propagate the value upwards through your tree.
What happens when you change a value? Do you cascade the delta
upwards? What is the delta?

Post your table structure. Preferably in the Table forum. There are some
highly skilled Table Design folks here. If you get that phase done, your
summation queries should be far easier.

Wayne