Auto-populate multiple Fields

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

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 said:
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.
Click to expand...

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,

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
 
Mike said:
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?
Click to expand...

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]

 
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 said:
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?
Click to expand...

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]

time
the
Click to expand...

.
Click to expand...
 
Mike said:
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).
Click to expand...

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.
 
Back
Top