Multiple Records (CREATION)

[Pele]

Hope somebody can help with this updated post...thanks.



Pele" wrote:

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

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.

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

 

[SteveS]

Hope somebody can help with this updated post...thanks.



Pele" wrote:

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

I agree with George, but since you reposted your question, I'll give it a shot.

Things I need to know:

1) What are the field types for the fields in table HEADCOUNT?
(ie Job_Title is TEXT)

2) What are the field types for the fields in table HEADCOUNT_EXTRA?

3) what are the names of the controls (including the button) on the form
"Add_Record"?

4) Are the controls on the form "ADD_RECORD" bound or unbound?

5) Is the "ADD-RECORD" bound to table "HEADCOUNT"?

BTW, forms do not have fields. Forms have controls. Tables (queries) have fields.

 

[Pele]

Steve,

Thanks for your willingness to help. Firstly, I should mention that I'd
inherited this application designed in VBA and I am just trying to redo the
application completely in Access since that is what I am more familiar with
and this will allow better maintenance of the application. o answer your
question, I am also now using the "real" names of the fields as opposed to
the easily describable names I'd used in my posting (I was planning on
changing the names in the VBA code somebody had given me).

Anyway, below are the answers to your questions:

1) Head count table fields are:
HC_ID (autonumber, Long Integer);
Job Title (since my last post, I now have two controls to replace this one)
Labor_rate_ID (Number, Integer)
Labor_Type_cd (Number, Integer))

Sch_no (Number, Integer)
Shift_cd (Number, Integer)
hct_start_wk Week (Number, Integer)
hct_end_wk (Number, Integer)
Head_Count (Number, Integer)

Steve, the job Title is a combo box that displayed the job title but it was
the first field in the underlying combo query (Labor_rate_ID that needs to go
to the Headcount Table). Since my last post, I have also added a combo to the
Add Record form to capture the Labor_type_cd. The Schedule and Shift controls
are also combo boxes which display the second field (text) of the underlying
query, but it is the first fields (as indicated above) in this combo that
needs to go into the table (integer).

2) Field Types for Headcount_extra
HC_ID (Number, Long Integer)
Week (Number, Long Integer)
Meetings and Training ST (Number, Long Integer)
Meetings and Training OT (Number, Long Integer)
Meetings and Training DT (Number, Long Integer)

3) Names of controls on form called Add Record:
i)Job Title (Combo box, First field in underluying query is captured in
Labor_rate_ID but second text field is being displayed)
ii) Labor Type (Combo box; first field captured but second field displayed)
iii)Schedule (Combo box; first field captured but second field displayed)
iV) Shift (Combo box; first field captured but second field displayed)
v) Start Week (Text Box)
vi) End Week (text box)
vii) Head Count # (text box)
viii) ST (Text box)
iX) OT (Text box)
x) DT (Text Box)
xi) Okay (Command Button)
xii) Cancel (command Button)


4) Are the controls on the form bound or unbound:

Controls are presently bound (except for ST, OT and DT).

5) The Add Record form is presently bound to the Head count table (since
that makes the first part of the project easier).

Hope you can help. Thanks.

Pele

Hope somebody can help with this updated post...thanks.



Pele" wrote:

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

I agree with George, but since you reposted your question, I'll give it a shot.

Things I need to know:

1) What are the field types for the fields in table HEADCOUNT?
(ie Job_Title is TEXT)

2) What are the field types for the fields in table HEADCOUNT_EXTRA?

3) what are the names of the controls (including the button) on the form
"Add_Record"?

4) Are the controls on the form "ADD_RECORD" bound or unbound?

5) Is the "ADD-RECORD" bound to table "HEADCOUNT"?

BTW, forms do not have fields. Forms have controls. Tables (queries) have fields.

 

[SteveS]

Steve,

Thanks for your willingness to help. Firstly, I should mention that I'd
inherited this application designed in VBA and I am just trying to redo the
application completely in Access since that is what I am more familiar with
and this will allow better maintenance of the application. o answer your
question, I am also now using the "real" names of the fields as opposed to
the easily describable names I'd used in my posting (I was planning on
changing the names in the VBA code somebody had given me).

Anyway, below are the answers to your questions:

1) Head count table fields are:
HC_ID (autonumber, Long Integer);
Job Title (since my last post, I now have two controls to replace this one)
Labor_rate_ID (Number, Integer)
Labor_Type_cd (Number, Integer))

Sch_no (Number, Integer)
Shift_cd (Number, Integer)
hct_start_wk Week (Number, Integer)
hct_end_wk (Number, Integer)
Head_Count (Number, Integer)

Steve, the job Title is a combo box that displayed the job title but it was
the first field in the underlying combo query (Labor_rate_ID that needs to go
to the Headcount Table). Since my last post, I have also added a combo to the
Add Record form to capture the Labor_type_cd. The Schedule and Shift controls
are also combo boxes which display the second field (text) of the underlying
query, but it is the first fields (as indicated above) in this combo that
needs to go into the table (integer).

2) Field Types for Headcount_extra
HC_ID (Number, Long Integer)
Week (Number, Long Integer)
Meetings and Training ST (Number, Long Integer)
Meetings and Training OT (Number, Long Integer)
Meetings and Training DT (Number, Long Integer)

3) Names of controls on form called Add Record:
i)Job Title (Combo box, First field in underluying query is captured in
Labor_rate_ID but second text field is being displayed)
ii) Labor Type (Combo box; first field captured but second field displayed)
iii)Schedule (Combo box; first field captured but second field displayed)
iV) Shift (Combo box; first field captured but second field displayed)
v) Start Week (Text Box)
vi) End Week (text box)
vii) Head Count # (text box)
viii) ST (Text box)
iX) OT (Text box)
x) DT (Text Box)
xi) Okay (Command Button)
xii) Cancel (command Button)


