The pop-up filter does not filter the name selected in the combo box

if you don't build a new table with  one SME_ID per SME_Name you are in for more troubles as you go on.

just imagine an SME_Name with 3 or more id...

Hi Cap,

This can take time to build a new table. Eghtebas suggested a good query and it works because now I have a different name in the combo box and the duplicates are removed. Now i'm trying to filter this.

I'm thinking....a SME_Name with 3 or more ID...that could be like this: one SME_name, so one record...and the SME_ID containing diffrent ID for just one record ?

it is up to you, i am just giving you my opinion..

Hi Cap,

I followed your opinion and created a table with one sme name per ID, then I linked it to the combio box, not it's on the form FrmFilter2. I'm still having trouble to apply it to the records selected because it does not return any value.

The d/b with the new table is here:https://filedb.experts-exchange.com/incoming/ee-stuff/3666-calendar-blank-copy-v9.zip

I hope you can find a way for this.

Thanks again
888

Is there anyone that can help me on this please?

Thanks

stronghold888

set the tag for Filter3 to


Tag  smeid


select Marc Lamontagne or Francois Bernier

check the tblCalendar for the values that are valid in the SMEID field

Hi Cap,

It seems that work for some names and not for the others. Instead of checking the table TblCalendar, I had to check the table sme_name and then the second table, TblSME2 and change the values in the SMEID field following the values in the sme_name table. By inserting the value of the last repeated name, it will show all records related to the name selected. That works....but it will only select records that were already in the database, not new records and for that I have to reset again the value in the TblSMe2.
Matybe I'm wrong somewhere or do you think there is another way?

tnanks again
stronghold888

stronghold888,
you want to do it your way, go ahead..

Hi cap,
I just asked if there was another way because each time a record is entered, I have to change the value in the SMEID field so the record can be filtered. I can't keep on updating this field each time a record is entered and that's why I asked you if there was another way to link the table I created to the other table.

stronghold888

stronghold888,
you have to update tblCalendar smeid field, but you have to use the smeid from the TblSME2

example
in the tblCalendar
smeid
27


table sme_name
smeid     smename
27          Pat Owen


table TblSME2
smeid    smename
11          TblSME2


you have to change all occurences of 27 in the smeid field of tblCalendar to 11
do the same for the rest


         

also, you have to change all other smeid that was assign to  Pat Owen to 11
do the same for the rest

Hi Cap,

I have to change the occurences to sme id 27 so it can watch the other smeid in TblSme2? That means I have to replace the field smeid in the TblCalendar with the other in the TblSme2 ? I normalized everything the name of Pat Owen to 27...but what to do when a new record is entered? In the form,an activity can also be assigned to a new owner and the same owner can use more than one activity. From this, I have to re-update the field smeid in the table TblSme2 :o(

<.but what to do when a new record is entered?>
just use the existing smeid.. you don't have to create a new one.

hi again stronghold,
Just took a look at the filter form. I will study the relationships for a while and ask some questions. As before, maybe we need a different approach.
jvf

Hi Jvf,

I know we need a new approach on this one. I tried everything, but the last that works, it works only on the old records that already exists, but on new records it does not. That's the hard part because the Directorate field is linked to the Activity field and this last one is linked to the SME field. So when a Directorate name is picked up, the Activity field will show activities that belong to this directorate and the SME field will show the business owner names that belong to an activity. It's possible that a business owner name is not related to a special activity, so in this case the user can add it to the list. That's the hard thing because the filter won't filter it because  of the ID associated for each business owner.
The first approach was to link these fields and it was long before users ask me to filter these business owner names.

Feel fre to ask some questions to have more clarity on this.

Thanks
stronghold888

Hi stronghold,
I just saw this post after I sent you the one about the form. This gives me something to study so my next questions can be more direct.

See you soon on this one, jvf

Hi stronghold,
I have studied your filter problem but still dont have the complete picture of what the users want because of the complicated nature of directorates/ business owners and activities. One of your explanations states that the relationship goes from directorate to the activity then to the sme table. So far, so good. But, I see two activity tables and you seem to refer to both of them in the filter form code. Could you give an explanation of how TblActivity2 fits in?

Also, you stated once    ... That's why when a new record is entered, if for a directorate, let's say OPS, we need a new activity name, this activity will be entered and added automatically to the list& I dont get which table is receiving these entries. Could you explain this? Thanks, jvf

Hi Jvf,

The first table, TblActivity, was the initial table before. Now the table in use is TblActivity2
The table sme_name is still the original table. I had to create a second one to try to filter the records because with the initial table, it never worked and the query inserted never worked on this one. It seems to work with the table TblSme, but as I explained, it just filter records that are already in the database, but not new records. I need to adjust manually the ID numbers in the second TblSme so it can work.

By exemple, if a new record is entered with the owner Marc Douville, I have to get its ID changed because the ID in the table sme_name changes as a new record is entered for the same business owner name, so I have to adjust it in the Table TblSme so it can be caught by the filter.

To answer to your second question, if a directorate, let's say OPS is selected, a list of activities are ready to be selected in the Activity field. The Activity field contains all activities for all directorates and these activities are in the table TblActivity2. Second thing, again if OPS is selected, but that a specific activity is not available in the Activity field, then the user can enter a new activity name. This new activity is automatically recorded in the table TblActivity2.

I hope now it's something more clear for you and less confusing. Any other questions are welcome.

Thanks again
stronghold888

Hi Stronghold,
OK, let me see if I am starting to understand things.
1) There is an activity table containing activities for all directorates.

2) the list of dictorates and their activities is kept in the DictorateTbl

3) business owners can also be associated with some of these activities. This list is kept in the sme_name table

If the above is true, the activities should be unique and I see at least one duplicate (Legal - s599). Also, I dont understand the ACTIVITY_DRCTE_ID field in the activity table. Has this field been added in an attempt to make the filter work?
 
see you soon, jvf

Hi Jvf,

Yes, you are right on 1, 2 and 3.

On 3, yes business owners an be associated with any activity.

Yes activities are unique and the one you mention, Legal - s599, this one is just used for test purposes. I don'T know why is there because I had to import all of this from an Oracle database and put them in Access.

About the field ACTIVITY_DRCTE_ID, this field was there before. In fact, you can notice that the numbers in this field refers to a directorate name. Number 1 refers to COMM, number 2 refers to DCEO and so on. So this field was there before and it has no any connection to the filter. What I can say is that the other fields in the filter work very well and it is not because of this field. In order to make the field work, I just had to change the query for it several times. The query that you can see in the form FrmFilter (SELECT First([SME_ID]) AS ID, [SME_NAME] FROM sme_name GROUP BY [SME_NAME] ORDER BY [SME_NAME]; ) under the SME field properties changed a lot. As mentioned, it can filter old records, but for new records it's different.

Now, in the form FrmFilter2 (a test form), in the field SME name, I had to modify the query (SELECT TblSME2.ID_name, TblSME2.sme FROM TblSME2; ). The only difference is that the query is based on the TblSme2 because there are no duplicate of names in this table...
The problem is that this table cannot be used because the ID numbers of each business owner name needs to be changed manually so the filter can filter new records each time a new record is saved. That's why I have not found any solution to work with the original table (table sme_name).

Any other questions you may have are welcome

see you around my friend
stronghold888

OK then, lets test my understanding again. The Report wants have records from Tblcalendar based on certain criteria specified by the Filter form. But, the filter doesnt seem to work properly and, to make matters worse, the users keep adding more criteria. Also, no matter what you try, the filter seems to ignore newly added records in TblCalendar that should be included.

If this is correct I am ready to go to work on the problem.

Yes, the Reports wants to have records from TblCalendar based on certain criteria specified by the Filter form.

For now, the other fields in the filter works, but not the SME field and even not the Event Type field. This last one is not yet on the filter form because my query was not working on this. Just for this field, I had to create 2 other different reports...but as you can see, these users seems to ask complicate things for just one report.

And yes, the field SME in the filter form sems to ignore newly added records in TblCalendr that should be included and this is correct, so you can work on the problem :o)

Users told me that these 2 fields are all they want on this report and nothing else...so hope that it will stop when these 2 fields work.

Thanks again

strongfhold888

Hi stronghold, I will take a look now. There are many things to work out. But, I think that one of the original posts by capricorn1will end up being part of the solution. Namely, I think we will need an sme table with unique entries. This would be similar to the TblDirectorate where the directorates are listed.

If this is the case I will try to generate this table from code and see if I can fit it in.

See you soon, jvf

Hi Jvf,

The solution from capricorn1 was the second table, TblSme with unique entries, but I ended up to change manually the ID numbers of each business owner. On the other side, I don't know what will happen when users will add new business owners from the form FrmCalendarModify.

At least if you can generate this table from code and see if it can fit, I hope it will fit. I don't know why is that difficult, but hope you will find out.

Thanks and see you soon
stronghold888

Hi Stronghold,
My brain hurts so I am drinking some expensive Tequila. Either I am drunk or someone else was drinking as the calendar database was created. If I am right, there are so many logical and spelling errors that your report filter will never run until everything is corrected. Or, I am on the moon and everything I say tonight is nonsense. Now, my comments are based on the earlier database I have so if some of the data is fake then some of what I say will not hold.

Lets begin: Based on our last posts it seems that the relationships are simple. There are activities, directorates and business owners. The activities are unique and each directorate and business owner can be associated with one or more activities. Further, the activities listed in TblCalendar are choices that originate in the activity table.

So, regardless of filter criteria, the above relationships must be maintained. Among the errors are: There are activities in TblCalendar that do not exist in either activity table (so using either one will get us into trouble). Also, there appear to be spelling errors of one of the SME names, I find Michele R. de Cotre and Michele R. de Cotret and another Cotre with the French e (cant find how to type it now). Also somewhere in a table, directorate is misspelled in a field name. Also, in the directorate activity table we had a DCEO(legal) but the directorate list only contains a DCEO directorate name. And, like I (and capricorn1) suspected, we need a SME table with unique values.

Ouch!

Well, the making of a unique SME_NAMES  table was easy. This is a new table. The original sme_names table is actually the sme activity list and I changed its name to make it easier to follow. Not so easy, but I have done it, was to pick an activity table (you said TblActivity2 was the real one) and add to it all the activities in Tblcalendar that were missing. I did this just to get everything in order. Also, I deleted the DCEO(legal) directorate entry and erased the Michele R. de Cotret SME entries (and French e just in case).

I have made other changes and some other table name changes. For example, we had a TblDirectorate and DictorateTbl-one was the directorate list and the other was the directorate activity list. ???????? I now have a TblDirectorateActivity-seems better to me. So, finally I can create the necessary relationships in the relationships window without errors and we now have data integrity. Some of the data may now be nonsense but at least it is logically consistent. Also, changing table names will really mess up existing code but I think things are pretty messed up already and some names maybe should be changed.

I think I am now in a position to work on the filter form but I am going to drink some more Tequila and wait until morning to review my changes first.

Please comment on what I am doing before I proceed too far.

See you soon, jvf

Hi Jvf,

At least if you took some tequila, hope it helped to have a more clearer mind :o)

About Michele R. de Cotre, yes one name is good not the others. I had to check and Michèle R. de Cotre is the good name. This name seems not to be used a lot and maybe I will remove it later. I see you've found other mistake errors. DCEO(Legal) is not used anymore and was replaced by DCEO. I think is more appropiate so I send you  a copy of the "live" database with the proper tables.

https://filedb.experts-exchange.com/incoming/ee-stuff/3927-Main-Calendar-Databse--v3.zip

There are no any mistakes in the tables in the d/b I just uploaded, so it might give you an idea. You will also see that the filter is little different, but you don't have to be confused. This one contains 3 check boxes on the filter, so users can just filter the event day they want, 36, 45 or 55. You can use it as a reference because as mentioned, tables there does not contain any errors and lots of changes have been made to business owners names. Some disappeared and some are new. At least, hope it will help and get you less confused.

I agree with you with the idea to have a unique SME talble with unique entries. As you saw in the test database, that you downloaded, this table is already there.
I see you already deleted the DCEO(Legal) and the error with the SME name. You can still look at the d/b I just uploaded for any other errors if you have time. The major changes were just to remove unusable activities and add some to other directorates. In fact, the activity list was considerably reduced. So you are still on the right way :o)

Ouchh!! I just came back after looking at the tables. DCEO(Legal) is still the good one and the other DCEO was removed.

About directrorate tbl, the good one is DirectorateTbl. The other was there before this big change has to be made to the tables. I left it there for later use, but as you can see, I forgot to change the name to a proper name to avoid confusion like TblDirectorate(not in use) and so on.
Tables that are in use are DirectorateTbl, TblActivity2, sme_name, TableApplic. At least these can be used for the filter.

I know that maybe there could be more changes than I thought, but I couldn't know that it could be that big. I agree with your idea to just have a TblDirectorateActivity and that is surely more clear than the other ones. I don't think it will mess with the rest of the code, maybe we have just to change the name in the export module in case it won't work when the filter will be ready.

Well my friend, I hope your second Tequila helped you to sleep better, so this morning, with my explanations, you are on the right way.

Thanks again and see you soon
stronghold888

Hello Stronghold,
Yes, I slept very well! I even didn't drink too much because I feel fine today.

Thanks for posting the "real" database. I thought the Tequila affected my eyes because I couldn't see the forms and tables. Turns out the 'show database window' in startup was unchecked! I will refer to your post and incorporate my changes later this evening.

I notice that the choice for the sme name isn't on the FrmFilter any more. Is this true or should I use the FrmFilter from your earlier blank copy v8? Earlier you posted "...Users told me that these 2 fields are all they want on this report and nothing else...so hope that it will stop when these 2 fields work..." I didn't quite understand what that meant. Could you explain it please?

But, even if the sme table is not used for this filter we must fix the relationships if the users need this information for something else.

see you soon, jvf

 

Hi Jvf,

Glad you slept very well because I just heard how hot is over there. From weather news, it's about 47C, so hope it's less hot now.

The choice of the sme_name was not in the "live" database because I had to work on a backup, but if you wish, you can work on this d/b because it holds a lot of records. The test d/b has only 12 records or less, just test records, but maybe you will have a chance to work on more records if the relationships won'T show another problem.

About the phrase "Users told me that these 2 fields are all they want..." I was referring to the field SME that contains the list of business owner names. The second field is the field "Event Type".(refer to table Stat_Applic). You rember this field has 3 choices of events, GE, BY, BOTH. So users want also be able to filter the report with his field inserted in the form filter. I will try to find where I inserted the solution about the second field (Event Type) and post it .

I got some news about Panda tech support. They suggested me to follow some different steps and if it won't work, then they will refund me. Let's hope that it will work...

See you around
stronghold888

Hi Jvf,

I had to do an exhaustive search through of all versions of the database to find the query, so I uploaded the d/b here: https://filedb.experts-exchange.com/incoming/ee-stuff/3930-DB-with-query-for-the-Stat_Appl.zip

In the Query section, you will see 2 queryes : TableCalendarQuery(GE+BOTH) and the other same name with BY+BOTH. I tried to insert them in the filter, but it never worked. In fact, you know that in the table Stat_Applic, we have 3 values, GE, BOTH, BY. Well, in the filter form, users wanted only 2 values, GE and BY. That means if the value GE is selected, it will show statements marked with GE/BOTH. On the other side, if BY is selected, it will show statements marked with BY/BOTH. You know, you used the same technique when we worked on the export, to export statements for a GE that contains GE+BOTH values....same thing applies to the filter.
First we need to finish to add the filter for the business owner names and then this last one.

Hope to see you around my friend
stronghold888

Hi stronghold,
So, you noticed that we are now roasting. Again, I am very lucky. The cloud cover returned and it is at least 10 to 15 deg C cooler at the beach.

OK, I am going to download the posted db and check things out.

Hi Jvf,

I hope you are not that roasted now. Again, you are very lucky to be near of a beach. Are there any sharks? Hope not :o)

On the posted d/b, it was similar like the one posted 2 days ago, I just inserted a query for the field Stat_Applic....but I noticed that it returned only a few records, not all records. It worked on my test d/b, but it seems to work different there.

Let's hope that it will work with the SME table. I'm still trying to solve this and now as my eyes want to close (it's not night yet), I will drink some red wine so my brain can relax a little.

See ya around :o)
stronghold888

Hi Stronghold,
Boy, being retired is keeping me too busy! I am just starting to look at our filter project. I hate to say this (its funny, really) but the real database has an error I found right away. You mentioned that the good table is DirectorateTbl. So, to check the assertion that DCEO(legal) was the good name, I decided to look at TblCalendar. Only the directorate IDs are recorded so I made a query linking the Directorate ID fields to look at the DIRECTORATE_NAME but got a type mismatch error. It turns out that although the DICTORATE_ID is a number in the DirectorateTbl, directorate ID is stored as Text in TblCalendar, not a number. This value must be stored as a number in TblCalendar so I corrected it.

Of greater concern is that the Directorate activities are stored in the DirectorateTbl. If a directorate can be associated with more than one activity then just as with the sme names, we really do need more than one table. We need a directorate list and a sme list with names only. Then, we need a list that contains the activities associated with the directorates and sme names.

For our colors in the spreadsheet our code looks like this:

Select Case rs_statements![DirectorateID]
                       
                            Case Is = 5 ' "OPS"
                                MyFontColor = vbGreen
                            Case Is = 1 '"COMM"
                                MyFontColor = vbBlue
                            Case Is = 15 '"LEGAL"
                                MyFontColor = &H4080&
                            Case Is = 2, 14 '"DCEO"
                                MyFontColor = &HFFFF00
                            Case Is = 3 '"IT"
                                MyFontColor = &HFF80FF
                            Case Is = 6 '"PFACS"
                                MyFontColor = &H4080&
                            Case Is = 4 ' "NROE"
                                MyFontColor = vbRed
                       
                       End Select

The Directorate ID comes from TblCalendar but had to come from a directorate table (the Directorate names are from your earlier code). As you can see, the DCEO(legal) directorate name is not on the list but the name is now DCEO(legal) in DirectorateTbl so we will change the code comment later. Also, theDirectorateTbl has 9 entries but we only have colors for 7 of the entries. That is OK, we dont need colors for all directorates, they will just end up being black. But, we do need a unique list of Directorate names. So, perhaps it is coincidence that DirectorateTbl has unique entries with no repeated directorates and so can hold the directorate activities for now. But, this will not hold for long if, as the users say, a directorate can be associated with more than one activity.

Fortunately, this is a small database and no matter what, we can fix things relatively easily. What I need is verification of the unique Directorate names. Are they as shown in the DirectorateTbl. If so, I will separate the activities into a new table like I did for the sme names. After this is done, our filters will start to work.

I have another question about the event days but I will save it for later.

Hi Jvf,

Oh boy, that's a huge change. I know this database is not as perfect as it had to be. As for other directorates names you saw in the database, they are not useful and we do not need them in the export module. They are there for an unknown reason, so I have to ask my supervisor when she will be back if these values are really useful or not. I think she will agree with me  on this point.

As for DIRECTORATE_ID, I made this stored as a Text just because I needed to filter things in special queries. It was stored as a number last time, so it's ok to leave it as a Number.

You are lucky that is a small database and it's gonna grow later when I won't be around to see how it goes.

Your suggestion to separate activities into a new table like you did for the sme names is good. Although as it is, it already worked with the first 2 fields in the pop-up filter (Directorate and Activity). I just hope that even if we have better tables, these 2 first filters will work as the others we need them on (Sme and Event Type).

You can ask any question about the event days. If you took a look, the 2 fields for the event days are working, but if you have something in mind, I will be pleased to answer any of questions you might have.

Thanks again my friend
stronghold888

Hi stronghold, I will be trying a filter soon. Had to work on my van again. Since it is hot, the water pump is complaining. I am going to get another one ASAP!

Hi Jvf,

I remember that your van is very old. You know, I had a similar problem once when I had my Jeep Wagoneer 1987. I had to get rid off it after 2-3 years of use because the water pump was not working properly and most, I lost lot of coolant from the cooler.

I hope yours will work fine when you will change the water pump.

Good luck and see you around
stronghold888

Hi stronghold,
Sorry for disappearing. The new water pump didn't completely fix the problem so I found a new (well, used really) van just a few kilometers from my house. Unbelievable! I searched once more before getting ready to go around LA all day and being stuck in traffic while looking for one. But, there it was very close. So, I went and got it. Of course, it has problems but not as many as the old one.

About our database, I guess your supervisor is still on vacation so we don't know the "real" directorate names. But, it doesn't matter for now. Since I have fixed the relationships ( I think), this means we can try some queries and if the names change it shouldn't affect anything. Unless she deletes some names we are using!

So, now that I have some transportation again, I can return to making a filter.

See you soon, jvf

Hi Jvf,
Don't worry about disappearing. I know that it pas important to fix your car first :o)
I think maybe it's better if you switch to a new car. It may cost some more money, but you don't have to invest on repairs. So the old van is in a cemetery and the other you bught is good? Hope the repairs it need won't be that expensive as the other van.

About our database, my supervisor is already back. And do you know what? The other supervisor before asked me to build another calendar because a possible by-election is coming in september and the writ can be dropped on July 28. Geez, I changed the 55 day calendar into a 51 day calendar. It took me about 2 hours because there were too many things to change (change the field EventDay 55 into 51). The code in the module was easy to change, but I ran into some problems. I fixed them in time and it was about the starting date because the 51 day calendar starts on a Saturday, so I changed it as well as the tblWeeks51 and everything worked fine.
Guess what? When my supervisor came back from vacation, she asked me where was the 55 day calendar? I told her that the other supervisor told me to remove it and replace it wikth the 51 day calendar. I don't know what happened or if it was a misunderstanding between me and the other supervisor....but she want the 55day calendar back! *sigh*
Yesterday, I had to work in the evening on it, so now the form has 4 Event Day fields, 36, 45, 51 and 55.

The only problem I have is with the switchboard. It seems that I don't have enough space to add the 55Day calendar (GE and BY) on the same switchboard. Do you think if I change it to a BY switcboard for the BY calendars and a GE switchboard for the GE calendars, it will affect the export code? I don't know if I have to make some changes to it or not.

The other problem is that my supervisor encoutered an error message "Error in CheckEventDay Function". I saw this part in the module code, but I don't know why it appears. By exemple, if we have a similar event day for 36, 45 and 51, this error message is not expected to appear. Maybe only if the event day is not part of the properly week table. Am I right? I attached the error message along with the "live" database that contains the 51 day calendar. There's the link: https://filedb.experts-exchange.com/incoming/ee-stuff/4064-database-v7.zip

On the other side, my supervisor was impressed with the new functions about the tracking versions, choose either to save as a version or not. She will submit this to other staff and when it's gonna be approved, I will make the necessary changes e.g add the 51 day calendar and so on.

