Help with making a table (1 Viewer)

[MBQ]

Hi, I am currently creating a table using checkboxes etc. I am stuck on the expression to use for the following task:

I have 5 checkboxes in each row. I want to create an expression/validation rule to say that if any of these boxes are not checked as completed then the next field checkbox cannot be ticked as "Ready for Submission".

I am new to Microsoft Access so any help would be greatly appreciated, thanks.

 

[Minty]

Generally, this type of design is a hangover from a spreadsheet design, and invariably leads to lots of issues when used in a database.

Can you explain (in plain English) the purpose of the table and maybe post a picture of its design, or list the fields?

Oh - And welcome to AWF!

 

[MBQ]

Thanks!

Basically I have a list of checks (eg. correct paperwork etc.) that need to be completed before a job is ready to be submitted for payment. At the moment I have the 5 "checks" that need to be completed in 5 fields & with checkboxes. I then have another field that is entitled "Ready for Submission". I want this field to say automatically say Yes when all of the 5 checks have been completed & No when at least one checkbox is not ticked.

 

[Minty]

Okay - It's not the ideal way to handle this, however, what is wrong is your Ready for Submission field.

First things first, please, please remove all the spaces from your field names, you will end up typing a whole heap of square bracket around everything you do. Don't use special characters anywhere either. CamelCase is your friend and if necessary an underscore.

Secondly, you can calculate if all those items are checked off so don't store the answer.
You can always calculate the correct result and when you can then if any of those items are unchecked then it's automatically going to update your calculation result rather than you having to capture that data change somewhere.

In a query you can use a calculated field to display this something like;

SubmissionReady: Field1 + Field2 + Field3 + Field4 + Field5

This will "And" all the values and as they are Boolean and false value will result in a False answer.

Bear in mind that if you need to add another checking step to your process you will have to adjust everything to accommodate it.
That is why this type of approach isn't really the Normalised way to go.

 

[Isaac]

I also would steer clear of creative datatypes of all kinds, when possible, in all database systems:
- Checkboxes
- Multi valued fields
- Hyperlink fields

I could only think of 3 at the moment.

Just use short text and populate with 1, 0, Yes, No, etc., or numbers if you prefer. Save yourself a lot of hassle....

For the checkbox visual, handle that at the form level.

 

[Pat Hartman]

I also would steer clear of creative datatypes of all kinds, when possible, in all database systems:
- Checkboxes

Access automatically displays Y/N fields as checkboxes. There is nothing wrong with that. SQL Server might do it also.

MBQ,
As the others have said, this is not a properly designed method when using a relational database. You can do it, but don't expect any Excel-Like functions to help you out.

I don't know how stable this work flow is but stand back and think about that. Talk to the primary user about when was the last time that the workflow changed or if he thinks that their might be some step in the future that might need to be added or deleted. Once you commit to this "flat" approach, you're committed, it will be a real PITA to add/remove/reorder the steps. When you first learned to drive, you were taught defensive driving techniques. Well, this is a defensive programming technique - NEVER hardcode something you can abstract. In a properly designed table, each of these status' would be a row. You would create a table that defines the workflow. It includes one row for each step. Including a name and sequence number. You might even build some logic into the table itself but we won't go there for now. When you create a new job, you copy the steps from the template table and append them to the JobSteps table. In the JobSteps table would be a FK to the Job, a FK to the Steps, and a Date that a step was completed. So you can count the completed steps using dcount()

If dCount("*", "tblJobSteps", "JobID = " & Me.JobID) = 5 then
    '''  path when everything is done
Else
    '''' path when not all steps are done
End If