Gerge,
You are very correct with your comments and I sincerely apologize for
posting such an inadequately worded question. Below is my up to date
question. I have no problem at all if you use VBA to solve this problem since
I am not even sure macros can do this.
NEW QUESTION
I have created a form (called ADD RECORD) that will be used for doing 2
things when a command button is pressed. The fields on the form are:
HC_ID (autonumber), Job Title, Schedule, Shift, Start Week, End Week, Head
Count#, ST, OT and DT
The two things the command button will do are:
1) Creating one record in one table (called HEADCOUNT) and enter values
from textboxes on the form into the table. The newly created field uses
Access autonumbering for the field called HC_ID. The fields needed by this
table are HC_ID,
Job Title, Schedule, Shift, Start Week, End Week, Head Count#.
2) The command button should also immediately create multiple records in
another table (HEADCOUNT_EXTRA).
The link between the two tables is HC_ID. That is, the button needs to use
the HC_ID created by Access in table HEADCOUNT as a field in HEADCOUNT_EXTRA.
That is, each record created will share the same HC_ID.
The table HEADCOUNT_EXTRA also has another field called WEEK which keeps
track of the number of weeks encompassed by the recordss created. The first
record will have WEEK = Start Week entered on the form and the last record
will have WEEK=End Week. Week increases from Start WEek to ENd Week.
Also, the numbers entered in ST, OT and DT on the form are then repeated for
each record created in the table called HEADCOUNT_EXTRA.
The required textboxes by this table (from the form) are HC_ID, WEEK, ST, DT
and OT. The table Headcount_extra has 5 fields.
Note that the number of multiple records required to be added to the table,
HEADCOUNT_EXTRA, will depend on the values typed in the textboxes as shown
in the equation below:
[END WEEK] - [START WEEK] +1
e.g. 52 records will be created if you evaluate 52-1+1
Week will start from 1 to 52 and HC_ID is repeated for all 52 records.
Can anybody help develop an event procedure that will do this (or tell me
macros that can do this). I only use macros in access and I am not savvy
enough to write event procedures from scratch.
Pele
George Nicholson said:
I saw your post on Friday in modulesdaovba and didn't respond because,
frankly, it didn't make sense from a table design perspective.
1) The first part of your request takes care of itself if your form is bound
to Headcount (or, more properly, a query based on Headcount).
2) The command button should also immediately create multiple records in
another table (HEADCOUNT_EXTRA). The required textboxes by this table
(from
the form) are ST, DT and OT. The table Headcount_extra has only 3 fields
ST, OT and DT.
2A) There is no connection between Headcount_Extra and Headcount ?!? Are
you sure you won't want to join these tables later on JobTitle or
something?
2B) You want to create up to 52 100% identical records?!? Are you sure you
don't want these records to include a WeekNumber (i.e., 1 to 52)? as well as
JobTitle?
Unfortunately, I don't write macros, only vba, so maybe someone else can
help you on that score, but I think you need to re-think what you are
asking, possibly starting with the basic concepts of table design and
structure in a relational database.
I generally try to answer the question a poster asks without second guessing
them too much. However, in this case, even if I told you how to create 52
duplicate records at the push of a button, with the design you've laid out I
don't see how you would possibly be able to do anything with them. That
would be a waste of time on both of our parts. Maybe someone else will see
it differently.
HTH & Good Luck,
--
George Nicholson
Remove 'Junk' from return address.
Pele said:
I have created a form (called ADD RECORD) that will be used for doing 2
things when a command button is pressed. The fields on the form are:
Job Title, Schedule, Shift, Start Week, End Week, Head Count#, ST, OT and
DT
The two things the command button will do are:
1) Creating one record in one table (called HEADCOUNT) and enter values
from
textboxes on the form into the table. The fields needed by this table are
Job Title,
Schedule, Shift, Start Week, End Week, Head Count#.
2) The command button should also immediately create multiple records in
another table (HEADCOUNT_EXTRA). The required textboxes by this table
(from
the form) are ST, DT and OT. The table Headcount_extra has only 3 fields
ST,
OT and DT.
Note that the number of multiple records required to be added to the
table,
HEADCOUNT_EXTRA, will depend on the values types in the textboxes as shown
in the equation below:
[END WEEK] - [START WEEK] +1
e.g. 52 records will be created if you evaluate 52-1+1
Can anybody help develop an event procedure that will do this (or tell me
macros that can do this). I only use macros in access and I am not savvy
enough to write event procedures from scratch.
Pele
