All,

I am trying to find a way in Access VBA, etc., to perform
the following;

I have 10 fields. They are matched in pairs such as
flda1 fldb1
flda2 fldb2
flda3 fldb3
flda4 fldb4
flda5 fldb5

What I want to do is on exit of the flda's is to copy the
information to the fldb's (actually the final application
will be dlookups and there will be 2 additional fields per
group). I do not want to create the whole step for each
group. Instead, I would like to create a common procedure
and call this procedure on exit from each flda. So I need
to be able to identify the correct flda and b each time the
procedure is called.

Any thoughts on how this may be done?

TIA

Mike

Mike wrote:
>I am trying to find a way in Access VBA, etc., to perform
>the following;
>
>I have 10 fields. They are matched in pairs such as
>flda1 fldb1
>flda2 fldb2
>flda3 fldb3
>flda4 fldb4
>flda5 fldb5
>
>What I want to do is on exit of the flda's is to copy the
>information to the fldb's (actually the final application
>will be dlookups and there will be 2 additional fields per
>group). I do not want to create the whole step for each
>group. Instead, I would like to create a common procedure
>and call this procedure on exit from each flda. So I need
>to be able to identify the correct flda and b each time the
> procedure is called.

This is a little confusing, fields are columns in a
table/query, the things on forms and reports are controls.
I'm assuming you mean that you have controls on a form that
are bound to fields with the same name in a table.

There are lots of ways to do what you ask. One simple way
is to use the fldax control's Tag property to provide the
corresponding fldbx's name. Then pass the two control
objects as arguments to your procedure:

theprocedure Me.flda1, Me(Me.flda1.Tag)

Public Sub theprocedure(A As Control, B As Control)

B.Value = A.Value
End Sub

But, hold on here, this flda1, flda2, ... and copying
values sounds like a pretty unusual arrrangement that is
probably violating more than one rule of data normalization.
I.e. repeating columns are a no-no, they should be in a
separate table along with a foreign key back to the main
table. You may have a good reason for duplicating the value
in one field into another field (e.g. original and revised
values), but be aware that you almost never store a value
that can be derived from another value.

--
Marsh
MVP [MS Access]

Marsh,

thanks for the reply and info. Yes I meant control. I
use field/control interchangeably as a field is also
something that is populated.

While you may be right in the repeating columns, etc., my
real application will require dlookups using the
information in flda as the key to look up values and
populate fldb in each row. And in my application, there
will be 4 columns per row (flda - fldd) to populate and 10
rows (flda1 - flda10). And not all rows will have
values. So instead of repeating 3 dlookup commands 10
times, I wanted to make a call to a procedure that would
have 3 dlookup statements. So will your suggestion still
apply for that?

thanks again.

Mike
>-----Original Message-----
>Mike wrote:
>>I am trying to find a way in Access VBA, etc., to
perform
>>the following;
>>
>>I have 10 fields. They are matched in pairs such as
>>flda1 fldb1
>>flda2 fldb2
>>flda3 fldb3
>>flda4 fldb4
>>flda5 fldb5
>>
>>What I want to do is on exit of the flda's is to copy
the
>>information to the fldb's (actually the final
application
>>will be dlookups and there will be 2 additional fields
per
>>group). I do not want to create the whole step for each
>>group. Instead, I would like to create a common
procedure
>>and call this procedure on exit from each flda. So I
need
>>to be able to identify the correct flda and b each time
the
>> procedure is called.
>
>This is a little confusing, fields are columns in a
>table/query, the things on forms and reports are controls.
>I'm assuming you mean that you have controls on a form
that
>are bound to fields with the same name in a table.
>
>There are lots of ways to do what you ask. One simple way
>is to use the fldax control's Tag property to provide the
>corresponding fldbx's name. Then pass the two control
>objects as arguments to your procedure:
>
> theprocedure Me.flda1, Me(Me.flda1.Tag)
>
>Public Sub theprocedure(A As Control, B As Control)
>
> B.Value = A.Value
>End Sub
>
>But, hold on here, this flda1, flda2, ... and copying
>values sounds like a pretty unusual arrrangement that is
>probably violating more than one rule of data
normalization.
>I.e. repeating columns are a no-no, they should be in a
>separate table along with a foreign key back to the main
>table. You may have a good reason for duplicating the
value
>in one field into another field (e.g. original and revised
>values), but be aware that you almost never store a value
>that can be derived from another value.
>
>--
>Marsh
>MVP [MS Access]
>.
>

Mike wrote:

