Auto populate a Yes/No box in a query

[SFCMatthews]

Hello all, I have a situation where I would like to have a check box turn on when training is complete. What I have is collective training events with several training requirement to be completed. I would like the competed check box to turn on after all training requirement have been meet for that collective training event. Again thanks for your help and support.

 

[plog]

Where is this checkbox? Is it on a form or report?

In general what you would do is create a query based on that form/report's underlying dataset, bring in the primary key from it and then all necessary tables to e to determine if all training was complete for that ID. That would leave you with a query whose result look like this:

ID, CompletedTraining
1, -1
2, 0
3, -1
4, -1

Then you would set your checkbox to use a Dlookup to retrieve that data for the ID it needs and determine if the checkbox should be marked (-1) or unmarked (0).

 

[SFCMatthews]

Thank you for your quick responce. I dont know if that will work, the data is arranged, I think wrong. This is what I have Table A with AID and table B with BID&AID. It is a one to one relationship, One record in A has one record in B.
But BID has 10 training task to complete befor AID can recored a complete.
Table B is arranged like this:
BID, AID, Task1, Task2, Task3, .... Completed
AN, AN, Date, Date, Date, .... CheckBox.
What im looking for is a way to have the completed box atomaticly populate after all of the dates are filled in. I know the table is not steup right, but it is what I have to work with right now. Please help.

 

[plog]

That will absolutely work, even with your improperly structured database, because it was a general method.

My suggestion is to properly structure you tables. Table B should have these fields: AID, TasNo, TaskDate. Once that's done it becomes extremely easy to make that query I was talking about.

 

[vbaInet]

What im looking for is a way to have the completed box atomaticly populate after all of the dates are filled in. I know the table is not steup right, but it is what I have to work with right now. Please help.

I would imagine you're the one developing the database so it would make perfect sense to re-structure your tables properly at this early stage. plog is giving you good advice.

By the way, if you can calculate the value there's really no point in storing it.

 

[SFCMatthews]

I agree it would make more sense to re-structure at an early stage in the development of the database. But there's a catch, I did not develop the database and its not in the early stages. I guess the reports have really driven the structure of this database. I am working on a database that will replace it. So What i am understanding there is no way to make this happen with the current structure. I really appreciate the advise. I hope I did not offend anyone just trying to work with what I have been handed.

 

[vbaInet]

In a new column in your query ...

Completed: CBool(Nz([[COLOR=Red]Date1[/COLOR]], 0)) + CBool(Nz([[COLOR=Red]Date2[/COLOR]], 0)) + [COLOR=Red]...[/COLOR] = -10

Replicate the CBool() code for the other 8 date fields and run the query.

 

[vbaInet]

Actually, that code can be shortened:

Completed: IsDate([[COLOR=Red]Date1[/COLOR]]) + IsDate([[COLOR=Red]Date2][/COLOR]) +[COLOR=Red] ...[/COLOR] = -10

 

[SFCMatthews]

Ok I have the first part working can get help with DLOOKUP function part.

 

[vbaInet]

What DLookup() function are you referring to? If there are two tables, link them via their related IDs in the query.

 

[SFCMatthews]

This DoLookup I ahve the first part working but dont understand the DoLookup.

Where is this checkbox? Is it on a form or report?

In general what you would do is create a query based on that form/report's underlying dataset, bring in the primary key from it and then all necessary tables to e to determine if all training was complete for that ID. That would leave you with a query whose result look like this:

ID, CompletedTraining
1, -1
2, 0
3, -1
4, -1

Then you would set your checkbox to use a Dlookup to retrieve that data for the ID it needs and determine if the checkbox should be marked (-1) or unmarked (0).

 

[vbaInet]

Alright, you don't need a DLookup() because of the method you're using and because the table is nor normalized. plog's idea was based on a normalized table. So perform the calculation in the original query and bind that to your form.

 

[SFCMatthews]

vbaInet, and everyone else that helped me Thank You. I understand what I need to do now.

 

[vbaInet]

Happy to hear!

And please don't save the value. Good luck with the rest of your project!

 

[SFCMatthews]

Ok I have run into a road block, I have trainees that fall into 4 different categories. Each category builds on one another. What I mean is cat_1 was training 1,2,3 out of 10 events, cat_2 has 1,2,3,4,5,6 out of 10, cat_3 has all of cat_1 and cat_2 pulse 7,8. Any way the expression I used above dose not separate the categories. Is there a way to (if trainee is in cat_1, and complete event 1,2,3 ckbox is checked, trainee in cat_2, and completes event 123456 ckbox is checked and so on).

 

[vbaInet]

I don't understand. Let me see some sample records.

 

[SFCMatthews]

I have a field called Category the records in that field are CAT I, CAT II, CAT III, CAT IV. There are 10 Training events. So
CAT I requires a trainee to complete training events 1-5.
CAT II requires a trainee to complete all of CAT I training events plus 6-8
CAT III requires a trainee to complete all of CAT I & II training events plus 9
CAT IV requires a trainee to complete all of CAT I-III training events plus 10
The higher the CAT the more event trainees have to complete. I am trying to auto populate a completed ckboxs, when all of the required training dates are inputed into the training_date field.
If said trainee is a CAT III and completes all training events 1-9, the ckbox = -1 if not = 0.

 

[vbaInet]

Seeing some sample records would make more sense.

 

[SFCMatthews]

oK
I have a UNIT_Table, Soliders_Table, and a Training_Table.

UNIT_Table:
UnitID, Unit_Name, Unit_Cat,
1, 123rd IN, CAT III
2, 456th IN, CAT II

Soldiers_Table
UnitID, SoldiersID, L_Name, F_Name, M_Name,
AN, SSN, Smith, Joe, D.,

Again the next table sturcture is wrong but it is what I ahve to work with.
Training_Table
TNID, SoliderID, UnitID, Event1, Event2, Event3 .... Training_Complete
AN, SSN, Date, Date, Date, ckBox -1/0
1 012345678 2 1jan11 1jan11 1jan11 .... ckbox = -1
2 123456789 2 1jan11 .... chbox = 0
The CKbox need to look at the CATAGORY and Training Events and decide if they are comeple or not.
CAT I event 1-6
CAT II all of CAT 1 & 7
CAT III all of CAT I & II plus 8
CAT IV all of CAT I-III & 9,10

one to many relationship ONE Unit may have Many Soldiers
one to one relationship ONE Soldier has one training Record