4) Are the controls on the form bound or unbound:

Controls are presently bound (except for ST, OT and DT).

5) The Add Record form is presently bound to the Head count table (since
that makes the first part of the project easier).

Hope you can help. Thanks.

Pele

Pele,

I had a little problem (still) with the names of the controls on the
"ADD_RECORD" form because I wasn't sure if they were the caption or the control
names.

It is easier if you follow a naming convention. This is how I name objects:

~ No spaces in ANY names. Use camel back (HeadcountExtra) or underscores
(Headcount_Extra). It is easier when you are creating queries or writing code
if there are no spaces in names.
~ Don't use special characters in names (!@#$%^&*), underscores are OK
~ For table fields, I use a three letter prefix: intLaborRateID, lng_HC_ID. For
more info about the field, use the description field.
~ For controls on a form/report, the three letter type: combo box
cboLabor_Rate_ID, lng or an for autonumbers, lst for list boxes, txt for text
boxes, btn (or cmd) for command buttons
~ Other objects: tbl for tables, qry for queries,...

I add a suffix to the name if it is a foreign key. So in table
'Headcount_Extra', HC_ID would be HC_ID_FK. And any field where the value is
selected by a combo box:Sch_no_FK, Shift_cd_FK, ....

BTW, the first field (usually) on a combo box row source is called the 'Bound
Field', although the Bound Field can be any field.

Place the following code between **Begin Code** and **End Code ** in the Click
event of your "Okay" button. (You also need a reference set to Microsoft DAO
3.x Object Library if you are using Access 2K or higher.)

(watch for line wrap)

'***** begin Code *********

'** you will have to change "btnOkay" to the name
' of your button in 4 places
On Error GoTo Err_btnOkay_Click '(***1)
Dim rst As DAO.Recordset
Dim SW As Integer
Dim EW As Integer
Dim i As Integer
Dim varHDCT As Long

'saves the record in table "HEADCOUNT"
Me.Dirty = False

SW = Me.Start_Week 'Start_Week is the control
EW = Me.End_Week 'End_Week is the control
varHDCT = Me.HC_ID ' HC_ID is a field

' here is where you could put a message box asking
' if (EW-Sw+1) records should be created


'add records to table "HEADCOUNT_EXTRA"
Set rst = CurrentDb.OpenRecordset("HEADCOUNT_EXTRA")
With rst
For i = SW To EW
' Add new record.
.AddNew
!HC_ID = varHDCT
!Week = i ' the letter "eye", not the number one
!ST = Me.ubST 'ub = unbound
!OT = Me.ubOT
!DT = Me.ubDT
'save the record
.Update
Next
End With

'say how many records created
MsgBox EW - SW + 1 & " records added to table HEADCOUNT_EXTRA"

Exit_btnOkay_Click: '(***2)
' clean up
rst.Close
Set rst = Nothing

'close the form
DoCmd.Close acForm, Me.Name

Exit Sub

Err_btnOkay_Click: '(***3)
MsgBox Err.Description
Resume Exit_btnOkay_Click '(***4)

'***** end Code **********


Check the names in the code, I don't think I have all of them the same as you.

Also, to force form 'ADD_RECORD' to open with a new record, set "Data Entry"
under the Form Properties/Data tab to YES.

HTH

 

[Pele]

Steve,

Thanks for your help. I was away for awhile and couldn't get your solution.
I will work at implementing your solution. Thanks.

Pele

 

[Pele]

Steve,

I have tried to implement your solution as you'd laid out (see below); I
have used the correct names of the controls. But unfortuantely, when I try to
run the code it gives me an error message; it says Compile Error: Method or
data member not found. It then stops at this line

!ST = Me.ubST 'ub = unbound

Please let me know if you have any suggestion. I am so sorry to bother you
with this again.

Pele


Private Sub CmdaddHeadCountRecord_Click()
On Error GoTo Err_CmdaddHeadCounTRecord_Click
Dim rst As DAO.Recordset
Dim SW As Integer
Dim EW As Integer
Dim totalrecords As Integer
Dim i As Integer
Dim varHDCT As Long

'check that data entry is complete and correct
If [Forms]![frm_add Head Count record]![test Entry] = 1 Then
DoCmd.RunMacro "Mac_check add Head count record entry"
Stop

'saves the record in table "HEAD COUNT TBL"
Me.Dirty = False

SW = Me.Start_Week 'Start_Week is the control
EW = Me.End_Week 'End_Week is the control
varHDCT = Me.hc_id ' HC_ID is a field
totalrecords = SW + EW + 1

' here is where you could put a message box asking
' if (EW-Sw+1) records should be created


'add records to table "HEAD_COUNT_EXTRA_TIME_TBL"
Set rst = CurrentDb.OpenRecordset("HEADCOUNT_EXTRA_TIME_TBL")
With rst
For i = SW To EW
' Add new record.
.AddNew
!hc_id = varHDCT
!Week = i
!ST = Me.ubST 'ub = unbound
!OT = Me.ubOT
!DT = Me.ubDT
'save the record
.Update
Next
End With

'say how many records created
MsgBox EW - SW + 1 & " records added to table HEADCOUNT_EXTRA_TIME_TBL"

Exit_CmdaddHeadCounTRecord_Click:
' clean up
rst.Close
Set rst = Nothing

'close the form
DoCmd.Close acForm, Me.Name

Exit Sub

Err_CmdaddHeadCounTRecord_Click:
MsgBox Err.Description
Resume Exit_CmdaddHeadCounTRecord_Click


End Sub