Need Check Boxes to Stay Checked!! 1

[gmillerinc]

I have a form (that is you used for our Time Tracking here at my job) that is based upon a table. The form has check boxes that grays out times (in the form of check boxes on the form) as the day goes along.

For instance, if I clicked check boxes 6-6:30 AM and 6:30-7AM, then this is recorded in the underlying table and the times are grayed out...which I put it this way so that users can not go and put more than one task for each 30 minute increment. These check box times on the forms are fields in the underlying table.

The problem here is that when I close the form and come back into it, guess what...the check boxes are no longer there. I tried the Unload event but its not working.

I used the Unload event here:

Private Sub Form_Unload(Cancel As Integer)

If (Me![6to630] = True) Then Me![6to630].Enabled = False
If (Me![630to7] = True) Then Me![630to7].Enabled = False
If (Me![7to730] = True) Then Me![7to730].Enabled = False
If (Me![730to8] = True) Then Me![730to8].Enabled = False
If (Me![8to830] = True) Then Me![8to830].Enabled = False
If (Me![830to9] = True) Then Me![830to9].Enabled = False

End Sub


For example 6to630 is the name of a check box. I want it to gray out...which is what 'Enabled = False' supposed to do if the box is check.

When I read the definition of an Unload event in Access, it seems exactly like what I need. According to Access 97, the Unload event is defined as follows: "The Unload event occurs after a form is closed but before it's removed from the screen. When the form is reloaded, Microsoft Access redisplays the form and reinitializes the contents of all its controls."

If you want to suggest another way, please advise. Please help, this shouldn't be complicated at all.

 

[GKProgrammer]

OK, supply me with a little more detail before I can suggest a way to handle this.

On Day one the user selects all checkboxes and all are now disabled and cannot be selected, which is what it seems like you are aiming for. Now what are you planning on doing for the next day? How do you have your form set up to recycle each day? How are you planning on refreshing these checkboxes back to enabled for the next day? How is this form setup, Continuous?

Regards,
gkprogrammer

 

[gmillerinc]

As far as having the check boxes to clear for the next day, let’s just try to handle this problem first. If you can get this working right, we have accomplished a lot. Plus I’m pretty sure it’s at least feasible to get them to clear or change back to “Enable” for the next day.

The form is a “Single Form” not Continuous.

Hey (FYI) I neglected to mention that in order to add the check boxes’ data to the underlying table, the box must be checked at first. After that, I have an button (called ADD)that has an OnClick event with the following code (please don’t dwell on this code, it already works...just giving you more detail of my project so you can understand more):

Private Sub Command21_Click()
On Error GoTo Command21_Click_Err

If (Me![6to630] = True) Then Me![6to630].Enabled = False
If (Me![630to7] = True) Then Me![630to7].Enabled = False
If (Me![7to730] = True) Then Me![7to730].Enabled = False
If (Me![730to8] = True) Then Me![730to8].Enabled = False
If (Me![8to830] = True) Then Me![8to830].Enabled = False
If (Me![830to9] = True) Then Me![830to9].Enabled = False

DoCmd.GoToRecord , , acNewRec

Command21_Click_Exit:
Exit Sub

Command21_Click_Err:
MsgBox Error$
Resume Command21_Click_Exit

End Sub

So, all in all, this code looks to see if the check box is checked and grays out whenever you click the . This is what I want to remain grayed out when I re-enter the form (this is important…the sole purpose for me posting this thread).

Please respond as soon as you possible with your solution.

 

[GKProgrammer]

OK, the only reason I asked is because personally I find the planning stage to be one of the most important parts to designing a successful and user friendly DB but that's only my opinion. Anyway, to answer your question you can simply place this code under the Form_Load() instead of unload and you will receive the result you are looking for.

Regards,
gkprogrammer

 

[gmillerinc]

Yes I agree planning is very important.

But I tried putting the code in the Form_Load() event and it is not working.

Everything works when I don't exit the form. But here's a scenario of it NOT working (when I exit the form and come back to it):

--I click 2 check boxes and click ADD
--then click another check box and click ADD
--Exit the form

After doing this and come back into the form the first 2 check boxes are checked but the 3rd check box isn't check.

