Help in shortening VBA

[Joelyue]

Hi,
i need help in condensing the following vba statement, i have a total of 35 sql to insert.
How to i use the Do...while or For... Next to shorten the statements?


SQL1 = "INSERT INTO tbl_shiftPlan([Date],[S#1],[S#2],[S#3],[S#4],[S#5],[S#6]) VALUES('" & InsertDate & "',' ',' ','2','1','1',' ')"
SQL2 = "INSERT INTO tbl_shiftPlan([Date],[S#1],[S#2],[S#3],[S#4],[S#5],[S#6]) VALUES('" & InsertDate & "','2',' ',' ','1','1',' ')"
..........

DoCmd.RunSQL SQL1
DoCmd.RunSQL SQL2
.........

 

[namliam]

Is there a logic to your VALUES('" & InsertDate & "',' ',' ','2','1','1',' ') ???

Or do you simply want to "for" the RunSQL part?

 

[Joelyue]

I would like to start with the SQL part first. I have tried using For.....Next for it but i keeps giving me syntax errors.

 

[KeithG]

Can you explain what you are trying to accomplish? It looks to me you are inserting random records. Let us know the whole picture and we can help.

 

[boblarson]

And repeating fields like:
[S#1],[S#2],[S#3],[S#4],[S#5],[S#6]

make me wonder if this is properly normalized.

 

[Joelyue]

The table is not properly normalized, that i have to admit. I am still working on re-structure the tables and database. Basically the table holds shift schedule information for 6 of my shift staff.

[Date]-Date of the shift
[S#1]-Staff no 1.
[S#2]-Staff no 2. .... etc

What i am trying to insert is their shift schedule which has a 35 day repeating cycle.
eg 1 = first shift, 2 = 2nd shift, blank = off day
InsertDate is a user input date field in my form which the user inputs the first date of the 35 day cycle.
My form has a command button which will insert the following SQL so as to automate the manual entry of shift schedule.

Hope this information helps. Thanks.

 

[WayneRyan]

Joel,

Granted, its not normalized, but why do you have to enter the data using
VBA? You could definitely cut down on the number of lines of code, but
I really don't see the utility of that.

Isn't there a pretty definitive pattern if you look at the problem as the
schedule for 1 person?

Need more info,
Wayne

 

[WayneRyan]

Joel,

Just some more thoughts.

Simplisticly, you could represent your table like this:

tblEmployees
==========
EmployeeID - AutoNumber
EmployeeName - String --> Jones
ShiftSchedule - String --> 11221xx11221xx11221xx11221xx11221xx
StartDate - Date

Where x is an off day.

Then a person's status for a day is just:

Mid(ShiftSchedule, DateDiff("d",StartDate, Date) Mod 35, 1)

Then, in a query, you can easily find out who's working the second shift next Thursday:

Select EmployeeName
From   tblEmployees
Where  Mid(ShiftSchedule, DateDiff("d",StartDate, Date+7) Mod 35, 1) = '1'

You can't do that with your repeating fields.

hth,
Wayne

 

[Joelyue]

I want to use VBA because i can enter the data with just a click of a button on the form. I used to be doing it manually and its quite a tedious job keying in like 35 rows of data with 7 columns in each row. Also there is the problem of human error when keying in the shift pattern which cycles itself every 35 entries.

so by clicking the button i insert 35 entries accurately and effortlessly each time. i just have to change my start date and i can append another 35 rows of accurate data entry.

if you need to know the shift pattern, it would be like

2 0 0 1 1 2 2 ---week 1
0 0 1 1 1 1 0 ---week 2
0 2 2 2 2 0 0 ---week 3
1 1 1 1 0 0 1 ---week 4
1 1 1 0 0 1 1 ---week 5

0-off day, 1-1st shift, 2-2nd shift

for some staff their cycle start with week 1, while another week 2 and another week 3 .. etc. After completing week 5, the pattern goes to week 1.

So if you can picture it, their pattern (irregardless which week they start) repeats itself every 35 days.

So if i were to manually key in all the shift schedule for the whole year, it would be like entering 7 x 365 = 2555 entries. (woah that is alot of keying and lets not even go about talking about checking 2555 entries so see if they are in the correct pattern)

 

[Joelyue]

Hi Wayne,

Thanks for your input! It is certainly a new direction which i can look at.

 

[WayneRyan]

Joel,

If the cycle is the same for everyone, EXCEPT for a different starting point,
then you need only 1 stored sequence. That's a very important point. You can
do fine with just one master schedule.

You can store it like: 20011220011110022220011110011110011

You don't have to display/maintain it looking like that. It could be a nice-looking
color-coded "calandar" type of entry.

Each person then has just a starting date.

Then a person's status for some day is just:

Mid(ShiftSchedule, DateDiff("d",StartDate, Date) Mod 35, 1)

You should really resist having to maintain the volume of data (2,555 entries)
in your last post, UNLESS everyone has their own "unique" repeating schedule.
Even then, it would be more like my last post, a short entry defining each person.

hth,
Wayne

 

[Joelyue]

Another operation i need to incorporate is shift changes. My staff from time to time change shifts with each other. i want to be able to enter and display the changes too. Also there are cases of staff taking leave.

By redesigning it this way, how do i incorporate shift changes to the database?

 

[WayneRyan]

Joel,

You just answered the most important question. A master schedule cannot be used
for everyone. Each employee must have their own schedule.

Again simplisticly:

tblEmployee
===========
EmployeeID - AutoNumber
EmployeeName - String
HireDate - Date

Table to store the employee's anticipated schedule for a week. There will be
one row for each week an employee is scheduled.

tblEmployeeSchedule
===================
ScheduleID - AutoNumber
EmployeeID - FK to tblEmployee
StartDate - Date of 1st day of week
Schedule - String --> 0011122

Again the 0011122 can be handled by a form, it doesn't have to look
like a string of numbers.

The form might display the current 5-week window for an employee.

You really need to manage this with forms. You don't want to write VBA code
every time employees swap shifts. Plus the table approach will let you research
and report far easier.

Wayne

 

[Joelyue]

Thanks for the inputs Wayne.

I am still having trouble picturing how the whole thing would work. Especially the form handling part.
So under the tblEmployeeSchedule, the Schedule is - String ----> 2001111002222......
If there were changes to the Schedule, i would need to change the whole string?
Also how would i track the changes?

 

[Joelyue]

Anyway, just to make sure this thread is still consistent with the VBA category....

While i am figuring a new way to organise my database, anyone can help with my original problem for the looping of SQL statements?

 

[namliam]

Well if you make your sql variable an Array
Then
SQL(1) = "Insert..."
SQL(2) = "Insert..."
Then you can do

for I = 1 to 35
    docmd.runsql sql(I)
next I

Instead of docmd.RunSQL you should use Currentdb.Execute

Or why even put these queries into strings first???
Why not run the SQL Directly??