About the directorate names, there is no any change so you can still use the "real" directorates that are in the database. In the live one I attached, these names did not changed either, so I don't think they will change.

Hope to see you around my friend
stronghold888

Hi Jvf,

I finally fixed the "Error in CheckEventDay Function". I found out that it was just for the EventDay36 field because if the field is empty while the others are filled, this error will appear. I just removed it from the EventDay36 properties, but it is still active in the module. Do you think it will have some effects on the whole export? I checked and the export still works.
I had to work until 7:00 p.m and my brain ran out because I had a very stressing day. In the previous post posted at 03:03pm EDT, I asked you about the switchboard. I found out that it can handle more buttons. Initially I knew, but as tired as I was, it took me more time to figure out. In the form's switchboard properties, I changed the ConstNum to 10 (it was at 8 before), so 10 buttons can be used on the same switchboard. I changed the code, added 2 more buttons and I changed also the switchboard_parked and the original switchboard because I had to re-add the 55 Day Calendar.

It took me another hour because I made mistake on mistake...but I had to stay sharp. Finally the switchboad was finalized. It does not look good anyway, but at least I was happy to see the last buttons on the GE/BY events.

It didn'T take long until the devil was around me....I pressed on the 55 GE calendar to see if the export works. Guess what? It didn't! I had a error message "Expected event day 55 at 54". Well, I had the same problem with the 51 day calendar and I fixed it because I forgot to change it to start on a Saturday. But for the 55 day calendar, it's just weird. I copied the same code, no changes...but still this error appears. After 2 hours of trying, I gave up and I was headed home because I started to have a big headache.
I noticed that my supervisor inserted "n/a" in some eventday fields. I checked if it has any effect on the export because if by exemple we have just EventDay36 with a special day and not the three others, that mean the statement won't be exported for the other calendars. That's logic...but...I noticed a big change in the TemplateBook. Some of statements are in bold and some days numbers are coloured, not lie before. I thought that someone changed something in the export module, so I checked each line, but everything was normal.
I just don't know what to do and my supervisor needs that 55 day calendar! She wanted the 51 day so...the 55 day can wait, no? because if a possible election will occur in september, then if will be for a 51 day, not a 55. Watever..I told her she can export the 55 day calendar in the previous backup versions  of the database while I have to fix it. The problem is how to fix that small error? Maybe you have an idea...
There's the database: https://filedb.experts-exchange.com/incoming/ee-stuff/4069-Copy-of-Main-Calendar-Databse-v.zip

Hope to hear from you soon
stronghold888

Hello stronghold,
Yikes! Supervisors sure can mess things up. I was looking at the filters and decided to see if you had posted anything new. I will look at the errors this weekend.

I have a question about the filter form. One one of the forms we have a choice of SME names because you mentioned the users suddenly wanted that choice. Do they still want SME names? What is the main thing the users will want?

As you know, it is easy to make choices that will result in no records being returned. If we just fill the combo box list all the possible choics it doesn't mean that all the combinations will result in returned records.

For example, I made a form once with combo boxes so users could make choices to run a report. I made it so that they could start anywhere on the form and choose any field first. I made sure the recordsource of the combo box would only return choices that were in the table. Then, as the users made other choices I would inspect the table and make sure this next combo box would only return data from the table that also contained previously chosen data. If there were no records that contained previously selected choices then that combo box would not contain any choices. This method insured that at least one record would be returned for the report. But, it was complicated and very slow (maybe it would be faster now with newer computers). I really don't want to try it again but it would help to know something about what the users might want to limit the combo box choices somewhat.

For instance, it seems that the either the directorate or actvity might be the most important choice. Based on that, the other combo box could be filled with choices that actually existed in tblCalendar. Also, some SMA names could be returned based on the activities or directorate chosen. But, if  they chose an SME name first, we could return only directorates or activities associated with the SME name. If the SME name is unlikely to be chosen first I would also hide it until a directorate or activity is chosen. The idea would be to slightly filter the choices for filling the combo boxes. But, to make it easy for us, users would still be able to choose criteria that will result in no records being returned.

Also, I thinkI will keep the EventDay range hidden until the users pick a calendar choice. Then we can limit the choices to valid event days.

I have been wondering if it is possible to eliminate the EventDay36, EventDay45, etc fields from tblCalendar (and now the new EventDAy51 if it must stay). It seems that once a calendar type has been chosen, the Eventday might just be filled in a generic EventDay field. But, I don't know the in and outs of the process very well. What do you think?

Anyway, the errors you are having are probably due to a problem with making the new TblWeeks51or an unplanned change in TblWeeks55. I will look at that first.

Hope some of this makes sense. I didn't spell check it either!

See you soon, jvf

Hi Jvf,

About the filter, in fact the combo box with SME names should be able to filter previous records in the table as well as new records. When I made the test with the second SME table, it returned only records that were already in the table, but not also new records even if the SME name was the same. I'm still trying to find a way similar with the other fields that are on the filter ex Activity, Directorate, EventDay. These fields return anything in the table as well as added records.

In fact, you are right, if users choose only the SME names, it will return directorates and activities associated with this SME name. Basically, it's the same thing, if users choose a directorate name, all records associated with that directorate will be filtered. Usually, in the form where the records are added, as you probably noticed, the SME field is empty. A list of possible users will appear in the combo box as a Directorate and an Activity will be picked up. Otherwise, the field will be empty.

About keeping the EventDay range hidden, you mean, hide it until one of the 4 check boxes for the calendars is active? It's a good choice. In fact it was like that before when we had only the 36 day calendar, but as calendars changed for different event days, I left it as it is. So if it can be hidden until a calendar check box is active, I don't see any problem.

About eliminating the EventDay36 from Tbl calendar and keeping only the EventDay51, I don't know...in fact, maybe it will look confusing for the supervisor to have only one EventDay field when she will check the records in the form. Did you noticed that for now, in the form some of the EventDay fields have a "n/a" option? It was just so users can understand that for a specific day, a calendar statement is not available. Of course, with "n/a', the calendar statement won't be exported for a specific calendar. I already tested it and it seems to work. If we eliminate the 3 EventDay field and keep the new one, I don't know how it will work. What I am afraid of is that could change and new EventDay could be added, so I have no idea what will happen. I can't predict the future...but it seems already complicated to change everything as a new event day is needed. In fact, eliminating the eventday fields 35, 45 and 55 and keep the event day 51..what will happen to the entries in the table? they will disappear or? Because I noticed a lot that my supervisor like to look on the other calendars, (36,45,55) to be able to judge if a calendar statement is good for another election or not.

Yesterday I was thinking about making a generic EventDay field that could generate a possible EventDay and automate the rest....but I realized how many changes it will involve. At least it's a good idea and that will eliminate the changes in the module code. Maybe not all...but at least it's an idea and I'm not sure if we can apply it to this database.

About the errors, not sure where they are from. I checked tblWeeks51 and Tblweeks55 and everything seems ok. As I mentionned in the previous post, the error" Expected eventday 55 at 54", I had the same error for the 51 Day calendar, but it was because I forgot to change the code in the module, the one about the starting day. So for the 55 day calendar, it's the same thing and there is no change...so I'm still looking at the code, but I don't see the error. Also we need to change the 55 day calendar to start on a Sunday instead of a Monday. My supervisor told me that it was a mistake to start it on a Monday because she did not notice that Day 0 will always fall on a Monday, never on the rest of the week.

Hope some of my explanations make sense. Don't worry about the spell check, I can understand through it :o)

The problem will be more difficult to handle if we have new eventday for a different planned election. Having this, I have to change the switchboard to handle more buttons. It does not seem that difficult, but just having to adapt the code each time without running into errors. Did you notice the changes of Days when the calendar is exported in Excel? These event days are not in their normal color that is black...most of them are, but the rest changed suddenly to green or brown. I don't know where they came from as the code was not changed about this. I also noticed that some of the statements are in bold. In fact they never were in bold before and in the form, there is no bold activated for any statement. I don't know if it's the computer that begins not to like the fact how I treat him or because there are more statements in the database than before (before 274, now about 286).

I will drink some wine, not too much, but it will surely refresh my brain for a little time.

Hope to see you around my friend and any questions you might have are welcome
stronghold888

Hi stronghold,

OK, Ive also had some wine and studied the new database errors. For starters, I could not find the ConstNum property to add switchboard buttons and try everything fresh. So, I had to use your posted database. Where can I add this number?  I notice you have correctly added the new switchboard calls. This is an excellent start.

Unfortunately, the last databases that you have posted do not incorporate all the changes that have been made along the way. So, it is becoming more difficult to find the problems. For instance, somewhere along the way, the Checkentries code on FrmCalendarModify has been lost. We had the call to the code placed on the afterupdate event and passed the calendar type. So, if a new calendar type was added (51 in this case) a new textbox was added (Eventday51) and the call to the code would be placed on the afterupdate box like so: =CheckEventDay(51). In addition, we might have modified the code to account for the new event day (I dont think we would have to in this case). If you inspect the form and look at all the eventday textboxes you will see that of the four, two are missing the call to the code and of the two that have the code, one is incorrect (eventday 51 calls for CheckEventDay(55). However, the CheckEventDay code itself is missing. I believe it was placed locally on the form to make the code easier. I must go back and check earlier posts and versions to find the code again. We only added this code so the users could not enter an event day that did not exist. I guess that is sort of a good idea.

In addition, there are at least two other places the code would be modified. We would add the 51 day calendar to the fill weekdays code because calendars can start on different days and also change the jvf export code to account for the new event day. In each place, we use a Select Case and so would add the Case for the 51 day calendar.

This is how we would change the start day of the 55 day calendar after changing the tblweeks55.

Also, perhaps because it has not been officially approved, the switchboard calls go to an earlier jvf module where we ask the questions one by one to the users instead of the later version that opens the Popup form where users answer all the questions at once.

My brain is getting tired now so I must continue in the morning. Can we determine if the supervisor wants to keep the Popup form? This will help. Also, it seems the main problem is that they keep adding different calendars. Adding different calendars (or changing start days) is one thing. We can make a list of how to change the code that would not be too difficult. But, I will continue to think about the event days because now the different calendars need fields and textboxes on forms to accommodate them and this is not good because it is more complicated and will affect reports and things as someone tries to add fields and textboxes everywhere.

Hi Jvf,

About the code that checks for a valid day entry, it is still in the module, but I had to remove it from the AfterUpdate event in the EventDay36 properties. The reason was because I noticed that if this field is left blank without any day entered, it will produce this error "Error in CheckEventDay Function". As you explained, this error will appear if an invalid error day is entered...but then why it appears if the field is left empty? My supervisor saw this error and asked me where it came from...so it came from the function in the EventDay36 properties. We can put it back, but how we can prevent the message to appear if the field is left empty?

About changing the number of buttons for the switchboard, use the switchoard_parked form and click on the properties of the form. In the properties of the form, click on the "On Current", then scroll down until you see this line "Const conNumButtons = 10". From there you can add more and more buttons on the switchboard. Unfortunatelly, the code does not create these buttons automatically, so after changing the number, the buttons need to be duplicated manually to reach the number wanted. And yes I made the changes to the switchboard_parked table...but as you can notice, the call to the 55 day calendar affects the "Return to Main switchboard" button. I don't know why...:(

On the other side, about keeping the Popup form, yes my supervisor is interested. As you can see, I hd no time to implement it on the "live" database. She added new calendar statements and since the "live" database is not in a replica mode, I have to copy/paste everything. It's boring...but I can't go faster than that. When I was asked to produce the 51 day calendar, it took me time to change the code back so I was already late in implementing the Popup form. At least my supervisor told me it is not an emergency, but the emergency is that the 51 day calendar and the 55 day calendar must be ready. She told me that this Sunday, the writ could be dropped any instant, so the calendar needs to be ready. As you can see, it is ready...but I'm trying to fix the errors with the colours on the event days when it's exported. I believe it may come from the EventDay properties of each EventDay field. If as you mentioned, this code calls to check the function...maybe it made something and the export of the calendar statements look different. I don't know if it is because of this or because of something else.

As you can notice, yes the main problem is by adding different calendars and the code change a lot. It is not good...and when I began to build this database, it started with the 36 and 55 day calendar....then 45 day was added and now 51 day...I don't have any idea when it's gonna end *sigh*
Also  my supervisor asked me to change the start date of the 51 day calendar for a Sunday instead of a Saturday, so I'm gonna change it tomorrow. I advised her that if it starts on a Sunday, then Day 0 won't fall on a Monday. That's for sure and I can'T imagine something else if statements for Day 0 are not on a Monday but on another day of the week *sigh*. They will find out and she told me it was just a "cosmetic correction", nothing more...so I don't have any idea how it will go.

About the Select Case as you mentioned, I think I added it in the code in the latest database I sent you. Is it in the right place? Hope to.

My eyes start to close and with the warmth and humidity outside, I can't think clearly. I just hope that everything will be ok with the database.

Hope to hear from you soon.
stronghold888

Hi stronghold,
OK, I understand better. will see about the problem when the field is empty.

 About the event days. I noticed something in tblCalendar. For the EventDay fields (EventDay36, EventDay51, etc.) there are entries for each EventDay. So, this means the users can make different calendars but use the same activities which now occur on a different day in the calendar? If this is true then our idea of having only one EventDay field might not work because I don't see a pattern between the different Calendars *sigh*. O well, we still might think of something to help make new calendars easier to add.

jvf

Hi stronghold,
Well, I made some fast changes (always dangerous) and posted a new database. I tested the export code for a few Calendars and it seemed to work but of course you need to try it. I also tried to fix the CheckEventDay code. I still couldnt find it so I found it in an earlier version, modified it and put it back.

Now, this database version does not have any of the changes I have made in preparation for a new filter form. We are just checking the new 51 day calendar and the revised 55 day calendar. But, I did include the Popup form. Also, I believe you have a copy of the new template book but I included it just in case.

As we have mentioned, other than making the Weeks table for a new calendar there are several places in the code that need to be changed. Some things are sort of duplicated because it was easy and who knew we would be making new calendars? I am still thinking about how to simplify adding a new one.

https://filedb.experts-exchange.com/incoming/ee-stuff/4127-Calendars_copy_legend_add_Cal51.zip

See you soon, jvf

Hi Jvf,

Yes it is right. Users will use different EventDay fields for the same electoral statement. I see that having only one EventDay field might not work, but at least what we have is ok. The other thing to make easier calendars to add is just change the code as I did for the 51 day calendar...but I don't know if there is a way to automate the process for the users without having to ask to someone that knows how to change the code in Access. I could explain how they can add more calendars for the future...but it could happen that they could make mistakes and run into errors. This fact could occur for someone that does not know Access. *sigh*

Also I was thinking about something...maybe we could chat on MSN or Yahoo to exchange our ideas and find something. I don't know if you have time for this, but if now, we can continue as it is. I don't mean to get everything faster, but it's an idea because if you need to ask me specific questions, you might get a more faster reply.

I see you made a dangerous change so I will check it. I have a copy of the templatebook, but I will use the one you already attached cause I think the one I have could be corrupt. Maybe it's because of that that colours changed some event days on the exported calendar.

Hope to see you around my friend and thank for that fast post
stronghold888

Hi stronghold, I am able to check in earlier today. This is still the best way for me to get messages. Mostly I am slow because I do not have time to read and study the posts. I hope that my changes were correct. I guess you will be finding out soon!

We can think some about automating the process of making new calendars and I may be able to simplify the way we change the code. But, unless they get lucky because we think of something, I think that someone will have to know Access to change the forms and make the new table. But, that is the cost of them changing their minds unless they hire a programmer full time.

If my posted database seems OK I will also go back to looking at the filter. I must study your earlier posts again and make sure I have the directorate list straight. If not, I will post a question.

I am still thinking about the silly event days. If we could just figure out how to account for new ones without adding fields and textboxes then the whole change process would be much easier.

See you soon, jvf

Hi Jvf,

It's not a problem if this is a better way for you to get newest messages than the chat program.

I downloaded the d/b this morning, but it seemed corrupt. I don't know why, but it may because of the network, not because the database changed. I re-downloaded it this afternoon. I have to re-make the switchboard because the last buttons are not visible in the database you sent, then test if the export of the 55 day calendar work. It will take about 30 mins to apply the necessary changes so I just hope not to run into trouble.

I agree you with the idea of better automating the calendar, and yes, if they can hire a programmer full time, it will get better....but if not...well, they have to read my explanations on how to add new event days and change the rest of the code. The thing is that is very easy (at least) to change the code because most of your explanations are there, so there's a way for them to find out how.

Did you have time to check the export function? because as I told you in the other posts, it seems that the coloured statements change also some of EventDays on the Excel calendar. I have not been able to find out where it comes from because the code in the export module changed only for the new EventDay 51 calendar and the rest of functions remained intact. Also I'm having problems with Excel each time I close my computer at work because Excel thinks that the TemplateBook file is still open. In fact, it's always closed...I will run some other tests on another computer to see if I can find something. If not, then I will tell my supervisor that some event days on the Excel calendar need to be changed manually as I won't be able to fix this.

Hope to see you around my friend
stronghold888

Hi Jvf,

Finally, I found out where the errors on the event days that were coloured in Excel. The problem was the Excel calendar itself. Last week, my supervisor saved by mistake an exported calendar directly on the TemplateBook sheet. I erased all the data saved ion this template, but it was kinda too late. I used the template you already attached along with the database you posted earlier and now everything works fine! Geez, it took me about 5 days to figure it out! I thought it was the code itself...but in time my doubts became different. Oufff. I hope not to deal with this next time he he

I was able to run the database you fixed. I had to change the paths back...but as I changed them, an error appeared "Data expression" and it opened the VB editor showing this line in yellow "Private Sub Value_AfterUpdate". I thought it was maybe that the path was not changed in dbpath.txt, so I changed it. Unfortunatelly, the error bounced back. When I finished to change all paths in the Admin template data, the error stopped to appear. I will take a look again this evening, but not sure to find where it comes from.

The second problem comes from one of the buttons on the "GE/BY events" switchboard. As the export of the 55 day works better, I clicked the button "Return to Main Switchboard". Guess what happened? It started the pop-up form about the 55 day calendar. That's weird...so I checked all commands in the switchboard table as well as in the switchboard_parked table and everything seems to be fine. I still have the same error in the "live" database as well. The additionnal buttons seems to work fine, their label names are OK...so I dont't know where to look. I will try to look into the switchboard's form code. Maybe there's something I missed in the past 2 days.

My supervisor decided to remove the "n/a'' from fields that had no any specific day. It's much better as it is and everything seems just to work. Of course if we can figure out how to account for new event days without adding fields and textboxes, the whole change process would be easier. For now, what I can see as a possible solution is something...could be more fields, but these event day fields won't be visible until a new event day field is created. It seem a complicate solution, but it's something for a start. Maybe you can find a better idea and we have time to discuss it a lot until we can find a solution.

Hope to see you around my friend
stronghold888

Hi Stronghold,

Remember our discussions about the dangers of using an Excel template if the users mucked up? O well, it still seems easier but keep a copy handy. I wonder if we could hide the file?

I just dont understand all the problems we have as the database travels back and forth. I guess it has mostly to do with Microsofts buggy programs. For example, unlike the beginning, the last few you sent me had the database window hidden. Also, I had a missing Excel reference in your last post and that has never happened before.

I also could not see all the new switchboard buttons at first so I put the form in design mode and stretched it out to make it taller. This worked and then I could see all the buttons. It must have to do with screen size and resolution.

We dont use the dbpath.txt anymore because now we dont save anything from Excel so Im not sure about the AfterUpdate error. But, I did get the 55 day calendar opening when trying to return to the switchboard. Pretty strange! I will look at it tonight.

On one hand its good to leave the n/a out. But, it shouldnt matter now. What we do is look for a number. If the field is blank or has text we just exit the code. If there is a number we check to see if it is a valid eventday for  the chosen calendar. So, they could type in Mary had a little lamb if they felt like it.

I will test this db for a while tonight and think about the event days.

OK Stronghold,
My brain is turning. In order to make the filter work and perhaps solve the eventday issue, I need to know more about the relationships of the fields in tblCalendar. For instance, I have recently learned that a statement can be used in more than one calendar and that was quite a surprise! There are other fields in the table. Are these fields associated with the statement or the calendar? Hopefully it is either one or the other for the rest of the fields. Here is the field list of interest:
Activity
DirectorateID
Rel_Link
EMSQCode
StatReq
Stat_Applic
EMS_Item
CheckLink
CheckEMS
CheckReq
SMEID
Comments

So, what I am hoping is that these fields will always go with the statement. For example, if a statement is in both the 36 and 45 day calendar, the values of all these fields will be the same for both calendars. This is the way it is set up on the FrmCalendarModify but I need to check anyway.

For example, if a different form was set up the CheckEMS field might be yes for the 36 day but no for the 45 day. I hope this is not the case. Also, there might be more than one business owner for the chosen activity. So I hope that for the statement only one business owner applies. Again, FrmCalendarModify only allows one business owner choice but who knows what might happen elsewhere because I havent studied all the forms.

 If these fields always go with the statement (and only one business owner is correct) I have an idea about the eventdays and making new calendars.

see you soon, jvf

Guess what?
I found the switchboard problem by setting a break point and watching for the Select Case value for the command. It should be 1 for returning to the main switchboard but it was 8 for executing code. There are 10 buttons on the form. The on click event looks like this for the first button:=HandleButtonClick(1). So, the () must refer to the button position. This button position is used for the lookup filter so the switchboard knows what to do. But, after button 8, the other two still said =HandleButtonClick(8).  So, seeing 8 (a coincidence between button number and command number) the code would execute the command for button 8 which is one of the 55 day calendars!  I changed the HandleButtonClick value to (9) and (10) for the last two buttons. If you look at the Switchboard table you will see that ItemNumbers 9 is with Switchboard ID of 7 (the export form).and the Command is 1 for going to the main switchboard. ItemNumber 9 will be chosen when the button has (9) as the HandleButtonClick value and now it works! I didnt see anything for button 10 so I guess it is not used yet?

Anyway, go to design view and change the On Click values for the last two buttons like I did and it should work.

HI Jvf,

About the database, things also happened. Just a few wees ago, it collapsed and I was not able to recover everything, so instead I had to re-build everything from a backup copy. I had a strange error message "Network connection lost". It also happened on my computer at home and it was not because of a network drive or anything else. As you said, it comes from buggy Microsoft. Access is not a high product, but these problems can be solved if we can have a special access to its core. I tried this before and I failed. I need to study something on how to access its core and see how to debug the rest. If I was a hacker, things would be easier...but I'm not and I wish to be...but it will come later :o)

You mentioned something about a missing reference. On the latest databases I sent you, I worked on with Access 2003. I think you are using a 2002 version, so that's why you saw the message about a missing reference. Unfortunatelly, I have not found anything that can change this automatically when the database is opened with a lower version than 2003. What I have to do is to run a second test on the database on another computer at work that has Access 2000 installed so I can check if all references are there and re-add the reference to Excel 9.0 Library.

About the switchboard, you just stretched it? For the buttons, you did not add some manually as I did? Because when I made the change, I changed the ConstNum in the switchboard's form code and added the buttons manually. In fact, I never tried to see if it can be automatic by just changing the ConstNum variable to a higher number than 8.

As for the 55 day calendar, you were lucky that it worked at first sight without any other error. It was not the same on my side and I still have to review the rest of the code and compare it with yours to see where I made a mistake or forgot something else to add. When it happens, could be mostly because of a great fatigue.

As for the calendar statements, yes, they can be used in more than one calendar. In fact, if the calendar statement is not gonna be used for a special event day, then the event day field will be left blank and the calendar won't be exported. Also a new calendar statement can be added if the others are not appropiate. So things works perfectly on this side.

About your question on the other fields, yes they are associated with the calendar. As you can see, these fields are mostly used in the report to have a global idea and see everything, every field that belong to a special calendar statement. You know already the use of Activity and Directorate field, so about Rel_Link field, it's used more to "connect" question numbers from an Oracle database. In fact users need to enter them manually as I had not figured a way on how to connect the database on the ORacle one because this one is connected on multiple servers across Canada so...this task is very complicated.
EmSQcode, same thing, question number
StatReq, it refers to an law article so a description of the calendar statement can be checked if needed
StatApplic, refers to the event day  as a GE, BY or BOTH
CheckLink, check box for the check link field
CheckEMS, check for a question from the ORacle database
CheckReq, check box for the Statutory Requirement field
SMEID, a number that identifies the SME business name
Comments, comments for the electoral statement. An electoral statement can be used for more than one calendar, but it can also contain a comment so users have to read it it they want to see this electoral statement on the calendar or not.

To answer your question about if a statement is in both 36 and 45 day calendar, the values of the fields mentionned are the same. You can look at it in the report using the form FrmFilter to have a global idea. The filter itself is not perfect. Some fields like Directorate and Activity can be filtered together, but in most cases is does not work perfectly. I had to build the filter so it can filter one thing at a time, not all...but surprisingly it can filter both Directorate and Activity fields. AS for the business owner, yes, one owner can deal with more than one activity.
So FrmCalendarModify allows only one business owner choice, not multiples choice of names. I already asked this to my supersivor and she told me that in fact, for a calendar statement, one business owner will be selected, not other. But but, if the calendar statement is the same, not identical, then it will be entered as a new record and the proper business owner will be selected or a new name will be entered depending of the calendar statement.

So finally, these fields always go with the statement and each statement has only one business owner despite the fact that the business owner selected exists also for another activity in another calendar statement. Hope your idea will work.

see you around my friend
stronghold888

Hi Jvf,

You mention that the switchboard problem it came from the value of the buttons after button 8. I already changed the value by inserting =HandleButtonClick(9) for button number 9 and =HandleButtonClick(10) for button 10 when I made the changes to ConstNum. Maybe I have not looked as carefully as I had to.

About the button 10, you are right, it is not used. I thought I had 9 calendar events + the Return to Main Switchboard button!

Anyway, I will return to the Switchboard and change check if these values are already changed..

20 mins later. It still does not work on my side. In the switchboard table as well as the switchboard_parked table, the button "return to main switchboard" is already marked with SwitchboardID7, ItemNumber 9 , Command 1 and Argument 1. I checked the HandleButtonClick for button 9 and it was correctly configured...so the error must be somewhere else. Anyway, I will take some sleep and tomorrow I will read your explanations and try to see where is the mistake.

have a nice night and see you around tomorrow
stronghold888

Hi stronghold,
I have to leave for the day but want to check in. Since you are having errors I was going to post the database again but I am also having troubles with the database. Now I get an error about the template and can't export anything! But, last night it seemed to work fine. I must have changed something by accident. Or, maybe it is the weather. I will fix it tonight. Perhaps you should also take a break for now.

On a more positive note: I worked on a plan to add or change calendars. I believe we can do this with a user form and make most of the new calendar. We ask for the type (36, 45, etc) and Start Day. It looks like all the calendars have similar event days to a point. For example they seem to go from the type down to -26 and then get different. Our code can make the calendar and fill in the event days down to -26 and we open a form where the calendar is laid out and they can change and fill in the rest. They could also use this form to rearrange an existing calendar

This will require some new tables but I tried them and it looks like a good start. Also, like I said, this will help with the filter. First I must fix the silly export problem and then I will show you my idea.

 About the tables (new or existing): As I have mentioned before, everyone (including administrators) MUST STAY OUT OF THE TABLES. This is one of the Cardinal rules of working with a database. The problem is that everyone is used to working with a spreadsheet and the table looks like a spreadsheet (or they try to design a table like designing a spreadsheet). But, in many instances, a properly designed table will contain fields with only ID numbers linking to data in another table so it wont make any sense anyway.

Unless a table is really small and I dont need to access it very often I dont even go there myself. I always make a form.

As you know, the power of a database is to link tables together and work with the data. At the very least, users should only work with queries so the right fields can be gathered up for display. But, even here, one wrong keystroke or mouse click can destroy or alter data. On a form we can have some control over the data for protection and integrity.

One more thing: If my new idea works then the switchboard buttons to export are no longer be needed so these problems with lots of buttons will go away. We will use one button and open up the Popup form and users will choose all the export conditions from there. This might be a good idea anyway with all these button issues.

Hi Jvf,

Finallu I can connect on the internet! The network was shut down at my work for the day so I was not able to check your messages. At least at my home it still works despite the fact that it's very warm, about 30C...but I can manage that :o)

It's strange that for a sudden you can't export any calendars on the database you worked on. Could be the weather or something else. I took a good break at work because as the network was shut down, I was not even able to access the ''live'' database that was on another drive. I worked on a backup copy and guess what? I haven't found any way to fix the "Return to main switchboard" button. I think the better thing is to remove the buttons and put them back. Maybe it will work...but the thing is that I made the change to both switchboard items table and switchboard items_parked. On the last database you sent me, I noticed you made the changes only on the swithcboard items table, so the switchboard items_parked was not changed at all. I will try just to come back and then change only the switchboard items table and leave intact the switchboard items_parked. Maybe then this last button will work...

Your idea looks interesting and I don't see any problem if by doing this, the filter will work better. I understand that it needs some new tables and that is ok too. About the tables, I already noticed the users in the user's manual and also my supervisor not to touch these tables or alter it in any way. What will happen is if they mess up with, the database will collapse and it's gonna be its end. You know, when everything is finished, I planned to use the ULS (user level of security). Again, it's a build-in wizard in MsAccess. That will password-protect the database and add levels of security for the tables and the rest of items. I made some tests, but it is not really secure because if you use the SHIFT button, you can unlock it and have admin access over all accounts! As least, there seems to be no other secure alternatives...maybe I will do some searches on internet later when the database will be fully operational

In fact, users use only the FrmCalendarModify and the FrmFilter. As you know, they don't have any special knowledge with the tables, forms and the rest of things. So far so good. My explanation in the user's manual is still good, so they never missed with anything...only my supervisor that saved an exported calendar directly on the TemplateBook. I think she was too tired and did not notice that

As you said, I know that a small keystroke or mistake can alter the data. If we can have some control on the form and on the data integrity, this control must be something that cannot be breaked. I found out that even if you have a form very highly protected, anyone can break in just by using any ctrl+alt+del or other controls. If I can gain some access to MSAccess core, we can change that...

I agree that if your idea works, the export switchboard won't be no longer needed. At least that will eliminate for good the hassle to change the code if new buttons needs to be added on the export switchboard. I can't wait to see how it will work! :o)