When I had this form with the UnLoad (instead of the Form_Load() as described above), none of the check boxes would be checked.

Please advice.

 

[GKProgrammer]

OK, how is your table setup. I am assuming that each record has a date which could probably be your primary key and then you have a checkbox field for each time slot (all for the same record) for that single day, is this correct? Then when your form loads it should only refer to one record and each checkbox is bound to the respective field that they represent for that single record, right?

Regards,
gkprogrammer

 

[gmillerinc]

There are only 6 fields in the table. They consist of the time increments only. The table design is as follows

Field Name Data Type
6-6:30 Yes/No
6:30-7 Yes/No
7-7:30 Yes/No
7:30-8 Yes/No
8-8:30 Yes/No
8:30-9 Yes/No

This is only a simplified verson of my database to get what I want it to do. I don't want to inroduce anything else right now until I get this piece working.

Anyway though, I can not have the times on a single record. This is because there are tasks (that I will put in a combo box) that could be different for each time increment. I can implement the tasks later if you can get this piece to working.

Please advise.

 

[GKProgrammer]

What???
"Anyway though, I can not have the times on a single record."

Field Name Data Type
6-6:30 Yes/No
6:30-7 Yes/No
7-7:30 Yes/No
7:30-8 Yes/No
8-8:30 Yes/No
8:30-9 Yes/No

With this setup you have displayed above you do have all of the times on a single record and there is no primary key for this table, which is a big no no right from the start.

Anyway seeing as you are unwilling to add anything at the moment, I will say this:

As long as your forms recordsource is set to this table and each checkbox Control Source is set to the respective field then when the checkboxes are clicked they will update the underlying table. Then when your form loads the checkboxes will be updated to the current record in your underlying table and with this code in the Form_Load:

If (Me.[6to630] = True) Then Me.[6to630].Enabled = False
If (Me.[630to7] = True) Then Me.[630to7].Enabled = False
If (Me.[7to730] = True) Then Me.[7to730].Enabled = False
If (Me.[730to8] = True) Then Me.[730to8].Enabled = False
If (Me.[8to830] = True) Then Me.[8to830].Enabled = False
If (Me.[830to9] = True) Then Me.[830to9].Enabled = False

It will ensure that all checkboxes that have a true value will be disabled. If this setup is not working as is there is some other code that you have not mentioned that is affecting the way the checkboxes are displayed. Like you said this is very simple and there is no reason why this won't work.


Regards,
gkprogrammer

 

[gmillerinc]

Yes you're right. Primary keys are important. I usually put it on tables that I know I will link to other tables or queries.

I guess I didn't make myself clear with the table setup. Consider again the table setup:
Field Name Data Type
6-6:30 Yes/No
6:30-7 Yes/No
7-7:30 Yes/No
7:30-8 Yes/No
8-8:30 Yes/No
8:30-9 Yes/No

The times' fields here are column headings for the table. The actual table looks like the following when data has been entered from the form:

6-6:30 6:30-7 7-7:30 7:30-8 8-8:30 8:30-9
YES YES NO NO NO NO
NO NO YES NO NO NO

So according to the table above, the user has entered in times for 6-6:30 on one record and for 7-7:30 on the next record.

If I didn't exit the form the "YES" times on the form would be grayed out, but when I went back in they are not gray..every check box is empty.

You explicitly stated in your last email that: when my form loads, the checkboxes will be updated to the current record in my underlying table.

I realize this. But I hope you know by now the current record will not help me out, this is because I need the form to be updated by ALL records that have been entered at any point during the day not just the current record. As you see from the table example above, I need the entered check box data to be on different records (trust me I do).
This is the reason I wanted to update the form to the last state the check boxes were before a user closes out the form. I don't think this is possible if I base it upon the underlying table since there are separte records, so this is why I suggesst update by the form's entered date (for when I open up the form for the second time).


On your last note you said: If this setup is not working as is there is some other code that you have not mentioned that is affecting the way the checkboxes are displayed.

This is the reason I kept it simple, I don't want to interject anything that would give me a solution to this problem. I am only dealing with one table and one form. I'm pretty sure there isn't anything causing it not work. You see how long these discussion are with just one table and form, I know communication b/t you and I would get confusing if I told you about other parts of the database.

