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?

Groer

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

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

Exit_cmd_07Bib1_____Core_Click:
Exit Sub

Err_cmd_07Bib1_____Core_Click:
MsgBox Err.Description
Resume Exit_cmd_07Bib1_____Core_Click

End Sub


The check box for this particular command button is called Chk_07Bib1

Could you please explain in a bit more detail?

Groer

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!

???

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?

Correct - that's what I mean - you shouldn't revert back to have 24 fields.
That would be a step backwards.

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

Groer.

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.

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?

Groer

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

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 :-)

Sorry - I forgot to include the reworked function for you lol
(Head screwed on and all that... :-)

Maybe I should just put a label on the form and put a tick in it when the command button is clicked.
Something like
lblCheckBib1.Caption = Chr(252)

That seems to me to be an easy solution.

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

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!

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:

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?

Groer,

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.

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)?

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
End If

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.

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.

However...

If DLookup("Check", "tbl_Check", "WHERE = ID_Check " & "07Bib1") Then
Me.Chk_07Bib1 = True ' then check it on the screen
End If

But you're hard coding that each time - for each varing name in each command button.

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.

Groer

>> "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?