Code Help

[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

 

[SkipVought]

You might want to post this in forum705