Switchboard filter

[Chris75 via AccessMonster.com]

Hello,

I have created a switchboard (not using the built-in tool). Works like a
charm. But recently I have been banging my head against a wall trying to
figure out the following.

The switchboard opens 2 forms via command buttons. One for employees and the
other for reports. However, not all employees are in the same office.

What I would like to do is have an unbound combo box with a command button on
the main page that already has the controls for the 2 forms as stated above.
From this combo box, I would then filter for the 2 other forms.

Example:

Some employees work in Office 1234. I would like to select from a combo box,
"Office 1234", click on the command button and from there when I clicked on
either of the 2 other command buttons, the employees not located at "Office
1234" are filtered out.

I already have an "office" field in my employee table.

Thank you for your help.

 

[Larry Linson]

With code, almost anything is possible. What you describe does not seem all
that difficult... you would simply create SQL with a condition to show only
the employees which uses the value chosen by the user and use that SQL as
the Record Source for your forms / reports. It's user-friendly design to
have the user selected what they DO want to see, however, instead of what
they do NOT want to see, so you might consider doing that, instead.

Larry Linson
Microsoft Office Access MVP

 

[Chris75 via AccessMonster.com]

Hi Larry,

How would I go about doing that?

Thanks.

With code, almost anything is possible. What you describe does not seem all
that difficult... you would simply create SQL with a condition to show only
the employees which uses the value chosen by the user and use that SQL as
the Record Source for your forms / reports. It's user-friendly design to
have the user selected what they DO want to see, however, instead of what
they do NOT want to see, so you might consider doing that, instead.

Larry Linson
Microsoft Office Access MVP

Hello,

[quoted text clipped - 25 lines]

Thank you for your help.

 

[Chris75 via AccessMonster.com]

I have something worked out now but the problem now is that when I open the
now filtered form, the unbound combobox that I use to move between records is
showing up blank now.

This is the code I have right now for the On Enter event procedure of the
unbound combo,

dim strSQL as string
strSQL = "SELECT Employees.Employee ID, " _
& " (Employees.Name) " _
& " FROM Employees"
if len(nz(me.filter)) > 0 then
strSQL = strSQL & " WHERE " & me.filter
end if
strSQL = strSQL & ";"

me.Modifiable4.RowSource = strSQL
me.Modifiable4.Requery

if me.Modifiable4.RowSource <> strSQL then
me.Modifiable4.RowSource = strSQL
me.Modifiable4.Requery
end if

Thanks again folks!

Chris

With code, almost anything is possible. What you describe does not seem all
that difficult... you would simply create SQL with a condition to show only
the employees which uses the value chosen by the user and use that SQL as
the Record Source for your forms / reports. It's user-friendly design to
have the user selected what they DO want to see, however, instead of what
they do NOT want to see, so you might consider doing that, instead.

Larry Linson
Microsoft Office Access MVP

Hello,

[quoted text clipped - 25 lines]

Thank you for your help.