I really appreciate your help. Thank you for being patient with me. I hope you understand what I'm asking. Even if you tell me I can not accomplish this, it would prevent me from spinning my wheels. But I do think it can be accomplished.

Help as you can.

 

[gmillerinc]

Ok here are corrections to that last post.



About half way down, change date to data as in
...this is why I suggesst update by the form's entered data (for when I open up the form for the second time

2nd to last paragraph should read:
This is the reason I kept it simple, I don't want to interject anything that would add unneeded complexity to solving this problem.

Sorry so long

 

[GKProgrammer]

Now this is making alot more sense, I was under the impression that these checkboxes were to be bound to the table but this is not the case. Well we would need a way to keep track of which checkboxes were selected the last time the form was open and the only way to do this would be with an external source from the form. So I would suggest creating some sort of text file that will hold the last value that the checkboxes were when the form was closed.

In form close we will need to edit this text file (which you can place anywhere on your computer, I would suggest in the same folder that the DB will live but that is up to you), try something like this:

Private Sub Form_Close()
Open "C:\YourFilename.txt" For Output As #1

Print #1, Me.[6to630].Value
Print #1, Me.[630to7].Value
Print #1, Me.[7to730].Value
Print #1, Me.[730to8].Value
Print #1, Me.[8to830].Value
Print #1, Me.[830to9].Value
Close #1
End Sub

Now on form open we will have to open this text file and make each checkbox equal the last value, something like this:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo errhandler
Dim s As String, loopcnt As Integer


