Feb 10 2007, 04:53 AM
I have a form with a lot of command buttons on it (24 of them).
Each command button runs an update query.
I want to use checkboxes so that I can remember which update queries have been run and which ones have not.
How can I do this?
My thought was to:-
Create a table called tbl_Check which has 2 fields:
ID_Check which is a text data type and the text I put in identifies the command button for me
Check which is a T/F Boolean
But I dont know how to click on a check box and have the appropriate tick updated in the table.
I did bind the table to the form. And I got one checkbox to work. But when I tried to do another checkbox, they both changed together when I clicked on one of them.
Perhaps there is an easier way than what I was trying?
Feb 10 2007, 05:13 AM
Well first of all kudos on the decision for a table to store values and not a non-normalized set of fields! ;-)
In the command button's click event you already have some code which executes the Update query.
If you just add to that a call to an update query for your tbl_Check
Private Sub cmdButtonName_Click()
CurrentDb.Execute "YourUpdateQuery" ' the code you already have in the button's click
'or even just the same call in all of your command buttons - so could be a single function/sub without parameter
Function fUpdateCmdCheck(strCommandName as String)
CurrentDb.Execute "UPDATE tbl_Check SET [Check] = True WHERE [ID_Check] =] '" & strCommandName & "'"
Feb 10 2007, 05:34 AM
I'm sorry but I don't understand you.
The code in my command buttons is like this:
Private Sub cmd_07Bib1_____Core_Click()
On Error GoTo Err_cmd_07Bib1_____Core_Click
Dim stDocName As String
stDocName = "qry_multiple_07-1"
DoCmd.OpenQuery stDocName, acNormal, acEdit
The check box for this particular command button is called Chk_07Bib1
Could you please explain in a bit more detail?
Feb 10 2007, 05:42 AM
I'm wondering if I should have had 24 fields in my tbl_Check.
But that might be what you meant when you said
not a non-normalized set of fields!
Feb 10 2007, 05:48 AM
I don't follow how that conforms to the normalized table structure (for tbl_Check) you described earlier.
When you say checkbox - are you talking about a Yes/No field in a table? (Rather than an actual checkbox control on a form). I see no value in trying to do this update through the UI with a bound checkbox.
How does Chk_07Bib1 relate to what you're wanting to do in tbl_Check?
Feb 10 2007, 05:49 AM
Correct - that's what I mean - you shouldn't revert back to have 24 fields.
That would be a step backwards.
Feb 10 2007, 06:01 AM
I will do some thinking and get back to you in a couple of days.
I have to make a decision on whether I want to
a) have the command button activate some kind of indicator to show that that update query has been run (which I think is what you are suggesting)
b) have a checkbox next to the command button (my original thinking) that I would click on to make the tick show to indicate that the update query was run.
The reason for a and b is that I might click on the command button and then cancel the update query - in which case it would not have been run.
Meanwhile I will have a think and endeavor to understand what you first wrote.
I'll probably get back to you in a couple of day.
Feb 10 2007, 06:04 AM
You can completely control the execution of the update query in code - checking the table value only if it successfully execute.
The method you're currently using is the only thing making it cancellable.
Feb 13 2007, 05:24 AM
I've had a good go at what you suggested but it isn't working as I expected:
I can't get tbl_Check to change showing a check mark anywhere
I can't get the checkbox on the form to show a tick in it.
I have attached a portion of the database with my attempt at your code on the first two command buttons on the left -
ie the ones labelled:
07Bib1 --> Core
07Bib2 --> Core
Would you mind having a look at the code for me please?
Feb 13 2007, 05:55 AM
I've just figured out what I had wrong in one area.
I now have the table (tbl_Check) updating correctly. Thankyou.
However, I want the checkboxes on the form to reflect the ticks in the table so that when I'm using the form, I will be able to tell by seeing the ticks, which queries have been run and which have not.
Feb 13 2007, 05:58 AM
If you're wanting to have a visible check mark on screen you'll have to code that separately with the setup you currently have.
The normalized table structure is fine. But that doesn't necessarily sit well with the flat layout of command buttons that you have.
The whole thing could perhaps be normalized - having the command buttons and checkboxes (and hence bound check fields) in a continuous form.
The naming conventions you have are making things harder.
For example your command button names. Were they all named consistently (with only minor changes) then you could write very generic routines.
For example you could include the query name in the table list - and run it all from there :-)
Feb 13 2007, 08:38 AM
Sorry - I forgot to include the reworked function for you lol
(Head screwed on and all that... :-)
Function fUpdateCmdCheck(strCommandName As String)
Dim strEntry As String
strEntry = strCommandName
Do Until InStr(strEntry, "__") = 0
strEntry = Replace(strEntry, "__", "_")
strEntry = Split(strEntry, "_")(1)
CurrentDb.Execute "UPDATE tbl_Check SET [Check] = True WHERE [ID_Check] = '" & strEntry & "'"
Feb 13 2007, 03:00 PM
Maybe I should just put a label on the form and put a tick in it when the command button is clicked.
lblCheckBib1.Caption = Chr(252)
That seems to me to be an easy solution.
Feb 13 2007, 03:29 PM
That would be easy. Of course it won't persist - it's a per form session set of information.
It's up to you how you want to represent it.
Standardizing the names and such could always allow you to make it efficient - but with some effort to develop first. :-)
Feb 13 2007, 07:24 PM
Ah yes of course! It wont stay there. I need something on the form to show a tick so that I have a visual indication that the query has been run. And I need the visual indication to remain after the form is closed.
You have said that my naming conventions are making the job harder. Could you please elaborate on that and suggest a better way?
I can't figure out the property of the checkbox that I can set to make it true or false.
I can't see how it would make the checkbox show a tick permanently.
I'm beginning to think that the non-normalised method of having 24 fields in the table would be a lot easier!
Feb 13 2007, 07:53 PM
Just a suggestion but Unbind and place Me.Chk_07Bib1 = -1 in the After Update Event of your Buttons? for each of your buttons would look like This:
Private Sub cmd_07Bib1_____Core_Click()
On Error GoTo Err_cmd_07Bib1_____Core_Click
Dim stDocName As String
' I REMed out the next 2 lines to stop Access giving an error cause the tables weren't included - not necessary
' stDocName = "qry_multiple_07-1"
' DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox "This is where the 1st checkbox should show as ticked", vbOKOnly
Me.Chk_07Bib1 = -1 .................................Added This
Feb 13 2007, 08:49 PM
Thanks John. That puts a nice tick in the checkbox. However, when the form is closed and reopened the tick is gone and I want it to reappear. Is there a way of looking at the table and seeing that there is a tick in the Check field for that checkbox and then making the form display it when it opens?
Feb 13 2007, 08:59 PM
Not that i know of with the current structure as you have it with check being the field name of the control. If you bind the check box and use the code I suggested ALL checkboxes will be ticked. The Impression I had in reading your post I was under the impression you just wanted a visual means to remember the ones you had updated and those you had not. Sorry I can not help you more first impression is that your DB is not Normalized but again with what I see hard to tell.
Feb 13 2007, 09:10 PM
Again I got no notification of your earlier post - sorry for the delay.
>> "I'm beginning to think that the non-normalised method of having 24 fields in the table would be a lot easier!"
Of course it would.
That's almost always the trick with normalization. That it's quick and easy to get a result if you don't follow it's occasionally rigorous path.
As I said before - strict normalization needn't necessarily apply here as this is application data.
But development life becomes worse when you don't follow it's rules. (If not sooner then later).
What I mean regarding your naming convention is things like your command buttons names containing "-->"
And those names not corresponding with the query they're to execute.
The query names not corresponding to the listings in the table - which themselves only partially co-incide with the command names. (Hence the need for the function alteration I suggested earlier).
It's just more effort than it needs to be.
Do you particularly want your command buttons laid out so - and not in a continuous scrolling vertical list (i.e. a subform)?
Feb 13 2007, 09:20 PM
Yes I want my command buttons laid out as they are as that makes it much easier to work with them. Each vertical column of buttons represents a different year (7, 8, 9, 10) of students.
Is it possible to do something like
If tbl_Check.ID_Check = "07Bib1" And tbl_Check.Check = -1 Then ' if it is checked in the table
Chk_07Bib1 = -1 ' then check it on the screen
I know that piece of code doesn't work at all, but maybe you could tell me how to write it so that it does.
Feb 13 2007, 09:25 PM
Yes that is possible (as you point out - not with that syntax) but my point is your widely varying naming conventions for the related objects makes it far harder than it need be.
A single generic function could be doing so many things for you here - if there were just some obvious cohesion to it all.
If DLookup("Check", "tbl_Check", "WHERE = ID_Check " & "07Bib1") Then
Me.Chk_07Bib1 = True ' then check it on the screen
But you're hard coding that each time - for each varing name in each command button.
Feb 13 2007, 10:48 PM
I have decided that I have spent far too long on this. It will only be used for about a week (maybe 2) at the start of each school year when students are being placed in their classes in the db. So I have decided to do what will give me the visual display I want without regard to "correct" normalisation procedures.
It is obvious that I need to learn more about normalising and naming conventions. I thought that my command buttons names were clear and related well to what they actually were (appart from the --> which I have removed).
I know that my decision will probably make you think badly of me and for that I am sorry. But I just wanted a visual display of which queries had already been run. Because if they are run again I may get duplicate data (and yes, I'm realising that that means the db was not structured well enough in the first place).
Thanks for your willingness to help - that's what I like most about this forum.
Feb 14 2007, 03:20 AM
>> "I know that my decision will probably make you think badly of me"
Not at all.
I'm not saying that you're making a big mistake - indeed as I've said this isn't a normal data situation.
I think you'll get away with it.
And in general I firmly believe in allowing people to discover things for themselves.
Are each of these queries to be run only once for the entire system - and not for each Pupil/Class etc?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here