Autofill subform when move to new record on Main form

[babs]

below is code that I used on a different database that worked well to
automatically populate the new record based on values inprevious record but
it was only a single form:

Function AutoFillNewRecord(F As Form)

Dim rs As DAO.Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer

'On Error Resume Next

'Exit if not on the new record.
If Not F.NewRecord Then Exit Function

'Goto the last record of the form recordset(to auofill form).
Set rs = F.RecordsetClone
rs.MoveLast

'Exit if you cannot move to the last record(no records).
If Err <> 0 Then Exit Function

'Get the list of fields to autofill.
FillFields = ";" & F![AutoFillNewRecordFields] & ";"

'If there is no criteria field, then set flag indicating ALL
'fields should be autofilled.
FillAllFields = Err <> 0

F.Painting = False

'Visit each field on the form.
For Each C In F
'Fill The field if All fields are to be filled Or if the
'....ControlSource field can be found in the Fill Fields list.

If FillAllFields Or InStr(FillFields, ";" & (C.Name) & ";") > 0 Then
C = rs(C.ControlSource)
End If
Next

F.Painting = True

End Function

With the new database I have a Main form (for the Week End Date)and schedule
of jobs for that given week in the Subform. ex. possible 8 employees list
6times in subform for Mon-Sat. jobs

When navigate to new record(new week) [or Prob. should tie to a command
button- to insert records} on Main form I want to autofill the new subform
with Each persons([man]) previous record from the Fri([day]-field name).
Insert Mon-Fri for all previous sched. people -prior record and grab job
name they were on that Fri - Since sat. jobs are odd jobs and 9Xout of 10
the new week schedules is usually where they left off on Fri. -

I currently have a command button with the following code but it only
inserts the six new records based on who is selected in the man drop down
list - would like it to grab the job from the previous record - Friday.???any
ideas???

thanks,
barb

 

[Steve Sanford]

Hi Barb,

I currently have a command button with the following code but it only
inserts the six new records based on who is selected in the man drop down
list - would like it to grab the job from the previous record - Friday.???any
ideas???

There doesn't appear to be any "following code"???


If I understand correctly, if there were two workers (Perl and Earl), you
would want to:

1) look at the previous friday's schedule
2) insert 6 records (Mon - Sat) records for this week's jobs for Perl and
3) insert 6 records (Mon - Sat) records for this week's jobs for Earl


If there were 10 workers on the previous friday, you would want 60 records
entered??

 

[babs]

Sorry here is the code attached to the command button I was referring
to:-know shouldn't use date as a field but I inhereted this and it is many
places so prefer not to change

Private Sub Command5_Click()
'START
Dim dteWeedday As Date
Dim i As Integer
Dim strSQL As String

