Reference a field from a table value.

  • Thread starter Thread starter Lance McGonigal
  • Start date Start date
L

Lance McGonigal

Hi all...Thanks in advance for your assistance.

I have a table of codes (A1):

Rep;Code; Amount
01;A; 1.00
02;B; 2.00
01;B; 2.00
etc

And a table (B2) for consolidation of values
Rep;A;B;etc
01;0;0
02;0;0

Is there any way I can reference the correct field in B2 using a the code
from A1. I know this won't work but here is some sample code:

set a = currentdb.openrecordset("A1")
set b = currendb.openrecordset("B2")

do while not a.eof
b2.findfirst "[repno] = '" & a!repno & "'"
MyField = "B2![" & a!code & "]"
a.edit
Myfield = MyField + a!amount
a.update
a.movenext
loop

I know this is silly and a poor design but it's what the customer wants and
it's the least amount of maintenance (if it can be done) that I can think
of.

Thanks again.
 
I'm not 100% sure of what you're trying to do but that won't stop me from
opening my mouth :-)
(You did mean to "consolidate" into B, not A, right? I assumed that in the
following.)

do while not a.eof
b2.findfirst "[rep] = '" & a!rep & "'"
' * Refine: Handling of "no match" situations
B.edit
' The value of a.code determines the name of the field in B to edit
B.Fields(a.code) = B.Fields(a.code) + a!amount
B.update
B.movenext
loop

(I use '*Refine: as an easily searchable marker for "things I should try to
come back to")

If what you need to do is really this simple, consider doing this with a
Crosstab query instead. Base the query on Table A, set Rep as a RowHeading,
Code as a ColumnHeading and Amount as the Value. Then, create a separate
Update query to add the results of the Crosstab to the preexisting values in
table B for each Rep.

At the very least, using a crosstab in place of table A in your code would
give you one record per rep in both tables and cut the number of expensive
FindFirst executions in half. You would also be able to edit more than one
field at a time using the actual field names.

Hope this helps,
 
You are the man! Worked like a charm.

Thanks much.

George Nicholson said:
I'm not 100% sure of what you're trying to do but that won't stop me from
opening my mouth :-)
(You did mean to "consolidate" into B, not A, right? I assumed that in the
following.)

do while not a.eof
b2.findfirst "[rep] = '" & a!rep & "'"
' * Refine: Handling of "no match" situations
B.edit
' The value of a.code determines the name of the field in B to edit
B.Fields(a.code) = B.Fields(a.code) + a!amount
B.update
B.movenext
loop

(I use '*Refine: as an easily searchable marker for "things I should try to
come back to")

If what you need to do is really this simple, consider doing this with a
Crosstab query instead. Base the query on Table A, set Rep as a RowHeading,
Code as a ColumnHeading and Amount as the Value. Then, create a separate
Update query to add the results of the Crosstab to the preexisting values in
table B for each Rep.

At the very least, using a crosstab in place of table A in your code would
give you one record per rep in both tables and cut the number of expensive
FindFirst executions in half. You would also be able to edit more than one
field at a time using the actual field names.

Hope this helps,

--
George Nicholson

Remove 'Junk' from return address.

Lance McGonigal said:
Hi all...Thanks in advance for your assistance.

I have a table of codes (A1):

Rep;Code; Amount
01;A; 1.00
02;B; 2.00
01;B; 2.00
etc

And a table (B2) for consolidation of values
Rep;A;B;etc
01;0;0
02;0;0

Is there any way I can reference the correct field in B2 using a the code
from A1. I know this won't work but here is some sample code:

set a = currentdb.openrecordset("A1")
set b = currendb.openrecordset("B2")

do while not a.eof
b2.findfirst "[repno] = '" & a!repno & "'"
MyField = "B2![" & a!code & "]"
a.edit
Myfield = MyField + a!amount
a.update
a.movenext
loop

I know this is silly and a poor design but it's what the customer wants and
it's the least amount of maintenance (if it can be done) that I can think
of.

Thanks again.
Click to expand...
Click to expand...
 
Back
Top