main form - subform

  • Thread starter Thread starter babs
  • Start date Start date
B

babs

reposting -still need help -thanks!!!

I have a Main form - (Overall purposeof Both forms combined is to Scheduling
jobs for the Upcoming week)
Main form has WEEK End date manually put in(Always a Sun-they don't work
Sunday) , and Man Name selected from Dropdown

As of right now in the subform the user has to type in all of the dates for
that week
ex. Week End is 8/9/09(put in on Main form)-all just have it infor now
8/9/09-auto put in Like it should

For the actual WORKDATE User has to put in on subform the sched. to work ie.
8/3/09, 8/4/09,8/5/09,8/6/09,8/7/09,8/8/09

When the user puts in the Week End Date on the Main Form - I would like 6
RECORDS AUTOMATICALLY ADDED with the Workdate put in ON the Subform- then the
scheduler can input what actual jobs they are doing on that day
Thinking there should be some VB code to put in on the After Update event of
the Week End date on the Main form

Not sure what code to Insert Records into the Subform.

thanks sooo much,
Barb


SuzyQ 8/31/2009 8:58 AM PST

check out the insert into sql statement and the dateadd function for
increasing your weekend date by a specified number of days. Put that in a
loop from 1 -5 for the days you want to add.

Are you verifying that the user only enters a Sunday date? What would
happen if the user enters a date other than Sunday?

:

babs 8/31/2009 9:57 AM PST
yes,
they only put in a Sunday date-

thinking I would add an Event procedure to the after update of the WEEK End
date on the main form-

Not sure what the exact code should be to for the Do Loop to add the record
and put in the Mon-Sat dates prior???

Thanks so much for your help,
BARb
 
Create a table named CountNumber with field CountNUM containing numbers 0
(zero) through 7.
Use this append query --
INSERT INTO YourTable ( [WORKDATE] )
DateAdd("d", -[CountNumber].[CountNUM], [Forms]![YourMainForm]![WeekEnd])
FROM [CountNumber]
WHERE [CountNumber].[CountNUM] Between 1 AND 6;
 
created query in SQL view- never do this - usually use design view or wizard
put in code below: get error in INTO Statement?????


This is code have in query in SQL view:
INSERT INTO TimeCardMDJEFF ( [WORKDATE] )
DateAdd("d", -[CountNumber].[CountNUM], [Forms]![Jeff Schedule ENTER NEW
Sched]![Date]) ------>For some bad reason weekend date is named just date
FROM [CountNumber]
WHERE [CountNumber].[CountNUM] Between 1 AND 6


thanks again for helping!
barb
KARL DEWEY said:
Create a table named CountNumber with field CountNUM containing numbers 0
(zero) through 7.
Use this append query --
INSERT INTO YourTable ( [WORKDATE] )
DateAdd("d", -[CountNumber].[CountNUM], [Forms]![YourMainForm]![WeekEnd])
FROM [CountNumber]
WHERE [CountNumber].[CountNUM] Between 1 AND 6;

--
Build a little, test a little.


babs said:
reposting -still need help -thanks!!!

I have a Main form - (Overall purposeof Both forms combined is to Scheduling
jobs for the Upcoming week)
Main form has WEEK End date manually put in(Always a Sun-they don't work
Sunday) , and Man Name selected from Dropdown

As of right now in the subform the user has to type in all of the dates for
that week
ex. Week End is 8/9/09(put in on Main form)-all just have it infor now
8/9/09-auto put in Like it should

For the actual WORKDATE User has to put in on subform the sched. to work ie.
8/3/09, 8/4/09,8/5/09,8/6/09,8/7/09,8/8/09

When the user puts in the Week End Date on the Main Form - I would like 6
RECORDS AUTOMATICALLY ADDED with the Workdate put in ON the Subform- then the
scheduler can input what actual jobs they are doing on that day
Thinking there should be some VB code to put in on the After Update event of
the Week End date on the Main form

Not sure what code to Insert Records into the Subform.

thanks sooo much,
Barb


SuzyQ 8/31/2009 8:58 AM PST

check out the insert into sql statement and the dateadd function for
increasing your weekend date by a specified number of days. Put that in a
loop from 1 -5 for the days you want to add.

Are you verifying that the user only enters a Sunday date? What would
happen if the user enters a date other than Sunday?

:

babs 8/31/2009 9:57 AM PST
yes,
they only put in a Sunday date-

thinking I would add an Event procedure to the after update of the WEEK End
date on the main form-

Not sure what the exact code should be to for the Do Loop to add the record
and put in the Mon-Sat dates prior???

Thanks so much for your help,
BARb
Click to expand...
Click to expand...
 
Unfortunately, Karl seems to have made a typo in his SQL satement.

INSERT INTO requires either a SELECT statement or a VALUE clause in
conjunction with it.

Try:

INSERT INTO TimeCardMDJEFF ( [WORKDATE] )
VALUES (DateAdd("d", -[CountNumber].[CountNUM], [Forms]![Jeff Schedule ENTER
NEW
Sched]![Date]))