Hope to see you around my friend
sronghold888

Hi Stronghold,

Boy, these databases are getting all mixed up. I found the After Update error. It was on the adminTemplateData form where we used to use dbtext. So, I deleted the bad code and also edited the form label. I sure thought this had been done before. But, when opening the form I also saw my error. I forgot to put the correct template path in! So, this is why I couldnt export anything this morning. Here is a copy that hopefully will run. But, use the admin form and check your paths.

If our new idea works, I will soon be posting yet another version without all the export buttons. If I understand your explanation then I think the new idea is a good one. I will work on it later and post with an outline of what I want to do. If it seems OK then I will post a test version for you to look at.

https://filedb.experts-exchange.com/incoming/ee-stuff/4162-Calendars_copy_legend_add_Cal51.zip

see you soon-jvf

Hi Jvf,

I see that you found the error and that's good. On my side, I have to fix another error with the After Update event because it's something similar to the error that came from the ChecknFunction message. I think I forgot to activate or desactivate something. I will compare it with the database you posted adn hope to find the error faster.

see you around my friend
stronghold888

OK, I will work on my idea tonight and try to post soon. We should really stop trying to fix errors that might go away with a new approach.

Here is my idea: It actually has two parts, one for the filter and one for the calendars. We have already discussed my idea of the new tables for the directorates and business owners and their activities (which is the main issue for this post). But, since logically the whole database is so wacky, we really need to address all the relationship issues for any filter to work smoothly. We can go over this again soon but first here is the calendar idea:

We should separate the statements from the actual calendar table. We will have three tables, a calendar type table, a statement table and a calendar table.

The calendar type table contains the calendar type (36,45,51, etc) and the StartDay and will only have these two fields.

The statement table contains all the fields about statements like we discussed.

The calendar table (new TblCalendar) only has four fields. 1) Its own ID, 2) the Statement table ID (called StatID in the original TblCalendar), 3) calendar type, and 4) the event day.

Now, how does this work? We put a subform on FrmCalendarModify (we should probably rename this form to something like FrmAddStatements). The subform will link to the new TblCalendar with the Statement ID fields. When users add or modify a statement they can assign it to as many calendar/eventday combinations they want. OR, they dont assign it to any calendar. Or, they could assign it to a calendar but not an event day yet. I have no idea what combinations the users would actually need but they all could work.

This method has many important advantages: Now we have eliminated the need to keep adding the EventDay fields in TblCalendar when the users make new calendars. Also, we dont have all the Switchboard export buttons anymore because one button will launch the Popup form Also, we would use the calendar type table and its associated form to make new calendars or change event day patterns in existing ones. This means that we should not have to change any code when a new calendar is introduced!

I have already made the calendar type table and separated an existing tblCalendar into the new Statement table and new TblCalendar. I have a simple statement form/subform like described and it seems to work very well. Now, I havent changed the export code yet but that will follow soon.

What do you think? The short term drawback to this is that if users are adding anything to the database these days they will have to stop so we can split up any existing data in TblCalendar and fix the directorate/ business owner tables. I think it would be well worth it to them.

So, we would make a test database with our changes and you could make sure the filters and reports worked. Then we would stop the users and modify the real database and have them try it.

Hi Jvf,

I don't see a problem to separate the statements from the actual calendar table and have three tables, a calendar type table, a statement table and a calendar table. I think the way you explain, these tables look already more logical than the ones I created. Also it's a good idea that the calendar type table will only contain the calendar and  StartDay fields.

As for the combinaisons, I don't even have an idea, but users could use any possible combination. I don't know it there will be electoral events greater than a 55 day event because this one was very very exceptionnal. Usually it's between 36 and 51...but things could change depending of the length of the election and the start date. Sounds very interesting to see that with this new method, any new calendar type won't need a special change in the code.

I think that it's just wonderful! Just take your time with the export buttons because there is no need to be in a hurry. As you said, dangerous changes could be...dangerous! :o)

I don't see a problem to make a test database with our changes and make sure the filters and reports works before implementking this on the "live" database so users could try when it will be ready.

Take some break or drink some tequila before attacking this. I will be around if you are looking for some other answers to questions you might have.

see you around my friend
stronghold888

OK Stronghold,
You can be sure I will have some tequila during this process! Actually, we  have already started both ideas. I have a database where I have fixed the directorate/business relationships and a database where I have made the new calendar tables.

So, first I will combine all the tables into a single database and make sure we can add statements, business owners and activities like we expect. Then I will finish the code to make new tables. Next we will adjust the existing export code. Finally, we will fix the filter form.

Then comes some new territory that you will have to look at first. There are some other forms, buttons and reports that I havent studied yet and they will all be affected. So, you will have to lead us through this jungle to the other side as we modify these items to use the new tables.  

Piece of cake!

See you soon (I hope)-jvf
 

Hi stronghold,
I forgot to mention something in my last post. For your database there is nothing wrong with the existing TblCalendar. The problems are with the Directorate/Business owner/ activities tables. But, since the users are starting to change the calendar requirements and do not have a full time Access programmer, we need to try and change the way calendars are made to help with their future needs.

I remember an earlier post where we discussed adding fields to a table rather than having separate tables to add similar data as requirements change. Sometimes, if one is familiar with Access and VBA programming, it is much easier to just add fields and make a few changes once in a while. So, for example, we add fields for the separate calendar event days to TblCalendar and  have EventDay36, EventDay45 and so forth when a new calendar is needed instead of a separate table. If we only have a few calendars this is a good idea. I have done this many times for databases I personally use.

But, if users change their minds too often or there is no one around to make these simple changes then we must complicate the code to make things easier in the long run. It seems like we are in this situation now. So, we get to work.

Hi Jvf,

I hope it's a piece of cake as you say he he. For the other things in the database like the reports and other tables, everything is linked to TblDirectorate, except TblArchive that is not entirely dependant of TblCalendar, but it has the same Directorate,Activity,Owner list like you saw in the form FrmCalendarModify.

Guess what? Yesterday I had to work until 8:30p.m! First I wanted to leave at 4:00pm, but my supervisor asked me if I could change the 51 day calendar into a 52 day calendar. Ok ok ok I will do this...but the entire process slowed down then all computers went down! I had to wait for about 45 min to re-access my account and the rest of the network. Changes were completed successful and the 52 event day calendar worked. Then today, she asked me to change it back to the 51 day! Well, no more play with that. I told her if she does not receive any directives from the rest of staff, then she could work on the 51 day calendar, not on the 52.....but today she had her head in the clouds and worked on the 52 calendar and then asked me to change it back to the 51 day. It took me only 20 min and I made dangerous changes "on the fly", but everything works. Geez, I hope not to do this again. Also she asked me one thing "The calendar can take care of calendar statements if there are holidays?"
Well well, she should tell me that a long time not now! I told her that he calendar cannot guess if there are holidays and move the statements to another day. Exemple on Sept 4 it's Labour Day, so if a by or ge election starts let's say by the end of August, then if we come to Sept 4, everyone does not work, then the statements will move to the next working day.

Hmm, I don't think it can't be done. It's too difficult to guess if there are a holiday or not in a specific month then move part of calendar statements on another day. Finally I told her that the best thing is to move these calendar statements manually. You know, I made once an Excel calendar that generates monthly calendars where you can add holidays and other activities and can be generated on a 12 month basis. Just this one, well we can't adapt to the database because the coding will be complex. I told her to forget about these holidays...if it happens, well a manual change can be made even if it takes about 15 mins or so, but it's better than nothing.

As for the event days, the users won't change their minds. What they will do, is try to generate calendars that could have different length days like these ones we have 36, 45, 51, 55....the next ones could be for a 60 day event or greater...or even for days less than event day 36. I think we can as you proposed....but if users change their minds to get a "cosmetic" change....well this one is different and it will be up to them. By exemple, the 51 day calendar that start on a Sunday date...when I finished to buold it, my supervisor told me that it does not look "good" and must start on the other Saturday, not the first Saturday. By exemple, I the 51 day calendar began with SATURDAY, SUNDAY, MONDAY, TUESDAY, WEDNESDAY, FRIDAY.  instead of having this, I had to change the week to display this: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY. As you can see, the first 6 days are empty on the calendar and the next one, the 7th day will be filled with the statements and so for the rest of event days (51, 50, 49, 48, etc).
This change is "cosmetic" and it was not difficult...but it was only for the 51 day calendar...so I hope any other cosmetic changes won't be needed for future event day calendars.

At least, hope that everything is ok with the database. If you need me, I'm around.

see you around my friend
stronghold888

O my God!

You are working with lunatics! Well, at least I think we can keep up with them sometimes. With our new database the users could erase a calendar and make it again with a new start day. But, then they might have to adjust the event days attached to any statements. This should be easy because we can give them a way to choose a calendar. So, they could go through the statements and change the event days if they need to.

But, you are correct about the holidays. I suppose it could be possible to keep a list of holidays and give the users a message to change the event day but I would not want to try this yet!

Hi Jvf,

Lunatics are everywhere *sigh*

About the database, can we keep old calendars? I think they don't want to erase old calendars because these calendars could be a reference to compare by/ge elections. Of course, they could save the excel file and keep it in a special folder...If we can keep these old calendars apart in an "archive" table, they can play with them if they need to. You noticed there are already an "Archive" table in the database, but it's only used to de-activate statements or re-activate them if needed.

Forget my initial post. It's better to see how it will look before changing anything again. When you will be ready to post a test database then I will have a better idea.

Sorry for the confusion

stronghold888

Hi stronghold,

If the users have exported calendars of course they could save those and they are disconnected from the database anyway. Other than that, whatever is now in TblCalendar will transfer to the new table and the users should see no substantial differences.

Now, when we fix the Directorate/Business owner tables, this might affect some existing calendar records and we will see about that when the time comes.

I have been curious about the Archive table. You say its purpose is to take statements in and out. But, in and out from where? I guess from TblCalendar. I am wondering why this is necessary if the statement is not exported unless it has a calendar and event day.

But, as you say, hang in there until I post and you can see what the new format might look like.

Hi Jvf,

About the Archive table, its purpose is to take statements in and out from TblCalendar. You know, on the form FrmCalendarModify, you will see the button "De-activate" statement. This button will select the record and move it to the Archive table. I had to create it so users can view deleted statements and re-activate them if necessary. Of course, the option to leave them in the TblCalendar could work, but the filter does not have to pick statements that are not active.

We will see about this later because as you said, major relationships between tables changed and other tables were created.

Here is very warm and humid. It rained a lot yesterday but just for 24 hours. At least it won't be like what is happening in England.

see you around my friend
stronghold888

Hi stronghold,

I was doing OK but decided I was getting too simple. For various reasons I had made the Activities table with a single field. Since activities are unique, the activity itself can be a primary key field. But, there are lots of activities. I can see where users might want to start making activity categories and then we would be in trouble. So, I went back to making an Activity table with an auto number ID. But, along the way. I found some data integrity problems that must be immediately resolved.

This is the source of our immediate problem: TblCalendar references some activity IDs that are not in any Activity tables. In the new activity table (TblActivity2) to help the filter, I notice that this ID field is not even an auto number. This is not a problem if there is code to increment the number and I have done this many times. But, I dont see any code to do this. So, when new activities are added to TblActivity2 there will be problems. Also, as I look through the databases the Activity tables can be very different from database to database. Which one is real? This must be a result of all the testing? Also, there are some spelling errors resulting in bad data.

Even stranger, you recently sent me a real database but this one showed very little activities in TblCalendar. The Activity field had very few entries. Where did they go? This doesnt seem right either. To say it again: There are Activity numbers in TblCalendar that do not have corresponding activities in either TblActivity or TblActivity2. This is not good.

So, we are again at a point where we must fix a problem before we continue. I can always alter enough tables to make everything work but it would be good to work with as real a database as possible when fixing errors. Are the users really working on a database and adding statements right now or is everything quiet for the moment and we are just practicing? It doesnt seem correct that TblCalendar in the recently posted real database had so many missing Activities.

If possible, post what you think is the best database you have and I will work with that one. Alternately, I will fix enough tables from what I have so we can show the users how it should really look and they (or we) will have to fix the bad records.

Now I get to have some tequila-jvf

Hi Stronghold,
Another thing about activities. I dont see where the activities might be entered. Were they entered into the Activity table directly? It doesnt seem likely because of the associated directorate id. I noticed some sort of lookup table for the directorates and sme names where activities could be entered in the directorate and sme tables. As I have explained, I never work directly in a table so I dont use this method and dont understand it very well. Were the Activities added to the activity table from these lookup tables?

I guess another way to put it would be this way: Is there be a place where users enter the possible activities or do they just add new ones as they go along?

Thanks-jvf

Hi Jvf,

About the TblActivity, the real one is TblActivity2. I just forgot to rename the TblActivity to TblActivity_parked because this table is not used, just the TblActivity2 is used because there all activities are linked to their own business owner names.

To answer to your question where the activities are entered...well, they are entered directly in the form FrmCalendarModify. By exemple, when users select a directorate name, the activities that belong to this directorate are available from the Activity list. If the activity is not there, it can be entered manually and directly in the Activity field on the form. A confirmation message will ask the user if he wants to add this new activity to the Activity field. I added this message on the form so users can know what is going on and that the activity was added to the Activity table.

So users enter new activities as they go along in the form. They are not allowed to enter activities directly in the table because it will result a mess up with the tables. That's why they can enter these activities directly in the form FrmCalendarModify. The thing is, a calendar statement can have its activity changed many times, depending of the owner of the activity. If the owner does not belong to this statement, then the activity will change, either another activity will be picked from the activity list or a new one is entered.
The other thing is if users want some activity names deleted, they have to ask me directly or ask my supervisor first because we don't want to have problems if users mess up with the tables.

There is one thing I don't understand. You said in your previous post that you see just a few activities, but where do you see them? In the TblActivity2 or in the form FrmCalendarModify? The Table TblActivity can be erased. This one was used before, but as users wanted to have activities for each directorate, I had to create the table TblActivity2.

As for Activity numbers in the TblCalendar, they correspond to the numbers in the table Activity2. It's hard to make a difference, but if you open the report RptCalendar2, you will see that the corresponding activity is there. By exemple, the first statement has the activity number 29 that corresponds to Management-RO. In the report RptCalendar2, the first statement has the right activity. You don't have to worry about that because activity numbers in TblCalendar correspond perfectly to numbers in the table TblActivity2 :o) If there was a mistake, my supervisor would notice me of that, but as you can see, the right activity correspond to its ID number :o)

Hope you feel less confused now :o)

see you around
stronghold888

Hi Stronghold,
OK, Im getting a better idea about the activities. Before I ask some more questions I will answer yours about me seeing just a few activities. Earlier you sent me a database named Main Calendar Databse -v3.mdb and you called  it a real database. If you look at its TblCalendar you will see that, unlike most previous posts, there hardly any entries in the Activity field. Now, I guess it is not a requirement to have an activity associated with a statement but it sure seemed strange because this TblCalendar was so different from all the rest. The other TblCalenders always had Activity field entries in all the records.

