Forms

[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.

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

I created a form to filter office based on an "office" field in my employee
table.

I have something worked out but the problem now is that when I open the
now filtered form "Employees", the unbound combobox "Modifiable4", that I use
to navigate between records is showing up unpopulated (blank).

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

 

[KenSheridan via AccessMonster.com]

Chris:

Your Employee ID column name includes a space, so, unless this is just a typo
in your post, should be wrapped in square brackets:

Employees.[Employee ID]

One other problem I can see with your code is that you are relying on the non-
zero length of the form's Filter property to indicate that it is filtered.
What determines whether a form is filtered, however, is whether the form's
FilterOn property is True. The Filter property is persistent, so even when
form is not filtered it will still be a string of greater than zero length.

The final If….End If construct serves no purpose as the code has just
assigned the value of the strSQL variable to the RowSource property, and can
be removed, but it should not make any difference to the functionality.

An alternative approach would be not to amend the control's RowSource
property in code at all, but to rely on parameters to restrict the list.
There are various ways you could do this, but one would be to add a hidden
unbound check box to the form, IsFiltered say. When you filter the form set
its value to True; when you open it unfiltered or disable the filter by
setting the FilterOn property to False, set the check box's value to False.
In each case requery the combo box. The RowSource property for the combo box
would then be:

SELECT Employees.[Employee ID],
Employees.Name
FROM Employees
WHERE (Office = Form!Office
AND Form!IsFiltered)
OR NOT Form!IsFiltered
ORDER BY Employees.Name;

Note that you can use the Form property here to return a reference to the
current form rather than referencing it as a member of the Forms collection
as the controls are on the same form.

BTW avoid Name as a column name. It’s the name of a built in property in
Access so could cause confusion. Qualifying it here with the table name
should avoid that, but EmployeeName would be a better choice.

Ken Sheridan
Stafford, England

 

[Chris75 via AccessMonster.com]

Hi Ken,

I tried that code in the record source for the form office, however a popup
appeared asking for an office rather than the drop down list.

Chris:

Your Employee ID column name includes a space, so, unless this is just a typo
in your post, should be wrapped in square brackets:

Employees.[Employee ID]

One other problem I can see with your code is that you are relying on the non-
zero length of the form's Filter property to indicate that it is filtered.
What determines whether a form is filtered, however, is whether the form's
FilterOn property is True. The Filter property is persistent, so even when
form is not filtered it will still be a string of greater than zero length.

The final If….End If construct serves no purpose as the code has just
assigned the value of the strSQL variable to the RowSource property, and can
be removed, but it should not make any difference to the functionality.

An alternative approach would be not to amend the control's RowSource
property in code at all, but to rely on parameters to restrict the list.
There are various ways you could do this, but one would be to add a hidden
unbound check box to the form, IsFiltered say. When you filter the form set
its value to True; when you open it unfiltered or disable the filter by
setting the FilterOn property to False, set the check box's value to False.
In each case requery the combo box. The RowSource property for the combo box
would then be:

SELECT Employees.[Employee ID],
Employees.Name
FROM Employees
WHERE (Office = Form!Office
AND Form!IsFiltered)
OR NOT Form!IsFiltered
ORDER BY Employees.Name;

Note that you can use the Form property here to return a reference to the
current form rather than referencing it as a member of the Forms collection
as the controls are on the same form.

BTW avoid Name as a column name. It’s the name of a built in property in
Access so could cause confusion. Qualifying it here with the table name
should avoid that, but EmployeeName would be a better choice.

Ken Sheridan
Stafford, England

Hello,

[quoted text clipped - 36 lines]

Chris

 

[KenSheridan via AccessMonster.com]

Chris:

The SQL statement is not the form's RecordSource; it’s the RowSource property
of the combo box. The form's RecordSource should be the Employees table or a
query based on it.

BTW I didn't go into the specifics of how the value of the hidden IsFiltered
check box would be set. All you need to do is set its ControlSource property
to:

=Form.FilterOn

Ken Sheridan
Stafford, England

Hi Ken,

I tried that code in the record source for the form office, however a popup
appeared asking for an office rather than the drop down list.

Chris:

[quoted text clipped - 46 lines]

 

[Chris75 via AccessMonster.com]

Hi Ken,

I actually got this to partially work.

The form recordsource is :

SELECT *
FROM Employees
WHERE (((Employees.Office)=[Forms]![Office]![Officecbo]));

This filters my Employees form according to the selection made in my Office
form.

Within the Employees form, the unbound combo labelled name (also called
Modifiable4) now has the rowsource:

SELECT Employees.[EMPLOYEE ID], Employees.Name
FROM Employees
WHERE (((Employees.Office)=[Forms]![Office].[Officecbo]));

In each case, I am now limited to the selection made in the Office form.

The problem now is that only half the employee information is displayed.
Those in Office 1 have all information displayed. Those in Office 2 only
have names displayed.

A few things I noticed:

1) When I select the Employee form and then go into the Data tab via
properties, the Filter is permanently Office = 1 regardless of the selection
made in the Office form.

