Adding "all" to combo box isn't working

N

NukeEng85

so I have two combo boxes in a form and I need to be able to look up data
with either one or both, meaning i need one to have an "all" option (or just
be null). I tried putting this in the rowsource:

SELECT DISTINCT [SNM Data].[SNM Type] FROM [SNM Data] UNION Select Null as
AllChoice, "(All)" as Bogus From [SNM Data]
ORDER BY [SNM Data].[SNM Type];

and it gave me an error message that says: "The number of columns in the two
selected tables or queries of a union query do not match."

I also tried changing the criteria for the field that is being filtered in
the query to this:

Forms!NameOfForm!NameOfCombo Or (Forms!NameOfForm!NameOfCombo = "any")

Another problem I'm having is that I have a command button that is supposed
to use the values selected in the combo box to filter data from the table
into a subform (basically filtering and locating records) I am using the
below code, and for some reason if I exit the form and pull it back up the
button stops working unless I first right click the form, select "Filter by
form" and then "apply filter/sort" I want other people at my work to just be
able to pull this up and find what they need via the combo boxes, so I really
need it to work automatically.

Option Compare Database
Option Explicit



Private Sub Command44_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
 
K

KARL DEWEY

Your first query only selects [SNM Data].[SNM Type]
whereas the second selects Null as AllChoice AND "(All)" as Bogus - that
is two fields.
Try this ---
Forms!NameOfForm!NameOfCombo Or (Forms!NameOfForm!NameOfCombo Is Null)

--
KARL DEWEY
Build a little - Test a little


NukeEng85 said:
so I have two combo boxes in a form and I need to be able to look up data
with either one or both, meaning i need one to have an "all" option (or just
be null). I tried putting this in the rowsource:

SELECT DISTINCT [SNM Data].[SNM Type] FROM [SNM Data] UNION Select Null as
AllChoice, "(All)" as Bogus From [SNM Data]
ORDER BY [SNM Data].[SNM Type];

and it gave me an error message that says: "The number of columns in the two
selected tables or queries of a union query do not match."

I also tried changing the criteria for the field that is being filtered in
the query to this:

Forms!NameOfForm!NameOfCombo Or (Forms!NameOfForm!NameOfCombo = "any")

Another problem I'm having is that I have a command button that is supposed
to use the values selected in the combo box to filter data from the table
into a subform (basically filtering and locating records) I am using the
below code, and for some reason if I exit the form and pull it back up the
button stops working unless I first right click the form, select "Filter by
form" and then "apply filter/sort" I want other people at my work to just be
able to pull this up and find what they need via the combo boxes, so I really
need it to work automatically.

Option Compare Database
Option Explicit



Private Sub Command44_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...
 
N

NukeEng85

so for it to do what I want it too, which should I keep? the Null as
AllChoice, or the "(All)" as Bogus? Or do I need to change it to something
else?

KARL DEWEY said:
Your first query only selects [SNM Data].[SNM Type]
whereas the second selects Null as AllChoice AND "(All)" as Bogus - that
is two fields.
Try this ---
Forms!NameOfForm!NameOfCombo Or (Forms!NameOfForm!NameOfCombo Is Null)

--
KARL DEWEY
Build a little - Test a little


NukeEng85 said:
so I have two combo boxes in a form and I need to be able to look up data
with either one or both, meaning i need one to have an "all" option (or just
be null). I tried putting this in the rowsource:

SELECT DISTINCT [SNM Data].[SNM Type] FROM [SNM Data] UNION Select Null as
AllChoice, "(All)" as Bogus From [SNM Data]
ORDER BY [SNM Data].[SNM Type];

and it gave me an error message that says: "The number of columns in the two
selected tables or queries of a union query do not match."

I also tried changing the criteria for the field that is being filtered in
the query to this:

Forms!NameOfForm!NameOfCombo Or (Forms!NameOfForm!NameOfCombo = "any")

Another problem I'm having is that I have a command button that is supposed
to use the values selected in the combo box to filter data from the table
into a subform (basically filtering and locating records) I am using the
below code, and for some reason if I exit the form and pull it back up the
button stops working unless I first right click the form, select "Filter by
form" and then "apply filter/sort" I want other people at my work to just be
able to pull this up and find what they need via the combo boxes, so I really
need it to work automatically.

Option Compare Database
Option Explicit



Private Sub Command44_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...
Click to expand...
 
K

KARL DEWEY

Read the second part of my post.
--
KARL DEWEY
Build a little - Test a little


NukeEng85 said:
so for it to do what I want it too, which should I keep? the Null as
AllChoice, or the "(All)" as Bogus? Or do I need to change it to something
else?

KARL DEWEY said:
Your first query only selects [SNM Data].[SNM Type]
whereas the second selects Null as AllChoice AND "(All)" as Bogus - that
is two fields.
I also tried changing the criteria for the field that is being filtered in the query
Click to expand...
Try this ---
Forms!NameOfForm!NameOfCombo Or (Forms!NameOfForm!NameOfCombo Is Null)

--
KARL DEWEY
Build a little - Test a little


NukeEng85 said:
so I have two combo boxes in a form and I need to be able to look up data
with either one or both, meaning i need one to have an "all" option (or just
be null). I tried putting this in the rowsource:

SELECT DISTINCT [SNM Data].[SNM Type] FROM [SNM Data] UNION Select Null as
AllChoice, "(All)" as Bogus From [SNM Data]
ORDER BY [SNM Data].[SNM Type];

and it gave me an error message that says: "The number of columns in the two
selected tables or queries of a union query do not match."

I also tried changing the criteria for the field that is being filtered in
the query to this:

Forms!NameOfForm!NameOfCombo Or (Forms!NameOfForm!NameOfCombo = "any")

Another problem I'm having is that I have a command button that is supposed
to use the values selected in the combo box to filter data from the table
into a subform (basically filtering and locating records) I am using the
below code, and for some reason if I exit the form and pull it back up the
button stops working unless I first right click the form, select "Filter by
form" and then "apply filter/sort" I want other people at my work to just be
able to pull this up and find what they need via the combo boxes, so I really
need it to work automatically.

Option Compare Database
Option Explicit



Private Sub Command44_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

select all 14
Search Form with a SubForm 3
Allen Brownes ECount() 5
Create Report On the Fly 1
Filtering using two controls 2
Filtering a multiple item form 1
Error on modified Allenbrowne SearchForm 6
Allen Browne's Search Form - Debugging help needed 2

Top