Check Box Control doesn't function after Query (1 Viewer)

[Coach Ty]

Hello,

I'm wondering if anyone can help me with the following issue:
I've based some forms on some queries.

Some of the forms are based on queries that are straight forward, returning all records in the query results.
Other queries are based on "IIF" statements and only return the results that are based in the "IIF" statements.

The results in both types of queries also return check boxes, as part of the record information. These check boxes are used to input filtering information for the next steps in the process.

Here's the problem:
The straight forward queries return the information requested, as well as the check boxes. These check boxes function normally and can be used to designate True/False values. Which is exactly what I want them all to do.

However, the check boxes that are contained within the results of the queries that are based on "IIF" statements, will not allow you to enter a True/ False value into the check boxes, after the query is ran.

Can someone please tell me why this is and how to resolve it? I need to be able to enter a True/False value into the check boxes that are returned as a result of a query that has used an "IIF" statement.

Please note that the table that contains the check boxes will allow the boxes to be modified and the queries that do not utilize "IIF" statements do the same. The boxes are only not functioning, after they are returned using a query that contains "IIF" statements.

Any help you can give me will be greatly appreciated.

Thank you!

 

[HiTechCoach]

Are you using Access 2003? If yes, I say a post in the last few daya were they had to apply a Hot Fix from Microsoft ot fix the issue.

see:
Programming issue. Bug

 

[boblarson]

Or it could simply be a case of fields with formulas in them (IIF is a formula) are not updateable. (at least the fields that use the IIF would definitely not be updateable and it may be that the query has other reasons why it is not updateable for the other fields.

 

[Coach Ty]

No I'm actually using Access 2007.

It's very strange and it must have to do with the IIF statements that run the query. For some reason this process causes the check boxes, that are returned from the query, to not have the ability to be modified after the query.

The queries that are run, without using any IIF statements are working fine.

I'm not sure if there is anything I need to do to modify the query? The check boxes, can still be modifed manually through the table. But cannot be modified using the results of queries that contain IIF statements

 

[boblarson]

No I'm actually using Access 2007.

It's very strange and it must have to do with the IIF statements that run the query. For some reason this process causes the check boxes, that are returned from the query, to not have the ability to be modified after the query.

The queries that are run, without using any IIF statements are working fine.

I'm not sure if there is anything I need to do to modify the query? The check boxes, can still be modifed manually through the table. But cannot be modified using the results of queries that contain IIF statements

Can you post your SQL for those queries and some screenshots that show what you are doing? It doesn't quite make sense when you say "IIF statements that run the query." HOW are these IIF statements running a query? IIF statements do not RUN a query. They can be part of a query, they can bring data back from a query, but they do not RUN a query.

 

[Coach Ty]

The Scheduling table contains basic employee information such as name and scheduling date.
Then it also has a series of check boxes that will determine job duties for that specific date.
Included in this list of check boxes is one entitled "Out" for employees who will be absent on that date. Fields are also ranked according to job performance.

The IIF statements I used to filter out are:
Date: IIf([Out]=False,[Date of Schedule])
Name: IIf([Out]=False,[Player Full Name])

These statments return information for only the employees that are not checked as being out for that date.
The information returned includes:
Date, Name, and check boxes including the various job duties for that date.

What I want to be able to do is check the job duty boxes for the employees, once the query has returned all of the employee information for those who will be working on that day.

It returns the information fine, however you cannot modify the check boxes or enter a value after the information is returned.

 

[Rich]

Take the Iif statements out of the query and use a Form filter instead

 

[HiTechCoach]

The IIF statements I used to filter out are:
Date: IIf([Out]=False,[Date of Schedule])
Name: IIf([Out]=False,[Player Full Name])

Your IIF() statements are really not valid. You are missing the parameter for when false.

The should be something like:

Date: IIf([Out]=False,[Date of Schedule], Null)
Name: IIf([Out]=False,[Player Full Name], Null)


I also agree that this should be done at the Form or Report level and not the query level.

 

[Coach Ty]

I actually tried adding the ", Null" to the IIF statements but it returned a "0" in place of the check boxes that followed.
I think the solution is to delete the IIF statements in the query, and filter the individual forms. But I'm not sure how to do it?
Should I filter the query, using the filter option in the property sheet or filter the form itself?
And how would I do this, in order for the report or query to return only those employees who have a "False" value in the check box?

 

[HiTechCoach]

I actually tried adding the ", Null" to the IIF statements but it returned a "0" in place of the check boxes that followed.
I think the solution is to delete the IIF statements in the query, and filter the individual forms. But I'm not sure how to do it?
Should I filter the query, using the filter option in the property sheet or filter the form itself?
And how would I do this, in order for the report or query to return only those employees who have a "False" value in the check box?

Note: I just use Null for false as an example. You will need to replace with the appropriate value

These appear to be calculated fields. How are they being used to filter the data? They do not appear to be part of the Where clause of the SQL.

It will probably help if you post the SQL for the query.

 

[Coach Ty]

I'm actually very new to Access, but I've learned a lot in a short time.
The project I'm working on is really very simple, or at least I think it should be.
The table that everything is based on contains the following column fielsd:
Date, Emp. ID, Emp Name, and then the following check box columns:
"Out", M, Tu, W, Th, F, Then additional text boxes for job duties.
I have the preliminary form to designate employees as being out for a specific day of the week.
Then the next form step is to designate only the employees that will be there, on a specific day, for the various job duties.
So, I need the second form to bring up the information for only the employees who have a "False" value for the "Out" check box. I also need it to bring up the additional check boxes for editing the days of the week and the job duties.
I had it bring up the employees that weren't listed as "out" using a query. But then when it brings up the employees, who aren't listed as out, along with the text boxes, it won't let me edit the check boxes.
So, if I program the query to bring up all of the employees, I will need to filter the form to only return the employees who have a "False" value in the "Out" check box, after the prior form step.

 

[HiTechCoach]

What you have been posting are not filters but calculated fields. We need to see how your are "filtering" the records, which is normally done in the Where cluse of the SQL. That is why we need to see your query.

Please post the SQL for your query that you are currently using. Oopen the query in design mode. Switch the view to SQL. COpy and paste the SQL to your post using the Code (#) forum tags

 

[Coach Ty]

Here is the SQL Statement:

# SELECT TOP 8 [Employee Table].Out, [QRY-Performance Ranking].[Date of Evaluation], [QRY-Performance Ranking].[Employee Full Name], [Employee Information].[ID], [QRY-Employee Performance Ranking].[Ranking], [Schedule Table].[All], [Schedule Table].[M], [Schedule Table].[Tu], [Schedule Table].[W], [Schedule Table].[Th], [Schedule Table].[F]
FROM ([QRY-Performance Ranking] INNER JOIN [Employee Information] ON [QRY-Performance Ranking].[ID] = [Employee Information].[ID]) INNER JOIN [Schedule Table] ON [Employee Information].[ID] = [Schedule Table].[ID]
ORDER BY [QRY-Performance Ranking].[Date of Evaluation] DESC , [QRY-Performance Ranking].[Ranking];

 

[SOS]

Well, right there is a big problem. A Query with a TOP clause (SELECT TOP 8) is immediately read only. (did you read the link Bob posted? It says that right near the top)?

 

[SOS]

(did you read the link Bob posted? It says that right near the top)?

Sorry, I thought it said that, but it doesn't (I went back to look).

 

[SOS]

Looks like we ALSO need the SQL behind: QRY-Performance Ranking as it is the main source of this query.