Code to create a query from a form

[stryder09]

Hey guys, I have a Process Change Form that has a bunch of yes/no check mark boxes on it. Is there a way depending which boxes are check marked for it to run a query and change another table to display only the valid information?

I already have the table created that has the Names of the employees listed and their position. I also have a query table created that when I put in the position it will only display those employees of that position I want.

What I am looking for though is depending which boxes are checked In the Process Change Form that it will give back only the employees needed on the Query table.

ex. 5 yes/no check boxes for Driver, Packer, Operator, Maintenance and Supervisor. I have information that only the Driver, Packer and Supervisor needs to see, so only their yes/no boxes are checked. I need to have the Query table then print with only their names. This is a check off sheet that they sign off on saying they read the information.



I am sure this is something that would need to be done in code.

Any help would be appreciated?

Brad

[rpeare]

Have you got an example database?

Why would you create a table when you could use a query to pull out the information you want, or display the information you want?

Record sources for forms/reports can be altered based on input from a form so I'm not sure where your use of tables comes in.

[June7]

Table structure has a field for names and field for position? Try:

Include expressions in query for the 5 positions and set Criteria for each as True (4 on different Or lines). Example for the driver:

IIf([Position]="Driver" And Forms!formname!chkDriver=True),True,False)

Or one long nested IIf for the 5 expressions and one criteria of True.

[stryder09]

The table or query I wish to change isn't actually linked to this form. But I want the query to sort and display only the positions needed.

I only have 4 fields Department, Employee, Position, Check Off. I don't have fields named Driver, Packer, Operator, Supervisor or Maintenance, they would be sorted under the field name position.

[June7]

My suggestion does not require the query to be linked to the form, only refer to controls on the open form. I assumed you had one field for positions and my suggestion reflects that.

What is the CheckOff field for?

[stryder09]

The CheckOff field is actually a nothing field! This table is actually being generated from an excel table that gets updated regularly by our Training coordinator in the plant so is always current and that is just one of the fields that that table creates.

How would I Nest the multiple IIF's? And am I putting this statement in the Criteria area of the query under Position?

[stryder09]

So I tried the following just to see if it works

IIF(([Position]="Driver" AND [Reports]![Process Update]![Driver]=True,True,False)

And I get the following Access error:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

[stryder09]

Any help would be appreciated

[June7]

There is an extra paren at beginning of the expression. Parens and quotes must be paired.

Nested 2 IIF:
IIF([Position]="Driver" AND [Reports]![Process Update]![Driver]=True,True, IIF([Position]="Packer" AND [Reports]![Process Update]![Packer]=True,True,False))

[stryder09]

I can't get this statement to work

IIF([Position]="Production Supervisor" AND [Forms]![PROCESS CHANGE]![Shift Supervisor]=True,True,False)

But This one works for me.

IIf(([Forms]![PROCESS CHANGE]![Shift Supervisor]=True),"Shift Supervisor")

The Problem I have now is that the Shift Supervisor check box will also refer to the reserve Supervisors.

I can get the statement to work this way

IIf(([Forms]![PROCESS CHANGE]![Shift Supervisor]=True),"Shift Supervisor") Or IIf(([Forms]![PROCESS CHANGE]![Shift Supervisor]=True),"Relief Supervisor")

BUt I want to write it this way and it won't work

IIf(([Forms]![PROCESS CHANGE]![Shift Supervisor]=True),"Shift Supervisor" Or "Relief Supervisor")

But it doesn't work?

[June7]

I am confused. My suggested expression should work and yours should not. The expression goes in the Field row. This is constructing a calculated field. The Criteria row has True. Are you referencing the names of the checkboxes? Are these checkboxes bound to a field?

See http://www.techonthenet.com/access/f...vanced/iif.php

[stryder09]

The check box are bound to a different table yes. I am putting my calculation in the Criteria field of my Query Table.

[June7]

You might follow my suggestion and see what happens.

[stryder09]

It causes an error because the equation is looking for the answer in the Position field, which is where the equation is.

Is there a way though in the criteria field to have the following equation and have it give back more than one answer.

IIF(([Forms]![Process Change]![Shift Supervisor]=True), "Production Supervisor")

I would like it to also return all the relief supervisors.

IIF(([Forms]![Process Change]![Shift Supervisor]=True),"Production Supervisor" OR "Relief Supervisor")

It doesn't work though, Is there another way to write it that it will let me bring back 2 or more positions?

[stryder09]

The error I get trying your equation is

Circular reference caused by alias 'Position' in query definition's SELECT list.