Hello

I attach here my little base to be clear. On the form named Clientcontrat I have the code of the client, the starting and ending date of the contract, and the days + number of agents that has to be covered.

I want to pick up those and (by pressing the button 'planning)populate the table planning with the days and the client code from this form (ex if the contract for the client CLAT begins 01/01/1900 and ends O1/02/1900 with all working days from monday to friday and starting hour 08:00 to ending hour 18:00, then my planning table will have all this month created with the client code, day, startt hour and end hour), and free spaces for the agent name.

I've did a lot of thinking and it's really too much for me. At least an idea of how I should start that straight is appreciated.
What's killing me is that array of dates that I have to create based on the week days. And not only that :-)

Thanks

For one thing, if you have a naming convention STICK to it ... You have one table tblContrat where you added tbl before the "real"name which is a smart thing to do. But if you do that do it to all your tables. But also for your forms let them all start with frm for instance...

Also DONT use spaces in names of forms and tables and fields AT ALL It causes problems....

Then all this french is nice but hard for us non-french people out there.... I have tried to figure it out.... and come up with this code:
Private Sub planningfaire_Click()
Dim rstH As DAO.Recordset 'Horaire
Dim rstP As DAO.Recordset 'planning1
Dim myDate As Date
Set rstH = Me![tblHoraire Sous-formulaire].Form.Recordset
Set rstP = CurrentDb.OpenRecordset("Planning", dbOpenTable)
'get the starting date from the form
myDate = Me![tblContrat Sous-formulaire].Form![datedebut]
rstH.MoveFirst
Do While myDate <= Me![tblContrat Sous-formulaire].Form![datefin]
Do Until rstH.EOF
'skip the first 3 fields, the next (4) is monday
' etc.... exit on match
If rstH.Fields(3 + Weekday(myDate, vbMonday)) Then Exit Do
rstH.MoveNext
Loop
If Not rstH.EOF Then 'if a match has been found
'add to planning
rstP.AddNew
rstP!nopl = Me![tblContrat Sous-formulaire].Form![nocontrat]
rstP!DatePlan = myDate
rstP!heuredebut = rstH!heuredebut
rstP!Heurefin = rstH!Heurefin
rstP.Update
End If
myDate = myDate + 1
rstH.MoveFirst
Loop
Set rstH = Nothing
Set rstP = Nothing
Me.tblHoraire_Sous_formulaire.Requery
End SubHope this will work for you.... BTW i had to change the Primary Key (PK) of your planning table from only nopl (project number?!) to nopl + DatePlan

Regards

The Mailman

Thank you a lot Mailman

I didn't hope for such a reply.
I'll try it now

PS I'll stick to name convention and to english next time :-)

Namliam, it works like a cold beer after a night of drinking...I just had to change rstP!nopl with rstP!codeclt cause nopl was the Primary Key and I needed only the client code to walk there.

Thank you Sir

Originally posted by hair
Namliam, it works like a cold beer after a night of drinking...I will take that as a compliment eventhough
1) the name is MAILMAN ! LOL
2) I dont drink thus dont know what your talking about....

Glad you figured it ....

Regards

Hi again

Do you have any idea where can I find online the complete syntax for the 'Loop' in Visual Basic ? I've searched the site of Microsoft but I got lost.

Thank you

Try pressing F1 in access (put your cursor on the while first)

Regards

hey mailman

if you could explain me a little thing in your code it will be great.

In the line:

If rstH.Fields(3 + Weekday(myDate, vbMonday)) Then


you are looking to see if Monday is selected, and if so you send over the loop to the next instructions. Correct me ls if I'm wrong.

After, you make date = date +1, and you check again the same thing. But how do you check the next fields?? I mean shouldn't you do something with vbtuesday or something, or when is next rst the value vbday is incrementing? I am totally lost there, your code is working just great and I am not understanding this part.

Thanks

Weekday is a function which will return 1,2,3,4,5,6,7 (the weekday of any date) depending on the date you put in and the vbMonday determans that 1 = monday, 2 = tuesday, etc...

So, taking into account that today (8 sept) is monday,
Weekday(myDate,vbmonday) will return 1
rstH.Fields(3 + Weekday(Date(), vbMonday))
Fields allows you to select a field number, your first 3 fields in this case do not matter (they are not checkboxes for weekdays). Therefor skip them, thus the 3 + 1, selecting the 4th field which is your checkbox for monday. If that is true (checked), exit do (exit the loop)
Create a schedual for it ...

On the next loop myDate will be tomorrow (tue sep 9), therefor you will get 2 returned by Weekday(myDate,vbmonday). Selecting the 5th field in your table... and so on....

Hope this helps to clarify the code....

Regards

yes, that helped


Thank you

I thought you had it working?

Regards

It was working since the beginning, but after my calculations it was weird it was working :-)
But now I kind of understood the loop in the form records.

calling Mailman (hopping not on vacation)

I tried but I didn't get it working

If I put two lines in the tblhoraire with the same day, it makes the planning for the first one, and its skiping the second or the third(cause at the end of loop the date is incremented) I killed my neurons but I didn't figure out how to say not to incrempent the date if its another recordset following with the same date

here is the bug:

rstP!heuredebut = rstH!heuredebut
rstP!Heurefin = rstH!Heurefin
rstP.Update
End If
myDate = myDate + 1
rstH.MoveFirst

Its skiping the next recordset if the day checked is the same
can you help with this one? Thanks in advance

actualy, what I should do is an if before mydate=mydate+ 1 that would check the other recordsets for the same day, and if he finds to restart the loop on the next recordset based on the form(rsth).

