Combobox controlled search

[Steve Moss]

I have a form with a combobox giving the options of a list of people
"Custodian". On update I wish for the option selected to be used to open a
report with filtered information. the report is a full list of books held by
everybody in my company. instead of writing a query and report for each
person (this an ever expanding list) I wish it to be filtered. I have used
the folloowing code in the past, buit for some reason the report opens blank
(only with headings). I am not sure what each part of the code represents,
can anyone shed light on this for me

Private Sub Combo0_AfterUpdate()
If Len(Trim(Me![Combo0]) & "") > 0 Then
DoCmd.OpenReport "Reportname", acViewPreview, , "[nameofpersonfield]='" &
Me![Combo0].Value & "'"
Else
MsgBox "Enter which person's holdings you require to view"
End If
End Sub

 

[Ken Sheridan]

Is the value of the combo box the name or a unique ID column? If the latter
(as should be the case) then no rows would be returned in the report as it
would be looking for somebody with a number as their name! Names are
unsuitable keys as they can be duplicated, even within a small environment (I
once worked with two Maggie Taylors), so a numeric key such as an autonumber
is better. If the combo box has a RowSource such as:

SELECT EmployeeID, Employee
FROM Employees
ORDER BY Employee;

and its BoundColumn property is 1, then the value of the combo box will be
the (usually hidden) EmployeeID, in which case the WhereCondition argument of
the OpenReport method should reference this column, e.g.

DoCmd.OpenReport "Reportname", View:=acViewPreview,
WhereCondition:="EmployeeID = " & Me.cboEmployees

The EmployeeID column would need to be included in the report's RecordSource
query of course, even though it would not be shown in the report.

BTW rather than accepting the default names like Combo0 given to controls,
change it to something meaningful like cboEmployees. Note, however, that if
you change the Name property after creating an event procedure for a control
the link between the code and the control will be lost, so you'd need to copy
the body code from the original procedure, delete the whole procedure, and
create a new event procedure, pasting in the original code (and amending it
if it refers to the control by name).

Ken Sheridan
Stafford, England

 

[Steve Moss]

Thanks ken I will give it a go.

Is the value of the combo box the name or a unique ID column? If the latter
(as should be the case) then no rows would be returned in the report as it
would be looking for somebody with a number as their name! Names are
unsuitable keys as they can be duplicated, even within a small environment (I
once worked with two Maggie Taylors), so a numeric key such as an autonumber
is better. If the combo box has a RowSource such as:

SELECT EmployeeID, Employee
FROM Employees
ORDER BY Employee;

and its BoundColumn property is 1, then the value of the combo box will be
the (usually hidden) EmployeeID, in which case the WhereCondition argument of
the OpenReport method should reference this column, e.g.

DoCmd.OpenReport "Reportname", View:=acViewPreview,
WhereCondition:="EmployeeID = " & Me.cboEmployees

The EmployeeID column would need to be included in the report's RecordSource
query of course, even though it would not be shown in the report.

BTW rather than accepting the default names like Combo0 given to controls,
change it to something meaningful like cboEmployees. Note, however, that if
you change the Name property after creating an event procedure for a control
the link between the code and the control will be lost, so you'd need to copy
the body code from the original procedure, delete the whole procedure, and
create a new event procedure, pasting in the original code (and amending it
if it refers to the control by name).

Ken Sheridan
Stafford, England

I have a form with a combobox giving the options of a list of people
"Custodian". On update I wish for the option selected to be used to open a
report with filtered information. the report is a full list of books held by
everybody in my company. instead of writing a query and report for each
person (this an ever expanding list) I wish it to be filtered. I have used
the folloowing code in the past, buit for some reason the report opens blank
(only with headings). I am not sure what each part of the code represents,
can anyone shed light on this for me

Private Sub Combo0_AfterUpdate()
If Len(Trim(Me![Combo0]) & "") > 0 Then
DoCmd.OpenReport "Reportname", acViewPreview, , "[nameofpersonfield]='" &
Me![Combo0].Value & "'"
Else
MsgBox "Enter which person's holdings you require to view"
End If
End Sub