Even so, in this real database, most Activity entries are double digit and there are a couple of six digit entries (100001 and 100002) that seem out of place. But, even if a six digit number was acceptable, there are no ID numbers in TblActivity2 with 100001 or 100002. This is the problem mentioned in my last post. In other databases we have, there are entries in the TblCalendar Activity field that have no corresponding number in either the real Activity table or any other unused Activity table.

That, my friend, is a problem. We must clean up the activity table and calendar entries so there is always a match.

This gets to my activity questions for the day: On the FrmCalendar modify I now understand that the users can choose an activity belonging to a directorate or add a new one. This implies a previous list of directorates and their activities but I dont see where the users do this. In any case, if they can choose any activity (or add a new one) then this activity must logically be assigned to the chosen directorate if there is a directorate activity list.

In similar fashion are the business owners.

How was the TblActivity made? Did the users sit around and think up names for the activities for a while? If not, I think they should have. This gets back to what I think is needed to fix the database and make any filters work. What is needed (and what I have done in one of the practice databases) is to have a separate Activity list, a directorate names list and a business owners name list. From this, by using simple forms,  the users make up two more lists (tables): the directorate activity list and the business owner (sme) activity list. Now they have some basis on FrmCalendarModify for choosing a directorate and getting a list of previously defined activities.

However, this approach implies that the users had a need to assign and keep track of activities belonging to directorates and business owners. Do they really need to do this? This seems to be the case because you say that the users start out by choosing an activity from a list of activities belonging to the chosen directorate. This definitely means a separate DirectorateActivityList table is needed.

Now, when they start adding statements and want to fool around with directorates/ activities and business owners, we can define some allowed actions and insure data integrity. For Example, if they insist on any combination of entries we can take action. If they choose to add a new activity first, we can have the directorate list populated with all the directorate names and make sure that this new combination is added to the DirectorateActivityList once a directorate is chosen.

This is a similar issue we talked about when we discussed the filters. Depending on what the users choose first, it can get complicated to keep track. The alternative is to force the users to choose things in a certain order if possible. It is another example of just because something CAN be done SHOULD it be done? In other words, just because users could choose anything first should they be allowed to if the end result will be the same?

Anyway, if it is not really important about which directorate or business owner is associated with which activity then all we need is the Activity list, directorate list and sme list and they just choose what they want. As the statements are made, soon enough a filter would start to find statements.

See you soon-jvf    

Hi Jvf,

I think you are using an older d/b version. I still don'T understand why there are a few entries in the Activity field, but check this d/b here https://filedb.experts-exchange.com/incoming/ee-stuff/4204-Calendar-Database-v9.zip
This one does not have any missing activities, so I hope it's ok for you. Also I checked the 6 digits entries. These entries are old and they were there from another database. I checked again and these 6 digits entries are not in the TblActivity2. Please use the version I uploaded and hope it will be less confusing for you.

About the form FrmCalendarModify, in fact to have access to an activity, users must choose a directorate name first. Doing this, then in the field Activity, a list of activities that belong to the directorate are available. If the activity is not on the list, it can be added and it's already automatically linked to its directorate.

As for how the TblActivity was made, well first users entered lots of activities and sooner, it made no sense to have all these activities in only one list. Just because the search could be enormous to come to the designated Activity name. Later we decided to split it and link it with each directorate. So each activity has its own directorate. If you look in the tblActivity2, activities are together, but in fact they are linked to their directorate and it makes sense. Same thing with business owners names. In the table sme_name, you will see an acvivity ID (sme_activity_id) number next to the business owner name, meaning that the business owner was already linked to its activity that belong to a specific directorate.
By example, if you want to select a business owner first, well, the list will be empty. The logic is to select a directorate, then an activity and then a business owner name. You cannot do everything in a reverse mode. So I agree wit you to have a separate DirectorateActivityList table. That means the business owner names will also be in a separated table like the one we have?

As for data integrity, there are already some actions in the form frmCalendarModify about the Activity and SMe field. By example, when users enter statements first and they want to enter a new record, well, they cannot leave Directorate, Activity, SME and EventDay fields empty. They cannot go to a previous record or to a new one until these fields are filled. For now I had to deactivate some fields, but they can be easily re-activated and the code is in each field.

Regarding the filter, it has to work with the same logic like the form FrmCalendarModify works. Users should select a Directorate, then an Activity and finally a SME name to see the records required. For now, it only works for Directorate and Activity field, but I was not able to make them work together and that's why in the filter these 2 fields work separate meaning that an Activity can be picked up in the Activity list even if there is no name picked in the Directorate list. I know that is not good, but I tried already to use the same code I use in the entry form, but it never worked.

We can made these fields so they can run the filter separately, but the logic should be the same as it  is in the entry form. I tried this, but can't figure out another way to make them work together. You already suggested that the event day fields will be hidden until a specific calendar day is chosen....so we can apply this to the Activity and sme names. Will it work? If not, leaving them work separately is not a problem. The problem is if the filter can filter everything, but we will see soon.

see you around my friend
stronghold

OK stronghold, I will get the latest database soon. Things are becoming clearer. But I still have questions and comments. First, I could already see that the activity list was getting rather large and could be a problem to find an activity. This is why I thought users might start to group activities by some other categories also like polling, Web, legal or a few others to make them easier to find. Since directorates can share activities, it seems rather pointless and not really a good idea to arbitrarily divide the activities among the directorates. Perhaps it is not arbitrary and directorates are usually associated with the same activity list although the directorate could sometimes get another activity assigned with a calendar statement. Then such a DirectorateActivityList would be useful.

Anyway, for now the users decided to group them by directorate then that is OK and directorates could share activities. But, the whole thing gets strange when users ignore the chosen directorates activity choices and add a new activity for a directorate. If nothing else, they could misspell an existing activity when they add it or make it just a little different when it should have been chosen from the master list. Now they could end up with more activities than they really need. So, if they get to ignore the list then we need to somehow make them view the master list before adding a completely new activity.

About the sme names. Your post indicates that the directorate and activity should be chosen before the sme name is assigned. This means to me that a separate smeActivityList would not be necessary because the sme wont be chosen first.

To sum up, let me see if I understand: there is no real importance (other than helping limit choices in a combo box)  to have either a DirectorateActivityList or a smeActivityList because other than the information about the statements, the users dont care about the activities of directorates or business owners. Is this correct? Other than the fact that directorates might tend to keep the same activities, I would imagine that my understanding is correct.

See you soon, jvf

Hi Stronghold,

At first glance the latest database looks better. Lets hope we can work with this one. Since you havent checked in yet, let me ask a question again about the sme names because as I recall, the sme table is part of the original filter problem of this post.

Like I asked last night, is there any reason to keep a list of sme names? If the sme name is added when statements are made and is not used for anything else then we dont even need the sme table. We can always gather a list of existing sme names from the statement list to fill a combo box row source for a form or filter.  

See you soon-jvf

Hi Jvf,

Sorry about replying you late. It seems that a hotwave is heading here from the South, so I don't know if it's the cause why we have network shutdowns from time to time. It happened at my home too, but it was restored within 2 hours. It will happen again until the summer ends. Anyway, it's not the end of the world.

Let's go back to your previous post from last night. "the users dont care about the activities of directorates or business owners. Is this correct?" . Yes this is correct despite the fact that we already told them the logical order.(pick up directorate first, then the proper activity, then a sme name) What I explained on my previous post was about the form where the data is entered. Users cannot access any Activity in the activity list if a directorate is not picked up and it's the same thing for the sme field.

To answer the second question, yes there is a need to keep a list of sme names. It would make things easier if we have this list than don't have this list. Imagine that a user enter a business owner name that is unknown to us, unknown to other directorates...or enter a name that is spelled wrong. We wanted to prevent this by keeping a list of sme names. The only problem is that it won't prevent any errors if users enter a new name, a name already approved by our directorate, but enter it in the wrong way or ignore how to enter it because the sme list is First, Second name to facilitate things. When a new statement is entered, we can use the same sme name and again along with the directorate and activity name.That's why the sme table is needed, but just for the data entry form.

You mentioned that it can always be possible to gather a list of existing sme names from the statement list to fill a combo box row source for a form or filter. Doing this, means we should have a sort of sme table? In fact I know that we cannot use the same sme table for the filter because as I mentionned at the beginning of the post, the row source in the filter gave me a list of duplicated sme names for every record. Not good and that's why a single sme table is needed. Ok I'm thinking.....let's just do this: When the user picks a directorate name from the directorate list, this name will give access to a list of specific acitivities. This one is ok...now for the SME field instead of having it regrouped to answer to a specific activity picked from the activity list, we can list in this field all SME names and users can pick a name from this list. But if the name is not in the list, the have to enter it. I think it will surely facilitate things to filter the report. What do you think?

About the activity list, lots of things happened and I have to remove unuseful activities when users add new ones. Not good either, but the other process would be to add a button to erase the acivity directly on the form, but users can't have this access. They will end up to erase important activities if they don't care about anything. That's why from time to time I get requests from my supervisor to delete unuseful activities and replace them with new ones or just reverse words. And as you said, we already ended up with a lot of activities and then reduced them to a more "shorter" list as the one you can see on the database I posted yesterday. This list will not be always look that short...but anyway, there is no any other process....:(

As you said "Other than the fact that directorates might tend to keep the same activities, I would imagine that my understanding is correct", so that is correct. It will not always end up this way because activities in each directorate could change from time to time. I noticed already that a few ones were moved from COMM to OPS...so it's not that much.

What do you think of my explanation about the SME table? We should keep a list of these names, maybe a table without any duplicated names could work, but it has to work also for the data entry form as well as the filter.

see you around my friend
stronghold888

Hi Stronghold,
Just a short note. Yes, you are on the right track about the sme names. I suspected that users might want to keep them in a list. However, unlike directorates who seem to have gotten a general activity list assigned to them, it doesnt seem like we need to keep a separate list of sme names and their activities. Is this correct?

But, I have always had a thought about these sme names. Like we discussed in an early post about general database practices, ideally they should really be separated into first name, middle initial and last name. If nothing else, this will help the sort order when choosing the name. The users can pick an order. I dont know about Canada but in USA usually names are sorted alphabetically by last names. We had a crazy telephone extension directory list in our company where they liked to sort it by first name. That always drove me nuts.

Sorry for the heat wave. We are having very pleasant weather here at the beach. But, when I go up the hill to see my new jobs, it really cooks up there. Its amazing. If you go only two hundred meters away and up in elevation from the oceans edge the temperature can easily be 15-20 deg C hotter!

See you soon-jvf.

Hi Jvf,

it doesn't seem like we need to keep a separate list of sme names and their activities. Is this correct? -- Yes this is correct. At least, I think it would help with the filter later.

As for the listing,  they are separated as it is: Last name, First name. I was wrong in my previous post and I took a look carefully so names are spelled as it is. I just don't know why they wanted to add a comma between the last and first name. I already told them that it won't facilitate the filter thing.
Usually, names are sorted alphabetically by first name and sometimes by last name as it is in USA, but it depends of the region/province. They don't have a common regulation about this, so by the end, if you want to reach someone, you can reach the wrong person!
So it's better to separate these names in 2 fields, last name, first name than to have them separated by a comma? We don't have a middle name, but it's easy to notice that one belongs to a Mr and other to a Mrs..:)

You're lucky to have a more pleasant weather than here. I think that I should move definitively there if you can still guarantee to have this weather :o) My appartment is up on a small mountain...so it gets very hotter. Not good anyway, but I can't move the sun to other place he he

see you around - stronghold888

Stronghold,
I forgot to mention something very important. It is something I have been thinking about and you mentioned it in your last post. It concerns cleaning up the Activities as they get old or change. Depending on how the reports and filters need to work we probably need to keep a relationship between the Activity ID in the statement table and the Activity itself.

Changing the text of the activity is one thing but if you delete an Activity in the Activity table, the Activity ID in the statement table will now be orphaned and bad things can happen. This is what I would find when checking relationships in some of the earlier databases. We cant set the relationship to cascade delete because it will delete the whole statement record. There is a relatively unknown cascade to null method introduced in Access 2000 where the foreign key will be set to null if a record with the primary key is deleted. This method must be coded because uncle Bill didnt give us a checkbox in the references window.

I dont remember if we require an activity to be assigned to a statement right away. If we do then we dont have to worry about keeping integrity with nulls in the field to begin with. If we dont require an activity right away and we want to insure referential integrity we need to use this method anyway because without using it we would always have to have an activity value in the statement record.

Anyway, its another painful detail for our brains to work on. Maybe we ignore it and dont enforce integrity. In this case, if a user chose an orphaned activity in a filter then no records would be returned.

What do you think?-jvf

Hi Jvf,

About this you are right. In fact, I had no any problem with that. To prevent this problem, the user had to replace the activity with a new one.. and then erase the old activity. Everything was fine, but again, before deleting an activity, I had to be sure that this one was not used in another statements because if it was the case, a problem could occur.

Anyway, if the activity is not replaced by a new one in another record that relates to a different statement, a null message will always keep to appear stating that an activity is missing somewhere. This message appeared some times, but it was mainly because the activity deleted was also shared by another electoral statements.

I agree with you on this case and I think we better enforce the referential integrity. Either way, if we don't enforce the integrity, no records would be returned by an orphaned activity. In fact, it happened some times with the filter even if the there were statements related to the activity and it returned nothing. I just found in some cases it was because of only 1 statement that had this activity! Weird! I designed the filter to show multiple statements but maybe he did not know how to guess that he can show one statement. It's not a big problem. At least now in the latest database I sent you, lots of statements share lots of activities, so that should diminish this problem.

As for uncle Bill...he's too rich to think about that. He just wants to be number 1 and now he's number 2 on the Forbes magazine...

see you around
stronghold888

That's funny, uncle bill is toast!
OK, we will work on referential integrity for the activities. What about the sme names though? Should we fix them into the separate fields? Also, do we really need a separate list of sme's and their activities?
see you soon-jvf

Hi Jvf,

About sme names, what is the best thing? If we fix them in separate fields, in the entry form, there will be only one field hmm. I don't know what it will look like if we separate them.
WE do not need a separate list of sme's and their activities. If we can combine both in one list, it's good.

see you around my friend
stronghold888

Hi Stronghold,
Im starting to get to work on our db. Working for me now is taking more time that I thought so I am a little behind!

Just for fun I thought I would send you the code that attempts to make new calendars. Like I said, it seems that they all go down to -26 so I fill the event days to there. I could have added the form that will enable users to modify the table and arrange the event days but its not quite ready yet.

Try to run it. The make table form should open so just make up any calendar (I limit the choice to 365 days) and choose a start day. A message box says done if you are successful. Then, go to the database window and open the table and have a look.

Like I said, this will get rid of adding the event day fields everywhere when users add a new calendar.

Later I will make a way for them to change the start day if they want like we just had to for the 55 day calendar.

https://filedb.experts-exchange.com/incoming/ee-stuff/4227-Add-calendars.zip 

Hi Stronghold,
The posts are starting to get mixed up. I just got your post about asking how to fix the sme names. I will be looking at that today or tomorrow (Friday). Did you get my post of the code to make a calendar?

jvf

Hi Jvf,

Don't worry, I just got home after an exhaustive day at work and I had to set-up a Ms Exchange Server and I was not able to check my messages on the internet. Now I can. I'm gonna download the database you posted and see how it goes.

Also don't worry if posts gets mixed up...I can find your comments easily because when I receive more than 2 e-mails from this posts, I know that you posted more than 1 comment :o)

see you around my friend
stronghold888

Hi Jvf,

I just downloaded the database you posted. When I opened the database, the make table form opened automatically. It was easy to enter any event day so it's a good start! Of course when I clicked on "Make Calendar" button, an error appeared saying that the tblWeeks36 already exists. I know the rest is not ready, but as I mentioned, it's a good start :o)

Like you said, this will get rid of adding the event day fields everywhere when users add a new calendar, but how users will know that a calendar statement belongs to a specific event day?

I'm gonna get a sleep and hope to feel better tomorrow.

see you around
stronghold888

Hi Stronghold,
Glad you got the database. Dont worry, we will check for existing calendars if we make a new one and the users will be able to easily match the statement to an event day when entering statements to existing calendars..

First, I was fixing the sme name table. In your post you agreed that, unlike the directorates, we dont need to keep a separate list (table) of sme names and activities. But, I notice on the FrmCalendarModify that you were trying to limit the sme name based on an activity ID.

So, I must assume that we dont need to do that anymore. Now of course it means that all the sme names will be available as a choice but it shouldnt matter. Later, we could ask the database to return sme names based on an activity if we need this information to filter a report.

Like I said, the fact that directorates can share activities makes even a directorate/activity table kind of useless. But if thats what the users want then OK. What it means is that if the users dont see the activity they want after choosing a directorate then we must show them the complete activity list. If they choose one, we will add it to the chosen directorates activity list so it will show up the next time this directorate is chosen. If they dont like any of those they can add a new activity.

I think it would be best at that point to force them to go to a different place to add an activity and then return to the statement form. This way we could show a larger form with the activity list easier to see. They would get a better chance to see all the activities and make sure they really wanted to add a new one.

See you soon-jvf

Hi Stronghold,
Hope you are not melting from the heat. It is 1:00 am Sunday morning and Im getting tired. My wife is fixated on a new solitaire game she found on the PC. She cant beat it like she could the other one and is spending hours at a time trying to win. This works out for us because now I can work on this crazy database in the evenings for a while. I hope we can finally win!

I am doing pretty well and the new statement entry form (FrmCalendarModify) is starting to work but the SME entries in TblCalendar are still messed up. I see what to do but I must sleep first.

See you soon-jvf

Hi Jvf,

I'm just back from a small break. I took 2 days and I went with some friends in the north on some mountains. It made me feel better as well as get some fresh air because it became too suffocating for me to live in this city. Now it seems that temperatures will be back to their normal level this week between 24 to 27C, so that is much better than last week :o)

I read your post on Aug 3 and the last on Aug 5, so I agree with your explanations. We can force the users to enter activities in another place...maybe we can build a sort of form that will enter these activities in the Activity table so they can use it later in the form FrmCalendarModify.

It looks funny that your wife is trying to beat this solitaire game. She can find some tricks on the Internet and win these games as well. At least you have some time to work on this crazy database and maybe help her win some, why not :o)

I see that everything works fine and that there is still some work to do around with the SME entries in TblCalendar, but there is always a solution. We all need to sleep so we can work better the next day.

Thanks again my friend and see you around
stronghold888