>Marsh,
>
>thanks for the reply and info. Yes I meant control. I
>use field/control interchangeably as a field is also
>something that is populated.
>
>While you may be right in the repeating columns, etc., my
>real application will require dlookups using the
>information in flda as the key to look up values and
>populate fldb in each row. And in my application, there
>will be 4 columns per row (flda - fldd) to populate and 10
>rows (flda1 - flda10). And not all rows will have
>values. So instead of repeating 3 dlookup commands 10
>times, I wanted to make a call to a procedure that would
>have 3 dlookup statements. So will your suggestion still
>apply for that?

I think I'm getting lost here. When you say "rows" do you
mean records in a table or something else? The use of flda2
through flda10 tends to make me think you mean something
alse??

If these are all controls on a (unbound?) form, then you can
take advantage of the uniform naming convention that you're
using (forget the Tag property stuff in my previous post)
and just pass the row number to the procedure:

theprocedure 1

Public Sub theprocedure(R As Integer)
Me("fldb" & R) = DLookup("fieldx","table", _
"fieldK=" & Me("flda" & R))
Me("fldc" & R) = DLookup("fieldy","table", _
"fieldK=" & Me("flda" & R))
Me("fldc" & R) = DLookup("fieldz","table", _
"fieldK=" & Me("flda" & R))
End Sub

but I'm not at all sure that's what you're trying to do.
--
Marsh
MVP [MS Access]


>>-----Original Message-----
>>Mike wrote:
>>>I am trying to find a way in Access VBA, etc., to
>perform
>>>the following;
>>>
>>>I have 10 fields. They are matched in pairs such as
>>>flda1 fldb1
>>>flda2 fldb2
>>>flda3 fldb3
>>>flda4 fldb4
>>>flda5 fldb5
>>>
>>>What I want to do is on exit of the flda's is to copy
>the
>>>information to the fldb's (actually the final
>application
>>>will be dlookups and there will be 2 additional fields
>per
>>>group). I do not want to create the whole step for each
>>>group. Instead, I would like to create a common
>procedure
>>>and call this procedure on exit from each flda. So I
>need
>>>to be able to identify the correct flda and b each time
>the
>>> procedure is called.
>>
>>This is a little confusing, fields are columns in a
>>table/query, the things on forms and reports are controls.
>>I'm assuming you mean that you have controls on a form
>that
>>are bound to fields with the same name in a table.
>>
>>There are lots of ways to do what you ask. One simple way
>>is to use the fldax control's Tag property to provide the
>>corresponding fldbx's name. Then pass the two control
>>objects as arguments to your procedure:
>>
>> theprocedure Me.flda1, Me(Me.flda1.Tag)
>>
>>Public Sub theprocedure(A As Control, B As Control)
>>
>> B.Value = A.Value
>>End Sub
>>
>>But, hold on here, this flda1, flda2, ... and copying
>>values sounds like a pretty unusual arrrangement that is
>>probably violating more than one rule of data
>normalization.
>>I.e. repeating columns are a no-no, they should be in a
>>separate table along with a foreign key back to the main
>>table. You may have a good reason for duplicating the
>value
>>in one field into another field (e.g. original and revised
>>values), but be aware that you almost never store a value
>>that can be derived from another value.

March,

We are very close. Let me fully explain.

This is a form (I am not sure if it matters whether it is
bound or not, but we can say it is for this). It is going
to be used for identifying model types and features based
on a serial number entered. There will be 30 controls on
this form that are grouped as follows; sn1 - sn10, mod1 -
mod10 and feat1 - feat 10. When you enter the serial
number in sn1, on exit, I need to perform a dlookup using
sn1 as the key to get the mod1 and feat1. On exit of sn2,
I need to do a dlookup using sn2 as the key to get the
mod2 and feat2, and so on through all 10 serial number
fields. So the procedure has to take in to account the
key fields (sn1-sn10) and the others (mod1-mod10 and
feat1-feat10).

Thanks