Open "C:\YourFilename.txt" For Input As #1
loopcnt = 1
While EOF(1) = False
Line Input #1, s$
Select Case loopcnt
Case 1
Me.[6to630] = IIf((s$ = "-1 &quot Or (s$ = "True&quot, True, False)
Case 2
Me.[630to7] = IIf((s$ = "-1 &quot Or (s$ = "True&quot, True, False)
Case 3
Me.[7to730] = IIf((s$ = "-1 &quot Or (s$ = "True&quot, True, False)
Case 4
Me.[730to8] = IIf((s$ = "-1 &quot Or (s$ = "True&quot, True, False)
Case 5
Me.[8to830] = IIf((s$ = "-1 &quot Or (s$ = "True&quot, True, False)
Case 6
Me.[830to9] = IIf((s$ = "-1 &quot Or (s$ = "True&quot, True, False)

End Select
loopcnt = loopcnt + 1
Wend
Close #1
Exit Sub

errhandler:
If Err.Number = 53 Then Exit Sub
End Sub

Let me know if this helps.

Regards,
gkprogrammer

 

[gmillerinc]

I'm in the process of trying the code you recommended. Shouldn't an exclamation mark (!) follow the word 'Me' to indicate that it is the current form or does it matter?

Does the "-1" and "True" in the following statement represent a check box checked or not?
Me.[6to630] = IIf((s$ = "-1 &quot Or (s$ = "True&quot, True, False)



I think it does. I just wasn't sure.

 

[GKProgrammer]

I always the the ".", so you will be OK, actually all you really need is the control name, the "Me." isn't even neccesary. Also yes the "-1 " (be sure to include the space at the end of the one) and the "true" indicate that it is checked. One last thing that I neglected to add to the code which I am sure you already figured out is the addition of the enabled code which would end up looking like this for the form_open() event:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo errhandler
Dim s As String, loopcnt As Integer


Open "C:\YourFilename.txt" For Input As #1
loopcnt = 1
While EOF(1) = False
Line Input #1, s$
Select Case loopcnt
Case 1
Me.[6to630] = IIf((s$ = "-1 &quot Or (s$ = "True&quot, True, False)
If (Me.[6to630] = True) Then Me.[6to630].Enabled = False
Case 2
Me.[630to7] = IIf((s$ = "-1 &quot Or (s$ = "True&quot, True, False)
If (Me.[630to7] = True) Then Me.[630to7].Enabled = False
Case 3
Me.[7to730] = IIf((s$ = "-1 &quot Or (s$ = "True&quot, True, False)
If (Me.[7to730] = True) Then Me.[7to730].Enabled = False
Case 4
Me.[730to8] = IIf((s$ = "-1 &quot Or (s$ = "True&quot, True, False)
If (Me.[730to8] = True) Then Me.[730to8].Enabled = False
Case 5
Me.[8to830] = IIf((s$ = "-1 &quot Or (s$ = "True&quot, True, False)
If (Me.[8to830] = True) Then Me.[8to830].Enabled = False
Case 6
Me.[830to9] = IIf((s$ = "-1 &quot Or (s$ = "True&quot, True, False)
If (Me.[830to9] = True) Then Me.[830to9].Enabled = False

End Select
loopcnt = loopcnt + 1
Wend
Close #1
Exit Sub

errhandler:
If Err.Number = 53 Then Exit Sub
End Sub


Let me know how it goes.




Regards,
gkprogrammer

 

[gmillerinc]

I have been out of the office over the weekend. I tried entereing the code you suggested (for opening and closing the form).

When I enter my choice(s) in the form when I open it up and close the form, I get the following error message:
"Run-time error '55'. File Already Open"

Actually that happens even if I don't enter data into the checkboxes. If I bypass the error and get back into the form, the only boxes that are check are still the first line of the underlying table. Even then it isn't grayed out, it's just checked.

At this point, if you are adamant what you are suggesting will work, please go ahead and proceed. Otherwise, let me know if it shoudln't.

I really appreciate your help.

 

[GKProgrammer]

It should work, the only way you could receive the error "Run-time error '55'. File Already Open" is if the code you entered doesn't reach the Close #1 on your Form Open event or you have the file opened manually. If you have the exact code as I do above then the close #1 should always be processed in the form_open. As for the checkboxes displaying the underlying table I can only see this happening if they are bound to the table which we have already established they shouldn't be right? Make sure they are not bound and then there would be no way for these controls to be pointing to the table. If you are still having problems and if you have made changes to the code that I have supplied then please post your code so I can have a look at it. I have made a quick test form with this code and all seemed to go well.

Regards,
gkprogrammer

 

[gmillerinc]

I haven't made any changes to the code. Can you email me a copy of the sample database that you created that works fine. My address is simply my tek-tips username at hotmail.

 

[GKProgrammer]

OK, I have emailed the file to you, let me know if you didn't receive it.

Regards,
gkprogrammer

 

[JerryDennison]

GKProgrammer:

Just an FYI. You can use the following syntax instead of what you are currently using:

Me.[830to9].Enabled = Not Me.[830to9]

rather than the longer IF statement.

"Advice is a dangerous gift, even from the wise to the wise, for all course may run ill." J.R.R. Tolkien

 

[danvlas]

Sorry to pop in...but I couldn't help myself.

This is the worst setup of all.
All fields in your table indicate time intervals (for now 30 minutes). What if you want to reduce the interval to 15 minutes? Or to 1 minute? Are you going to change the entire table structure?????
A table with:

Employee
EventDate
StartTime
Interval
Unit

will be able to store the agenda much better. You can say:
Schedule Employee Jim, on October 15, for a job starting at 09:05 and lasting 28 minutes
or
Schedule Employee Bob, on December 24, for a job starting at 14:30 and lasting 2 hours
and so on.

Use the BeforeUpdate event of the form to detect possible schedule conflicts and deny adding the record if such conflict occurs.

That would be pure SQL to get the conflicts: if any row is returned for that employee in the given period of time, you have a conflict.

But DON'T use repeating groups. They're nothing but trouble.

GKProrammer mentioned it:
"With this setup you have displayed above you do have all of the times on a single record and there is no primary key for this table, which is a big no no right from the start."

Read about normalization.

Re-think the database setup and relationships while you still can. The longer you keep this setup, the harder you will hit the wall when the time comes...

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[GKProgrammer]

Hi Jerry,

Thanks for your input, while you may be right this particular line of code was written by gmillerinc and in his email he stated:
"....I have an button (called ADD)that has an OnClick event with the following code (please don’t dwell on this code, it already works...just giving you more detail of my project so you can understand more):"
Obviously he is happy with it and doesn't want it changed.

Regards,
gkprogrammer