Hi stronghold,
Glad you got away for awhile. I will get away for a couple of days at the end of this month. My plan to work on the db got messed up some. My laptop hard drive is failing (it's 4 or 5 years old) so I had to share the computer with my wife because it has my back up files! I really need to get my other computers set up.

Anyway, I believe I have fixed the sme names. Now I will check the new statement entry form for a while. Perhaps I will post it tomorrow. If it seems to work I will modify the export code. Then we will go back and enable the users to add new activities, sme names and stuff. Then we can finally work on the filter.

see you soon-jvf

OK Stronghold,
Here is a practice database for statement entries only. Forget the switchboard and exporting. Open the FrmCalendarModify (I havent renamed it yet) and add some statements. See if you can crash the form. You have some code in here to check data entry and I have modified some of it pertaining to the directorate, activity and event entries but tried to leave the rest alone.

We have a subform showing calendars and event days. Each statement can be assigned to any valid calendar and event day. In fact, statements could be added more than once to the same calendar. This might be OK or maybe not. What do you think? We can check it later for anything we like.


Notice that there are some textboxes next to the subform showing the various IDs. We will hide these later but I left them visible as we test.

The directorate, activity, and sme combo boxes are unbound and serve to give the users a friendly view of the choices. Based on their choices, we store the underlying ID in the soon to be hidden ID textboxes. I can explain this more later.

For now, the users pick a directorate first. Then the activity combo box is limited to the preordained directorate activity list. We can add a choice later for users to pick an activity normally used by another directorate or add a new activity. The sme name can be any valid sme name and is not filtered in any way. I found (and fixed) yet another sme name problem. There were entries for Sue Torossian and Susan Torossian.  Good grief. We will provide a way for users to inspect and enter new sme names.

Anyway, try it and see if it works. Later we will export some calendars from both databases and see if the new statement table returns the same results. I sure hope so!

Here is the link. I am on the other computer and dont have winzip installed yet so I thought I would try XPs zip. You make a special zip folder and put files in it. I put the practice db in and in got smaller but didnt change its extension. But, I think you click unzip somewhere when you open the folder. EE upload seemed to take it so let me know how it worked.

https://filedb.experts-exchange.com/incoming/ee-stuff/4275-New-Compressed--zipped--Folder.zip
See you soon-jvf

P.S., I haven't really checked the comboboxes for "limit to list". Also, I don't think I check for a valid calendar, only a valid event day for a chosen calendar so be "fair" for now and try to only add valid data.

Worked for over an hour on a new Administer Tables form (saving as I went). The db crashed and ate all my form work. I'm pissed. I just don't understand how a form can disappear after a small code change.

OK, I rebuilt the form and finally got my laptop working. That only took eight hours. I could have reinstalled everything faster (maybe). It kept doing the log on/log off dance when I restored the image. I finally had to do a drive copy. Good thing the old drive still works!

I hope you are surviving the weather. It looks pretty crazy on the East coast.

I will now modify the export code and see if we export the same calendars as the old databases. This will be our first real test.

Hi Jvf,

My computer crashed and with, I lost several files. I had some luck to recover some files, but the big ones are really lost! :o(
Also my wife is really angry against me because she lost also all her email addresses. I think there are some way to reciover the lost data, but I have to find how. You know, a hard disk can be readable and data on it can be recovered, but I think I need proper software for that and frankly, I've never done this operation before. For now, I re-installed XP, but you know, a pirated copy remains a pirated one and I cannot install Xp Sp2. Months before I was able to re-install it because I found a way in its core to cancel its serial code key check, but now I don't remember how I did. I've to search on the Internet to find out.

Now as everything works, I will download the database you posted and follow your explanations. I still have to re-install Access, but it's just a matter of time and I hope to run everything.

The weather is quite ok here. Now temperatures are between 24 to 29C, so it's much better and we are not roasted ouff :o)

I will now re-install Office 2003, then run the database and come back. Also I will do some searches on EE to see if there were posts about my problem with recovering data. If I can recover it and put it back, then my wife won't get angrier...

see you soon
stronghold888

Hi Stronghold,
Sorry for your misfortune.  Since you were able to recover some files, I assume the hard drive is still spinning. If you reinstalled everything on the same hard drive you are probably out of luck because most likely you have written over some or all of the old data. If you kept the drive separate you may have a chance.

If it is separate, you could put it in the computer and try to look at it as a second drive. But, you probably know that already.

In any case, you could download a demo version of r-studio (www.r-tt.com) and see if it can find anything. I use this one and it has worked for me. But, every time you write to the drive if it is the crashed one, you are increasing the chance of writing over the old data.

Hi Jvf,

I was not able to recover any files, da*n! As it looks, I only have one hard drive...In this case I should buy a new one, re-install Win Xp (not sure that will work because it's already re-installed on the drive that I formatted... :o(
As for now, I re-installed Win XP, but not Office 2003 so it won't rewrite any files that were formatted. I tried Stellar and installed it on another partition then I ran it to check the C partition, but it returned no any data...there was data, but files were empty :o(

For the hard drive, I already have an old one, but I this one work only on an IDE port...and the one that I have in my computer is connected via a SATA cable. I don't know if an IDE hard drive can be connected along with a SATA drive. In the past, I had 2-3 drives, all connected in IDE mode....but I'm not sure it can work with a separate channel. I never done this before, but I might as well try this weekend if I have some more time.

I downloaded the database you posted and tried to run it on my laptop, but unfortunatelly, I did not noticed that MsAccess was not installed on this one. Badly I can't find the CD disks of Office 2000 so I can install Access....anyway, tomorrow I will be at my work and it looks that someone else used my office since I had to work at home and at the other work in another place. Anyway, I will have more luck to run the database you posted and then come with any observations.

About the hard drive, did you tried to recover the data by running the recovery program from another partition? If it worked, then I guess there is no need to install another hard drive and make this one I have (the SATA drive) act as a slave...
I will download the demo version of the software you suggested and try to see if it can recover the data  by installing it on another partition. Let's hope I will have some luck...

see you around my friend
stronghold888

Hi Stronghold,
Sorry you are still having problems. Your old IDE drive should connect to your motherboard even with SATA. You probably have a CD drive and most of them are still IDE so you will have an IDE cable already. But, there should be an extra IDE connector on the cable. Most cables have two but it is possible to get an IDE cable with only one connector. Actually, it will be a two or three connector cable because we need one for the motherboard. Your CD is probably set to Master so the hard drive must be set to Slave. Some computers dont like this and the Hard drive must be set to Master with CD as Slave. Or, you could try the CS (Cable Select jumper (s). This is supposed to make everything work. Sometimes it does, sometimes not.

Anyway, you want an extra drive (or a partition on the drive being inspected is OK) to write any found data. The recovery program wont write to the drive (or partition) that it is inspecting. So, to sum up: one drive is OK if it has at least one other partition. Otherwise you need another drive (of course, either way it needs enough free space). The program might write to a USB drive or even a USB memory stick. I never checked on this.

R studio (like some others) has a filter you can set to look for particular file extensions and this can make things easier. Were your wifes emails in Outlook Express? I think they are stored in a .wab file. See if you can find out.

Anyway, I have tried my first query of the new database tables and it seems to return the same records as the old database. Yay! So, after you get going you will have some new things to check out.

Good luck-jvf

Hi Jvf,

I'm at my other work, so I can now download the database you posted and try it to see how it works.

Yesterday evening, I installed the program you suggested on a different partition and ran it to check in the C partition because it's the one I formatted before. It returned lots of different queries with a name and a number next to them. I had no idea if these files came from the C drive becuse I set the scan to check the C drive. It seems that everything comes from the C drive and I found files in a folder called Microsoft Outlook. They had a .pst extension, so it might be the right ones. The thing is that these files and all other files in other folders have a number next to their exension...so it's difficult to make sure if these are the right files. You said these emails can be stored in .wab, but it seems most they look that they are stored as a .pst

The next thing is to buy this software and give it a try to recover these files. I did not checked it how much it cost, but I hope it won't be too expensive. The demo version can recover files with sizes smaller than 86 bytes...so that's not much and the one I want to restore are bigger than the size limit...so the demo need to be activated to recover bigger files. At least, I hope it will work without having to open the computer case.

I see you had already lots of luck with your first query and that's great! Yay! I will download now the database you posted earlier this week and come back.

see you around
stronghold888

Hi Jvf,

I'm back after downloading the database. I'm impressed with the new look of the form FrmCalendarModify! Mostkly, there was a table to select calendar and event days for these statements. I added some fake statements and it looks like everything works like a charm!
Also, I see that sme business names are now separated and it's much better. Also having all of them in the sme list is much better. I don't know if it will be confusing for the users, but it eliminates some trouble because I discovered that when a name is no longer used, users add a new one, but the old one always needs to be removed directly from the sme_name table.

When I added a test record, I tried to add a fake name as well in the sme list and it seems that for now it is limited to the list itself. I think you suggested before that it's better to add new names directly in the sme_name table? I think it was that...maybe I'm wrong.

For the rest, everything looks fine. I just don't understand how you lost a form after a small change on the code. I think it happens because some bugs are still unfixed in MsAccess. At least, it's always better to work on a backup copy and save it regularly in lots of numbering versions as your code goes on. At least with this you won't lose the form and if you lose it, you can return back to the last minor changes.

I hope you had time to read my previous post at 4:00Pm EDT where I explained the hard drive problem adn hope to get everything fixed soon

see you around my friend
stronghold888

Hi Stronghold,
It seems you are having some progress on your data recovery. Hope it continues! I think the program cost about $100 US but you should be able to find it on their site. Just try and not use the computer very much so you dont overwrite the files. I dont know what the numbers next to the file might mean. Yes, if you are using Outlook and not Outlook Express there will be .pst files. I am really not good at email programs so just study to be sure. The trick is, once you recover it hopefully it can be imported into the program. I think this is easier in Outlook than Outlook Express.

In any case remember the Golden Rule: When the wife is upset, the remedy is flowers and chocolate. Especially chocolate!

Glad to see the new form seems to work. I am getting all kinds of errors trying to export. But, I expected some problems and I am going to work on them now. We will have a simple form for the users to add sme names. When I get the export working again we should think about the new switchboard. As I mentioned, we will only have one button for export. I was thinking that we should put a couple of buttons for the administration of the database. Perhaps one form could lead to others but we need to get the administrator to the basic forms for Calendar administration, adding sme names and the stored paths. Stuff like that.

Good luck-jvf

Hi Stronghold,
We discussed the version numbers before and I think we (or the supervisors) decided it was not necessary to keep track of the calendar type (36, 45, etc) only version numbers. But, per the supervisors request, we can now overwrite versions. This means that a 36 day calendar could be overridden by another calendar type and no longer be a 36 day calendar. I dont think this is what the supervisors would have in mind, do you? I think they would want to keep the different calendars separate and only overwrite the same calendar type. So, I will prepare for this just in case.

See you soon-jvf

Hello Stronghold,
I am finally working on the filter form and my brain is turning to mush because I am out of tequila. Here are a couple of observations/questions:

First, what is the purpose of the Filter Form? Recall that we have discussed that the users might have a statement not yet associated with any calendar. As I worked on the new Add Statements Form I wondered if the users might want to look at statements that werent assigned to a calendar yet. Now, this Filter Form seems to assume that the users are looking for statements that are already associated with a calendar. If so, any statements that are not yet assigned to a calendar will not be returned. This is OK if the users are aware that they wont see unassigned statements. Perhaps they might want to be able to filter the statement form to see unassigned statements?

But, I see a larger problem with the Filter form. It is another interesting puzzle. The form contains a From-To possibility for the event days. But, aside from your original sme problems, I think that the code that tries to account for this From-To choice will fail. Thus, this filter form would never have worked anyway.

Remember that the calendars skip a lot of days when going below day 0. I observed that the original database field for the event days was a text data type. I tried changing it to a number (integer) but it caused no end of problems when encountering a blank event day. The main problem is that with a numerical data type, attempts to deal with nulls using Nz or other methods will return 0. And, 0 is a legal day. Ouch! So, we end up with lots of day 0s when trying to deal with the event day data as numerical and more than one day 0 really messes things up. So, I went back to having the event day as text. If you run a query using a calculated field with your code --cint(nz([EventDay52],0)---for example, you will see lots of 0s where the event days are really blank. So, I think we need another idea if users get a From-To choice (and it seems a reasonable thing to want to get a portion of the calendar).

I have an idea but I need to get some more tequila tomorrow so I can think about it further.

See you soon-jvf

P.S. Is your wife enjoying the chocolate you got her?

Hi Jvf,

As for the recovery program, my wife just changed her mind and told me it's much too expensive and it's better to forget about. Well, I worked over a week trying to figure out a way and she is changing her mind already! Unfortunatelly, flowers et chocolate never worked even hang out in a good restaurant... Anyway, hope she will remember what to do next time because I can't be next to her to tell her what to do and when. Anyway, I haven't installed Outlook yet, but I will install it on the D partition. Some Office files are on this partition so maybe it will be less difficult.

Yes I remember we discussed version number some weeks ago. For now, supervisors overwrite other saved calendars, but as you already know, fow now they save it themselves and will use the versioning this septembre. I think around Sept 4 or so, a by-election is imminent, so they are gonna use it a lot. I don't have any idea if the database will hold, but I hope to. So to answer your questions, what they did and still do for now is to save different calendar types and overwrite a the same version (ex if using a 36 day calendar, they will overwrite the versions). But for now as I told you, they prefer to save themselves until the by-election will start.

About the filter form, its purpose it's to open a report, then filter this report based on different filter choices such as Activity, Directorate, EventDay and so on. When I made this small form, it was something easier than build a more complex form with advanced research. I saw a lot of exemples with an advanced filter that will only filter a table and show any requests based on the choice made. I tried this one, but I encoutered lots of problems and then I decided it will be easier to filter everything.
As for looking about statements that are not associated for any calendar, I haven't think about this. Usually, statements that are not associated with any calendar are de-activated and transferred to the Archive table. There is already a report so users can see de-activate/deleted/inactive statements. I thought it was better this way, but of course we can leave them in the FrmCalendarModify and add a command in the filter so they can see active or inactive statements. It's a good idea to add this so users can see unassigned statements.

About the "From To" for th event day, it's sort of a puzzle as you mentioned. Usually the code never failed, but with things that have been modified and the relationships and so on, maye this one will fail or will survive...You have to test it to see how he's gonna answer to something new. Usually it should work in any way. If it does not, then we have to think about something else because users always wanted to filter the report and see statements associated with a specific event day. If we remove it from there, we should add something else that will work.

As for the data as a text, I had to make it this way so I can order the report when it's opened. The "Nz" command will order all statements folloing their ID number. I thought with this it will be easier to see statements and how they are ordered. By exemple if users want to see statements from day 36 to 30, the report will show these statements in order. It's the only logical choice because the event days next to electoral statements will be ordered in the same way (statements beginning with a day and ending with another day in a decreasing order). I haven't tried with --cint(nz([EventDay52],0)-- because I only left the firs that work with event days 36, but surprisingly, it worked with the other calendars, so I never modified to follow calendar types after the 36 day calendar. Seeing lots of 0s where event days are really blank, we don't need to see this, but as you mentioned if users want to see unassigned statements (statements with blank fields), then something will be on the filter form.maybe a check box indicating active/unassigned statements. What do you think?

Do you have lots of tequila at home? I still only have wine and it's not easy to drink because we have another heatwave...but next week it's gonna be a little bit fresher.

As for the chocolate, she likes lots of different chocolates....but yesterday she wanted to drink some beers and brought a 12 bootle pack. It seems that she looks more relaxed with the beer altough she is not strong on alcohol beverages. I'm also thinking to buy another computer...maybe a Dell so she will use it and forget the e-mail problems.

se you around my friend
stronghold888

Hi Stronghold,
Boy, I dont know what is worse: Dell or HP. Try and build one yourself. Anyway, just keep her happy.

When things get tough I drink Scotch. So, I went out and got some Chivas Regal. I got their least expensive one in case I drank a lot figuring out the database.

In order to avoid the null problem I took another shot at making the event day a number while still exporting the calendar and I think I got it to work. If it does work, I have fixed the From-To problem. But, for your reports needing it to remain text for sorting, we must see what might happen. It would seem that we could still sort correctly with a number but I need to study your explanation some more. What is the name of the report that you use for this? The filter form used RptCalendar 2. Is this it?

You mentioned that the code usually never failed. But, it only would fail under certain conditions that might not have occurred yet.


I know we must get the users something soon. The problem with fixing the inconsistencies and table errors is that the Reports are going to have to be fixed as well. So, if we are going to submit the new database we must look at the most important reports quickly.

Sorry, but your explanation of the versions is still not clear. I think you are saying that the users expect that each calendar type will have its own version numbers and they can overwrite one if they want to. This is what I think they want but it is not what we are doing. Now, the version numbers just go up by one every time any calendar type is saved. However, I think it is an easy modification to make each type have its own version number.

See you soon-jvf

Hi Jvf,

I don't know how good is Dell, but the problem is they don't have any store here so it means you have to order and receive it by postal mail. The central store is in Alberta...very far from where I live...
About HP, I never liked them because they are somehow very limited. A few years ago, I helped some of my friends to repair their HP computer. Geez, was not that easy because every part inside the computer was so hard to access.

Hope you are not gonna drink the entire Chivas Regal bootle as you said it was the last expensive one :o)

About the report, yes, the filter uses RptCalendar2.

I am aware that the code under certains conditions that might not have occured yet. Nothing's perfect, but we will see how it respond as we make modifications to the rest of the code.

As you know, there is no hurry in fixing all this. If it was the case, you could imagine that I have to work overnight and get tired at the far end. In fact, there is no big pressure and we still have lots of time to fix everything.

About my explanation about the versions, users expect expect that each calendar type will have its own version numbers and they can overwrite one if they want to. If I explained it in another way, then you already guessed it. So far so good! :o)

I just made some tests and in fact version numbers just go up by one every time any calendar type is saved, but I found out that the versioning can be overwritted....and this will happen if users select the "save as" instead of the automatic save that is already inserted in the code. At least, this one is not a big problem.
By the way, my supervisor asked me to add some other search options on the other fields. I don't understand why add a filter for each field because the report won't be a report and it's gonna be a table. She just wants to be able to find a statement by its KPI and or EMSQCode question. Well, that will return a null record or the right record...but I still don't get the idea. You see, she has a copy of all statements and their respective fields in excel and these ones are updated automatically when a change in the statements that are in the database is made. So, with Excel, she can filter anything....so why complicate things with the filter form? I will try to have more explanations from her about this because filtering all fields in the report is not an easy thing.

see you around my friend
stronghold888

Hi Stronghold,
I have Plenty of Scotch left for fixing the filter form. I am in the process of fixing the (hopefully) last bugs in the new code where the event days are a number. It seems to be working OK. Now I will fix the versioning issue and continue work on the filter form. I started it but then saw the null issue and made a detour about the event days becoming numbers.

OK I will modify report2 after I make some changes to the filter form. You are right about your supervisor. Changes to the filter are going to be hard sometimes and if she can filter from Excel she should do that instead.

Also, it is not only about changing the filter and code but, depending on what the filter is, a new report might be needed. So, I hope we can get the filter working in a simple fashion and let it be done at that. I will see about filtering unassigned statements though. Perhaps we can do it on the statement form.

See you soon-jvf

Hi Jvf,

I spoke to my supervisor today to know more about these other options she wants to add on the filter. Again, I told her that she can to that from Excel because if we add a keystroke search, the filter won't be a filter anymore, but a search engine and that's not good either. I already tried in the past to add a keystroke search, but it gave me more headaches and I had to leave it.

If you think another report is needed, I don't see a problem. I knew in the beginning that this filter won't be that simple and that we could need another report. They could solve this if they can buy a pre-assembled software that can do these tasks. Anyway, they choose MsAccess...so, vive Bill Gates! :o)

Did you took a look on the Archive form? As I mentioned, deleted or inactive statements are there and a report is also there to view these statements. This one was not used a lot since the past weeks. I will say more than 3 months because only a few statements were deactivated.

I see you have plenty of Scotch for fixing the filter problem :o) I will only drink a tea so I can relax my brain. As for the weather, we had a lot of wind today and this evening it's very fresh. No need to power up the a/c again :o)

see you around my friend
stronghold888

Hi Stronghold,
I still have some Scotch because I havent gone back to the filter form yet. However, I could have drunk it all during my last adventure. I have rounded out the Admin Calendars form. It can make new calendars and change the start day of an existing calendar. I got twisted on that one.

Also, I am finishing up the Add Statements form where I add a way to filter by calendar type and if a calendar type is chosen we can also got to a specific Event day. I will finish by adding a button to show unassigned statements (if there are any). Of course, we have a button to remove the filter.

I just know the darn users are going to want these features sooner or later.

This will be ready tomorrow but may not look pretty. I will send you a copy to check out as I go back to the filter form.

It was a beautiful day here. The best it has been in weeks. Of course, any day at the beach is a good day!

See you soon, jvf

Hi Stronghold,
I am running out of time today so I hope to publish something tonight. I want to alter the switchboard some and make one more simple form for some administration chores.

See you soon, jvf

Hi Jvf,

Hope you are not too drunk because of the Scotch. Hope you haven't finished the entire bottle :o)

Take all your time you have because as you mentioned, changes on the fly can be dangerous. No problem in altering the switchboard and make one more simple. About me, I'm running out of ideas about adding the rest of the fields of the report on the filter form. I wanna kill this supervisor! Can't understand that it's better to use Excel than this damn!

see you around my friend
stronghold888

Hi stronghold,
Well, I'm going to post the db soon, perhaps later tonight. It may not have the filter form ready but you need to see how the rest of it has turned out so far.

I think it will be easy enough to put all the fields on the filter form. The problem, like we said, is how to display the information depending on what they have chosen. Also, we have to keep the choices relatively simple or it will be impossible to do this on one Report.

What the supervisor really needs to do is decide on two or three ways of filtering and draw on paper how she thinks the Report(s) should look like. But, lets dont get her started yet. The best we can do for now is to use your Report2. I think it has all the fields. If not I will add them and pick a sorting/ grouping order. Maybe we will get lucky and she will accept the one report regardless of what choices were made in the search.

I noticed that Report2 showed the StatID. Do we really need to show this? I wouldnt think so unless they keep track of it somewhere and want to be able to filter with it. If we leave it off the report it gives us a little more room for stuff. But, I keep forgetting they can print with a large printer. What is the paper size for Report2? Anyway, Id like to leave it off if we can.

See you soon-jvf

Hello Stronghold,

I am working on the filter form now but in the meantime here is a test database to study. I may post again later with a filter form but its unlikely since it is already 9:00 pm here.

I made some changes to the Switchboard. Your first button HQ Aide& is rather cryptic (to me) but it seemed to open the page to get to working with statements. So, when we get to this page I show the Add New Calendar Statement button. This will open our new Add Statements Form. I see another button here to View/Modify/Delete Calendar statement. I dont know what this button used to do but it doesnt work now. Anyway, it seems redundant. Depending on how things work out, this whole switchboard page is either unnecessary or will get the buttons for some more stuff. Also, I put the Switchboards close button on so we can close it and get it out of the way when testing.

I also fixed the version numbers for Calendars.

I have some fake calendars made up but we will delete them later. Like I mentioned earlier. By observation, I see that the real calendar event days seem to go numerically from the calendar type through 0 and down into the -20s before things get strange. So, when we make a calendar, we start at the type and fill it in until we get into the low 20s. From there the user will have to adjust the event days. I auto increment the weekday as users enter new weeks. But, users cannot delete weeks. This would mess up the ordering of the weeks. It is safe to delete the last record or a group of records at the end because that doesnt mess up the week numbers. The administrator could do that at the table level.
If worse comes to worse, the users could just delete the calendar and start over. I can put this feature in later.

Just type wildly here and there and see if you can get anything to work (or crash). Regardless of the usual troubles getting it to work as we pass things back and forth, I believe this table format and relationships will become the new database. As such, these tables and forms cant be put into any of the older databases. At some point, the users will have to use our final revision of this database and retype any statements from the old database. There are 309 statements in the table. Are they all valid or are these practice statements also? At any rate, since this is a test you can add any statements to any calendar (even new calendars you make up).

OK-now I look at the filter. I gotta finally get it to work so you might award me the points! Here is the link to the test db :https://filedb.experts-exchange.com/incoming/ee-stuff/4351-v10_filter_numericOK.zip

P.S. do you think the users would want to look for actual statements by typing in some search words? This is possible if again done in a simple fashion. We could add it to the statement form. If the supervisor wants all the fields on the filter form it won't really make any sense to have the large text fields without having some way to enter search criteria for them.

Until I hear otherwise I am going to leave out these test fields on the filter form. Like I said, maybe we can do something on the statement form if you think the users would use it.

Hi Stronghold,
I hate to pile up the posts before you reply but let me give you an idea of what I mean by keeping the search form (filter form) simple. We will use two examples: the EMSQcode field and the StatReq field. Take a quick look at the statement table and the data contained in these fields.

For starters, even presenting the data to the user will result in the usual problem of a long combo box list. So, they could just type in something and hope for the best. But, more importantly, if they wanted to search for a few EMSQcodes or StatReqs at once that will mean a lot of coding. Also, look at those StatReq entries. No one will be able to type in one of those correctly unless they have a StatReq list handy for reference. Maybe they do. But, probably weve got to present the list for these. Or, make a wildcard search like the one I am considering for the text fields on the Add Statements form. Also we cant start mixing ANDs and Ors together.

So, for me, simple means only one choice per field strung together with AND statements. I have made filter forms where users could make a list of values (such as a list of EMSQcodes) and pass it to the sql statement. But, its a lot of work and I am not getting paid for it on this database (when we meet, you will owe me one beer, though!).

So, keep hounding the supervisor to use Excel for filtering. I wonder-can Excel easily filter based on a list of EMSQcodes?

Hi Jvf,

I'm not sleeping yet and it's already 1:30a.m! I took a rapid look to my e-mail account and I saw that there were 3 messages from you. At least, I can read and don't feel too tired. I had to drink 2 cup of tea and it made me relax :o)

I just finished to read your post at 11:48pm EDT. To answer your question about the StatID number, in a way  it help them to track statements that need to be changed. In fact I wanted to remove it from the report or at least hide it temporarily because as you can see, there's already too much stuff on it and I already told my supervisor that adding more fields won't make the report look the way it look now. I had to reduce the font to 7 so I can have some space...but it won't be that good anyway. The report prints on 11x17 paper, exactly the same size like the calendar in Excel.

Now finished to read your second post at 12:48am EDT. About View/Modify Deleted statements button, it still works. Maybe for now it need to be updated as changes have been made to the tables and their relationships. Not sure if it's that, but usually this one needs to be updated manually.
As for the calendars, I still have to change the tblWeeks if users want another week or modify one of them. Actually, they are not ready and not enough skilled to make these changes themselves because it's a risk that everything could collapse. I planned to add more detailed explanations in the user's manual, but these explantions needs to be readed by an administrator or someone with enough knowledge of MsAccess, otherwise, as you said, when there will be worse and worse, they have to go back to a backup version. I already told them to make backup copies if they change anything so they can go back if something does not work after some changes.
As for now, I haven't downloaded the d/b yet, but I will download it in a few minutes and make some tests on my computer at home. It still run on MsAccess 2003, so there won't be any particular problem.

About the statements, now they are about 358 statements and they are all valid. If ones are for practice, they are removed after. My supervisor already printed a big copy of the calendar on a large plot paper and put it on a wall so it's easy to check if there are minor changes to the statements or if some need to be moved to another day and so on.

As for the points, do you wish to have them right now? There is no a problem and you know, the 500 points are not that much. Would be better to give you more like 1 000 000 points. The system won't allow that...but if it was the case, you will receive an Expert, Master or Guru rank :o)

Now I finished to read your last post at 01:03am EDT. To answer your question about typing search words, yes they wanted this. When I tried this before, it looked too complex and it returned null values even if users type some simple words like "Targeted revision'' or any other words. They wanted to have this in the filter form and I don't know if we can do this in a simple fashion, but if you say so, then hopem it will work :o)

About my supervisor, she did not wanted to add all the fields on the filter form, but some specific ones. At least it makes some sense than having all fields on the filter form. You talked about large text fields, but which one do you refer to? Are you talking about the title of each field?

About the statement form, you are talking about the form FrmCalendarModify? Sorry to ask that, but I don't know if I"m going in the right way. If it's about that, yes we can do something on this statement form and then hope that no any more changes will be made and users will accept it until the last end :o)