Another potential issue, though, is that Date is a reserved word, and you
should never name fields, variables, controls. etc. the same as reserved
words. In other words, you might need to rename the Date field on your form.
For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


babs said:
created query in SQL view- never do this - usually use design view or
wizard
put in code below: get error in INTO Statement?????


This is code have in query in SQL view:
INSERT INTO TimeCardMDJEFF ( [WORKDATE] )
DateAdd("d", -[CountNumber].[CountNUM], [Forms]![Jeff Schedule ENTER NEW
Sched]![Date]) ------>For some bad reason weekend date is named just date
FROM [CountNumber]
WHERE [CountNumber].[CountNUM] Between 1 AND 6


thanks again for helping!
barb
KARL DEWEY said:
Create a table named CountNumber with field CountNUM containing numbers 0
(zero) through 7.
Use this append query --
INSERT INTO YourTable ( [WORKDATE] )
DateAdd("d", -[CountNumber].[CountNUM], [Forms]![YourMainForm]![WeekEnd])
FROM [CountNumber]
WHERE [CountNumber].[CountNUM] Between 1 AND 6;

--
Build a little, test a little.


babs said:
reposting -still need help -thanks!!!

I have a Main form - (Overall purposeof Both forms combined is to
Scheduling
jobs for the Upcoming week)
Main form has WEEK End date manually put in(Always a Sun-they don't
work
Sunday) , and Man Name selected from Dropdown

As of right now in the subform the user has to type in all of the dates
for
that week
ex. Week End is 8/9/09(put in on Main form)-all just have it infor now
8/9/09-auto put in Like it should

For the actual WORKDATE User has to put in on subform the sched. to
work ie.
8/3/09, 8/4/09,8/5/09,8/6/09,8/7/09,8/8/09

When the user puts in the Week End Date on the Main Form - I would like
6
RECORDS AUTOMATICALLY ADDED with the Workdate put in ON the Subform-
then the
scheduler can input what actual jobs they are doing on that day
Thinking there should be some VB code to put in on the After Update
event of
the Week End date on the Main form

Not sure what code to Insert Records into the Subform.

thanks sooo much,
Barb


SuzyQ 8/31/2009 8:58 AM PST

check out the insert into sql statement and the dateadd function for
increasing your weekend date by a specified number of days. Put that in
a
loop from 1 -5 for the days you want to add.

Are you verifying that the user only enters a Sunday date? What would
happen if the user enters a date other than Sunday?

:

babs 8/31/2009 9:57 AM PST
yes,
they only put in a Sunday date-

thinking I would add an Event procedure to the after update of the WEEK
End
date on the main form-

Not sure what the exact code should be to for the Do Loop to add the
record
and put in the Mon-Sat dates prior???

Thanks so much for your help,
BARb
Click to expand...
Click to expand...
Click to expand...
 
thanks so much for still helping - I put the exact code you had in the SQL
view of a NEW query

when I run it

I get box Parameter value CountNumber.CountNum - I created the table with
that name and countNum field (with 0-7) values- do I need to add this
table??? somewhere in the code??

I also get another erro Data type Mismatch in criteria expression???

thanks again for helping!!!
Barb
Douglas J. Steele said:
Unfortunately, Karl seems to have made a typo in his SQL satement.

INSERT INTO requires either a SELECT statement or a VALUE clause in
conjunction with it.

Try:

INSERT INTO TimeCardMDJEFF ( [WORKDATE] )
VALUES (DateAdd("d", -[CountNumber].[CountNUM], [Forms]![Jeff Schedule ENTER
NEW
Sched]![Date]))

Another potential issue, though, is that Date is a reserved word, and you
should never name fields, variables, controls. etc. the same as reserved
words. In other words, you might need to rename the Date field on your form.
For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


babs said:
created query in SQL view- never do this - usually use design view or
wizard
put in code below: get error in INTO Statement?????


This is code have in query in SQL view:
INSERT INTO TimeCardMDJEFF ( [WORKDATE] )
DateAdd("d", -[CountNumber].[CountNUM], [Forms]![Jeff Schedule ENTER NEW
Sched]![Date]) ------>For some bad reason weekend date is named just date
FROM [CountNumber]
WHERE [CountNumber].[CountNUM] Between 1 AND 6


thanks again for helping!
barb
KARL DEWEY said:
Create a table named CountNumber with field CountNUM containing numbers 0
(zero) through 7.
Use this append query --
INSERT INTO YourTable ( [WORKDATE] )
DateAdd("d", -[CountNumber].[CountNUM], [Forms]![YourMainForm]![WeekEnd])
FROM [CountNumber]
WHERE [CountNumber].[CountNUM] Between 1 AND 6;

--
Build a little, test a little.


:

reposting -still need help -thanks!!!

I have a Main form - (Overall purposeof Both forms combined is to
Scheduling
jobs for the Upcoming week)
Main form has WEEK End date manually put in(Always a Sun-they don't
work
Sunday) , and Man Name selected from Dropdown

As of right now in the subform the user has to type in all of the dates
for
that week
ex. Week End is 8/9/09(put in on Main form)-all just have it infor now
8/9/09-auto put in Like it should

