Filtering Form

[Guest]

How do you limit the records shown on a form to a specific subset? For
example, orders placed by a certain employee? If you look at the Northwind
sample database, I have several forms similar in design to their Orders form.
I would like to limit the records you can navigate through (using the
navigation buttons at the bottom of the form) using a combobox.

Now, I know you can base the form on a query, and use an unbound combobox
control to provide the parameter for said query, but I'm not sure on the
query design. Using the Northwind database as a sample, how would you make
the Salesperson control be unbound and used to limit the records shown to
that salesperson only?

Thanks

Dave

 

[Allen Browne]

Add another combo to the form, typically in the Form Header section so it is
distinct from the bound controls in the Detail section.

Use the AfterUpate event procedure of this unbound combo to set the Filter
of the form:

Private Sub cboFilterEmployee_AfterUpdate()
Dim strWhere As String

If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If

With Me.cboFilterEmployee
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "EmployeeID = " & .Value
Me.Filter = strWhere
Me.FilterOn = True
End If
End With
End Sub

 

[Guest]

Thanks for your help.

May I ask what this line does:

With Me.cboFilterEmployee

Thanks

Dave

 

[Guest]

Don't worry, figured it out now.

How would you stop the records from being editable whilst still allowing the
unbound combo box to be changed? Do I have to allow edits on the form, and
disallow edits on a control by control basis?

Thanks

Dave

 

[Allen Browne]

As you discovered, you cannot even use the unbound control if you set the
form's AllowEdits property to No, so, yes, you do have to set the Locked
property of the bound controls if you do not want the user to be able to
edit them.

If you want to be able to toggle the bound controls to allow editing, you
could copy the code in this article:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html

 

[Guest]

Thanks, that article was very helpful.

As you discovered, you cannot even use the unbound control if you set the
form's AllowEdits property to No, so, yes, you do have to set the Locked
property of the bound controls if you do not want the user to be able to
edit them.

If you want to be able to toggle the bound controls to allow editing, you
could copy the code in this article:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html

 

[Guest]

One further question. How do you get the controls to be displayed when there
are no records to display? Or at least a popup to notify the user that no
records were found? I can't help thinking it looks like the program has
crashed when the controls disappear.

Thanks

Dave

 

[Allen Browne]

The Detail section of the form goes completely blank when:
a) there are no records to display, and
b) no new records can be added.

If new records cannot be added simply because you set the form's
AllowAdditions property to No, change the property to Yes, and cancel the
form's BeforeInsert event instead. (This allows it to show the new record
row when there is nothing else to display,but it does not accept entries.)

If the form is based on a query that is read-only, you might be able to
modify the source query so it is not read-only. If that is not practical,
you will have to cope with the fact that the form does not work correctly,
and even the controls in the Form Header and Form Footer section can be
misunderstood when no records are present, as described here:
http://allenbrowne.com/bug-06.html

If you wish to display a message when no records match:
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Nobody home."
End If

 

[Guest]

Thanks, all sorted

The Detail section of the form goes completely blank when:
a) there are no records to display, and
b) no new records can be added.

If new records cannot be added simply because you set the form's
AllowAdditions property to No, change the property to Yes, and cancel the
form's BeforeInsert event instead. (This allows it to show the new record
row when there is nothing else to display,but it does not accept entries.)

If the form is based on a query that is read-only, you might be able to
modify the source query so it is not read-only. If that is not practical,
you will have to cope with the fact that the form does not work correctly,
and even the controls in the Form Header and Form Footer section can be
misunderstood when no records are present, as described here:
http://allenbrowne.com/bug-06.html

If you wish to display a message when no records match:
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Nobody home."
End If