DoCmd.SetWarnings False
' run your code
For i = 1 To 6 '(Monday to Saturday)
dteWeekday = DateAdd("d", Me.txtDate, -i) 'increment sunday by i days
strSQL = "INSERT INTO [TimeCardMDJEFF] ([workdate],[date],[man
name],[actualRate]) VALUES (#" & Format(dteWeekday, "m-d-yyyy") & "#," & "#"
& Format(Me.Date, "m-d-yyyy") & "#,""" & Me.cboman.Column(0) & """,""" &
Me.cboman.Column(1) & """)"
DoCmd.RunSQL (strSQL)
Next i
'END

Me.JeffTimeCardMDJEFFSubform.Requery


If I understand correctly, if there were two workers (Perl and Earl), you

would want to:

1) look at the previous friday's schedule
2) insert 6 records (Mon - Sat) records for this week's jobs for Perl and
3) insert 6 records (Mon - Sat) records for this week's jobs for Earl

YES - you got it!!

If there were 10 workers on the previous friday, you would want 60 records
entered??

Woudl Love to have the option to Insert each employees prev. Fri job(new 6
records) AND/OR do Entire previous week schedule-All last week schedule (is
On record on main form based on week end date)- subform list who is all
scheduled for what in that week.

thanks sooo much for your help,
barb

 

[Douglas J. Steele]

From your Insert statement,it looks as though you have separate tables for
each employee ([TimeCardMDJEFF]). If so, you should merge them all into the
same table.

Also, your use of the DateAdd function is technically backwards. The syntax
is

DateAdd(interval, number, date)

so you should have:

dteWeekday = DateAdd("d", -i, Me.txtDate)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry here is the code attached to the command button I was referring
to:-know shouldn't use date as a field but I inhereted this and it is many
places so prefer not to change

Private Sub Command5_Click()
'START
Dim dteWeedday As Date
Dim i As Integer
Dim strSQL As String

DoCmd.SetWarnings False
' run your code
For i = 1 To 6 '(Monday to Saturday)
dteWeekday = DateAdd("d", Me.txtDate, -i) 'increment sunday by i days
strSQL = "INSERT INTO [TimeCardMDJEFF] ([workdate],[date],[man
name],[actualRate]) VALUES (#" & Format(dteWeekday, "m-d-yyyy") & "#," &
"#"
& Format(Me.Date, "m-d-yyyy") & "#,""" & Me.cboman.Column(0) & """,""" &
Me.cboman.Column(1) & """)"
DoCmd.RunSQL (strSQL)
Next i
'END

Me.JeffTimeCardMDJEFFSubform.Requery


If I understand correctly, if there were two workers (Perl and Earl), you

would want to:

1) look at the previous friday's schedule
2) insert 6 records (Mon - Sat) records for this week's jobs for Perl and
3) insert 6 records (Mon - Sat) records for this week's jobs for Earl

YES - you got it!!

If there were 10 workers on the previous friday, you would want 60
records
entered??

Woudl Love to have the option to Insert each employees prev. Fri job(new 6
records) AND/OR do Entire previous week schedule-All last week schedule
(is
On record on main form based on week end date)- subform list who is all
scheduled for what in that week.

thanks sooo much for your help,
barb

Hi Barb,


There doesn't appear to be any "following code"???


If I understand correctly, if there were two workers (Perl and Earl), you
would want to:

1) look at the previous friday's schedule
2) insert 6 records (Mon - Sat) records for this week's jobs for Perl and
3) insert 6 records (Mon - Sat) records for this week's jobs for Earl


If there were 10 workers on the previous friday, you would want 60
records
entered??

 

[babs]

I only have One table for the employees schedule it is the [TimeCardMDJEFF]-
It is grabbing items selected FOR NOW of the combo man box in the Main form
and inserting it into the new records for that given week.
For some reason the DateAdd is working
All tied to the command button is working

see previous post - just want it be able to grab what job they were on the
previous week .......... see above post for more explanation

thanks sooo much,
barb

From your Insert statement,it looks as though you have separate tables for
each employee ([TimeCardMDJEFF]). If so, you should merge them all into the
same table.

Also, your use of the DateAdd function is technically backwards. The syntax
is

DateAdd(interval, number, date)

so you should have:

dteWeekday = DateAdd("d", -i, Me.txtDate)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry here is the code attached to the command button I was referring
to:-know shouldn't use date as a field but I inhereted this and it is many
places so prefer not to change

Private Sub Command5_Click()
'START
Dim dteWeedday As Date
Dim i As Integer
Dim strSQL As String

DoCmd.SetWarnings False
' run your code
For i = 1 To 6 '(Monday to Saturday)
dteWeekday = DateAdd("d", Me.txtDate, -i) 'increment sunday by i days
strSQL = "INSERT INTO [TimeCardMDJEFF] ([workdate],[date],[man
name],[actualRate]) VALUES (#" & Format(dteWeekday, "m-d-yyyy") & "#," &
"#"
& Format(Me.Date, "m-d-yyyy") & "#,""" & Me.cboman.Column(0) & """,""" &
Me.cboman.Column(1) & """)"
DoCmd.RunSQL (strSQL)
Next i
'END

Me.JeffTimeCardMDJEFFSubform.Requery


If I understand correctly, if there were two workers (Perl and Earl), you

would want to:

1) look at the previous friday's schedule
2) insert 6 records (Mon - Sat) records for this week's jobs for Perl and
3) insert 6 records (Mon - Sat) records for this week's jobs for Earl

YES - you got it!!

If there were 10 workers on the previous friday, you would want 60
records
entered??

Woudl Love to have the option to Insert each employees prev. Fri job(new 6
records) AND/OR do Entire previous week schedule-All last week schedule
(is
On record on main form based on week end date)- subform list who is all
scheduled for what in that week.

thanks sooo much for your help,
barb

Hi Barb,

I currently have a command button with the following code but it only
inserts the six new records based on who is selected in the man drop
down
list - would like it to grab the job from the previous record -
Friday.???any
ideas???

There doesn't appear to be any "following code"???


If I understand correctly, if there were two workers (Perl and Earl), you
would want to:

1) look at the previous friday's schedule
2) insert 6 records (Mon - Sat) records for this week's jobs for Perl and
3) insert 6 records (Mon - Sat) records for this week's jobs for Earl


If there were 10 workers on the previous friday, you would want 60
records
entered??

 

[Steve Sanford]

Not quite sure what you mean by this:

records) AND/OR do Entire previous week schedule-All last week schedule (is
On record on main form based on week end date)- subform list who is all
scheduled for what in that week.

Examples???

Based on Doug's post, what are your tables and relationships (the main form
table/ subform table)? Do you have a table for each employee?

What is the record source (SQL) for the main form?
What is the record source (SQL) for the combo box?
Do you have a table of employees? It looks like the person's name is entered
into the record.

Is the field [actualRate] a text field or a number?
In your code, I don't see a field for the job. How do you know what job they
were doing on last Fri.?

 

[babs]

<What is the record source (SQL) for the main form
The Main form record Source is a table Jeff Week End table - with Only
[Date] which is the Week End Date so - (so would have for one year would have
52 records -weeks on main form)
<What is the record source (SQL) for the combo box?
I have on the Main form a combo box for Man name called cboman- yes that I
have the recordsource from the qry current employees (any better idea-here?)

On subform see all people that are sched for the 6 days M-Sat. for which
jobs and # hours per day.- also with new week - can click on command button
for now to insert 6 records to who is picked in combo box - would like the
option to grab job#, & name from previous friday.- to fill new week 6 records

The main form and subform are Linked by the [Date] (week end date)
The subforms record source it the Table TimeCardMDJEFF The fields are:
Man Name,Job#,Name(actually job name),Date(which is Week End Date),
Workdate,Day,ST(for hours in straight time),OT,DT,ActualRate - there are more
fields but these are most impt.

<Do you have a table for each employee?
No- the list is in the qry currently employed


<Do you have a table of employees? It looks like the person's name is entered
into the record.
Yes the qry of current employess - persons name for now is entered based on
what is picked in combo box on main -when click on Insert 6 rec button
In subform may need to ind. add a new record - would like drop down there
also>

<Is the field [actualRate] a text field or a number?
It is a currency field

<In your code, I don't see a field for the job. How do you know what job they
were doing on last Fri.?
there is a field job# and name(jobname) in the subform - and for now it is a
drop down (with record source - qry currentjobs)


Hope this clarifies things - thanks for helping a ton!!!
BArb

Not quite sure what you mean by this:

records) AND/OR do Entire previous week schedule-All last week schedule (is
On record on main form based on week end date)- subform list who is all
scheduled for what in that week.

Examples???

Based on Doug's post, what are your tables and relationships (the main form
table/ subform table)? Do you have a table for each employee?

What is the record source (SQL) for the main form?
What is the record source (SQL) for the combo box?
Do you have a table of employees? It looks like the person's name is entered
into the record.

Is the field [actualRate] a text field or a number?
In your code, I don't see a field for the job. How do you know what job they
were doing on last Fri.?

 

[Steve Sanford]

babs,

Since I can't see your database, I have to ask questions to learn about the
structure and relationships. Asking about SQL for forms and combo boxes helps
me to know actual table, field and control names. Examples of inserted
records helps ensure I am going in the right direction.

Without this info and examples, I will describe how I would procede. There
are many posters that might be able to do this using a query.. but I am not
one of them (yet).

So, on a button click, I would:

- Get the new week ending date (store in a variable)
- calculate the last Fri date
- calculate the next Monday date (from the last Fri date)
- open a recordset based on the table "TimeCardMDJEFF" WHERE [date] = Last Fri
- check for records in recordset. If no records, exit sub.
(this will be one record per worker)
- step thru the recordset, inserting 6 records for each worker, incrementing
the Workdate for each new record.
- close the recordset.


Or you could open a form with a list box of workers. With the list box set
to multiselect, you could select the workers you want to have new records
added from the previous Fri. The code would be like the above code, but
anyone that was selected in the list box that had NOT worked on the previous
Fri, would still have 6 records inserted, but the Job number and related data
would be blank.



--------
There is a "Find and Replace" tool that would help you rename objects (like
[date] to [WkEndDate]) written by Rick Fisher at

http://www.rickworld.com/download.html

It is shareware good for 30 days, after which the registration cost is $37
per copy. Very reasonable.
--------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

<What is the record source (SQL) for the main form
The Main form record Source is a table Jeff Week End table - with Only
[Date] which is the Week End Date so - (so would have for one year would have
52 records -weeks on main form)
<What is the record source (SQL) for the combo box?
I have on the Main form a combo box for Man name called cboman- yes that I
have the recordsource from the qry current employees (any better idea-here?)

On subform see all people that are sched for the 6 days M-Sat. for which
jobs and # hours per day.- also with new week - can click on command button
for now to insert 6 records to who is picked in combo box - would like the
option to grab job#, & name from previous friday.- to fill new week 6 records

The main form and subform are Linked by the [Date] (week end date)
The subforms record source it the Table TimeCardMDJEFF The fields are:
Man Name,Job#,Name(actually job name),Date(which is Week End Date),
Workdate,Day,ST(for hours in straight time),OT,DT,ActualRate - there are more
fields but these are most impt.

<Do you have a table for each employee?
No- the list is in the qry currently employed


<Do you have a table of employees? It looks like the person's name is entered
into the record.
Yes the qry of current employess - persons name for now is entered based on
what is picked in combo box on main -when click on Insert 6 rec button
In subform may need to ind. add a new record - would like drop down there
also>

<Is the field [actualRate] a text field or a number?
It is a currency field

<In your code, I don't see a field for the job. How do you know what job they
were doing on last Fri.?
there is a field job# and name(jobname) in the subform - and for now it is a
drop down (with record source - qry currentjobs)


Hope this clarifies things - thanks for helping a ton!!!
BArb

Not quite sure what you mean by this:

records) AND/OR do Entire previous week schedule-All last week schedule (is
On record on main form based on week end date)- subform list who is all
scheduled for what in that week.

Examples???

Based on Doug's post, what are your tables and relationships (the main form
table/ subform table)? Do you have a table for each employee?

What is the record source (SQL) for the main form?
What is the record source (SQL) for the combo box?
Do you have a table of employees? It looks like the person's name is entered
into the record.

Is the field [actualRate] a text field or a number?
In your code, I don't see a field for the job. How do you know what job they
were doing on last Fri.?

 

[babs]

Steve,
I really like the idea of the listbox to grab who we should sched for that
week and EVERYTHING else you said! One problem is I am not very strong at VB
code with Records set???- get it for grabbing values from drop down boxes
columns and doing calculations


Here is a sample of a few records for the data - The mainform is just Week
End date so pretty self explanitory there - thinking would have the List box
instead of combo box of who to sched. on that mainform like have now

For subform
Man Name,Job#,Name(actually job name),Date(which is Week End Date),Smith, Rick ;80140020 ;The Legacy at Millennium Park; 9/6/09; 8/31/09;Mon;
8;other hours for overtime double time are blank, $28.20
Smith, Rick 90170054 Outdoor Pipe at 1060 Northpoint 9/6/09; 9/1/09 Tues 8
$28.20
Smith, Rick 80140020 The Legacy at Millennium Park 9/6/09; 9/2/09Wed 8
$28.20
Smith, Rick 80140014 Wheaton College Warrior Dome 9/6/09; 9/3/09Thur 8
$28.20
Smith, Rick 80140014 Wheaton College Warrior Dome 9/6/09; 9/4/09Fri 8
$28.20


Additional employees(man name) and would schedule them for week end 9/6/09

Any code that you can help me with for adding the new records and grabbing
(example of code) prev. fri job would be GREAT!

thanks again so much for help!
Barb

babs,

Since I can't see your database, I have to ask questions to learn about the
structure and relationships. Asking about SQL for forms and combo boxes helps
me to know actual table, field and control names. Examples of inserted
records helps ensure I am going in the right direction.

Without this info and examples, I will describe how I would procede. There
are many posters that might be able to do this using a query.. but I am not
one of them (yet).

So, on a button click, I would:

- Get the new week ending date (store in a variable)
- calculate the last Fri date
- calculate the next Monday date (from the last Fri date)
- open a recordset based on the table "TimeCardMDJEFF" WHERE [date] = Last Fri
- check for records in recordset. If no records, exit sub.
(this will be one record per worker)
- step thru the recordset, inserting 6 records for each worker, incrementing
the Workdate for each new record.
- close the recordset.


Or you could open a form with a list box of workers. With the list box set
to multiselect, you could select the workers you want to have new records
added from the previous Fri. The code would be like the above code, but
anyone that was selected in the list box that had NOT worked on the previous
Fri, would still have 6 records inserted, but the Job number and related data
would be blank.



--------
There is a "Find and Replace" tool that would help you rename objects (like
[date] to [WkEndDate]) written by Rick Fisher at

http://www.rickworld.com/download.html

It is shareware good for 30 days, after which the registration cost is $37
per copy. Very reasonable.
--------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

<What is the record source (SQL) for the main form
The Main form record Source is a table Jeff Week End table - with Only
[Date] which is the Week End Date so - (so would have for one year would have
52 records -weeks on main form)
<What is the record source (SQL) for the combo box?
I have on the Main form a combo box for Man name called cboman- yes that I
have the recordsource from the qry current employees (any better idea-here?)

On subform see all people that are sched for the 6 days M-Sat. for which
jobs and # hours per day.- also with new week - can click on command button
for now to insert 6 records to who is picked in combo box - would like the
option to grab job#, & name from previous friday.- to fill new week 6 records

The main form and subform are Linked by the [Date] (week end date)
The subforms record source it the Table TimeCardMDJEFF The fields are:
Man Name,Job#,Name(actually job name),Date(which is Week End Date),
Workdate,Day,ST(for hours in straight time),OT,DT,ActualRate - there are more
fields but these are most impt.

<Do you have a table for each employee?
No- the list is in the qry currently employed


<Do you have a table of employees? It looks like the person's name is entered
into the record.
Yes the qry of current employess - persons name for now is entered based on
what is picked in combo box on main -when click on Insert 6 rec button
In subform may need to ind. add a new record - would like drop down there
also>

<Is the field [actualRate] a text field or a number?
It is a currency field

<In your code, I don't see a field for the job. How do you know what job they
were doing on last Fri.?
there is a field job# and name(jobname) in the subform - and for now it is a
drop down (with record source - qry currentjobs)


Hope this clarifies things - thanks for helping a ton!!!
BArb

Not quite sure what you mean by this:

records) AND/OR do Entire previous week schedule-All last week schedule (is
On record on main form based on week end date)- subform list who is all
scheduled for what in that week.

Examples???

Based on Doug's post, what are your tables and relationships (the main form
table/ subform table)? Do you have a table for each employee?

What is the record source (SQL) for the main form?
What is the record source (SQL) for the combo box?
Do you have a table of employees? It looks like the person's name is entered
into the record.

Is the field [actualRate] a text field or a number?
In your code, I don't see a field for the job. How do you know what job they
were doing on last Fri.?

 

[Steve Sanford]

babs,

Is this right??

In table "TimeCardMDJEFF"

Field Type
----------------------
Man Name string
Job string
job name string
Date date
Workdate date
Day string
ST single
ActualRate single



What is the name of the main form table?

Is the main form recordsource a query?


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve,
I really like the idea of the listbox to grab who we should sched for that
week and EVERYTHING else you said! One problem is I am not very strong at VB
code with Records set???- get it for grabbing values from drop down boxes
columns and doing calculations


Here is a sample of a few records for the data - The mainform is just Week
End date so pretty self explanitory there - thinking would have the List box
instead of combo box of who to sched. on that mainform like have now

For subform
Man Name,Job#,Name(actually job name),Date(which is Week End Date),Smith, Rick ;80140020 ;The Legacy at Millennium Park; 9/6/09; 8/31/09;Mon;
8;other hours for overtime double time are blank, $28.20
Smith, Rick 90170054 Outdoor Pipe at 1060 Northpoint 9/6/09; 9/1/09 Tues 8
$28.20
Smith, Rick 80140020 The Legacy at Millennium Park 9/6/09; 9/2/09Wed 8
$28.20
Smith, Rick 80140014 Wheaton College Warrior Dome 9/6/09; 9/3/09Thur 8
$28.20
Smith, Rick 80140014 Wheaton College Warrior Dome 9/6/09; 9/4/09Fri 8
$28.20


Additional employees(man name) and would schedule them for week end 9/6/09

Any code that you can help me with for adding the new records and grabbing
(example of code) prev. fri job would be GREAT!

thanks again so much for help!
Barb

babs,

Since I can't see your database, I have to ask questions to learn about the
structure and relationships. Asking about SQL for forms and combo boxes helps
me to know actual table, field and control names. Examples of inserted
records helps ensure I am going in the right direction.

Without this info and examples, I will describe how I would procede. There
are many posters that might be able to do this using a query.. but I am not
one of them (yet).

So, on a button click, I would:

- Get the new week ending date (store in a variable)
- calculate the last Fri date
- calculate the next Monday date (from the last Fri date)
- open a recordset based on the table "TimeCardMDJEFF" WHERE [date] = Last Fri
- check for records in recordset. If no records, exit sub.
(this will be one record per worker)
- step thru the recordset, inserting 6 records for each worker, incrementing
the Workdate for each new record.
- close the recordset.


Or you could open a form with a list box of workers. With the list box set
to multiselect, you could select the workers you want to have new records
added from the previous Fri. The code would be like the above code, but
anyone that was selected in the list box that had NOT worked on the previous
Fri, would still have 6 records inserted, but the Job number and related data
would be blank.



--------
There is a "Find and Replace" tool that would help you rename objects (like
[date] to [WkEndDate]) written by Rick Fisher at

http://www.rickworld.com/download.html

It is shareware good for 30 days, after which the registration cost is $37
per copy. Very reasonable.
--------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

<What is the record source (SQL) for the main form
The Main form record Source is a table Jeff Week End table - with Only
[Date] which is the Week End Date so - (so would have for one year would have
52 records -weeks on main form)
<What is the record source (SQL) for the combo box?
I have on the Main form a combo box for Man name called cboman- yes that I
have the recordsource from the qry current employees (any better idea-here?)

On subform see all people that are sched for the 6 days M-Sat. for which
jobs and # hours per day.- also with new week - can click on command button
for now to insert 6 records to who is picked in combo box - would like the
option to grab job#, & name from previous friday.- to fill new week 6 records

The main form and subform are Linked by the [Date] (week end date)
The subforms record source it the Table TimeCardMDJEFF The fields are:
Man Name,Job#,Name(actually job name),Date(which is Week End Date),
Workdate,Day,ST(for hours in straight time),OT,DT,ActualRate - there are more
fields but these are most impt.

<Do you have a table for each employee?
No- the list is in the qry currently employed


<Do you have a table of employees? It looks like the person's name is entered
into the record.
Yes the qry of current employess - persons name for now is entered based on
what is picked in combo box on main -when click on Insert 6 rec button
In subform may need to ind. add a new record - would like drop down there
also>

<Is the field [actualRate] a text field or a number?
It is a currency field

<In your code, I don't see a field for the job. How do you know what job they
were doing on last Fri.?
there is a field job# and name(jobname) in the subform - and for now it is a
drop down (with record source - qry currentjobs)


Hope this clarifies things - thanks for helping a ton!!!
BArb
:


Not quite sure what you mean by this:

records) AND/OR do Entire previous week schedule-All last week schedule (is
On record on main form based on week end date)- subform list who is all
scheduled for what in that week.

Examples???

Based on Doug's post, what are your tables and relationships (the main form
table/ subform table)? Do you have a table for each employee?

What is the record source (SQL) for the main form?
What is the record source (SQL) for the combo box?
Do you have a table of employees? It looks like the person's name is entered
into the record.

Is the field [actualRate] a text field or a number?
In your code, I don't see a field for the job. How do you know what job they
were doing on last Fri.?

 

[Steve Sanford]

OK, now I need to get my head around *when* you add the new week
records........

Here is a sample of a few records for the data - The mainform is just Week
End date so pretty self explanitory there - thinking would have the List box
instead of combo box of who to sched. on that mainform like have now

For subform
Man Name,Job#,Name(actually job name),Date(which is Week End Date),
Smith, Rick ;80140020 ;The Legacy at Millennium Park; 9/6/09; 8/31/09;Mon;
8;other hours for overtime double time are blank, $28.20
Smith, Rick 90170054 Outdoor Pipe at 1060 Northpoint 9/6/09; 9/1/09 Tues 8
$28.20
Smith, Rick 80140020 The Legacy at Millennium Park 9/6/09; 9/2/09Wed 8
$28.20
Smith, Rick 80140014 Wheaton College Warrior Dome 9/6/09; 9/3/09Thur 8
$28.20
Smith, Rick 80140014 Wheaton College Warrior Dome 9/6/09; 9/4/09Fri 8
$28.20

Given the records above, I think the main form table already has 9/6/2009
entered.

So do you want to select 9/6/2009 in the main form, have a new record
entered in the main form with a date of "9/13/2009" , THEN find everyone that
worked on 9/4/2009 and enter records in the table "TimeCardMDJEFF" for the
dates 9/7/2009 thru 9/11/2009?



Which brings up another question: do you want {Mon thru Fri} or {Mon thru
Sat}??

 

[babs]

You have the TimecardMdJeff table correct!

What is the name of the main form table? WeekEndDate


Is the main form recordsource a query? No it is just a table for the Week
End Date - ie. just 52 records for one year. The combo box on the main for
is not bound and just set up for now to list Available people to schedule
when click on them - would like to be able to insert their 6 new records
based on the previous week.

For next post - we schedule Sunday night or very early Sunday morning.

thanks again for helping ! would love some code to help with your above
ideas - it is really what I would like to have happen.
Barb

 

[Steve Sanford]

Barb,

Is the main form recordsource a query? No it is just a table for the Week

I would recommend using a query for the main form record source. A table is
just a bucket the you put data into; it is not guaranteed to be in any order,
whereas a query is sortable.


I have 98% of the code done. I have a pretty good idea of what you want
done, but I don't know *how* you do it.

Let's say you have one worker "Bob".
Bob worked 9/14 - 9/18/2009.
NO records have been entered for the week 9/21 - 9/25/2009. (Week ending 9/26)
And let's say today is Sun. 9/20/2009.

So, my questions are:

- Is 9/27/2009 already entered in the main form? (do you pre-enter sundays
for the year?)


- Do you want to select 9/20/2009 in the main form and have the code enter
records for 9/21 - 9/25 (9/27 must already be entered in the main form)

- Do you want to select 9/20/2009 in the main form and have the code enter
the next sunday date (9/27), then have the code enter the records for the
week 9/21 - 9/25?

- Do you want to enter 9/27 in the main form, then select it and have the
code enter the new records for 9/21 - 9/25?


I'm looking for how you manually add records now.

HTH

 

[babs]

<- Is 9/27/2009 already entered in the main form? (do you pre-enter sundays
for the year?)

They can be pre entered but for now they are not(actually not using this yet
- still developing obviously)
It is all in Excel for now - each worksheet Tab is a new Week end - the user
just copies and pastes the last job done from previous week and pastes it
into the new worksheet for new Week End.

<Do you want to enter 9/27 in the main form, then select it and have the
code enter the new records for 9/21 - 9/25?

This is what the code is doing now - when 9/27/09 for week End (puts in
9/21-9/26)is entered on the main form and they click on the Add 6 records
button from the Main button- see Previous posts it - it grabs the man name
they have in the drop down list on main and add the 6 records with the date
for that one person -(like what you said on previous post) -to have
option(maybe one button for Add all who were scheduled prev week(Fri-or
anyday last week), and have the option also to add additional people with the
dropdown (like presently doing)

thanks soo much !
Barb

 

[babs]

Steve,
this was what you stated before and this is what I would love - Also I think
the Insert 6 record button (code in previous posts)I have on the main form
with the man name combo box would take care of inserting the records - for a
New man not sched. last week.

Your prev. post
If I understand correctly, if there were two workers (Perl and Earl), you
would want to:

1) look at the previous friday's schedule
2) insert 6 records (Mon - Sat) records for this week's jobs for Perl and
3) insert 6 records (Mon - Sat) records for this week's jobs for Earl


If there were 10 workers on the previous friday, you would want 60 records
entered??

Thanks sooo much for still helping would love to get it done Before Thurs.
if possible.

Barb

 

[Steve Sanford]

Barb,

<unasked for advice>
Read this site, especially #3...... no spaces/ naming convention

http://mvps.org/access/tencommandments.htm


A naming convention will keep you from using reserved words (ex. "Date" &
"Day") as names for Access objects. See:

http://allenbrowne.com/AppIssueBadWord.html


You should read up on normalization. The table "TimeCardMDJEFF" is in need
of being normalized. See:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101


Here is Crystal's Access Basics:

http://www.allenbrowne.com/casu-22.html


</unasked for advice>



OK, here is what the code does. In your main form you select a date (that is
a Sunday), then click a button, and all of the employees that worked on the
preceding Friday will have records for this week (Mon - Sat).


Example: if a group of workers worked 9/7 - 9/12 (week ending 9/13), and
*TODAY* is Sunday 9/13, to enter records for the week 9/14 - 9/19, in the
main form, you would select *9/20/2009*, then click on the "ADD NEW" button.

The code will look back at Fri 9/11, and create 6 records for each worker
for the dates 9/14 - 9/19/2009.


!!!!!!!!!!!!!!!!!!!!!!!!!!!!
DO THE FOLLOWING ON A COPY OF YOUR MDB!!!
When/if you think it is doing what you want, put it to your production mdb.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!


The first two line of every code page should have:

Option Compare Database
Option Explicit


Add a new button on the main form, name it something like "AddNEW", and
paste the following code in the click event:

'----------------------------------
Private Sub AutoFillNewRecord_Click()
On Error GoTo Err_HandleError

'number of days to add
'change to 5 if you want Mon - Fri
Const intNumDays As Integer = 6

Dim d As DAO.Database
Dim r As DAO.Recordset

Dim dteWeekEndDay As Date ' week ending date
Dim dteFri As Date ' last friday date
Dim dteMon As Date ' next monday date
Dim tmpDate As Date
Dim i As Integer ' loop counter
Dim sSQL As String
Dim WkDay As String

' saves record in main form
If Me.Dirty Then
Me.Dirty = False
End If

Set d = CurrentDb

'- Get the new week ending date (store in a variable)
dteWeekEndDay = Me.txtDate
If Weekday(dteWeekEndDay) <> vbSunday Then
MsgBox "Selected date is not a Sunday. Please check the date and try
again."
Exit Sub
End If

'get the previous Sunday date
dteFri = DateAdd("d", -7, dteWeekEndDay)
'- calculate the last Fri date
Do Until Weekday(dteFri) = vbFriday
dteFri = DateAdd("d", -1, dteFri)
Loop

'- calculate the next Monday date (from the last Fri date)
dteMon = DateAdd("d", 3, dteFri)

'- open a recordset based on the table "TimeCardMDJEFF" WHERE [date] =
Last Fri
sSQL = "SELECT [Man Name], Job, [job name],"
sSQL = sSQL & " [Date], Workdate, [Day], ST, ActualRate"
sSQL = sSQL & " FROM TimeCardMDJEFF"
sSQL = sSQL & " WHERE [Workdate] = #" & dteFri & "#;"
' Debug.Print sSQL
Set r = d.OpenRecordset(sSQL)
'- check for records in recordset.
' (there should be one Fri record per worker)
' -If no records, exit sub.
If r.BOF And r.EOF Then
MsgBox "ERROR! No records found for the week ending Fri " & dteFri
Else
r.MoveLast
r.MoveFirst

' MsgBox r.RecordCount

'loop thru the recordset
Do While Not r.EOF

tmpDate = dteMon

'- step thru the recordset, inserting 5 or 6 records for each
worker, incrementing
'the Workdate for each new record.
For i = 1 To intNumDays '(Monday to Saturday)

Select Case Weekday(tmpDate)
Case 1
WkDay = "Sun"
Case 2
WkDay = "Mon"
Case 3
WkDay = "Tue"
Case 4
WkDay = "Wed"
Case 5
WkDay = "Thu"
Case 6
WkDay = "Fri"
Case 7
WkDay = "Sat"
End Select

'cerate the insert string
sSQL = "INSERT INTO [TimeCardMDJEFF] ([Man Name], Job, [job
name],"
sSQL = sSQL & " [Date], Workdate, [Day], ST, ActualRate)"
sSQL = sSQL & " VALUES (""" & r.Fields(0) & """, " & r.Fields(1)
sSQL = sSQL & ", """ & r.Fields(2) & """, #" & dteWeekEndDay
sSQL = sSQL & "#, #" & tmpDate & "#, """ & WkDay
sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";"

' Debug.Print sSQL

'increment day
tmpDate = DateAdd("d", 1, tmpDate)

'insert the record
d.Execute sSQL, dbFailOnError

Next i

r.MoveNext
Loop
End If

Exit_HandleError:
On Error Resume Next
'clean up
r.Close
Set r = Nothing
Set d = Nothing
MsgBox "Done"
Exit Sub

Err_HandleError:
Select Case Err.Number
Case 3021
MsgBox "Help"
Case Else
MsgBox Err.Description, vbExclamation, "Search Error " & Err.Number
End Select

Resume Exit_HandleError

End Sub
'----------------------------------



HTH

 

[Steve Sanford]

Barb,

If you want to use a list box instead of or in addition to the combo box,
you could have a multiselect list box that would list the workers. You select
one or more people, then click on a button. It could look to see if there was
a record for the previous Fri and add records for the week. If there wasn't a
record record, it could add a weeks worth of blank records for him.


Another question....(I know - but it is important).

What do you do if you have two John Smiths or Jim Jones?? Since you use a
person's name, how do you tell them apart??



HTH

 

[babs]

Steve,
Yeah - I know they should use the SS# as PK - but they have been doingthis
for years and seems to be okay.

I added a new record on main for new weekend date- have people on fri. of
prev. week - added All of your wonderful suggest code to the button on click
event

I am getting Error on Insert Into - then Done- but no records show up.
a few of the field names- I changed to reflex there name - job is Job
#,job name is Name, ST is HOurs(ST) Not sure but just always add the [ ]
just in case.

below is the code I have in it- just grab the 2 sections out. I really
don't get the Insert into code and what the last line(sSQL = sSQL & """, " &
r.Fields(6) & ", " & r.Fields(7) & ";") means???HELP



