Looked at the Immediate Window in Visual code view and see this below- don't
know if the order
In the tables behind the scenes for some strange reason Job # is a number
field - not sure if that would explain the error and where would I change the
code.
Immediate window - not sure also why grabbing Mon and not Fri???
INSERT INTO [JeffTime Card MD Query] ([Man Name], [Job #], [name], [Date],
Workdate, [Day], [Hours(ST)], ActualRate) VALUES ("BROOKS, CLAIRE",
"1800270", "U.S. Post Office, Rockford", #10/4/2009#, #9/28/2009#, "Mon", 0,
42.05;
INSERT INTO [JeffTime Card MD Query] ([Man Name], [Job #], [name], [Date],
Workdate, [Day], [Hours(ST)], ActualRate) VALUES ("BROOKS, CLAIRE",
"1800270", "U.S. Post Office, Rockford", #10/4/2009#, #9/28/2009#, "Mon", 0,
42.05;
INSERT INTO [JeffTime Card MD Query] ([Man Name], [Job #], [name], [Date],
Workdate, [Day], [Hours(ST)], ActualRate) VALUES ("BROOKS, CLAIRE",
"1800270", "U.S. Post Office, Rockford", #10/4/2009#, #9/28/2009#, "Mon", 0,
42.05;
INSERT INTO [JeffTime Card MD Query] ([Man Name], [Job #], [name], [Date],
Workdate, [Day], [Hours(ST)], ActualRate) VALUES ("BROOKS, CLAIRE",
"1800270", "U.S. Post Office, Rockford", #10/4/2009#, #9/28/2009#, "Mon", 0,
42.05;
INSERT INTO [JeffTime Card MD Query] ([Man Name], [Job #], [name], [Date],
Workdate, [Day], [Hours(ST)], ActualRate) VALUES ("BROOKS, CLAIRE",
"1800270", "U.S. Post Office, Rockford", #10/4/2009#, #9/28/2009#, "Mon", 0,
42.05;
Steve Sanford said:
Yes, still here.
When you got the error, what line was highlighted?
Have you tried using " Debug.Print sSQL" statements after the sSQL lines
to see if the SQL statement are formed correctly?
When you look at the line in the debug window, the delimiters for field
types are:
Type Delimiters Example
------------------------------------------
strings " " or ' ' "Hi" or 'Hi'
dates # # #1/1/2009#
numbers no delimiters 28.50
Using data you provided, I put the sSQL lines in my code and ran it. The
debug window showed the value for "[Job #]" didn't have quotes around it.
Here is the modified insert SQL line:
'create the insert string
sSQL = "INSERT INTO [TimeCardMDJEFF]"
sSQL = sSQL & " ([Man Name], [Job #],"
sSQL = sSQL & " [name], [Date],"
sSQL = sSQL & " Workdate, [Day],"
sSQL = sSQL & " [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) & ";"
Debug.Print sSQL
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
babs said:
Steve,
I added the comma after name on both sql - but now getting a different error -
In title bar of box it says Search error 3075
inside - syntax error in query expression '42.05'
I looked it up but can figure out what is wrong???
inserted code below for both sSQL
I had to go out of town and just got back - really want to get this figured
out today if possible - thanks soo much for helping,
Barb
'- 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) & ";"
Steve Sanford said:
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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
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
:
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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
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
:
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),
Workdate,Day,ST(for hours in straight time),OT,DT,ActualRate - there are more
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