For the actual WORKDATE User has to put in on subform the sched. to
work ie.
8/3/09, 8/4/09,8/5/09,8/6/09,8/7/09,8/8/09

When the user puts in the Week End Date on the Main Form - I would like
6
RECORDS AUTOMATICALLY ADDED with the Workdate put in ON the Subform-
then the
scheduler can input what actual jobs they are doing on that day
Thinking there should be some VB code to put in on the After Update
event of
the Week End date on the Main form

Not sure what code to Insert Records into the Subform.

thanks sooo much,
Barb


SuzyQ 8/31/2009 8:58 AM PST

check out the insert into sql statement and the dateadd function for
increasing your weekend date by a specified number of days. Put that in
a
loop from 1 -5 for the days you want to add.

Are you verifying that the user only enters a Sunday date? What would
happen if the user enters a date other than Sunday?

:

babs 8/31/2009 9:57 AM PST
yes,
they only put in a Sunday date-

thinking I would add an Event procedure to the after update of the WEEK
End
date on the main form-

Not sure what the exact code should be to for the Do Loop to add the
record
and put in the Mon-Sat dates prior???

Thanks so much for your help,
BARb
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I didn't look that closely at the SQL!

If you've got a table named CountNumber with a field named CountNum, try the
following SQL:

INSERT INTO TimeCardMDJEFF ( [WORKDATE] )
SELECT (DateAdd("d", -[CountNUM], [Forms]![Jeff Schedule ENTER NEW
Sched]![Date]))
FROM CountNumber


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


babs said:
thanks so much for still helping - I put the exact code you had in the SQL
view of a NEW query

when I run it

I get box Parameter value CountNumber.CountNum - I created the table with
that name and countNum field (with 0-7) values- do I need to add this
table??? somewhere in the code??

I also get another erro Data type Mismatch in criteria expression???

thanks again for helping!!!
Barb
Douglas J. Steele said:
Unfortunately, Karl seems to have made a typo in his SQL satement.

INSERT INTO requires either a SELECT statement or a VALUE clause in
conjunction with it.

Try:

INSERT INTO TimeCardMDJEFF ( [WORKDATE] )
VALUES (DateAdd("d", -[CountNumber].[CountNUM], [Forms]![Jeff Schedule
ENTER
NEW
Sched]![Date]))

Another potential issue, though, is that Date is a reserved word, and you
should never name fields, variables, controls. etc. the same as reserved
words. In other words, you might need to rename the Date field on your
form.
For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), see what Allen Browne
has
at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


babs said:
created query in SQL view- never do this - usually use design view or
wizard
put in code below: get error in INTO Statement?????


This is code have in query in SQL view:
INSERT INTO TimeCardMDJEFF ( [WORKDATE] )
DateAdd("d", -[CountNumber].[CountNUM], [Forms]![Jeff Schedule ENTER
NEW
Sched]![Date]) ------>For some bad reason weekend date is named just
date
FROM [CountNumber]
WHERE [CountNumber].[CountNUM] Between 1 AND 6


thanks again for helping!
barb
:

Create a table named CountNumber with field CountNUM containing
numbers 0
(zero) through 7.
Use this append query --
INSERT INTO YourTable ( [WORKDATE] )
DateAdd("d", -[CountNumber].[CountNUM],
[Forms]![YourMainForm]![WeekEnd])
FROM [CountNumber]
WHERE [CountNumber].[CountNUM] Between 1 AND 6;

--
Build a little, test a little.


:

reposting -still need help -thanks!!!

I have a Main form - (Overall purposeof Both forms combined is to
Scheduling
jobs for the Upcoming week)
Main form has WEEK End date manually put in(Always a Sun-they don't
work
Sunday) , and Man Name selected from Dropdown

As of right now in the subform the user has to type in all of the
dates
for
that week
ex. Week End is 8/9/09(put in on Main form)-all just have it infor
now
8/9/09-auto put in Like it should

For the actual WORKDATE User has to put in on subform the sched. to
work ie.
8/3/09, 8/4/09,8/5/09,8/6/09,8/7/09,8/8/09

When the user puts in the Week End Date on the Main Form - I would
like
6
RECORDS AUTOMATICALLY ADDED with the Workdate put in ON the Subform-
then the
scheduler can input what actual jobs they are doing on that day
Thinking there should be some VB code to put in on the After Update
event of
the Week End date on the Main form

Not sure what code to Insert Records into the Subform.

thanks sooo much,
Barb


SuzyQ 8/31/2009 8:58 AM PST

check out the insert into sql statement and the dateadd function
for
increasing your weekend date by a specified number of days. Put that
in
a
loop from 1 -5 for the days you want to add.

Are you verifying that the user only enters a Sunday date? What
would
happen if the user enters a date other than Sunday?

:

babs 8/31/2009 9:57 AM PST
yes,
they only put in a Sunday date-

thinking I would add an Event procedure to the after update of the
WEEK
End
date on the main form-

Not sure what the exact code should be to for the Do Loop to add the
record
and put in the Mon-Sat dates prior???

Thanks so much for your help,
BARb
Click to expand...
Click to expand...
Click to expand...
 
Back
Top