Ok, I'm gonna download and test the database you posted. I hope not to get asleep because it's already 2:00a.m. If it is the case, I will come with my comments tomorrow first time in the morning.

thank again my friend and take some sleep too
see  ya around
stronghold888

O my God! Ive just looked down the EMSQcode data. A large part of it consists of more than one code separated by commas. In terms of users making a note of the EMSQ data this is OK and the rule of one item per field may not have to apply here. But, to put this field on the filter form either means we have to inspect the data and try to parse it out and present a list of single EMSQ codes in the combo box or we use the wildcard because if the user types in just one code but it is among a record where it is part of a list, it wont be returned.

Filtering is FUN!!!
jvf,jvf, JVF,jvf888,888jvf

Hi Jvf,

Yes, EMSQcode data consists in more that one code separated by commas. It was my supervisor idea to have it....and I know that this one will be hard to filter. Anyway, I'm not sure to have this one on the filter form, but I have to look into my papers somewhere because my supervisor wrote what she needed.
Also, some codes can have multiple statements, so it can return possibly some ones. The problem are these commas because even if we put them in a combo box, I'm not sure that the record will be filtered even if the user enters just one code but not the other. I have to check this one.....just for your information, on the form FrmCalendarModify, this field is there with commas....but if we remove these commas and just insert a space between the codes, do you think it will work on the filter in a combo box?

Anyway, I'm glad you like filtering! It's more than fun...but not always this way :o)

I finished to download the d/b, but I downloaded it on my laptop! and my laptop does not have MsAccess installed on...just my computer and my wife just turned it off 2 hours ago before going to sleep. I will open it or open it tomorrow. I think tomorrow is the best thing because I will have a fresh mind. You should take a rest too and tomorrow I will come back with comemnts and also with the fields that we need to put on the filter form. I remember we needed to add the SME field to the filter form and GE/BY/BOTH....for this last one, I think my supervisor wanted to see them filtered separately. If GE, it will show only GE statements and so on. I"m not sure of that, but hat's what I have in my brain for now. I've gotta find this paper where she wrote everythig then add it here.

good night my friend and see ya around tomorrow
stronghold888

Hi Stronghold, It will be good to see what your supervisor has in mind before we go too far in the wrong direction for the filter form.

About the EMSQcodes. We could probably make a list of each separate code if the users put in commas and then put the list in a combo box. But, this is dangerous if they dont use a comma by mistake. For example, they might type in a semicolon because it is right next to the comma. It would be better for them to just type in a code in a text box on the filter form. I mean, the code had to come from a list somewhere so let them have the list handy.

 In either case, since there can be more than one code in the data, we will search with the like operator. This will find all records with instances of the code even if there is more than one code in the string.

Now, if they want to actually look for the certain string of codes then we will have to just gather up all the entries and put them in a combo box. But, this is probably not what they want because then only that typed in combination will be returned. Again, even if it IS what they think they want there will probably be typing errors such as extra spaces, etc that will almost certainly ruin the search effort if they have tried to type in that certain combination more than once. This is an example of another of the database golden rules: type once then choose from a list after that.  

See you soon-jvf

Hi Jvf,

The database you posted is a must! The new form for the calendar statements look much better. I had not enough time to show it to my supervisor, but I will when she will be less busy.

The pop-up filter also looks very good. Usually when it opens, the report opens automatically. Maybe it was removed, but we can fix it later.

I agree with you about the EMsQcode. Usually the best thing is that they insert it in the appropiate field without a comma. Maybe when pushing the Enter button to enter another code in the same field could be a good idea too. At least, if we add this field to the filter form, if users select one code, then it might return the records...but what about the other records that have more than one code? I hope it won't be a problem.

I spoke again with my supervisor today and there is what is needed on the filter form:
- check box for GE statements
-check box for BY statements
-check box for BOTH statements (statements that have BOTH as event type)

As you guessed, she still wants a keyword search. Users will enter any word and it will search through the statements. I don't know if it will work on the filter form because if not, we can then create another report if needed.

The famous EMSQcode is back! She wants this one too on the filter form. The thing is because codes entered are separated with a comma, but as I mentioned, when these codes are entered in the Statement Form, they can be separated (insert a space) or press Enter. The field will be without commas. At least if it helps to filter the report, then it's ok.

Other search to search all KPI's (that's the field named Rel_Link.

That's all. Hope it's not that much because as I mentioned, if the filter form won't work to filter and search through statements, we can create a new report with a new filter. That's an idea...but if we can keep the same filter form, then hope it will work

As for the keyword search for words in statements, KPI's and EMSQCode, we could do something like a field on the form...user will check the proper field to search on (exemple EMSQcode). When the checkbox is checked then the user can search any codes, but cannot search any specific words related to statements. I don't think it can work if everything is active like EMSQCode checked, KPI'checked, Statement checked...it will return null values for sure. Users will have to use one at a time, not all at the same time. What do you think? If you have a better idea and less complicated, go on.

Tomorrow morning I will move in another place nearer of the beach so I can drink a tequila in the evening as you can :o) I will check my messages on Saturday because my Internet connection needs to take at least 24h to be connected there.

hope to see you around my friend
stronghold888

Hi Stronghold,
Hope you are reading this in your new place!

I am happy to hear that most of the new database worked. The filter form was not ready in that one so that is why the report didnt open right away. It probably was the start of the one I am working on now. I thought it was strange to have the report open behind the form because it didnt have any filters yet.

Actually, if we end up searching for the EMSQcodes using the like operator (again discussed below) I think we cant open the report and then apply the filter. I was trying that as a test but I think the filter cant use the like operator. At least I couldnt get it to work but Ive never used the filter method much. The filter method seems OK for simple filters but my filters always ended up being complicated. So, I came up with another idea. For some reason, unlike forms, Access Reports can not be opened directly using an SQL statement. So, to filter reports, I came up with the method of basing the report on a query. I would modify the query SQL, resave the query then open the report. I wonder if newer versions of Access still have this restriction.

I must study you supervisors notes about the filter form and I will post about it again later.

About the EMSQcodes. As I mentioned, because of the way the users enter more than one code in the field, we must use the like operator and we will return any record with the code. As you say, we hope this is not a problem because if they only want records containing one code they will never get any records with more than one.

If they dont care about getting records with more than one code entry but want the ability to search for a list of codes then they will have to wait until we get the basic filter working because, like I said earlier, this is somewhat complicated.

See you soon-jvf

Hi Jvf!

Now everything seems to work in my new place, except the phone..it seems that someone from Bell made a mistake because the number they gave me is not the same *sigh*. At least, tomorrow morning a technician will come here to fix this problem. At least, my Internet connection works and that's good :o)

As for the filter, I see that it gets complicated. When I tried with a query, it returned the wrong values, so the SQL query worked in a way. Not as perfect as I thought, but as you saw, it worked on some fields. The ''like'' operator should work if the SQL query is properly adressed. In a way, the filter can open, users can select any choice they want and then the report will open. I don't see a problem about this. Newer versions of Access still have this restrictions. Access 2003 has it, but Access 2007, I don't have any idea. What I know is that our dear Bill G removed the ULS, so if you plan to use Access 2007, it won't be that interesting.

About the EMSQcode field, users don't care if they get more than one record with the same code. I agree with you that if some records contain more than 1 code, it will be something difficult...but we will end up with something. In my opinion, EMSQcode is not a very interesting field....but this supervisor is too stubborn! I already emptied one bottle of rose wine and I feel much better. Do you still have some tequila? :o)

See you around my friend
stronghold888

Hi Stronghold,
No tequila but Im still sipping my Scotch as I work on the filter form. Its tough getting all the data entry errors trapped.

I have tried most of the combinations and my SQL statement seems to be working. I can paste it into a query and it will run. Next I will study your post about what fields the supervisor wants and try to put it all together.

What is ULS? I dont know this term.

See you soon-jvf

Hi stronghold,
I just saw your post about the points. If I get this filter form to work I think I will have earned them. I have read your post about what the supervisor wants but I think some fields are implied. Your post says she wants the GE/BY/BOTH and the Rel_Link and the EMSQcode. But, the other fields arent mentioned. I assume the usual stuff wants to be on there like Directorate, Activity, sme name, calendar type, etc. I will make up the filter form with these fields and you can have a look.

Also, about the search words: I am confused about where your supervisor wants this. It would seem if we could do it we would put it on the Add Statement form so users could try to find a statement. It could go on the filter form also but it seems like a report wouldnt be made from a search for a couple of words in the statement. What should we do?

Anyway, in the test database you shouldnt have to modify the weeks tables because we now do that with the Admin tables form. I have changed the table structure and that is why the users will have to start entering statements in the new database soon.

I will post a filter form later today.

jvf

Hi Jvf,

Guess what happened today? I'm not really lucky *sigh*. You know, when I moved to the new place, I bought a new bike. Unfortunatelly, the Shimano derailer that is used to change vitesses each time does not seem to work very well. The chain was stuck 2 times. The last it was when I was on my way home. I've had enough of this. In fact the bike was a gift from my father...but I will try to contact him to see if he still have the invoice and then see if we can get another one or buy it in another place. Let you know I was nearly to have an accident because when the derailer stucked, I had no time to put the brakes on because the light passed from orange to red very fast! Geez...the world can be full of unpleasant surprises *sigh*

About ULS, it means User Level of Security. It's used to secure a database, but in fact, even a novice to Access can disable this feature very easily! That's why MsAccess does not offer something stronger to protect its data.

I'm happy to see that your SQL queries work and that's a good step in the right direction :o)

As for the form filter, I only mentioned what fields my supervisor wanted, but that it does not mean that the other should be removed. Fields like Directorate, Activity, Sme_name, EventDay will always be on the form filter. And for the points, please don't worry. You know very well that you will earn them and as I said, I can give them to you right now if you wish and we will continue to work on the form filter as well. What do you think? If you prefer to get them when everything's done, it's your choice.

About the search words, the supervisor want this on the form filter as well. It will look around in the statements and then reveal ones that contain a specific word. Could be ''Targeted Revision' or ''ROs'' or anything else. I still don't know if this one will work because as I mentioned if it won't, we can create a second report that will use the search word criteria.
In the Add/Check statements, there are no any search button for statements that contain a specific word, number or anything else. I already explained to my supervisor that if she wants to search for a statement number, then the mouse pointer should be pointed on the statement ID number, then hit cTRL+F and enter a statement number. This one is a classic method and relatively simple of use.
In other words, if the search word criteria cannot be used on a report, then we can use it in the Add statement form as you mentioned.

About the Admin table, what I remember is that we used it to change the paths of the calendar export and I find it a good idea to make a change to the tblWeeks from there. I'm pretty sure users will understand how it will work. We just need to ne sure that they won't go ''in the back'' to enter it manually in the proper tables. We know that it will alter everything.

Do you still have some Scotch left? I just emptied a 6 beer pack. The beer was not that strong, so I still feel ok :o)

see you around my friend
stronghold888

Hi Jvf,

Just let you know I finished to download the d/b you posted and I will see how it looks. Don't worry about the look of the form. We can refine that later.

I'm going to check the database and then come back with further comments.

see you around my friend
stronghold

Sure I hope it will work too :o)

Hi Jvf!

As you can see, I awarded you the necessary points for this post :o)

I was not been able to test the d/b entirely. When I finished to download it at my work, they told me to clean up my desk. It seems that I won't have my office anymore but work in a room with other people. I'm not used to work in a noisy environment. Anyway, I had to clean my desk and put everything in a box so I will make some order later. Time passed very quiclkly and I was already at my home.
I gave it a try and downloaded the d/b on my computer. As for the form, yes I noticed that it takes some space, but we can adjust it to show the background picture. We can worry about this later.

I tried some tests with some fields. Directorate field return all statements that belong to a specific directorate, activity list respond very well to the choice selected, business owner list is complete and it returns the business name without any problem. EMSQCode seems to respond to some codes I entered, but strangely sometimes it does not return any value.  I don't know why, but we can check this later on.
For the Rel_Link field, I entered the number 4300. Usually this one is associated with a BOTH statement and I haven't selectioned any in the Stat_Applic field. Surprisingly, the records associated with this number appeared. I made a second attempt, this time I entered 4300 and then selectioned BOTH in the Stat_Applic field. It returned the same values and that's good.

The only problem I encoutered so far was that I was not able to see the other fields when the report was filtered with the values selectioned. I believe it's because I don't have a printer that prints on 11x17...but it worked in other test databases even if my printer was not the same. I will have to continue these tests on the computer at work to be able to see all fields because it's not easy to know if a statement belong exactly to some field, business owner name by exemple if I can't see it. I think at my work I will see all of them for sure and continue the rest of the test on the filter form.

About opening the report when the filter form opens, we can do that like the old one. For now, I will leave it as it is. I think I will minimize it or add the possibility to minimize the filter form so users can look through the statements in the report. As the filter form loogs bigger than the first one, if it is minimized after filtering some criteria, then it will be easy to look on the report. This one can be fixed easily.

As for filtering the combo boxes to show values that depends on Directorate vs Activity fields, it's a good thing. You have plenty of time to complete this one, no rush needed my friend :o)

About the filter form, you mentioned if we can close it after the opening of the report. This can be done or as I mentioned, minimize it. As I mentioned, I need to "crash" things and see where we need work, but  as I cannot see all fields on my computer (I see as it look on a 8x14 paper). I should be able to put everything at maximum at my work and make a review list with things we need to fix.

As for the text search on the Add statements form, there is no problem. Just take your time and again, no need to rush. Try to relax and if there are some Scotch left, then it will be a good idea to drink some :o)
Also as mentioned, I'm pretty sure users will be happy to see that the EMSQcode field can return records with multiple values. They just need to enter one code in the field to search for statements, not 2 3 or 4 codes at the same time. It won't make any sense. I will take a look at it closer tomorrow when I will see the entire report on a 11x17.

Tomorrow I will also add some statements to test the filter's behaviour and take notes on any error messages that could appear. I was very impressed with the Admin table! I think it's a good place to change values to sme business owner names and other things. Later on we will add some password protection to this Admin table so only administrators will have access to it.

I also noticed some problems in exporting the calendars in 45 and 52 days. It seems that there is no data exported, just the event days. We will check this along with other problems I will find tomorrow.

How many bikes do you wife has? I hope it's not like one bike for each season! :o)
Mine has Shimano derailers, but I think they are fake just because when you change the speed to a low one, the chain gets stuck and that's not good. Anyway, I don't have any news from my father about this, but I hope to get a call or e-mail from him soon so we can exchange this bike with another one or....I could change the derailers with some better ones. I will see about that later because tomorrow and Friday it's gonna rain over here, so I can't get the bike fixed outside with the rain...

I will let you know more about the database testing tomorrow
see you tomorrow my friend and again a very big big big big thanks for your help
stronghold888

Hi Stronghold,
I just checked your post. Thanks very much for the points! I am glad to see that at least some of the filter is starting to work. It is really hard to see the report. I think if you scroll through the pages you see the rest of the fields. The pages seem to go from left to right until all the fields have been shown and then the next page goes down to get more records.

I am also making a form to administer the Activities. I will try to make this same form work for the case where users want to assign a different activity to a Directorate while entering statements. I believe you did mention that this possibility exists. So, the fact that we filter the activities by chosen Directorate on the statement form means the users need a way to see the other activities if they need to (or add a new one).

I tried exporting the 45 day calendar and got some error messages but it did export some data. I will look into this. Keep trying to crash everything and make a bug list. It seems like you are doing a good job of testing and this is what we need.

My wife and her sons were into mountain biking for a while. So, she has a mountain bike and a regular bike. She was riding down the hill one day in a park near the beach and a mountain lion jumped right over her! Mountain biking sounds like fun but that seems a little too exciting to me.

See you soon-jvf

Just a quick note: I found my export error. It was a problem filling the day name header on the spreadsheet. Ill post some changes soon and you can try exporting again.

Hi Jvf,

Just a quick note to tell you that my computer at work was not ready, so no Access in it...I waited the day long ant the techie never came to check why Access was not transferred. Anyway, I will test the database at home despite the fact that I cannot see the report in full. I will add some test statements and make a list with possible bogus and anything unusual. At least I hope to cover everyting.

Good you found the error export. You can post a database with the problem fixed, but there is no hurry.

How that there are lions over there in CA ? I thought they were in Africa...but in CA..that's very surprisingly! I think your wife has a better bike than mine, maybe a $600 bike because mine was sold on $100, and that's why most of its parts are not that good.

see you around my friend
stronghold888

OK Stronghold,
I am somewhat reluctant to post this db because I did a few things but not much testing. I worked on the Activity list. On the Add statements form you will see a way to add new directorate activities. We will also add this form to our Administer database switchboard page (after it works OK).

I fixed my export error. Try calendar 45 again.

Will work on searching the statements soon. Hopefully we just search the English? Let me know ASAP. Also I will check out the 4300 Rel_Link.
https://filedb.experts-exchange.com/incoming/ee-stuff/4421-v12_filter_AdminLists.zip 


See you soon-jvf

Hi Stronghold,
I tried to find Rel_Link 4300 using the filter form and it showed up OK. Maybe you specified extra criteria that werent in the record?

If you notice, there is a table called TblCalendar_original_with_statements. This is the table I used for my attempt to separate the statements from the event days. I have been carefully using this table to check the new table and filter form. For example, the Rel_Link 4300. If we search for Rel_Link 4300 in TblCalendar_original_with_statements we find an entry. Then I click the filter and see if there are more entries. In this case there is only one. Be aware that the default is to search the whole field and sometimes we need to change it to search any part of the field to find anything. This gets me all the time!

So, if you go to the filter form and only search for REl_Link 4300, one record will turn up.

See you soon-jvf

Hi Jvf,

I'm not done with the testing, but there are not too many errors and I will post a list this weekend with all errors encoutered.

As for searching statements, yes it will be only in English. My supervisor does not know a word of French...so even if other people around are bilingual, I think it's better to search only in English so everyone around will feel confy with it.

About the Rel_link code 4300, it's possible I entered it incorrectly or it was something else. I checked again and it looks OK. Maybe the other time I saw double, that's why I thought it was an error :o)
I checked again a few mins ago and I noticed if the code 4300 is entered and no Calendar is selectioned, it will show 3 records. If a calendar type is selectioned, 36, it will show 3 records, if 45 is selectioned, it will show one record. At least everything seems OK on this side. I will have to continue the testing on the EMSQCode field because as you remember, more than one code is recorded, but on my initial tests with the form filter, I entered by exemple 105516 because this record in the AddStatement form contains 2 codes...and the filter worked very well on this. I will have to check the rest and I haven't added any statements yet, but I will do that this evening as it looks fresh outside. All day it was another heatwave and the humidity raised dramatically to 77%! That's not so unusual...but I had to drink lots of water and wait for the alcohol for later :o)

I noticed the table called TblCalendar_original_with_statements and it looks good to search for anything in there. Of course as I mentioned, if no event day calendar is selectioned, it will show any records based on any calendar event. It just seems strange because if we select calendar type 36, it will show the same result if no calendar type is selectioned. I think it's normal because for now we haven't added something to invite the user to select a calendar type, but we will do this later on.

I will download the d/b you posted and continue the testing. I hope to make a list not that long with possible problems because the one I begain yesterday is still short :o)

Take some rest and enjoy the beach! I think it's more hotter in CA than in here because here temperatures came back to normal as the autumn is not that far :o(

see you around my friend
stronghold888

OK, Stronghold,
 Try this one. I have put the admin activities form on the switchboard. Also, I have added a search box for English Calendar statements on the filter form and add statements form. The problem (other than if it works at all) is showing/hiding the various buttons and labels depending on what the user is doing. I will test some more later.

You may notice that I have the close X showing on the switchboard form. This is to get it out of the way when working on things. I guess we should turn it off when we finally get the users to work in this database.

Once we get everything working, your brain will explode because you will have to write the user manual all over again.

https://filedb.experts-exchange.com/incoming/ee-stuff/4432-v12_E_statement-search.zip 


See you soon-jvf

Hi Jvf,

I took the d/b you posted and I continued the testing. First, I'm very impresionated with the search criteria on the form filter! Almost any word I entered, the filter showed up specific records and that's ok. For other words that does not exist, an error message appeared, then it's OK too. At least users will know that the word they typed.

There are a list of errors I encountered (some other will come later as I continue the test):

Error1: I added a new record. Before, I used the Admin form to enter a new name in the sme_name table and it worked perfectly because the name appeared in the AddStatement form. Same thing with the Activity list and this one is just very beautiful! Activities can be transfered from one directorate to another and new ones can be added, others can be deleted. This one is just perfect because users won't need to go through the real tables to add/delete/rename activities. So the error appeared only when I selectioned a calendar day. I selectioned a 36 day calendar and in the EventDay field I entered the day 30. A error message appeared "Compile error: Wrong number of arguments or invalid property assignment". The line "Private Sub EventDay_AfterUpdate() was highlighted in yellow. I repeated the same step with another calendar, a 45, then 52 and it showed the same error. Maybe something in the AfterUpdate is causing this error.

