Code Help 1

[ptrifile]

I use the following code to automatically schedule a group of people.

DoCmd.RunSQL "INSERT INTO tblShiftDetail " & "(EmployeeNumber,ShiftNumber,ShiftDetailDate,numberofhours ) VALUES " & "('" & vEmployee & "', '" & vShift & "', '" & vDate & "', '" & vDate & "');"
        vShift = 1
        vcount = 1
            Do While vcount <= 16 ' need # of employees for this shift
                vEmployee = vEmployee + 1
                If vEmployee > 27 Then ' change to however many employees you have
                    vEmployee = 1
                End If
                ' store employee and shift and date in detail new record
        DoCmd.RunSQL "INSERT INTO tblShiftDetail " & "(EmployeeNumber,ShiftNumber,ShiftDetailDate ) VALUES " & "('" & vEmployee & "', '" & vShift & "', '" & vDate & "');"
                vcount = vcount + 1
            Loop

I am attempting to change the following line of code so that when i want to change the number of people that are scheduled for a particular shift number that it will pull data from either a table or a field on a form instead of having to go into the code and change it there:

Do While vcount <= 16 ' need # of employees for this shift

I thought this would be simple but i cannot get it to run properly, i am not getting any errors but my schedule is not being produced properly. I have tried something like this:

Do While vcount <= DLookUp("[need]","tblshiftneed","shiftnumber =  '1'") ' need # of employees for this shift

and

Do While vcount <= forms!frmtestpulldata!m1 ' need # of employees for this shift

Again, I am not getting any errors but I am not getting the desired results. I am not sure if anyone can help me with the information I have given here but if you can, I would sincerely appreciate it.

Thanks for any help or suggestions.

Paul

 

[MajP]

should be easy to debug. Also I think you can simplify a little

...
dim vcount as integer
dim ShiftsNeeded as integer
shiftsNeeded = .. either a reference to a control or a dlookup
'for Debug Purposes you can delete later
msgbox ShiftsNeeded

for vCount = 1 to ShiftsNeeded
  debug.print vcount
  vEmployee = vEmployee + 1
  If vEmployee > 27 Then ' change to however many employees you have
    vEmployee = 1
  End If
  'store employee and shift and date in detail new record
  DoCmd.RunSQL "INSERT INTO tblShiftDetail " & "(EmployeeNumber,ShiftNumber,ShiftDetailDate ) VALUES " & "('" & vEmployee & "', '" & vShift & "', '" & vDate & "');"
next vcount

 

[ptrifile]

Thanks MajP! I tried something similar to what you suggested earlier and still had issues but I missed that i wasn't storing the number as a number.

Your suggestion worked perfectly and I thank you SO much!

Paul

 

[MajP]

am not getting any errors but I am not getting the desired results.

Your biggest friend in debugging code is Debug.print, and Msgbox.
My code is peppered with debug.prints and at times message boxes. Especially in writing sql strings and whenever looping.
My code would look more like this when developing

dim strSql as string
'verify values passed correctly
debug.print "Employee, Shift, Date " & vEmployee & ", " & vshift & ", " & vdate
strSql = "INSERT INTO tblShiftDetail " & "(EmployeeNumber,ShiftNumber,ShiftDetailDate ) VALUES " & "('" & vEmployee & "', '" & vShift & "', '" & vDate & "');"
'verify I made a good sql string
debug.print strSql
docmd.runSql strSql

 

[Andrzejek]

I would also question:
[tt]
strSql = "INSERT INTO tblShiftDetail (EmployeeNumber, ShiftNumber, ShiftDetailDate) " _
& " VALUES " & "('" & vEmployee & "', '" & vShift & "', '" & vDate & "');"
[/tt]

If Employee[blue]Number[/blue] is a Number (as the name suggests), you don't need single quotes around vEmployee, same for Shift[blue]Number[/blue]. ShiftDetail[blue]Date[/blue] suggestes it is a Date, so instead of single quotes around vDate you should use #'s

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.