Allowing form combo box to show all records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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])
 
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


Ofer said:
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


rupertsland said:
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
Click to expand...
Click to expand...
 
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


rupertsland said:
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


Ofer said:
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


rupertsland said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top