If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
"All" as a combo box option
I have the exact same problem as Jon has, I go to select "ALL" and nothing
comes up. I'm not using a value list, my combo box pulls it's data from a field in a table. I have SELECT DISTINCT [SNM Data].[ICA] FROM [SNM Data] UNION SELECT "ALL" as Null From [SNM Data] ORDER BY [SNM Data].[ICA]; in my row source. any thoughts? "Jon M." wrote: I got the error to go away there was a labeling issue I had to address. Now my code is exactly as it should be, so it seems. However when I select (All) it still does not display all the records for that area, in fact it doesn't show me any records. I know I'm missing something but I don't know what it is. Any suggestions? Thanks in advance. -- Jon M. "Klatuu" wrote: On which line is the error occuring? -- Dave Hargis, Microsoft Access MVP "Jon M." wrote: I tried to use this code but I am getting an error message when I open the form. The message is Compile Error: Method or data member not found. My code is correct I think it is: Private Sub Form_Open(Cancel As Integer) With Me.OfficeLoc .RowSourceType = "Value List" .RowSource = "(All);" & .RowSource End With End Sub OfficeLoc is the name of my control box. Any suggestions what I'm doing wrong? -- Jon M. "Maurice" wrote: Take a look here maybe this will help you on your way... http://www.mvps.org/access/forms/frm0043.htm hth -- Maurice Ausum "Jon M." wrote: I have a form with 3 combo boxes. One for Building, Dept., and Floor#. They retreive employee records and display them on a subform. I would like to have an "All" option in each of the combo boxes that will display all the records for that field. For example I could then choose Building A, Banking Dept, and "all" in the last combo box and see all employees in Building A in the banking dept, on every floor. Or, "All", Banking, 3rd Floor and I would see every employee in the banking dept on the 3rd floor in each of the buildings. Does that make sense? As always any help is greatly appreciated! -- Jon M. |
#12
|
|||
|
|||
"All" as a combo box option
You already have this link
http://www.mvps.org/access/forms/frm0043.htm In your case there is just one column in the Row Source, so the Select Null as AllChoice line from the line does not need to be in your SQL, if I understand the situation correctly. The problem here seems to be that you are selecting something as Null. It may go something like this in your case: SELECT [ICA] FROM [SNM Data] UNION SELECT "(ALL)" as SelectAll From [SNM Data] ORDER BY [ICA]; The Parentheses around All should assure that it goes to the top of the list (special characters are sorted in front of letters and numbers). This should set the Row Source using a Union Query. The example using the Open code assumes the existence of a Value List rather than a query as the Row Source. You need a value list in the combo box properties for that system to work. You could create a value list in code, I suppose, but that isn't covered in the code example, and I don't see that it would help. The next consideration is what happens after the selection is made. What is the After Update code for the combo box? "NukeEng85" wrote in message ... I have the exact same problem as Jon has, I go to select "ALL" and nothing comes up. I'm not using a value list, my combo box pulls it's data from a field in a table. I have SELECT DISTINCT [SNM Data].[ICA] FROM [SNM Data] UNION SELECT "ALL" as Null From [SNM Data] ORDER BY [SNM Data].[ICA]; in my row source. any thoughts? "Jon M." wrote: I got the error to go away there was a labeling issue I had to address. Now my code is exactly as it should be, so it seems. However when I select (All) it still does not display all the records for that area, in fact it doesn't show me any records. I know I'm missing something but I don't know what it is. Any suggestions? Thanks in advance. -- Jon M. "Klatuu" wrote: On which line is the error occuring? -- Dave Hargis, Microsoft Access MVP "Jon M." wrote: I tried to use this code but I am getting an error message when I open the form. The message is Compile Error: Method or data member not found. My code is correct I think it is: Private Sub Form_Open(Cancel As Integer) With Me.OfficeLoc .RowSourceType = "Value List" .RowSource = "(All);" & .RowSource End With End Sub OfficeLoc is the name of my control box. Any suggestions what I'm doing wrong? -- Jon M. "Maurice" wrote: Take a look here maybe this will help you on your way... http://www.mvps.org/access/forms/frm0043.htm hth -- Maurice Ausum "Jon M." wrote: I have a form with 3 combo boxes. One for Building, Dept., and Floor#. They retreive employee records and display them on a subform. I would like to have an "All" option in each of the combo boxes that will display all the records for that field. For example I could then choose Building A, Banking Dept, and "all" in the last combo box and see all employees in Building A in the banking dept, on every floor. Or, "All", Banking, 3rd Floor and I would see every employee in the banking dept on the 3rd floor in each of the buildings. Does that make sense? As always any help is greatly appreciated! -- Jon M. |
#13
|
|||
|
|||
"All" as a combo box option
You can't just add a row ALL to your combo box and expect queries based on
that combo box to know what ALL means. Assuming you've currently got a criteria of Forms![NameOfForm]!NameOfCombo] in your query, change that to: Forms![NameOfForm]!NameOfCombo] OR (Forms![NameOfForm]!NameOfCombo] = "ALL") That assumes, of course, that the bound column of the combo box returns ALL. If you have it returning something else, such a number or a Null, you'll need something like: Forms![NameOfForm]!NameOfCombo] OR (Forms![NameOfForm]!NameOfCombo] = 0 or Forms![NameOfForm]!NameOfCombo] OR (Forms![NameOfForm]!NameOfCombo] IS NULL) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "NukeEng85" wrote in message ... I have the exact same problem as Jon has, I go to select "ALL" and nothing comes up. I'm not using a value list, my combo box pulls it's data from a field in a table. I have SELECT DISTINCT [SNM Data].[ICA] FROM [SNM Data] UNION SELECT "ALL" as Null From [SNM Data] ORDER BY [SNM Data].[ICA]; in my row source. any thoughts? "Jon M." wrote: I got the error to go away there was a labeling issue I had to address. Now my code is exactly as it should be, so it seems. However when I select (All) it still does not display all the records for that area, in fact it doesn't show me any records. I know I'm missing something but I don't know what it is. Any suggestions? Thanks in advance. -- Jon M. "Klatuu" wrote: On which line is the error occuring? -- Dave Hargis, Microsoft Access MVP "Jon M." wrote: I tried to use this code but I am getting an error message when I open the form. The message is Compile Error: Method or data member not found. My code is correct I think it is: Private Sub Form_Open(Cancel As Integer) With Me.OfficeLoc .RowSourceType = "Value List" .RowSource = "(All);" & .RowSource End With End Sub OfficeLoc is the name of my control box. Any suggestions what I'm doing wrong? -- Jon M. "Maurice" wrote: Take a look here maybe this will help you on your way... http://www.mvps.org/access/forms/frm0043.htm hth -- Maurice Ausum "Jon M." wrote: I have a form with 3 combo boxes. One for Building, Dept., and Floor#. They retreive employee records and display them on a subform. I would like to have an "All" option in each of the combo boxes that will display all the records for that field. For example I could then choose Building A, Banking Dept, and "all" in the last combo box and see all employees in Building A in the banking dept, on every floor. Or, "All", Banking, 3rd Floor and I would see every employee in the banking dept on the 3rd floor in each of the buildings. Does that make sense? As always any help is greatly appreciated! -- Jon M. |
#14
|
|||
|
|||
"All" as a combo box option
I did what you said and still no results, the after update code is as below:
Private Sub Combo24_AfterUpdate() End Sub 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 Private Sub Command43_Click() 'Purpose: Clear all the search boxes in the Form Header, and show all records again. Dim ctl As Control 'Clear all the controls in the Form Header section. For Each ctl In Me.Section(acDetail).Controls Select Case ctl.ControlType Case acComboBox ctl.Value = Null End Select Next 'Remove the form's filter. Me.FilterOn = False End Sub "BruceM" wrote: You already have this link http://www.mvps.org/access/forms/frm0043.htm In your case there is just one column in the Row Source, so the Select Null as AllChoice line from the line does not need to be in your SQL, if I understand the situation correctly. The problem here seems to be that you are selecting something as Null. It may go something like this in your case: SELECT [ICA] FROM [SNM Data] UNION SELECT "(ALL)" as SelectAll From [SNM Data] ORDER BY [ICA]; The Parentheses around All should assure that it goes to the top of the list (special characters are sorted in front of letters and numbers). This should set the Row Source using a Union Query. The example using the Open code assumes the existence of a Value List rather than a query as the Row Source. You need a value list in the combo box properties for that system to work. You could create a value list in code, I suppose, but that isn't covered in the code example, and I don't see that it would help. The next consideration is what happens after the selection is made. What is the After Update code for the combo box? "NukeEng85" wrote in message ... I have the exact same problem as Jon has, I go to select "ALL" and nothing comes up. I'm not using a value list, my combo box pulls it's data from a field in a table. I have SELECT DISTINCT [SNM Data].[ICA] FROM [SNM Data] UNION SELECT "ALL" as Null From [SNM Data] ORDER BY [SNM Data].[ICA]; in my row source. any thoughts? "Jon M." wrote: I got the error to go away there was a labeling issue I had to address. Now my code is exactly as it should be, so it seems. However when I select (All) it still does not display all the records for that area, in fact it doesn't show me any records. I know I'm missing something but I don't know what it is. Any suggestions? Thanks in advance. -- Jon M. "Klatuu" wrote: On which line is the error occuring? -- Dave Hargis, Microsoft Access MVP "Jon M." wrote: I tried to use this code but I am getting an error message when I open the form. The message is Compile Error: Method or data member not found. My code is correct I think it is: Private Sub Form_Open(Cancel As Integer) With Me.OfficeLoc .RowSourceType = "Value List" .RowSource = "(All);" & .RowSource End With End Sub OfficeLoc is the name of my control box. Any suggestions what I'm doing wrong? -- Jon M. "Maurice" wrote: Take a look here maybe this will help you on your way... http://www.mvps.org/access/forms/frm0043.htm hth -- Maurice Ausum "Jon M." wrote: I have a form with 3 combo boxes. One for Building, Dept., and Floor#. They retreive employee records and display them on a subform. I would like to have an "All" option in each of the combo boxes that will display all the records for that field. For example I could then choose Building A, Banking Dept, and "all" in the last combo box and see all employees in Building A in the banking dept, on every floor. Or, "All", Banking, 3rd Floor and I would see every employee in the banking dept on the 3rd floor in each of the buildings. Does that make sense? As always any help is greatly appreciated! -- Jon M. |
#15
|
|||
|
|||
"All" as a combo box option
Douglas, I did what you said, and still no results, thank you for trying!
"Douglas J. Steele" wrote: You can't just add a row ALL to your combo box and expect queries based on that combo box to know what ALL means. Assuming you've currently got a criteria of Forms![NameOfForm]!NameOfCombo] in your query, change that to: Forms![NameOfForm]!NameOfCombo] OR (Forms![NameOfForm]!NameOfCombo] = "ALL") That assumes, of course, that the bound column of the combo box returns ALL. If you have it returning something else, such a number or a Null, you'll need something like: Forms![NameOfForm]!NameOfCombo] OR (Forms![NameOfForm]!NameOfCombo] = 0 or Forms![NameOfForm]!NameOfCombo] OR (Forms![NameOfForm]!NameOfCombo] IS NULL) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "NukeEng85" wrote in message ... I have the exact same problem as Jon has, I go to select "ALL" and nothing comes up. I'm not using a value list, my combo box pulls it's data from a field in a table. I have SELECT DISTINCT [SNM Data].[ICA] FROM [SNM Data] UNION SELECT "ALL" as Null From [SNM Data] ORDER BY [SNM Data].[ICA]; in my row source. any thoughts? "Jon M." wrote: I got the error to go away there was a labeling issue I had to address. Now my code is exactly as it should be, so it seems. However when I select (All) it still does not display all the records for that area, in fact it doesn't show me any records. I know I'm missing something but I don't know what it is. Any suggestions? Thanks in advance. -- Jon M. "Klatuu" wrote: On which line is the error occuring? -- Dave Hargis, Microsoft Access MVP "Jon M." wrote: I tried to use this code but I am getting an error message when I open the form. The message is Compile Error: Method or data member not found. My code is correct I think it is: Private Sub Form_Open(Cancel As Integer) With Me.OfficeLoc .RowSourceType = "Value List" .RowSource = "(All);" & .RowSource End With End Sub OfficeLoc is the name of my control box. Any suggestions what I'm doing wrong? -- Jon M. "Maurice" wrote: Take a look here maybe this will help you on your way... http://www.mvps.org/access/forms/frm0043.htm hth -- Maurice Ausum "Jon M." wrote: I have a form with 3 combo boxes. One for Building, Dept., and Floor#. They retreive employee records and display them on a subform. I would like to have an "All" option in each of the combo boxes that will display all the records for that field. For example I could then choose Building A, Banking Dept, and "all" in the last combo box and see all employees in Building A in the banking dept, on every floor. Or, "All", Banking, 3rd Floor and I would see every employee in the banking dept on the 3rd floor in each of the buildings. Does that make sense? As always any help is greatly appreciated! -- Jon M. |
#16
|
|||
|
|||
"All" as a combo box option
I did it this way, and it works! I would still prefer the user to be able to
select "all" instead of just leaving it blank, but I'll take what I can get "Ron2006" wrote: What I have done in some situations when the data is relatively limited, is the following In the qurey that is actually retrieving the records, change the criteria to be like "*" & form![Formname]![comboxname] & "*" instead of the normal form![Formname]![comboxname] That way when there is NO value in the combobox it get all records. Whereas using "ALL" requires logic to change the query when the ALL condition occurs. I don't believe that this would work if the bound informatioin is the ID instead of the data in a combobox situation. Where I use this mostly is when I have no positive control over the data that is stored in the field that I am allowing the search on. Ron |
#17
|
|||
|
|||
"All" as a combo box option
Define "No results." Do you don't get (All) to show up in the combo box?
Nothing happens when you select from the combo box? You have posted no After Update code for Combo24. I don't know what there is for Combo 22. However, if you select (All) from Combo 24, then run the code, strWhere is looking for a record in which ICA is (All). The Debug.Print line should show you the SQL string. You can view it after the code has run by Pressing Ctrl + G and looking in the Immediate window. If you use a two-column row source you can set the first column to Null with an adaptation of the two-column code shown in the link. If you use a one-column Row Source you will need to test for (All), maybe something like: If Nz(Me.Combo24,"(All)") "(All)" Then strWhere = strWhere & "[ICA] = """ & Me.Combo24 & """ AND " End If Again, check the string as you go to be sure it is what it should be. "NukeEng85" wrote in message ... I did what you said and still no results, the after update code is as below: Private Sub Combo24_AfterUpdate() End Sub 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 Private Sub Command43_Click() 'Purpose: Clear all the search boxes in the Form Header, and show all records again. Dim ctl As Control 'Clear all the controls in the Form Header section. For Each ctl In Me.Section(acDetail).Controls Select Case ctl.ControlType Case acComboBox ctl.Value = Null End Select Next 'Remove the form's filter. Me.FilterOn = False End Sub "BruceM" wrote: You already have this link http://www.mvps.org/access/forms/frm0043.htm In your case there is just one column in the Row Source, so the Select Null as AllChoice line from the line does not need to be in your SQL, if I understand the situation correctly. The problem here seems to be that you are selecting something as Null. It may go something like this in your case: SELECT [ICA] FROM [SNM Data] UNION SELECT "(ALL)" as SelectAll From [SNM Data] ORDER BY [ICA]; The Parentheses around All should assure that it goes to the top of the list (special characters are sorted in front of letters and numbers). This should set the Row Source using a Union Query. The example using the Open code assumes the existence of a Value List rather than a query as the Row Source. You need a value list in the combo box properties for that system to work. You could create a value list in code, I suppose, but that isn't covered in the code example, and I don't see that it would help. The next consideration is what happens after the selection is made. What is the After Update code for the combo box? "NukeEng85" wrote in message ... I have the exact same problem as Jon has, I go to select "ALL" and nothing comes up. I'm not using a value list, my combo box pulls it's data from a field in a table. I have SELECT DISTINCT [SNM Data].[ICA] FROM [SNM Data] UNION SELECT "ALL" as Null From [SNM Data] ORDER BY [SNM Data].[ICA]; in my row source. any thoughts? "Jon M." wrote: I got the error to go away there was a labeling issue I had to address. Now my code is exactly as it should be, so it seems. However when I select (All) it still does not display all the records for that area, in fact it doesn't show me any records. I know I'm missing something but I don't know what it is. Any suggestions? Thanks in advance. -- Jon M. "Klatuu" wrote: On which line is the error occuring? -- Dave Hargis, Microsoft Access MVP "Jon M." wrote: I tried to use this code but I am getting an error message when I open the form. The message is Compile Error: Method or data member not found. My code is correct I think it is: Private Sub Form_Open(Cancel As Integer) With Me.OfficeLoc .RowSourceType = "Value List" .RowSource = "(All);" & .RowSource End With End Sub OfficeLoc is the name of my control box. Any suggestions what I'm doing wrong? -- Jon M. "Maurice" wrote: Take a look here maybe this will help you on your way... http://www.mvps.org/access/forms/frm0043.htm hth -- Maurice Ausum "Jon M." wrote: I have a form with 3 combo boxes. One for Building, Dept., and Floor#. They retreive employee records and display them on a subform. I would like to have an "All" option in each of the combo boxes that will display all the records for that field. For example I could then choose Building A, Banking Dept, and "all" in the last combo box and see all employees in Building A in the banking dept, on every floor. Or, "All", Banking, 3rd Floor and I would see every employee in the banking dept on the 3rd floor in each of the buildings. Does that make sense? As always any help is greatly appreciated! -- Jon M. |
#18
|
|||
|
|||
"All" as a combo box option
sorry, nothing happens when I select from the combo box, (ALL) does show up
"BruceM" wrote: Define "No results." Do you don't get (All) to show up in the combo box? Nothing happens when you select from the combo box? You have posted no After Update code for Combo24. I don't know what there is for Combo 22. However, if you select (All) from Combo 24, then run the code, strWhere is looking for a record in which ICA is (All). The Debug.Print line should show you the SQL string. You can view it after the code has run by Pressing Ctrl + G and looking in the Immediate window. If you use a two-column row source you can set the first column to Null with an adaptation of the two-column code shown in the link. If you use a one-column Row Source you will need to test for (All), maybe something like: If Nz(Me.Combo24,"(All)") "(All)" Then strWhere = strWhere & "[ICA] = """ & Me.Combo24 & """ AND " End If Again, check the string as you go to be sure it is what it should be. "NukeEng85" wrote in message ... I did what you said and still no results, the after update code is as below: Private Sub Combo24_AfterUpdate() End Sub 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 Private Sub Command43_Click() 'Purpose: Clear all the search boxes in the Form Header, and show all records again. Dim ctl As Control 'Clear all the controls in the Form Header section. For Each ctl In Me.Section(acDetail).Controls Select Case ctl.ControlType Case acComboBox ctl.Value = Null End Select Next 'Remove the form's filter. Me.FilterOn = False End Sub "BruceM" wrote: You already have this link http://www.mvps.org/access/forms/frm0043.htm In your case there is just one column in the Row Source, so the Select Null as AllChoice line from the line does not need to be in your SQL, if I understand the situation correctly. The problem here seems to be that you are selecting something as Null. It may go something like this in your case: SELECT [ICA] FROM [SNM Data] UNION SELECT "(ALL)" as SelectAll From [SNM Data] ORDER BY [ICA]; The Parentheses around All should assure that it goes to the top of the list (special characters are sorted in front of letters and numbers). This should set the Row Source using a Union Query. The example using the Open code assumes the existence of a Value List rather than a query as the Row Source. You need a value list in the combo box properties for that system to work. You could create a value list in code, I suppose, but that isn't covered in the code example, and I don't see that it would help. The next consideration is what happens after the selection is made. What is the After Update code for the combo box? "NukeEng85" wrote in message ... I have the exact same problem as Jon has, I go to select "ALL" and nothing comes up. I'm not using a value list, my combo box pulls it's data from a field in a table. I have SELECT DISTINCT [SNM Data].[ICA] FROM [SNM Data] UNION SELECT "ALL" as Null From [SNM Data] ORDER BY [SNM Data].[ICA]; in my row source. any thoughts? "Jon M." wrote: I got the error to go away there was a labeling issue I had to address. Now my code is exactly as it should be, so it seems. However when I select (All) it still does not display all the records for that area, in fact it doesn't show me any records. I know I'm missing something but I don't know what it is. Any suggestions? Thanks in advance. -- Jon M. "Klatuu" wrote: On which line is the error occuring? -- Dave Hargis, Microsoft Access MVP "Jon M." wrote: I tried to use this code but I am getting an error message when I open the form. The message is Compile Error: Method or data member not found. My code is correct I think it is: Private Sub Form_Open(Cancel As Integer) With Me.OfficeLoc .RowSourceType = "Value List" .RowSource = "(All);" & .RowSource End With End Sub OfficeLoc is the name of my control box. Any suggestions what I'm doing wrong? -- Jon M. "Maurice" wrote: Take a look here maybe this will help you on your way... http://www.mvps.org/access/forms/frm0043.htm hth -- Maurice Ausum "Jon M." wrote: I have a form with 3 combo boxes. One for Building, Dept., and Floor#. They retreive employee records and display them on a subform. I would like to have an "All" option in each of the combo boxes that will display all the records for that field. For example I could then choose Building A, Banking Dept, and "all" in the last combo box and see all employees in Building A in the banking dept, on every floor. Or, "All", Banking, 3rd Floor and I would see every employee in the banking dept on the 3rd floor in each of the buildings. Does that make sense? As always any help is greatly appreciated! -- Jon M. |
#19
|
|||
|
|||
"All" as a combo box option
"Nothing shows up" because the code does not allow it to show up. The code
as written is using the value (All) in the filter string. Your brief answer does not explain whether or not you tried anything different from what has already proven not to work. "NukeEng85" wrote in message ... sorry, nothing happens when I select from the combo box, (ALL) does show up "BruceM" wrote: Define "No results." Do you don't get (All) to show up in the combo box? Nothing happens when you select from the combo box? You have posted no After Update code for Combo24. I don't know what there is for Combo 22. However, if you select (All) from Combo 24, then run the code, strWhere is looking for a record in which ICA is (All). The Debug.Print line should show you the SQL string. You can view it after the code has run by Pressing Ctrl + G and looking in the Immediate window. If you use a two-column row source you can set the first column to Null with an adaptation of the two-column code shown in the link. If you use a one-column Row Source you will need to test for (All), maybe something like: If Nz(Me.Combo24,"(All)") "(All)" Then strWhere = strWhere & "[ICA] = """ & Me.Combo24 & """ AND " End If Again, check the string as you go to be sure it is what it should be. "NukeEng85" wrote in message ... I did what you said and still no results, the after update code is as below: Private Sub Combo24_AfterUpdate() End Sub 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 Private Sub Command43_Click() 'Purpose: Clear all the search boxes in the Form Header, and show all records again. Dim ctl As Control 'Clear all the controls in the Form Header section. For Each ctl In Me.Section(acDetail).Controls Select Case ctl.ControlType Case acComboBox ctl.Value = Null End Select Next 'Remove the form's filter. Me.FilterOn = False End Sub "BruceM" wrote: You already have this link http://www.mvps.org/access/forms/frm0043.htm In your case there is just one column in the Row Source, so the Select Null as AllChoice line from the line does not need to be in your SQL, if I understand the situation correctly. The problem here seems to be that you are selecting something as Null. It may go something like this in your case: SELECT [ICA] FROM [SNM Data] UNION SELECT "(ALL)" as SelectAll From [SNM Data] ORDER BY [ICA]; The Parentheses around All should assure that it goes to the top of the list (special characters are sorted in front of letters and numbers). This should set the Row Source using a Union Query. The example using the Open code assumes the existence of a Value List rather than a query as the Row Source. You need a value list in the combo box properties for that system to work. You could create a value list in code, I suppose, but that isn't covered in the code example, and I don't see that it would help. The next consideration is what happens after the selection is made. What is the After Update code for the combo box? "NukeEng85" wrote in message ... I have the exact same problem as Jon has, I go to select "ALL" and nothing comes up. I'm not using a value list, my combo box pulls it's data from a field in a table. I have SELECT DISTINCT [SNM Data].[ICA] FROM [SNM Data] UNION SELECT "ALL" as Null From [SNM Data] ORDER BY [SNM Data].[ICA]; in my row source. any thoughts? "Jon M." wrote: I got the error to go away there was a labeling issue I had to address. Now my code is exactly as it should be, so it seems. However when I select (All) it still does not display all the records for that area, in fact it doesn't show me any records. I know I'm missing something but I don't know what it is. Any suggestions? Thanks in advance. -- Jon M. "Klatuu" wrote: On which line is the error occuring? -- Dave Hargis, Microsoft Access MVP "Jon M." wrote: I tried to use this code but I am getting an error message when I open the form. The message is Compile Error: Method or data member not found. My code is correct I think it is: Private Sub Form_Open(Cancel As Integer) With Me.OfficeLoc .RowSourceType = "Value List" .RowSource = "(All);" & .RowSource End With End Sub OfficeLoc is the name of my control box. Any suggestions what I'm doing wrong? -- Jon M. "Maurice" wrote: Take a look here maybe this will help you on your way... http://www.mvps.org/access/forms/frm0043.htm hth -- Maurice Ausum "Jon M." wrote: I have a form with 3 combo boxes. One for Building, Dept., and Floor#. They retreive employee records and display them on a subform. I would like to have an "All" option in each of the combo boxes that will display all the records for that field. For example I could then choose Building A, Banking Dept, and "all" in the last combo box and see all employees in Building A in the banking dept, on every floor. Or, "All", Banking, 3rd Floor and I would see every employee in the banking dept on the 3rd floor in each of the buildings. Does that make sense? As always any help is greatly appreciated! -- Jon M. |
#20
|
|||
|
|||
"All" as a combo box option
"NukeEng85" wrote in message
... I did it this way, and it works! I would still prefer the user to be able to select "all" instead of just leaving it blank, but I'll take what I can get "Ron2006" wrote: What I have done in some situations when the data is relatively limited, is the following In the qurey that is actually retrieving the records, change the criteria to be like "*" & form![Formname]![comboxname] & "*" instead of the normal form![Formname]![comboxname] That way when there is NO value in the combobox it get all records. Except those that are Null. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) |
Thread Tools | |
Display Modes | |
|
|