Mike
>-----Original Message-----
>Mike wrote:
>
>>Marsh,
>>
>>thanks for the reply and info. Yes I meant control. I
>>use field/control interchangeably as a field is also
>>something that is populated.
>>
>>While you may be right in the repeating columns, etc.,
my
>>real application will require dlookups using the
>>information in flda as the key to look up values and
>>populate fldb in each row. And in my application, there
>>will be 4 columns per row (flda - fldd) to populate and
10
>>rows (flda1 - flda10). And not all rows will have
>>values. So instead of repeating 3 dlookup commands 10
>>times, I wanted to make a call to a procedure that would
>>have 3 dlookup statements. So will your suggestion
still
>>apply for that?
>
>I think I'm getting lost here. When you say "rows" do you
>mean records in a table or something else? The use of
flda2
>through flda10 tends to make me think you mean something
>alse??
>
>If these are all controls on a (unbound?) form, then you
can
>take advantage of the uniform naming convention that
you're
>using (forget the Tag property stuff in my previous post)
>and just pass the row number to the procedure:
>
> theprocedure 1
>
>Public Sub theprocedure(R As Integer)
> Me("fldb" & R) = DLookup("fieldx","table", _
> "fieldK=" & Me("flda" & R))
> Me("fldc" & R) = DLookup("fieldy","table", _
> "fieldK=" & Me("flda" & R))
> Me("fldc" & R) = DLookup("fieldz","table", _
> "fieldK=" & Me("flda" & R))
>End Sub
>
>but I'm not at all sure that's what you're trying to do.
>--
>Marsh
>MVP [MS Access]
>
>
>>>-----Original Message-----
>>>Mike wrote:
>>>>I am trying to find a way in Access VBA, etc., to
>>perform
>>>>the following;
>>>>
>>>>I have 10 fields. They are matched in pairs such as
>>>>flda1 fldb1
>>>>flda2 fldb2
>>>>flda3 fldb3
>>>>flda4 fldb4
>>>>flda5 fldb5
>>>>
>>>>What I want to do is on exit of the flda's is to copy
>>the
>>>>information to the fldb's (actually the final
>>application
>>>>will be dlookups and there will be 2 additional fields
>>per
>>>>group). I do not want to create the whole step for
each
>>>>group. Instead, I would like to create a common
>>procedure
>>>>and call this procedure on exit from each flda. So I
>>need
>>>>to be able to identify the correct flda and b each
time
>>the
>>>> procedure is called.
>>>
>>>This is a little confusing, fields are columns in a
>>>table/query, the things on forms and reports are
controls.
>>>I'm assuming you mean that you have controls on a form
>>that
>>>are bound to fields with the same name in a table.
>>>
>>>There are lots of ways to do what you ask. One simple
way
>>>is to use the fldax control's Tag property to provide
the
>>>corresponding fldbx's name. Then pass the two control
>>>objects as arguments to your procedure:
>>>
>>> theprocedure Me.flda1, Me(Me.flda1.Tag)
>>>
>>>Public Sub theprocedure(A As Control, B As Control)
>>>
>>> B.Value = A.Value
>>>End Sub
>>>
>>>But, hold on here, this flda1, flda2, ... and copying
>>>values sounds like a pretty unusual arrrangement that is
>>>probably violating more than one rule of data
>>normalization.
>>>I.e. repeating columns are a no-no, they should be in a
>>>separate table along with a foreign key back to the main
>>>table. You may have a good reason for duplicating the
>>value
>>>in one field into another field (e.g. original and
revised
>>>values), but be aware that you almost never store a
value
>>>that can be derived from another value.
>
>.
>

Mike wrote:
>This is a form (I am not sure if it matters whether it is
>bound or not, but we can say it is for this). It is going
>to be used for identifying model types and features based
>on a serial number entered. There will be 30 controls on
>this form that are grouped as follows; sn1 - sn10, mod1 -
>mod10 and feat1 - feat 10. When you enter the serial
>number in sn1, on exit, I need to perform a dlookup using
>sn1 as the key to get the mod1 and feat1. On exit of sn2,
>I need to do a dlookup using sn2 as the key to get the
>mod2 and feat2, and so on through all 10 serial number
>fields. So the procedure has to take in to account the
>key fields (sn1-sn10) and the others (mod1-mod10 and
>feat1-feat10).

Except for the specific control and field names, I think we
already had it. I still don't know the names of the fields
in the table, but, assuming the sn field is a numeric type,
try something like this:

Public Sub theprocedure(R As Integer)
Me("mod" & R) = DLookup("modfield","table", _
"snfield=" & Me("sn" & R))
Me("feat" & R) = DLookup("featfield","table", _
"snfield=" & Me("sn" & R))
End Sub

If the sn field is a text field, then it would be:

Me("mod" & R) = DLookup("modfield","table", _
"snfield=""" & Me("sn" & R) & """")
Me("feat" & R) = DLookup("featfield","table", _
"snfield=""" & Me("sn" & R) & """")

Instead of calling the procedure in the Exit event, I
suggest that you use the AfterUpdate event of each snX text
box. I.e. the sn1 text box AfterUpdate event would be:
theprocedure 1
and for the sn2 text box:
theprocedure 2
etc.

--
Marsh
MVP [MS Access]