Allowing form combo box to show all records

[rupertsland]

Greetings.

I am building a report tracking database using Access 2003.

I have a main form that's tied to an underlying query that displays a list
of Projects. On the main form I have a subform that displays a list of
published reports (the subform is tied to an underlying query that displays a
list of reports - each report record has a foreign key [PROJ_ID] that allows
me to associate a report with a project). I added a combo box using the combo
box wizard to allow me to filter out reports associated with a project and
display the filtered result in the subform.

What I would like to do is to provide an option to clear the filter and
display all records in the subform. The combo box is only able to display a
list of projects, but I am unable to find a way to provide a "Show all
records" option in the combo box.

Is there a way to do this? The VB code I'm using was generated by the
wizard, and is as follows:

-----
Private Sub Combo62_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PROJ_ID] = " & Str(Nz(Me![Combo62]))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


End Sub
-----

If the above code can not be changed, is there an alternative workaround?
Any help would welcome.

Dirk Schmid
Winnipeg, Canada

 

[Ofer]

One way that you could use is, when no project selected then display all
records

Select * From ProjectTable Where ProjectField Like
NZ(Forms![FormName]![ComboName],"*")

Or
Select * From ProjectTable Where ProjectField Like
IIf(Forms![FormName]![ComboName] is null or Forms![FormName]![ComboName] =
"","*",Forms![FormName]![ComboName])

 

[rupertsland]

Hi Ofer.

Thank you for your help. However, your suggested solution didn't work for
me. It could be that I did not implement your solution correctly. I was not
sure whether your solution was an actual SQL statement, or whether it was a
Visual Basic code framework. I tried the statements in the row source for the
combo box (I made sure I used the correct query name and field name). I'm not
proficient enough in VB, so I was a bit baffled when attempting to construct
a VB equivalent.

Any suggestions?


Dirk
Winnipeg, Canada

One way that you could use is, when no project selected then display all
records

Select * From ProjectTable Where ProjectField Like
NZ(Forms![FormName]![ComboName],"*")

Or
Select * From ProjectTable Where ProjectField Like
IIf(Forms![FormName]![ComboName] is null or Forms![FormName]![ComboName] =
"","*",Forms![FormName]![ComboName])

--
I hope that helped
Good luck

Greetings.

I am building a report tracking database using Access 2003.

I have a main form that's tied to an underlying query that displays a list
of Projects. On the main form I have a subform that displays a list of
published reports (the subform is tied to an underlying query that displays a
list of reports - each report record has a foreign key [PROJ_ID] that allows
me to associate a report with a project). I added a combo box using the combo
box wizard to allow me to filter out reports associated with a project and
display the filtered result in the subform.

What I would like to do is to provide an option to clear the filter and
display all records in the subform. The combo box is only able to display a
list of projects, but I am unable to find a way to provide a "Show all
records" option in the combo box.

Is there a way to do this? The VB code I'm using was generated by the
wizard, and is as follows:

-----
Private Sub Combo62_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PROJ_ID] = " & Str(Nz(Me![Combo62]))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


End Sub
-----

If the above code can not be changed, is there an alternative workaround?
Any help would welcome.

Dirk Schmid
Winnipeg, Canada

 

[Ofer]

The sql I provided, should be in the record source of the sub form,

On the after update event of the combo, write the code
Me.SubFormName.Requery

To refresh the selection
--
I hope that helped
Good luck

Hi Ofer.

Thank you for your help. However, your suggested solution didn't work for
me. It could be that I did not implement your solution correctly. I was not
sure whether your solution was an actual SQL statement, or whether it was a
Visual Basic code framework. I tried the statements in the row source for the
combo box (I made sure I used the correct query name and field name). I'm not
proficient enough in VB, so I was a bit baffled when attempting to construct
a VB equivalent.

Any suggestions?


Dirk
Winnipeg, Canada

One way that you could use is, when no project selected then display all
records

Select * From ProjectTable Where ProjectField Like
NZ(Forms![FormName]![ComboName],"*")

Or
Select * From ProjectTable Where ProjectField Like
IIf(Forms![FormName]![ComboName] is null or Forms![FormName]![ComboName] =
"","*",Forms![FormName]![ComboName])

--
I hope that helped
Good luck

Greetings.

I am building a report tracking database using Access 2003.

I have a main form that's tied to an underlying query that displays a list
of Projects. On the main form I have a subform that displays a list of
published reports (the subform is tied to an underlying query that displays a
list of reports - each report record has a foreign key [PROJ_ID] that allows
me to associate a report with a project). I added a combo box using the combo
box wizard to allow me to filter out reports associated with a project and
display the filtered result in the subform.

What I would like to do is to provide an option to clear the filter and
display all records in the subform. The combo box is only able to display a
list of projects, but I am unable to find a way to provide a "Show all
records" option in the combo box.

Is there a way to do this? The VB code I'm using was generated by the
wizard, and is as follows:

-----
Private Sub Combo62_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PROJ_ID] = " & Str(Nz(Me![Combo62]))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


End Sub
-----

If the above code can not be changed, is there an alternative workaround?
Any help would welcome.

Dirk Schmid
Winnipeg, Canada