'- open a recordset based on the table "TimeCardMDJEFF" WHERE [date] =Last Fri
sSQL = "SELECT [Man Name], [Job #], [name],"
sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate"
sSQL = sSQL & " FROM TimeCardMDJEFF"
sSQL = sSQL & " WHERE [Workdate] = #" & dteFri & "#;"


'create the insert string
sSQL = "INSERT INTO [TimeCardMDJEFF] ([Man Name], [Job #], [name]"
sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate)"
sSQL = sSQL & " VALUES (""" & r.Fields(0) & """, " & r.Fields(1)
sSQL = sSQL & ", """ & r.Fields(2) & """, #" & dteWeekEndDay
sSQL = sSQL & "#, #" & tmpDate & "#, """ & WkDay
sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";"



AGAIN - thanks a ton for helping!!
Barb

 

[babs]

Steve,

Still trying to understand the Insert Into statement to help make it work -
Do the Names of the text boxes in the Subform(tied to TimecardMDJeff) have to
Match the names that is after the Values part of the Insert statement -
when and why use -r.Fields(7) and the others ones like this.

I really
don't get the Insert into code and what the last line(sSQL = sSQL & """, " &
r.Fields(6) & ", " & r.Fields(7) & ";") means???HELP

see prev. post also,
thanks soooo much,
Barb

 