Error2: Now that adding a new calendar can be possible from the AdminTable, I went there. First I wanted to change the 52 calendar into a 51 day calendar, but finally I decided to create a new one. It worked and the 51 day calendar was created without any problem. The error appeared when I wanted to change the start day in the "change start day for calendar 51". The error message that appeared was this one: "Invalid use of Null" and then the line highlighted in yellow was "MyCal = Me.MyCalendar.Column(0)". Do you think it's possible to keep the week the same as it is for the 36 day calendar? I mean, the week starts on Sunday and ends on Saturday. If this can be applied to the other calendars, the 51 and 52 calendars and new calendars to come, then we can be sure that Day 0 will always be on a Monday day. That's what I did in the last database I sent to you. If you had time to look at the table tblWeeks51, you will notice that Sunday, Monday,Tuesday,Wednesday,Thursday are empty, but Friday (or Saturday, I'm not sure which one) have an event day.
Secondly, it's not an error, but when I created the 51 day calendar, the week was in French instead of English. Is it normal? I don't see a problem with this, but it will be for users that are not accustomed to French language. If they can make an effort to understand, then it's ok...we will see later on this one :o)

Error3: You know, when I added a new record, I was not able to assign it to any event day, so I thought this record will remain "unassigned". When I pointed the mouse on the bouton "View unassigned statements", I saw one statement that was unassigned, but it was not the one...I mean this statement was unassigned because initially, it was the last one, but when I entered a new record, it was not listed in the "unassigned" statements list. Do you think it's something wrong with the query? You can try to add a new statement then check if it can be seen as unassigned and you will notice this.

About the filter form, I tried something...when I opened the filter form, instead of select something, I clicke d on the "Open report" button and the report opened with all statements and all event days. Do you think that unassigned statements can be seen there? Because they does not need to appear if they were not assigned to something. I still need to check every record to see if unassigned statements are on the report. For now we only have one unassigned statement...and I populated it with an EMSQcode number and a Rel_Link number. It does not show on the report..at least because when I tried to open the report, a message appeared and told me that such statement does not exist. That's good to know. I'm just a bit confused...an unassigned statement will change its state to assigned once it has a specific event day?

At least, I still have to continue with the testing for tomorrow.

Yes, you are right, my brain will explode once I have to re-write the user manual because everything changed. I think this one will be bigger than the other. At least, let's hope users won't fall asleep when they will read this manual he he

see you around and thanks again my friend
stronghold888

Hi Stronghold,
I just got your post. I will see about these errors later today. Looks like you are doing a great job of testing! I will study the errors one by one and respond to each.

See you soon-jvf

Hi Jvf,

No problem.

Another error and this one is about exporting calendars to Excel. I still see the message "problem with weekdays names". I tried to export the 36 day calendar and this message appeared, but once I repeated lots of clicks on the error message, I was able to see all statements in Excel. I tried to fix this error, but I was not able to :o( This error appears also on 45 and 52 calendars. Clicking a lot on the error message to make it disappear will only export the event days, but no the statements.

I will continue the rest of the test.

see you soon- stronghold888

Hi Stronghold,
Im doing this in a blur so I hope my fixes are actually fixes.

Error 1-adding event days-I had a duplicate function-one was in the form but I want to use the one in my jvf module. Code will look in the form first for a module name before looking in other modules (whoops!). So, I found it and erased it and things seem to work. That was strange!

Error 2-changing start days. I think I forgot to requery the combo box to update it after we make a calendar. I added that and now it seemed to work. Dont understand reference to French. Did the weekdays come out in French? Seems to be OK now but keep testing

Error 3 unassigned statements disappearing-I could not duplicate this error. Try it again and if things still seem strange then please re-explain the problem

Filter Form-yes, the filter forms job is to find calendar records so unassigned statements will not appear. We can put a find unassigned statements button on the form and get these. We could use the existing report and see what it looks like. We may finally need a new report if it looks too weird.

Your second post about Export error  problem with weekday names. I think this one is fixed also. When writing the new code, I decided to change the field names to the abbreviation of the day name. So, Monday is now Mon, etc. Some calendars existed, like the 36 and 45. I though I had gone in the existing table and renamed the weekday fields but I must have missed them. So, I went to the tables, TblWeeks36 for example and made sure the field names all had the three letter abbreviation for the weekday. If it happens again, go take a look at field names.
Here is a new post:
https://filedb.experts-exchange.com/incoming/ee-stuff/4447-v13_Fixing_Errors.zip

See you soon-jvf

Hi Jvf,

I just downloaded the fixed d/b you posted and I'm continuing the testing...

Error1: now adding event days work fine so the error does not appear and everything else is OK.

Error2: As for weekdays in French, well, they are still there! hahaha that's very funny! In fact, I pushed the test and created another calendar, a 47 day calendar and guess what? the weekdays were in French, not in English as they are for other calendars. I don't know if you saw this one...but try it with any possible combination of calendars because with the 47, it made this...but try with another number and see what happens.

The other thing is about start days. This one works perfectly, but it's not needed to make it look differently than a normal week. A normal week is always Sun,Mon,Tue,Wed,Thu,Fri,Sat and it should remains the same for any calendar created. By exemple, when the calendar is a 51 day calendar and its start date is on Friday, then the rest of the week will be empty. I mean Sun,Mon,Tue,Wed,Thu will not be filled, but Fri,Sat and the rest of second weeks and so on will be filled. Is that possible? Because in the previous database I posted, I had to change manually the 51 day calendar and put it on a normal week. If it is possible to make it look as the 36 and 45 day calendar, that could be fine. If you feel confused, I can draw something and post here.

Error3: As for viewing unassigned statements, I pushed the test to another level. I did 2 things: first I took an unassigned statemend and populated it, then I tried to see if it will be assigned and it worked because it did not appeared in the unassigned statements list. The second thing, I took an existing statement and I de-populated it. In fact I removed its event days and then tried to see if it will be unassigned. It did not appeared in the unassigned list. I don't know if something's wrong because if a statement needs to be unassigned, I assumed that its event days should be removed or doing something else. When a new record is created but not populated entirely, then it remains unassigned until it is populated, but when an existing record needs to be unassigned for some time, what will happen? Maybe for this, we could add a checkbox somewhere that will make the record look unassigned. What do you think? If you have a better idea, it's ok.

Error4: The export problem...The message "problem with weekdays names appeared again". It only appeared for the calendars 52 and 51. Another error is about the date. I tried to enter any date and the message "must supply a valid date appeared". That's strange because I entered the date in any format, dd/mm/yy, dd/mm/yyyy, mm, dd yyyy. The thing is that the export module should not export anything until  a valid date is entered, but as I entered any date, the error message appeared and the exporting continued.  You remember, in the other databases, when a wrong date is entered, the export module won't work until a valid date is entered. Now, I tried any valid date for any calendar and the error continued to appear.

About unassigned statements, that's true, they don't appear on the report. Sure we can add something to find unassigned statements and see what it look like on the report. If it does not look good, we can show them on another report.

I will continue the rest of the testing.

see you around my friend
stronghold888

Hi Stronghold,
Ill really have to see about the French weekdays. Maybe the users will finally have to learn French!

I will see about the start days for new calendars. I did it because, as you said, some calendars started on Sunday and some started on Monday. So, I figured they might want to start them on any day so I made the code universal. So, when you say A normal week is always Sun,Mon,Tue,Wed,Thu,Fri,Sat and it should remains the same for any calendar created. I dont understand that because before we had calendars starting on two different days (Sun, Mon). Have the users changed their minds about this? To me, it doesnt make any sense to have blank days in front of the statements. If it is a 55 day calendar with 3 blank days in the beginning it should be a 52 day calendar that starts right away. But then again, none of the calendars make any sense to me. Its just a code exercise.

I will study Error 3 again about the unassigned statements. Hopefully, it is something simple.

About the weekday name problem, I guess I have to look at all the weeks tables for errors. I sure though I had fixed them all. But, if the weeks change to being all the same like you say above, we will be changing them all anyway. You should probably give another explanation of how the weekdays should be done. I really need to understand how some weeks start on Sunday and some on Monday but have the same Sun-Sat order in the tables.

Since I changed the export code to be on the form, I have not tried to fill in all the extra stuff like dates and election numbers. I have just been exporting blank data. I will start adding in all the other stuff and find the errors.

See you soon-jvf

Hi Stronghold,
I was thinking about a way to explain the calendar day format. The users want to see the Excel spreadsheet with the weekdays always Sun-Sat but the start day could be any of the days and so some days might be blank in the beginning. Is this the correct explanation?

jvf

Hi Jvf,

I added here the difference between tblWeeks36 and TblWeeks52. You will notice that for tblWeeks52, it starts on a Friday, but it uses a normal week, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday and Saturday. This one starts on a Friday and you will notice that for the days Sun to Thu, it's nothing, so the event will be populated beginning with Friday as a start date. The tblWeeks52 will look same when the statements are exported. There's the link to the file: https://filedb.experts-exchange.com/incoming/ee-stuff/4481-explanations.zip

I hope this will be more clearer for you. As you explained in the latest post at 01:07AM EDT "The users want to see the Excel spreadsheet with the weekdays always Sun-Sat but the start day could be any of the days and so some days might be blank in the beginning". Yes that's the correct explanation. Or if this gets too complicated for you, then there is no need to do this and I will change the start day manually without having to enter in the Administer form. I just don't want to make this too complicated for you....so if it is really complicated, then I will explain users that they will have to modify the start date manually somehow...at least an experienced person will be able to do this, not some stupid user that does not know anything about Access.

The most important thing is that EventDay 0 will always be on a MONDAY. As for now it falls on another day of the week, but I fixed this. By exemple, let's say we add a different calendar lenght, a 51 day calendar. If it starts on a special day of the week (could be any day), we just need to keep in mind that statements that have the EventDay 0 will be on a Monday because Day 0 always starts on a Monday. If you look at tblWeeks36, you will notice that day 0 is on a monday (week 6). I don't know when the generating calendars form generates any calendar, users will know if Day 0 is in its exact position, on a Monday. Hopefully, as I told you, if it's getting too complex, I can go through the tables manually and change the order of the event days.

If you need any extra explanations, I will be around

see you soon my friend
stronghold888

Hi Stronghold,
Thanks for the explanation. Once you mentioned your supervisor worrying about Event Day 0 not being on Monday and I guessed Event Day 0 was really important. I wondered if we might have to assure that it landed on a certain day and your post confirms this.

I thought we might have to try checking for Event Day 0 when we made a calendar. So, it would seem that the calendar works up from 0 to the calendar type and the start day follows from that. Let's review:

Users want to see the Calendar in Excel in the weekly format Sun-Sat. Further, Event Day 0 must be a Monday regardless of Calendar type. This is another interesting puzzle. I think we should be able to do this. I will work on this first and then find my Export errors.
see you soon, jvf

Hi stronghold,
I just figured the formula to make Day 0 a Monday for any calendar. Later I will work it into the code to make calendars. I guess this means we don't need the code for users to change the start day because it is predetermined by Day 0 being a Monday.

This is a big holiday weekend in the US so we are going out of town for a couple of days. I might be able to work on the code some in the evening. Either way, I will post something Monday or Tuesday.

see you soon-jvf

Hi Jvf,

I had to leave for Montreal because one of my friends wanted to get his laptop fixed. It's a very old laptop and I am almost finished to fix it. I thought it was a hard drive problem, but it seemps to be only a bug problem from Win XP. I will get around it soon and fix it because oncle Bill is too busy to get bugs fixed for Vista..pfrt

As about the formula for Day 0 for any calendar it's good. I hope it will work, but if it does not, you don't have to worry because I can go through manually.

There is also a big holiday weekend in Canada too and I will be back in Ottawa this Monday, just in time to get the Dell computer from Purolator and my wife will be happy ( I hope ). Take some rest and we will talk again when you will be back too.

see you again my friend
stronghold888

Hi Stronghold,
Hope you got your friend's computer to work. Some day I am going to let uncle Bill know just how much pain his programmers have caused us! We are staying at a hotel this weekend. I tried to email you this morning. We are supposed to have free wireless Internet access in the lobby but the signal was too weak and I didnt have time to walk around.

We are making progress! The new Make a Calendar form is ready. I am wondering about letting users delete weeks from the form. We have to be careful about re-numbering the weeks. We could re-number them when the form closes but there are some other issues we can discuss later.

About the unassigned statements: I figured it out. Just deleting the data in the fields doesnt delete the underlying record. So, I made the navigations buttons visible. Now a row can be selected and properly deleted. Once all the rows have been deleted (if the statement was assigned to more than one calendar) then the statement will show up in the unassigned statement group. Try it. The sub forms name is CalSub. Open it in design view and set the Navigation buttons to Yes.

Maybe it would be a good idea to put a button on the form to mark the statement as unassigned. Then the code would just delete all the records at once. But, the users still have a choice to delete the statement from only one calendar if it is assigned to multiple calendars.

I looked at the export code and adjusted some things. It seems to be OK but I will export some different calendars to see if it still works.

see you soon-jvf

Hi Jvf,

I finally fixed my friend's computer! I had to re-install Win XP on his laptop because most of its files were corrupted. At least it works now and I made a backup copy for him in case the problem appears again.

You know, hotels that have wireless access aren't that good as it seems. I never tried these just because my laptop does not have a built-in wireless card...could buy one, but the laptop I am using is very very old since yeùr 2001.

That's good that the new Make a Calendar form is ready! You don't have to worry about users deleting the weeks. In factm these weeks will remain there because there won't be any change to remove any event day. Any calendar of any varying length will use the same says until day -365.  But as you mention, we can discuss these isues later.

About unassigned statements, I will try it and see how it will go. As it looks for now, I haven't found any other errors that the ones I already mentionned, I think we are set to go to another level as the testing is almost complete. I think it's also a good idea to put a button on the form to mark statements as unassigned then the code would just delete all the records at once, In this case, I think we can remove the link to the Archive table because I think we won't use this anymore. We can see unassigned or inactivated statements directly on the form, so there won't be a need to transfer statements there.

About seeing these unassigned statements on the same report, did it worked? Because if it looks ugly or anything else, we can add a button to open the filter on another report.

That's good you can test if everything works in the export code. I just hope that the message ''problem with weekdays names'' won't appear again :o)

About the hotel where you are staying, is it near of the beach as  your house? It looks like that it's still warm over there because here temperatures dropped and are now betweeen 14 to 21C...not much, but it's because we are already in September and the fall is already here :o(

see you soon my friend
stronghold888

Hi Stronghold,

Wireless is definitely overrated. We were at an outside activity for the weekend. Our hotel was near the beach but the temperatures were still near 37 deg. It was frickin HOT.

Here is a post of another test db that I hope has corrected most of the errors you have found. I want to keep this post short so here are a few explanations.

First, I have gotten confused about how many real calendars we have. So, go to the AdminCalendars form and look at the real calendars. You may have to adjust some of the days.

About the AdminCalendars form: It occurred to me that the users dont care about the week numbers. These are only for us to export the statements properly. So, I hid the week field. When I mentioned users deleting weeks I was more thinking about the last weeks where the statement days get strange (to me) with some weeks empty and some weeks with strange EventDay numbers only on a couple of the weekdays. I made it so users can delete and add records however they like to adjust the EventDays. However, there is not yet a check to see if the user has entered the same day again. Sigh, I guess we should add that in. Anyway, the form is now modal as it should be. So, users have to finish the calendar before doing anything else. When the form is closed, we go through the table and number the weeks consecutively so the export code will work. Note that blank weeks are possible just as before. I have more to say about the weeks tables but it can wait.

I changed the Archive button on the add statements form to unassign the statement. This button just deletes the references to this statement in tblCalendar and returns to the record showing no assigned calendars.

Now, to get a report on unassigned statements I had to make a new report because the filter report has calendars and eventdays in it and the Report complained when it tried to open with no calendars and eventdays. I made a query for the unassigned statements and gathered up the user friendly information such as the Directorate and activity name instead of ID numbers, etc. It is called qryUnassignedStatements. Using the report wizard, I created a new report with this query as the data source and grouped it by Directorate and sorted by StatementID. There is a Find unassigned Statements button on the Filter form that will open this new report. As you can imagine, it is also very ugly. You will have to choose a printer (perhaps the 11*17 printers) and try to make it look like something other than sh*t. Or, just delete it and make a new report based on qryUnassignedStatements. Hey, I didnt say I could actually design forms and reports. I said I could write code. Hehe.

Anyway, hammer this db and find some more errors. I am looking at a way to exit better when we have an export error.

 Here is the link:
https://filedb.experts-exchange.com/incoming/ee-stuff/4516-v15_Cleanup.zip 

See you soon-jvf

Hi Jvf,

I just checked my e-mail today and saw your reply to the post. I was back from Montreal on Monday evening and my friend was quite happy that his laptop now works! I recommended him to buy a new one because his laptop is very very very old with Win Xp Pro, 20Gb HDD and 256Mb memory..,,not as powerful as the laptops today, but it works even if it's very slow. He's using it mostly for videoconferencing and that does not take too much HDD space, but I told him the HDD will crash sometime soon because it happened already and he had to replace the HDD with another one, another 20Gb of course.

You're lucky that's still hotter over there. Here it was 9C in the morning! brrrr. Will be about 21C in the afternoon, but at least it's sunny and hope will continue like that the rest of the week. I'm beginning to think that it would be better to move to CA where at least it's hot during the year because I don't like the winter. Due to Earth' warm up conditions, everything changed and it does not look like before *sigh*

I just downloaded the d/b you posted and will begin to test it in a few minutes. Don't worry if the reports are ugly. Even if you are good in writing code, at least you were able to create simple reports and that's good! :o) There is no need to have a good looking report, just keeping it simple will suffice. I will see what I can to adjust it, but I think there won't be too many adjustments...just some for the text alignment and that will be all. In a way, I see it was better to see unassigned statements on a different report than on the same that is used. I will check it as soon as the d/b will be unzipped and ready.

About the "real" calendars, there are some used for the test like 12, 18 I think, so I will remove them soon as I will go through the rest of the testing process. Good idea to hide the "week number column". Users won't get really confused. They will just need some training and that's where the user's manual I work on (will work on it sooner) will help them to understand better.

On the other side, the other better way to exit when we have an export error will maybe to get back to things that were not correctly entered. It's like the exemple I told you about the export dates. If the calendar is exported with a specific export date and the date is not valid, then the user need the possibility to correct it and try again to export the calendar. I don't know if it was fixed in the d/b you posted, but I will check it. First of all I will begin with the report viewing unassigned statements and the export will be at the last end.
Also I had some minutes to talk with my supervisor. She's always busy and I think the only way for her to get the pressure out is some s*x and everything will be back to normal! :o)
As you know, I haven't show the d/b to her with new changes because it's better I test it myself to maske sure everything work then I will do a presentation to her and swow her how it works. Just for 15 mins, I told her how it will look the new add form statements and the administer d/b form. I told her that new calendars can be generated without having to go through the tables or the code to change anything and she likes this idea a lot. One thing that got me confused is that she told me that it's not always necessary that EventDay 0 have to be on a Monday *sigh*. Well, as it is it's ok, but according to the Gregorian Calendar, day 0 can be on another day if there's a holiday or some other event. Grrrr, that makes it way too complicated. I told her that changes to day 0 can be made directly on the Excel file by copy/paste data and so on...
Anyway, we will see about that and you know I hate when she changes her mind just for things that are not really important, not that important for now and I know also you don't like either when she changes her mind that way. We will see how things will go because when the testing is complete, I will show the d/b to her, then we will see for the next step. You know, the filter report was the most important thing, just getting things that can be filtered with any option...well, as you see, she does not have any experience on administering databases so I think she should sh*t up and wait till everything is ready before making comments on changes that need to be done.*sigh*

I will get back this afternoon when I will complete the rest of the testing

see you soon my friend
stronghold888

Hi stronghold,
Pretty funny post about your supervisor! The problem is not only that she has no experience in database administration, but she also doesnt know what she really wants. I suppose we could put back the ability to change start days now while keeping in mind that the week always goes from Sun-Sat. I will take a look.

This project reminds me of a project I worked on a few years ago. Based on a recommendation of a friend, I volunteered to help with a database for a charitable organization. They were trying to match a list of volunteers and their skills to those who needed them. The idea was if someone called in and need a ride to the doctor or some house repair work, the office could easily find out who was available to do the work and how far they would go, etc.

The woman in charge (gee-what a coincidence) kept changing her mind about what she wanted. So, instead of getting something up and running, she had me adding and reworking the thing for months. Finally, she left and the new supervisor decided to drop the whole thing and go back to pencil and paper! O well. At least I got some new database ideas while doing it.

Anyway, I think were close. Keep testing and let me know how it goes.

Deleting a test table is a two step process. There is its entry in tblCalendarType and then there is the table itself. I got tired of going back and forth so I put a list box on one of the code forms. If you want to delete any test tables, open the CodeTest form (not CodeTest1). You will see a list box with the tables. Just click on the one to delete and it will ask if you really want to and then it will delete everything if you say Yes.

This brings us to some questions about the calendars and some other relationships. I dont think we have really linked everything together yet and we must look at this. But, back to the Calendars: what if a Calendar type needs to be deleted but has statements assigned to it? Or, horror of horrors, is it possible for the supervisor to want to change a Calendar type but keep as many entries as possible. For example, what if the 36 day wanted to turn into a 52 day? Or, a 55 day shrinks to a 43 day? Is this likely to occur? Remember, we were convinced in the beginning that there would only ever be two or three calendars and look what happened.

See you soon-jvf

Hi Jvf,

Yes you are right. When someone does not have any experience in administering databases, then that means something will happen and all people workin on it will get enough and leave. I saw this sort of problem in other places. When a projetct has a specific deadline and then it needs to be changed, sometimes it will get finished later after the deadline just because the direction wanted to "reverse" things to another level and so on.

As for changing a Calendar type into another, a 36 day into a 52 day. I don't think it will occur. If the supervisor wants to change it to a 52 day, she would likely add a calendar, then in the add form, select statements that will match for the 52 day. As you, I was convinced in the beginning that there will be only 2-3 calendars, no more....but as I can notice, it seems that there will be more than 3 calendars. I don't have any idea how the d/b will handle these calendars over a long period. Likely, it would be better to transfer these calendars into a second database and keep there for a later use. I already told my supervisor this idea, but she's too stubborn and wants to keep everything in the same place. But...what it could occur is that: if they want to change the 52 into a 50...then I think chances can be done to the calendar....but I know that statements that were assigned to 52 will be difficult to track and change them back to 50. This could occur. Remember, I had to change the 52 calendar into 51. It did not took too much time, just 30 mins, but it took more time to re-assign statements to match the 51 day calendar, just statements in the beginning, the other were fine.
If and if a 55 day shrinks to a 43 day occurs, then changes will be made manually and for this it will be a horror. I just hope it won't happen...or if it happens, I won't be there to work for them when my contracts ends. They should know what they want instead of changind their minds. As it is for now, it was just the gregorian calendar, nothing that important.

Still testing the d/b. I haven't found any errors, so I put the testing as deep as I can.

Did you hear about these "working at home" web sites? I don't know if they are really reliable....but if they are, then it will be another way to make money faster.

see you around my friend
stronghold888

Hi Stronghold,
I hate to agree with your supervisor but since this database is so small I think it would be best to keep all the calendars together. I am intrigued about changing calendar types like when you went from a 52 to a 51 day calendar. Depending on things, sometimes it might be easy. For example, did the statements shift down a day like from 52 to 51 and 51 to 50, etc. or did the day stay the same but the calendar changed and day 52 was erased. Either way it would seem that on one end, a day of statements will have to get unassigned.

I have not heard of any work at home websites but it sounds like a scam to me. Back in the day (or maybe even still) you could read ads in magazines or newspapers advertising work at home schemes. The most popular was make $300 a week stuffing envelopes at home. So, you send off the payment and what you get back is instructions on how to prepare and place an ad to make $300 a week stuffing envelopes at home. So, you would copy the instructions you received and pass it along. Ouch!

See you soon-jvf

Hi Jvf,

Wghen changing from a 52 to a 51, the day statyed the same, only the calendar was changed. I know that one day of statements will get unassigned, but maybe not one event day, could be lots of them.  When I was changin from 52 to 51, I had to re-assign statements to match with the new calendar type because they were all assigned to the 52 day calendar. I don't know if with the new database it will look the same, but I think the change will be done manually. This way we can't change that even if we try to automate this better. At least user can check themselves and re-assign statements.

The only work at home website I heard so far was ''datanetry.com''. They claim not to be a spam, but I need to check further to see if that's true. I always dreamed to be a millionaire...but not this way. Either I can't be lucky at winning in lottery, even if I can win small amoints from $20 to $70. Better than nothing.

I am almost done with the testing.

see you around my friend
stronghold888

Hi Jvf,

I think I'm done with the testing, but I will continue to test a few things.

I have found some errors:
Error1. It's about the LasUsedRow command. As statements begin to be inserted at Row 6 (The LastUsedRow was then set to 5, so I changed it to 4)...surprisingly, there was not any effect on the Excel sheet. I changed it to 6 and another number, then I tried to export, but again no any effect because it insert everything at Row6. I inspected the rest of the ExcelExportCode_jvf module to see if there was any other reference to this line and I have not found any. I remember you mentioned this in the other post, but it has no any effect. I went in the other modules to see if there was something, but I have not found anything.
Just one thing...I was hard laugh at the line in the module DefaultValues. It was the line ''bill gates and his band of programming monkeys strikes again!''. It's a very funny line and I will leave it there. I'm sure other programmers will see it in a near future he he

Error 2: I made another attempts to see if all calendars are exported to Excel. All worked except for the 51 day calendar. This one is exported without any problem, but as I scrolled down, I noticed that the event days from -28 t0 -548 were not there. I found this weird and I think you already mentioned it later in the post. I went back to the Administer form  and opened the form Change/Add calendars. Then I noticed that these days were not there and I added them manually. I tried the export again and everything worked. I created a new calendar to see if these days will appear, but these days weren't there. I think they need to be added manually. I went through the code and was not able to find anything to it. You know better where to look :o)
If it can't be fixed, I can add some info in the user's manual about it.

The following are not errors, just suggestions.
I tested the ''Unassign this statement'' button and it work perfectly. The unassigned statement button de-activate the statement by removing the calendar type and its event days and that's good. I hope users won't get confused and I will explain more in the user's manual about it and mention that for future reference if the unassigned statement needs to be re-activated, they need to select a calendar type and a proper event day. Of course, they could also insert the calendar type and event day in the ''comment'' field so next time they will remember that it belonged to a special calendar type/event day. I also noticed that the ''Unnassign this statement'' button remains visible when I click on the ''View unassigned statements'' button. I tried to find a way to hide it when the unassigned statements can be viewed on the form, but I was not able to. When a button is on the same form, to appear or to be hidden, it has to have a control or something else as it is the case for the ''filter calendar''. When I cliked on it, it make appear the ''event day'' field to filter for a specific day.
For the ''unassign this statement'' button, to reduce confussion for the users, I will add a message next to it informing them that there is no need to press it twice when they are in the ''unassigned statements'' mode because it won't do anything else.

Also I finished to re-arrange the unassigned statement report and everything looks fine on a 11x17. I just need to find the code for the colours of the statements and put it there.

About the export to Excel, the buttons Save as temp, New version works perfectly. I think my supervisor will ask me again to de-activate this temporarily. I think she will...but I will try to convince her that ever if the calendar is saved automatically as a new version or as a temporary version, she can rename it with the name she wants and save it on another folder. Adding another export button that will export the statements without versioning or temporary save will make things more complicated than what is on now. I just want to fire her because my second supervisor agrees with me on what needs to be done and what we have. This one does not need to complicate the database...but he has no any control of it because my principal supervisor has control on this database. If she's retrograted, I will feel better....if she is not..then I hope God will preserve me to get involved into complicated things that won't make any sense. By exemple, she wanted to know if the database could ''guess'' if there were holidays on the calendar because if it's the case, the statements can be moved with a simple copy/paste...no need to automate it in any way.*sigh*

In the previous post, I noticed you mentioned that: ''However, there is not yet a check to see if the user has entered the same day again. Sigh, I guess we should add that in.''. I agree with you that we need to add this there just to make sure the user has not entered the same day. If it's a wrong day that does not belong to the calendar, by exemple day -999, I don't know what we can do with this....but my idea is that the user will notice his mistake by himself, then go back to the Admin form and correct the event day entered previously.

Other errors than that I don't know if there are, but I will continue the testing. We're getting close.
We should also write an e-mail to oncle bill to ask a refund because his programmer monkeys did not made Access free of any bugs he he

see you around my friend
stronghold888

Hi Stronghold,
I am still curious enough to consider changing calendars. But, we must be very clear on things. For example, you say that the day stayed the same. I take it you mean that the EventDay stayed the same, not necessarily the weekday. So, EventDay 45 in Calendar 52 remained EventDay 45 in Calendar 51. This is the scenario where the statements for EventDay 52 (and perhaps some eventdays at the other end) would have to get unassigned. Although I havent tried it yet, I am tempted to say that this should not be too difficult.

What about EventDay 0? Shall we try a choice to pick (or change) the weekday for EventDay 0 with the default being Monday? Again, we already did this once. I could put it back but this time keep the weekdays going from Sun-Sat.

jvf

Hi Jvf,

Yes you are right on this. Changing the calendar won't replace the EventDay following the new calendar that was created, so they stay the same. I made some tests so I changed all statements that belonged to calendar 52 and assigned them to calendar 51. I have not deleted the calendar 52..but from the AddStatements, I selectioned it in the list to see if there were other calendars. What happened is that the filter showed a new record (without any data).

On the other side, I changed some back to EventDay52 and assigned them to Calendar 51. It seemed that they don't get unassigned because they don't figure in the unassigned statement list as they look active. We could introduce some warning message to warn the user that the event day entered does not match with the calendar type. What do you think? or as you mentioned, try something and get them unassigned until they get an event day and calendar assigned to them.

About EventDay 0, yes we can try a choice to pick (or change) the weekday for EventDay- with the default being Monday.  I know we did this once so you can put it back and keep the weekdays going from Sun-Sat. I wonder how it will go from there, but I hope everything will be ok.

see you around
stronghold888

Hi Stronghold,
I have to go to the dentist today. Ouch! Later today I will try changing the calendars. My idea is to write some code to get a recordset of statements assigned to the old calendar. Then we loop through the records checking event days as we go. If the record has a valid event day in the new calendar we change the calendar type to the new calendar. If the event day is not valid for the new calendar we will just unassign the statement. Sounds simple! Hope it works. There are still some issues about how we make the relationships that I must look at so we dont get into a situation where we cant change the calendar type if we want to.

After this works I will put back the ability to change Event day 0.

See you soon-jvf

Hi Jvf,

I hope everything will be ok with the dentist. I went there 4 months ago and it was quite expensive! ouch!

Sounds good your idea to write some code to get a recordset of statements assigned to the old calendar and then loop through records checking event days as we go. Let's hope it works.

see you soon my friend
stronghold888

Hi Stronghold,

Im broke now. Dentists are sure expensive. I make my dentist drill on me without any Novocain. Im built tough to take it. He and his assistants cant believe it!  Id rather hurt pretty bad for a few minutes and get it over with so I dont get all numb and slobber for hours. Besides, I hate needles.

The database changes are looking good (at least when I press the button, statement assignments and calendars change). I will test some more tomorrow for code crashes and then post it for you to check out.

How it the filter form working? Is the supervisor getting happy yet?

See you soon-jvf

Hi Jvf!

Wow! at least you saved a few bucks, thourgh the pain was still there.  The best thing is when you have a fiend of yours that is a dentist, then eh can help you for free. None of my friends are, then maybe someday we will have more luck ;o)