easy to say in words...:-)

Geepers thats a while ago.... I might help if you would post your db again with the right addapted coding and stuff....

Your trying to plan say monday twice, 9:00-10:00 and 12:00-13:00

If that is correct try changing the place of the mydate line... from after the endif to the else of the if like so:
If Not rstH.EOF Then 'if a match has been found
'add to planning
rstP.AddNew
rstP!nopl = Me![tblContrat Sous-formulaire].Form![nocontrat]
rstP!DatePlan = myDate
rstP!heuredebut = rstH!heuredebut
rstP!Heurefin = rstH!Heurefin
rstP.Update
else
myDate = myDate + 1
End If
This will make the code only add a date when it has reached the EOF of rstH creating a planning for every monday in the recordsset.

Hope that does the trick, Let me know.

Regards

unfortunately its making a infinite loop lile this
I have tried a lot of things but I dont know how to restart the loop, because its jumping after to the next date and missing the rest.

Here is the db, thanks for helping

Originally posted by hair
unfortunately its making a infinite loop lile this
I have tried a lot of things but I dont know how to restart the loop, because its jumping after to the next date and missing the rest.

Here is the db, thanks for helping DUH! infinate loop DUH! UHM ... WAKE UP MAIL!!!! Geez.... sorry my bad.

Also move the rstH.movefirst command like the mydate = mydate+1 line...

Sorry

i did this and it works

Private Sub Commande16_Click()


Dim rsth As DAO.Recordset 'Horaire
Dim rstP As DAO.Recordset 'planning
Dim mydate As Date
'Dim i As Integer

Set rsth = Me![tblHoraire subform].Form.Recordset
Set rstP = CurrentDb.OpenRecordset("tblPlanning", dbOpenTable)


mydate = Me![tblContrat subform].Form![datedebut]


rsth.MoveFirst
Do While mydate <= Me![tblContrat subform].Form![datefin]


Do Until rsth.EOF
'skip the first 3 fields, the next (4) is monday
' etc.... exit on match
If rsth.Fields(3 + Weekday(mydate, vbMonday)) Then Exit Do

If Not rsth.EOF = True Then 'if a match has been found
'add to planning

For i = 1 To rsth.Fields(12)
rstP.AddNew
rstP!codeclt = Me![tblContrat subform].Form![codeclt]
rstP!dateplan = mydate
rstP!heuredebut = rsth!heuredebut
rstP!heurefin = rsth!heurefin
rstP!qualification = rsth!qualification
rstP.Update
Next i


End If
rsth.MoveNext
Loop






mydate = mydate + 1

rsth.MoveFirst
Loop
Set rsth = Nothing
Set rstP = Nothing
Me.[tblHoraire subform].Requery

End Sub


I just moved the loop after adding the values to the table

Mailman, it took me like 3 hours to make it working
Do you think I have any chance to become ever a programer?
maybe I should quit

Thanks a lot for your help, it was really important for me
Nice day

talked too fast
as usual
its working but making planning for the days that are not checked..
:-)
and for the checked ones nope

Try this then:Private Sub Commande16_Click()
Dim db As DAO.Database

Dim rstH As DAO.Recordset 'Horaire
Dim rstP As DAO.Recordset 'planning
Dim myDate As Date
Set db = CurrentDb
'Dim i As Integer

Set rstH = Me![tblHoraire subform].Form.Recordset
Set rstP = CurrentDb.OpenRecordset("tblPlanning", dbOpenTable)


myDate = Me![tblContrat subform].Form![datedebut]


rstH.MoveFirst
Do While myDate <= Me![tblContrat subform].Form![datefin]
Do Until rstH.EOF
'skip the first 3 fields, the next (4) is monday
' etc.... exit on match
If rstH.Fields(3 + Weekday(myDate, vbMonday)) Then Exit Do
rstH.MoveNext


Loop
If rstH.EOF = False Then 'if a match has been found
'add to planning
rstP.AddNew
rstP!codeclt = Me![tblContrat subform].Form![codeclt]
rstP!DatePlan = myDate
rstP!heuredebut = rstH!heuredebut
rstP!Heurefin = rstH!Heurefin
rstP!qualification = rstH!qualification
rstP.Update
rstH.MoveNext
Else
myDate = myDate + 1
rstH.MoveFirst
End If

Loop
Set rstH = Nothing
Set rstP = Nothing
Me.[tblHoraire subform].Requery

End Sub

Regards

Hi Mailman and thanks a lot for help
Is doing the same, an infinite loop


I already tried this version.
I am really stocked with this one

I have been verry bussy for a while... thus unable to surf the web. But is this still a point or can i leave this be?

Regards

actualy is still not working, and I spent hours and hours trying to figure it out.

I thought you let it go too(and didn't want to press you or something), but if you have a while to "throw an eye" as french people say, I would really appreciate it.

I will put the base on working process on next monday. I can do the contracts line by line even if this one is not working, but it would help a lot if it would.

Thanks again man

above sample seemed to work for me.... Will have a look see tomorrow....

Regards

It seems to work just fine for me. I attached the working sample for ya....

You had better note me in this app lol....

Regards

Mailman, I'll note you with some wine if you come around. I already proposed that to Mile and he seemed to be interested at the moment :-)

Seriously, if you pass not far away from Alsace it would be a pleasure

I am almost finished with my app , it would have been impossible without this forum, really.

I am not a drinker.... thanx

But just promote the forum, maybe mension my name or even my company :)

Regards

the forum I am doing it all the time
your company ..what's the name? :-)

Hit my homepage :)