2) If I go into the builder to look at the SQL of the Employee form
recordsource, and then close it(with or without saving it). The Filter is
blank instead of Office = 1. From here, the Employee form works as it should.


Browsing around this site, I have learnt that deleting the filter is not a
viable option, I've tried it and it doesn't work. I have looked into
requerying the recordsource, but I'm not sure if that will work because the
issue arises after I have gone into it, exited and then the Filter (Office=1)
disappears.

Thanks.

Chris:

The SQL statement is not the form's RecordSource; it’s the RowSource property
of the combo box. The form's RecordSource should be the Employees table or a
query based on it.

BTW I didn't go into the specifics of how the value of the hidden IsFiltered
check box would be set. All you need to do is set its ControlSource property
to:

=Form.FilterOn

Ken Sheridan
Stafford, England

Hi Ken,

[quoted text clipped - 6 lines]

 

[KenSheridan via AccessMonster.com]

Chris:

First lets dispose of your last two points.

1. The Filter property of a form is persistent, i.e. when you filter the
form whatever expression it is filtered on becomes the Filter property and
stays as such until a new filter is set. This doesn't matter from the point
of view of the records shown in the form as the Filter property has no effect
until the form's FilterOn property is set to True. However you filter a form,
whether its by means of the WhereCondition argument of the OpenForm method
when opening it, or by setting the filter via the toolbar when its open this
sets both the Filter and FilterOn properties. When you unfilter a form only
the FilterOn property is set, in this case to False. In any event, as you
are now restricting the rows returned by means of a parameter in the form's
RecordSource the Filter property is immaterial.

2. The RecordSource property on the other hand is static unless you
specifically change it. In your case it’s a query which references [Forms]!
[Office]![Officecbo] as a parameter. This restricts the results of the query
to those rows where the Office value matches the selection in the combo box
on the Office form. Even if you change the RecordSource dynamically in code,
unless you specifically save the form definition in code also, the
RecordSource property in the form definition won't change.

The only reason I can think of for your only getting name data for the office
2 employees would be that something is amending the Employee form's
RecordSource when you select office 2 so it only returns the name columns
rather than all columns from the Employees. This sounds unlikely, however,
but I can't offer any other explanation at this distance I'm afraid. I am
assuming that the office 2 employees do have values in columns other than the
name ones and the Office column.

What code are you using behind the Modifiable4 combo box, and in what event
procedure, to navigate to an employee record? I can't see that this would
have any effect of the type you describe, but nevertheless its worth
examining.

Ken Sheridan
Stafford, England

Hi Ken,

I actually got this to partially work.

The form recordsource is :

SELECT *
FROM Employees
WHERE (((Employees.Office)=[Forms]![Office]![Officecbo]));

This filters my Employees form according to the selection made in my Office
form.

Within the Employees form, the unbound combo labelled name (also called
Modifiable4) now has the rowsource:

SELECT Employees.[EMPLOYEE ID], Employees.Name
FROM Employees
WHERE (((Employees.Office)=[Forms]![Office].[Officecbo]));

In each case, I am now limited to the selection made in the Office form.

The problem now is that only half the employee information is displayed.
Those in Office 1 have all information displayed. Those in Office 2 only
have names displayed.

A few things I noticed:

1) When I select the Employee form and then go into the Data tab via
properties, the Filter is permanently Office = 1 regardless of the selection
made in the Office form.

2) If I go into the builder to look at the SQL of the Employee form
recordsource, and then close it(with or without saving it). The Filter is
blank instead of Office = 1. From here, the Employee form works as it should.

Browsing around this site, I have learnt that deleting the filter is not a
viable option, I've tried it and it doesn't work. I have looked into
requerying the recordsource, but I'm not sure if that will work because the
issue arises after I have gone into it, exited and then the Filter (Office=1)
disappears.

Thanks.

Chris:

[quoted text clipped - 16 lines]