[Steve Sanford]

Barb,

When you changed the field names in the insert statement, a comma was also
removed. A comma must be at the end of this line (after name, but inside the
quote):

sSQL = "INSERT INTO [TimeCardMDJEFF] ([Man Name], [Job #], [name],"

Add the last comma, and it should run correctly.

don't get the Insert into code and what the last line(sSQL = sSQL & """, " &
r.Fields(6) & ", " & r.Fields(7) & ";") means???HELP

"r" is the record set name and "r.Fields(6)" is the 7th field in the
recordset.

The record set field numbers are zero based, so the first field is zero, the
2nd field is 1, the third field is 2,........

Using "r.Fields(6)" is a way of getting the data from a field in a record
set without knowing the name if the field. Instead of "r.Fields(6)" ,
I could have used r.Fields("[Hours(ST)]").

In the recordset "r":
Field # Your Name
-------------------------------------
0 [Man Name]
1 [Job #]
2 [name]
3 [Date]
4 Workdate
5 [Day]
6 [Hours(ST)]
7 ActualRate



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve,
Yeah - I know they should use the SS# as PK - but they have been doingthis
for years and seems to be okay.

I added a new record on main for new weekend date- have people on fri. of
prev. week - added All of your wonderful suggest code to the button on click
event

I am getting Error on Insert Into - then Done- but no records show up.
a few of the field names- I changed to reflex there name - job is Job
#,job name is Name, ST is HOurs(ST) Not sure but just always add the [ ]
just in case.

below is the code I have in it- just grab the 2 sections out. I really
don't get the Insert into code and what the last line(sSQL = sSQL & """, " &
r.Fields(6) & ", " & r.Fields(7) & ";") means???HELP



'- open a recordset based on the table "TimeCardMDJEFF" WHERE [date] =Last Fri
sSQL = "SELECT [Man Name], [Job #], [name],"
sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate"
sSQL = sSQL & " FROM TimeCardMDJEFF"
sSQL = sSQL & " WHERE [Workdate] = #" & dteFri & "#;"


'create the insert string
sSQL = "INSERT INTO [TimeCardMDJEFF] ([Man Name], [Job #], [name]"
sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate)"
sSQL = sSQL & " VALUES (""" & r.Fields(0) & """, " & r.Fields(1)
sSQL = sSQL & ", """ & r.Fields(2) & """, #" & dteWeekEndDay
sSQL = sSQL & "#, #" & tmpDate & "#, """ & WkDay
sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";"



AGAIN - thanks a ton for helping!!
Barb

Barb,

If you want to use a list box instead of or in addition to the combo box,
you could have a multiselect list box that would list the workers. You select
one or more people, then click on a button. It could look to see if there was
a record for the previous Fri and add records for the week. If there wasn't a
record record, it could add a weeks worth of blank records for him.


Another question....(I know - but it is important).

What do you do if you have two John Smiths or Jim Jones?? Since you use a
person's name, how do you tell them apart??



HTH