How to use field's content as a field name

[David S. Calef]

I am using a table called GLAccount that consists of name, acct, date, amt

I am trying to use the field "name" contents as the name of a field to
gather data from the active form. I cannot find the correct syntax for
brackets, quotes, parantheses, etc to make it work: See my use of the
varialble ITEMSEL

Private Sub cmdLink_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open "Select * from GLAccounts"

Do Until rst.EOF

ITEMSEL = rst![Name]

rst![Amt] = Nz(me![ITEMSEL])

rst.MoveNext

Loop


End Sub


Thanks,

David

 

[Douglas J. Steele]

rst![Amt] = Nz(me.Fields(ITEMSEL))

Note that you forgot to declare ITEMSEL: you should have something like

Dim ITEMSEL As String in your routine as well.

 

[David S. Calef]

That still doesn't work.

I get a Compile error: method or data memebr not found.

I tried:

rst![Amt] = Forms![Daily Summary Input]!Fields(ItemSel)

and it still doesn't work. The fields I am reaching for are on the form
that called the subroutine.

Thanks,

David

rst![Amt] = Nz(me.Fields(ITEMSEL))

Note that you forgot to declare ITEMSEL: you should have something like

Dim ITEMSEL As String in your routine as well.

--
Doug Steele, Microsoft Access MVP

I am using a table called GLAccount that consists of name, acct, date, amt

I am trying to use the field "name" contents as the name of a field to
gather data from the active form. I cannot find the correct syntax for
brackets, quotes, parantheses, etc to make it work: See my use of the
varialble ITEMSEL

Private Sub cmdLink_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open "Select * from GLAccounts"

Do Until rst.EOF

ITEMSEL = rst![Name]

rst![Amt] = Nz(me![ITEMSEL])

rst.MoveNext

Loop


End Sub


Thanks,

David

 

[Douglas J. Steele]

Try replacing the bang (!) in front of Fields(ItemSel) with a dot (.), as I
did in the example.

--
Doug Steele, Microsoft Access MVP

That still doesn't work.

I get a Compile error: method or data memebr not found.

I tried:

rst![Amt] = Forms![Daily Summary Input]!Fields(ItemSel)

and it still doesn't work. The fields I am reaching for are on the form
that called the subroutine.

Thanks,

David

rst![Amt] = Nz(me.Fields(ITEMSEL))

Note that you forgot to declare ITEMSEL: you should have something like

Dim ITEMSEL As String in your routine as well.

--
Doug Steele, Microsoft Access MVP

I am using a table called GLAccount that consists of name, acct, date, amt

I am trying to use the field "name" contents as the name of a field to
gather data from the active form. I cannot find the correct syntax for
brackets, quotes, parantheses, etc to make it work: See my use of the
varialble ITEMSEL

Private Sub cmdLink_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open "Select * from GLAccounts"

Do Until rst.EOF

ITEMSEL = rst![Name]

rst![Amt] = Nz(me![ITEMSEL])

rst.MoveNext

Loop


End Sub


Thanks,

David

 

[David S. Calef]

Tried a dot: rst![Amt] = Forms![Daily Summary Input].Fields(ItemSel)

Tied a double dot rst![Amt] = Forms.[Daily Summary Input].Fields(ItemSel)

Tried a dot bang: rst![Amt] = Forms.[Daily Summary Input]!Fields(ItemSel)

Thought about trying a bang with a hammer!

Is the issue I need to connect to the form through the ADO method? The
reason this occurs to me is that Fields is not given as a possible choice
when you are typing the "Forms![Daily Summary Input]." and choices pop
up.... Fields is not among them.

Or I am trying to do something the hard way? 58 fields on the form are
associated with a GL account number, and need to wind up in either a table
or text file that has the GL number and the Amt for that date. This then is
used to import into an accounting package we use.

David

Try replacing the bang (!) in front of Fields(ItemSel) with a dot (.), as I
did in the example.

--
Doug Steele, Microsoft Access MVP

That still doesn't work.

I get a Compile error: method or data memebr not found.

I tried:

rst![Amt] = Forms![Daily Summary Input]!Fields(ItemSel)

and it still doesn't work. The fields I am reaching for are on the form
that called the subroutine.

Thanks,

David

rst![Amt] = Nz(me.Fields(ITEMSEL))

Note that you forgot to declare ITEMSEL: you should have something like

Dim ITEMSEL As String in your routine as well.

--
Doug Steele, Microsoft Access MVP



I am using a table called GLAccount that consists of name, acct,

date,
amt

I am trying to use the field "name" contents as the name of a field to
gather data from the active form. I cannot find the correct syntax for
brackets, quotes, parantheses, etc to make it work: See my use of the
varialble ITEMSEL

Private Sub cmdLink_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open "Select * from GLAccounts"

Do Until rst.EOF

ITEMSEL = rst![Name]

rst![Amt] = Nz(me![ITEMSEL])

rst.MoveNext

Loop


End Sub


Thanks,

David

 

[Douglas J. Steele]

D'oh! Thanks for bailing me out, Dirk!

--
Doug Steele, Microsoft Access MVP

PMFJI, but I think its the form's Controls collection you want to
reference:

Me.Controls(ItemSel)

or

Forms![Daily Summary Input].Controls(ItemSel)

(provided you have a control on the form whose name is held in ItemSel.

Failing that, I believe you could use

Me.Recordset.Fields(ItemSel)

or

Forms![Daily Summary Input].Recordset.Fields(ItemSel)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Tried a dot: rst![Amt] = Forms![Daily Summary
Input].Fields(ItemSel)

Tied a double dot rst![Amt] = Forms.[Daily Summary
Input].Fields(ItemSel)

Tried a dot bang: rst![Amt] = Forms.[Daily Summary
Input]!Fields(ItemSel)

Thought about trying a bang with a hammer!

Is the issue I need to connect to the form through the ADO method?
The reason this occurs to me is that Fields is not given as a
possible choice when you are typing the "Forms![Daily Summary
Input]." and choices pop up.... Fields is not among them.

Or I am trying to do something the hard way? 58 fields on the form
are associated with a GL account number, and need to wind up in
either a table or text file that has the GL number and the Amt for
that date. This then is used to import into an accounting package we
use.

David

Try replacing the bang (!) in front of Fields(ItemSel) with a dot
(.), as I did in the example.

--
Doug Steele, Microsoft Access MVP



That still doesn't work.

I get a Compile error: method or data memebr not found.

I tried:

rst![Amt] = Forms![Daily Summary Input]!Fields(ItemSel)

and it still doesn't work. The fields I am reaching for are on the
form that called the subroutine.

Thanks,

David



rst![Amt] = Nz(me.Fields(ITEMSEL))

Note that you forgot to declare ITEMSEL: you should have something
like

Dim ITEMSEL As String in your routine as well.

--
Doug Steele, Microsoft Access MVP



I am using a table called GLAccount that consists of name, acct,
date, amt

I am trying to use the field "name" contents as the name of a
field to gather data from the active form. I cannot find the
correct syntax for brackets, quotes, parantheses, etc to make it
work: See my use of the varialble ITEMSEL

Private Sub cmdLink_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open "Select * from GLAccounts"

Do Until rst.EOF

ITEMSEL = rst![Name]

rst![Amt] = Nz(me![ITEMSEL])

rst.MoveNext

Loop


End Sub


Thanks,

David

 

[David S. Calef]

Hi Doug:

I apologize for not saying thank you sooner.

Dirk's syntax in your last message worked great!

So Thank You!

David