array issue

[hair]

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

 

[namliam]

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 Sub

Hope 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

 

[hair]

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

 

[hair]

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

 

[namliam]

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

 

[hair]

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

 

[namliam]

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

Regards

 

[hair]

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

 

[namliam]

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

 

[hair]

yes, that helped


Thank you

 

[namliam]

I thought you had it working?

Regards

 

[hair]

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.

 

[hair]

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

 

[hair]

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...

 

[namliam]

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

 

[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

 

[namliam]

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

 

[hair]

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

 

[hair]

talked too fast
as usual
its working but making planning for the days that are not checked..

and for the checked ones nope

 

[namliam]

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