Macro to auto fill fields

[Kass]

Hi. I know I can select a field in record 2 and use Ctrl+' to repeat the
data from the same field in record 1. I need a macro or something that when
I put a check in a checkbox (or some control) in record 1, the contents of 7
fields of my form in record 1 will repeat in record 2. Each time the
checkbox is checked, the data from those 7 fields will repeat in the
subsequent record leaving the other fields in the form blank. I don't want
the data to always repeat, I need it to repeat at times, that is why I want
to use a checkbox or some indicator to tell the fields to repeat in the next
record as long as the checkbox or indicator is engaged. This is just to save
data entry time instead of needing to type the data each time or use Ctrl+'
for the 7 records. Anyone have any ideas?

Thanks!

 

[tina]

if you have that many fields with identical values across multiple records,
i have to wonder if your table structure is normalized. the description
suggests that the seven fields may belong in a parent table, with the
non-repeating fields in a related child table.

hth

 

[Kass]

Tina,

Thanks for your reply. I'm not sure what you are getting at with the parent
and child tables. I will try to investigate this. In this particular
instance, I'm using the database to keep track of feedlot livestock. I may
repeat the 7 fields of data (Name, Start Date, Gender, Number of Head,
Initial Weight In...etc) for say 6 records (each record including a months
worth of feed data in a subform). I guess actually, I just need data from
the 7 fields to repeat to the next record. So say I have cattle in the lot
for 5 months (5 records)... when I start a new group of data, I type in the
new data in record 6 and it will repeat until I change it after the next set
up 5 or 6 records and so on. But, in each record, I will also have fields
that will be different over the course of the 5 or 6 records... (like Number
Head Sold, Number Head Dead, Vet Costs,... etc). So that is why I need some
fields to repeat and some not to in subsequent records. Am I making any
sense?

Basically, is there a way to tell a form field to repeat in the next record?
(not using the Default property... I need it to be more automatic within the
form.. I don't want the data entry people to need to get into design mode or
properties or anything).

Thanks for your help!

Kass

 

[tina]

well, yes there are probably several ways. there is a particular solution
that i use in a database in my job (heavy data entry). it's simple enough to
set up, let's see if i can explain it simply.

as an example, let's say you have a control on a form called txtData, which
is the first editable control in the form's Tab Order. you have another
control on the form called txtMoreData; its' place in the tab order is not
important. you want to set a "default" value in txtMoreData as you enter
each record, with the value being whatever was entered in the previous
record. add the following code to txtData's AfterUpdate event procedure, as

If IsNull(Me!txtMoreData) Then Me!txtMoreData = Me!txtMoreData.Tag

add the following code to txtData's Exit event procedure, as

On Error Resume Next
If Not IsNull(Me!txtMoreData) Then Me!txtMoreData.Tag = Me!txtMoreData

add the following code to txtMoreData's AfterUpdate event procedure, as

If Not IsNull(Me!txtMoreData) Then Me!txtMoreData.Tag = Me!txtMoreData

so when you open the form and begin entering a new record, control
txtMoreData is *not* preset. but when you enter data in txtMoreData, the
value is saved in the control's Tag property. when you begin entering data
in the *next* record, as soon as you enter data in txtData, the string
stored in txtMoreData's Tag property will be used to set the value of that
control. if/when you change the value of txtMoreData in a record, the new
value is stored in the Tag property, and that new value will be used as the
"default" entry in the next record, and on and on.

an important point here is that the code will *not* overwrite an existing
value in txtMoreData - it will only set a "default" value when the control
has a Null value.

so, yes, you can do it, but from the information you've posted, i really
recommend that you STOP before going any further in your database
development, and invest some time in learning the basics of relational
design principles. and then re-evaluate your table(s) structure. i believe
you'll find that what you really need is not the code posted above, but a
rework of your basic tables/relationships. for more information, see
http://home.att.net/~california.db/tips.html.

hth