Sounds good that everything works when you press the button for staetment assignments and calendars change.

About the filter form it works perfectly and I have not found any other errors so far. I haven't told to my supervisor about it as she is bussy with other things, but I will let her know next week if she have some time...or if she have no free time, I will beginto work on the user's manual and plan to show everything when it will be ready. My contract was extended a little bit in order to get everywthing work with the database, so my estimates at the very best is that I will work FT from September until the end of October, then the rest of months i will work ''on call'' when they need me until March 2008. It's not much even if I am still trying to find a more stable job. Next Tuesday I have an interview and I hope to get the post. Anyway, I planned to travel to California for December, so if you are still over there, we could meet in a bar around a tequila ;o)

I don't know if you read the reply I posted on Sept 5, ID 19835273. Yesterday and the day before, I tried to fix the LastUsedRows but I still can't understand why it has no effect. I'm missing something. Also yesterday I finished to add colours to the unassigned statements report and everything works fine.

Do you have any idea on how many calendar types can be entered in the AddStatements form? For now we have only 4 calendar types: 36,45,51,55 (I removed the 52 calendar).
Also yesterday I compared all Activities from the ''live'' d/b with the ones we have and just one Activity name changed. I tried to see if removing an activity and add a new one won't have any effect on the statements. I noticed that adding a new activity to the corresponding directorate (from the Administer table form), well the activity will not take any effect and the statements marked with previous activity will not show the new one. The activity I changed was ''Correspondence CEO signoff'' and I changed it for ''CEO/DCEO Signoff(s)''. The solution was to go in the ActivityList table and change it from there. We can add something so users can re-name an activity, but it won't be required for now. If i receive requests from users about this, we can add something on the Activity form or, I could add explanations in the user's manual about how to rename an activity, but users should be careful with this.

Hey you know what? My computer was infected with Spyware, adware, trojans, data miners and so on! It began 2 days ago and my anti-vius was not able to erase something! Also it seemed that someone got control over my computer and I have no idea how even if my firewall was set to high *sigh*. Even if you have the best of the best, hackers or others can break them. I tried Ad-Aware, but no effect. It happened once and I reformatted the HDD (that's why my wife lost her e-mails). Now I don't wanna reformat it again, so I posted a post in the Virus section on EE. A more experienced person told me to try different tools. For now I was able to quarantine all of them using SuperAntiSpyware that was very effective. I will have to try other tools to remove these infections manually *sigh*
After that, I will make an image of the drive and store it somewhere hopping not to have this problem again.

see you around my friend
stronghold888

Hi Stronghold,
I did have a friend that was a dentist. That saved a lot of money until he moved to Florida.

Like I said before, sometimes your posts seem to be delayed. Based on your question, I am just now reading your earlier post about changing the LastUsedRow. I dont understand that one and will have a look. Also, where the UnassignStatement button stays visible. These are things we can fix and it is part of your inspection process to find them. You really are doing pretty good at it. I think there will be some more to find on the Admin Calendar form. As I said, its really hard to show/hide buttons and boxes in an attempt to make it easier for users to just see what they need at the moment. As I study the database today I am going to be looking for things like this. One thing I see on the Statement form is that if the user chooses  to filter by a calendar that has no assigned statements, the form goes to a new record. This is OK but could be confusing. I am going to add a message saying that there are no statements for this calendar do you wish to add a statement? If the user says no then we will revert back to showing all statements (and I must again check for everything to show/hide as appropriate-it does get confusing).

About the EventDays later in the calendar. Earlier I mentioned that I just looked at some existing calendars to get an idea of a common format. Like I said, it seemed that they all started from the Calendar type and went down through 0 and down to about -26 or -28. Then, things got strange. Some had skipped weeks before more EventDays. Some had really large (and different from calendar to calendar) minus days one under the other in various places, etc. I really couldnt see a pattern. So, I decided to just get the calendar started and go from the Cal type down to about -26 or so and let the users change/fill in the rest. Also, I retained the ability for users to click on the new record button more than once to add blank rows. But, if they wanted to put in a blank row in between filled in EventDay weeks then they will have to erase everything below what they want to keep then add a blank row and manually fill everything back in. I dont think they would need to do that.

So, to sum up, when users make a calendar they have to finish it off unless you can come up with a common ending. If there can be similarity at the end, post an example in Excel or Word and we can add it in.

When users add an eventday to a statement after choosing a calendar we do check to see if it is a valid day. I guess I will try to add a check to see if theyve already chosen this combination calendar/eventday before. You would think they would notice, but this is how errors happen.

About the Calendars: We havent really discussed if the users will ever need to delete a calendar completely. Could this happen? Changing from a 52 to a 51 doesnt necessarily mean that they dont want the 52 anymore. They might have just changed their minds and will use the 52 later. What do you think? We can add the ability to delete a calendar and unassign its statements.

Also, there are no limits on entering calendar types on the Statement form other than the fact that the calendar type must actually exist!

About  renaming activities: We only link the Activity ID to the form so if the Activity name is changed in the Activity Table (ActivityList) as you found out, the new activity name should show up as the user goes through the statements. I dont think we put this ability on the Activity admin form but I guess we should.

I remember you mentioning about holidays once. I hope we dont have to think about this! Besides, one of the many things I find strange about these calendars is that they can be exported without dates but we give users a choice to supply a start date before export. Logically, this means to me that the calendar can have no dates or start on different dates if the users want. These conditions make it impossible to think about holidays. Anyway, try to keep putting them off about this.

Sorry to hear about your virus problem. Making an image after a fresh install is a good idea. I've done this before and it came in handy.

See you soon-jvf

Hi Jvf,

Well, it seems you moved around a lot before choosing to live in CA. Which one is the best, FLA or CA? :o)
If your dentist is still there in FLA, you could give him a visit and save money, but you won't save any money on the trip from CA to FLA. Hope teleportation will come in a near future as we see it on StarTrek Enterprise :o)

I understand that my replies are delayed somehow, and if you like, I can refer to them anytime  by adding their ID number. It's the best thing so far and you will notice that in time.
As for the calendar statement that don't have any statement, yes it's a very good to add a message informing the users that there is no such statement associated with the calendar, but that they can add a statement. If the users says no, yes we can go back and showing all statements. A very good idea!

About the EventDays that go afer -28, I understand that it's strange not to understand why it's not working. In this case, I don't see a problem if users enter themselves the rest of EventDays from -28 to -548. I will see if I can get something from my supervisor about these last event days because if we can change them to something, to a ''continuity'' number, let's say the number after -28, meaning could be -29 to -35. If we can get that, then we can fix this. A few minutes ago I spoke with my supervisor and he told me chances are that a specific calendar will have an end date around EventDay-28 or -20. He was not sure as this decision should come from a higher level. The other supervisor is still busy, but I will talk to her on Monday first time in the morning to know more about this.

About adding a check for eventdays, we can try to check them but of course, if any errors happen, these errors will be mostly noticed when the calendars will be exported in Excel. I even can't imagine the horror, but let's hope it won't happen. We can also leave them withuot a check and either way, users or someone better will notice the error in time.

About the Calendars, as you know, I removed completely the 52 day calendar because the 51 day was the right one. On the other side, it can occur that a specific calendar won't be deleted completely, but if it's deleted, the statements assigned to this calendar will become unassigned. You marked a good point because if users later change their mind and want to go back.

stronghold888

Hi jvf,(rest of the message from ID: 19850000)

I had to cut the rest of the message because a technician came to fix up the problem with .net framework on my computer. Now updates from Windows servers work better.

About putting the ability to rename a specific Activity directly on the Admin form would be great. If it is work, at least users won't need to go though tables to change things.

As for making an image after a fresh install, I was planning to make one just until I get all my programs installed, but it was not the case. I just hope when I will remove each virus one by one I wil make a copy.

Hey, I wonder if you wanted to get more and more points because we can open a new post so I can give you as many points as it requires so you can become an Expert :o)

see you around my friend
stronghold888

Hi stronghold,
We might have to consider how I can get some more points.
 I liked Fla better for the balmy nights and warmer water but I really prefer living here on the West Coast because it is so pretty.

About the Event days, its not so much what happens right after -20 or -28. What I see, for example, is that Calendar 52 stops at -28 and the rest of the week is blank. Then, the next four weeks have Sunday and Thursday filled in and the last week has only a Sunday entry like so:

Sunday      Monday      Tuesday      Wednesday      Thursday      Friday      Saturday
-27      -28                                   
-35                           -120              
-51                           -150              
-60                           -180              
-75                           -548              
-90                                          


The 36 day looks like this:
-20      -21      -22      -23      -24      -25      -26
-28                           -120              
-35                           -150              
-51                           -180              
-60                           -548              
-75                                          
-90                                          

As I fighred, it doesn't paste so good here but you get the idea. Notice that for Calendar 36, -27 is skipped and the Sunday, Thursday only entries are not arranged in the same week as Cal 52. Also, I thought I saw a different -5xx day once somewhere.

So, if anyone can formalize this we can finish off the calendars when we make one. Otherwise, the users have to do it. As always, they can go back and rearrange EventDays all they want. Even if they have to polish the calendar up a little, I think we have given them a great tool for managing (and screwing up) their calendars. he he

jvf

Hi Jvf,

It's up to you if you want to get more points. I would like to give you 10 000 000 points and you will have the highest rank among other experts. :o)

I see that you like more on the West Coast than in Fla. I hope to go there this December for some time and enjoy surfing even if I don't know anything about he he

I get the idea about between calendar 36 and 52. I just hope to get something about that, if not, users will have to do it. As you mentioned, this tool is good for managing...but also for screwing up their calendairs if they don't take care of anything he he

Let me know what you think about my offer for more points. Likely it will look like I would open another post, you post a possible answer, you will get the points even if the answer is not 100% sure and so on. This way it will increase your points exponentially.

see you around my friend
stronghold888

Hi Stronghold,
I am waiting to see if your supervisors decide on the extra calendar days that could be common to all calendars. This also affects how we put back the change start day feature (actually, we have them change Day 0). I have just realized that the way I do it now is to erase all the calendar days and fill in the default days in a new order. But, this does not add in the odd days at the end. I knew my fix happened too easily! Anyway, I could attempt to redo this but then all the days will move. This means the last odd days (most of which seem to be on Sun and Thurs) would move to a different day. I think these days are just on Sun, Thurs to line up the statements. So, they should be left alone if possible so the export will look nice but this will make it difficult if not impossible to code unless a standard default is decided upon.

So, it would be nice to settle on the last day in order, say -26 or -28. Then we could skip a week or two (blank lines in the spreadsheet) and then add a series of default days on Sun-Turs. Remember, users can always add and subtract days. It is only a problem if they want to insert anything in between filled weeks. The odd days seem to be mostly

-35                           -120
-51                           -150
-60                           -180
-75                           -548
-90                            

If we could keep them looking something like this, things can get easier for us.

See you soon-jvf

Hi Jvf,

I took some time to talk with my supervisor yesterday about these event days. She told me as far as she's concerned that event days running from -60 to -548 are useless because the electoral system won't keep answers to these questions. She don't have a better idea if these days should be removed for good or not.

The other thing is that event days from -35 to -548 does not need to be assigned to a specific day of the week. As you saw, some are for Sunday and others for Thursday, but the fact is that they don't belong to any day of the week and if they were assigned on Sunday and Thursday, it was mostly to ''decorate'' the Excel calendar. I remember that on old calendars these days were apart, mostly on another page. We could make them insert on a new page or after some lines and that will be ok.

About generating new calendars, I think users will have to entry these last event days before inputting any new calendar statements. I don't think it will be that unconfortable, but it's the best we can do. Either having these days inserted apart a little bit separated from the Excel calendar or havin the users enter them won't be that difficult. What do you think? As you can notice, I did not get a clearer explanation about them because even my supervisor does not have any clue about these ones.

see you around my friend
stronghold888

Hi Stronghold, as I thought, the last entries are just arranged to look good. I will think about what to do.

There is another issue to be confronted with these calendars if they are adjustable. If users can add or subtract an event day number that wasnt in the original calendar then we must take this into account. For example, if they delete an EventDay then we need to unassign the statements for this day. This also means that if EventDay 0 is changed we cant just delete the days and rearrange them because we would not put back any user entered days. We must gather up all the days in the current calendar and move them around to change EventDay0. This is where the days at the bottom might get scrambled up because there might not be any way to tell where they really begin. If they get out of order and the users want to change them around to make the spreadsheet look better then they can do that themselves.

Im going to be busy for a day or two and I will attend to this as I can.

See you soon-jvf

Hi Jvf,

Yes, you,re right, if uses can add or substract an event day number that wasn't in the original calendar, then the statements need to be unassigned for this day. Given the fact, I don't think that EventDay 0 will not change that much, but who knows. It's hard to predict everything because I don't have any information on what will happen later :o(
If that changes, then as you mention, days at the bottom could get scrambled up. If these days get out of order, then users will arrange them.

see you around my friend
stronghold888

Hi Jvf,

I was thinking about the unassigned statements. If a calendar event is deleted, then these statements will become unassigned. I just found a possible problem: Many of these statements also belong to other event calendars, so if we make them unassigned, it will then be hard to know which calendar they were associated. In other words, let's take this exemple: we delete the 52 day calendar and most of statements also belong to other calendars such as 35, 45, 55. How statements can be unassigned without unnasigning them from other calendars? I was thinking about this last night and then this morning I tried to see if we can find a solution to this. What do you think?

see you around my friend
stronghold888