By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,247 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,247 IT Pros & Developers. It's quick & easy.

Text Disappears in Combo Box after Filter returns no records

P: 3
MS Access 2003, Windows XP SP2, VBA

I have a continuous form that allows edits and filters, but not deletions or additions. I filter the form based on combining selections the user makes in several combo boxes on the form. The filter is updated whenever the user changes his/her selection.

My problem is when the user selects a filter option that is not present in the data. The filter works fine - no records are displayed; however, the text in the combo box used to build the filter disappears. This is only in the most recently changed filter selection. So say I picked Name="Fred" and got 3 results. Then I picked age="42" and I got no records and a blank 'age' combo box, but the 'Name' combo box still says "Fred". Has anyone run into this problem before and know how to fix or get around it?

On a similar form where additions are allowed, the text does not disappear from the combo box in such a situation; however, in this form, there is still one blank record displayed for inputing a new record.

This is the basic code, except the filter combines the values of several controls, rather than just one as in this ex. If you want to see more, just let me know, but I feel the problem has more to do with the form/control settings? Not sure...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim tempFilter As String
  3. tempFilter = "A LIKE 'B'"
  4. Form.filter = tempFilter
  5. Form.FilterOn = True
  6.  
  7.  
Thanks for reading!
Aug 9 '07 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,638
MS Access 2003, Windows XP SP2, VBA

I have a continuous form that allows edits and filters, but not deletions or additions. I filter the form based on combining selections the user makes in several combo boxes on the form. The filter is updated whenever the user changes his/her selection.

My problem is when the user selects a filter option that is not present in the data. The filter works fine - no records are displayed; however, the text in the combo box used to build the filter disappears. This is only in the most recently changed filter selection. So say I picked Name="Fred" and got 3 results. Then I picked age="42" and I got no records and a blank 'age' combo box, but the 'Name' combo box still says "Fred". Has anyone run into this problem before and know how to fix or get around it?

On a similar form where additions are allowed, the text does not disappear from the combo box in such a situation; however, in this form, there is still one blank record displayed for inputing a new record.

This is the basic code, except the filter combines the values of several controls, rather than just one as in this ex. If you want to see more, just let me know, but I feel the problem has more to do with the form/control settings? Not sure...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim tempFilter As String
  3. tempFilter = "A LIKE 'B'"
  4. Form.filter = tempFilter
  5. Form.FilterOn = True
  6.  
  7.  
Thanks for reading!
Please post all the relevant code.
Aug 9 '07 #2

P: 3
My form's code:
Expand|Select|Wrap|Line Numbers
  1.  
  2. ' one of these for all other combo boxes, text boxes, etc...
  3. Private Sub cmbSupplier_AfterUpdate()
  4.     updateFilter
  5. End Sub
  6.  
  7. ' A separate button that clears all filters
  8. Private Sub cmdRemoveFilter_Click()
  9. On Error GoTo Err_cmdRemoveFilter_Click
  10.  
  11.     Dim db As DAO.Database
  12.     Dim formQry As DAO.QueryDef
  13.  
  14.     ' Remove the filter on the subform
  15.     'Form.FilterOn = False
  16.     Form.filter = ""
  17.  
  18.     ' Set all filter selection boxes back to empty state
  19.     cmbSupplier.Value = ""
  20.     cmbMicro.Value = ""
  21.     cmbCompiler.Value = ""
  22.  
  23.     Set db = Access.CurrentDb
  24.     Set formQry = db.QueryDefs("qryInfo") ' set the initial query to the default form query
  25.     Set Form.recordset = formQry.OpenRecordset()
  26.  
  27. Exit_cmdRemoveFilter_Click:
  28.     Exit Sub
  29.  
  30. Err_cmdRemoveFilter_Click:
  31.     MsgBox Err.Description
  32.     Resume Exit_cmdRemoveFilter_Click
  33.  
  34. End Sub
  35.  
  36. ' Function that concatenates all combo box values into one filter string
  37. Private Sub updateFilter()
  38. On Error GoTo Err_updateFilter
  39.  
  40.     Dim tempFilter As String
  41.     Dim qry As String
  42.  
  43.     Dim db As DAO.Database
  44.     Dim rst As DAO.recordset
  45.  
  46.     Set db = Access.CurrentDb
  47.     tempFilter = ""
  48.  
  49.     If cmbSupplier.Value <> "" Then
  50.         tempFilter = translateFilter("txtblSuppliers", "Supplier", cmbSupplier.Value)
  51.     End If
  52.     If cmbMicro.Value <> "" Then
  53.         If Len(tempFilter) > 0 Then
  54.             tempFilter = tempFilter & " AND " & _
  55.                 translateFilter("txtblMicros", "Micro", cmbMicro.Value)
  56.         Else
  57.             tempFilter = translateFilter("txtblMicros", "Micro", cmbMicro.Value)
  58.         End If
  59.     End If
  60.     If cmbCompiler.Value <> "" Then
  61.         If Len(tempFilter) > 0 Then
  62.             tempFilter = tempFilter & " AND " & _
  63.                 translateFilter("txtblCompilers", "Compiler", cmbCompiler.Value)
  64.         Else
  65.             tempFilter = translateFilter("txtblCompilers", "Compiler", cmbCompiler.Value)
  66.         End If
  67.     End If
  68.  
  69.  
  70.     qry = "SELECT tblL.Supplier, " _
  71.         & "tblL.Micro, tblL.Compiler, tblA.ID " _
  72.         & "FROM tblL INNER JOIN tblA ON " _
  73.         & "tblL.ID = tblA.ID"
  74.  
  75.     If tempFilter <> "" Then
  76.             qry = qry & " WHERE " & tempFilter & ";"
  77.     Else
  78.             qry = qry & ";"
  79.     End If
  80.  
  81.     ' Update the form's recordset
  82.     Set Form.recordset = db.OpenRecordset(qry)
  83.  
  84. Exit_updateFilter:
  85.     Exit Sub
  86.  
  87. Err_updateFilter:
  88.     MsgBox Err.Description
  89.     Resume Exit_updateFilter
  90.  
  91. End Sub
  92.  
Aug 9 '07 #3

ADezii
Expert 5K+
P: 8,638
My form's code:
Expand|Select|Wrap|Line Numbers
  1.  
  2. ' one of these for all other combo boxes, text boxes, etc...
  3. Private Sub cmbSupplier_AfterUpdate()
  4.     updateFilter
  5. End Sub
  6.  
  7. ' A separate button that clears all filters
  8. Private Sub cmdRemoveFilter_Click()
  9. On Error GoTo Err_cmdRemoveFilter_Click
  10.  
  11.     Dim db As DAO.Database
  12.     Dim formQry As DAO.QueryDef
  13.  
  14.     ' Remove the filter on the subform
  15.     'Form.FilterOn = False
  16.     Form.filter = ""
  17.  
  18.     ' Set all filter selection boxes back to empty state
  19.     cmbSupplier.Value = ""
  20.     cmbMicro.Value = ""
  21.     cmbCompiler.Value = ""
  22.  
  23.     Set db = Access.CurrentDb
  24.     Set formQry = db.QueryDefs("qryInfo") ' set the initial query to the default form query
  25.     Set Form.recordset = formQry.OpenRecordset()
  26.  
  27. Exit_cmdRemoveFilter_Click:
  28.     Exit Sub
  29.  
  30. Err_cmdRemoveFilter_Click:
  31.     MsgBox Err.Description
  32.     Resume Exit_cmdRemoveFilter_Click
  33.  
  34. End Sub
  35.  
  36. ' Function that concatenates all combo box values into one filter string
  37. Private Sub updateFilter()
  38. On Error GoTo Err_updateFilter
  39.  
  40.     Dim tempFilter As String
  41.     Dim qry As String
  42.  
  43.     Dim db As DAO.Database
  44.     Dim rst As DAO.recordset
  45.  
  46.     Set db = Access.CurrentDb
  47.     tempFilter = ""
  48.  
  49.     If cmbSupplier.Value <> "" Then
  50.         tempFilter = translateFilter("txtblSuppliers", "Supplier", cmbSupplier.Value)
  51.     End If
  52.     If cmbMicro.Value <> "" Then
  53.         If Len(tempFilter) > 0 Then
  54.             tempFilter = tempFilter & " AND " & _
  55.                 translateFilter("txtblMicros", "Micro", cmbMicro.Value)
  56.         Else
  57.             tempFilter = translateFilter("txtblMicros", "Micro", cmbMicro.Value)
  58.         End If
  59.     End If
  60.     If cmbCompiler.Value <> "" Then
  61.         If Len(tempFilter) > 0 Then
  62.             tempFilter = tempFilter & " AND " & _
  63.                 translateFilter("txtblCompilers", "Compiler", cmbCompiler.Value)
  64.         Else
  65.             tempFilter = translateFilter("txtblCompilers", "Compiler", cmbCompiler.Value)
  66.         End If
  67.     End If
  68.  
  69.  
  70.     qry = "SELECT tblL.Supplier, " _
  71.         & "tblL.Micro, tblL.Compiler, tblA.ID " _
  72.         & "FROM tblL INNER JOIN tblA ON " _
  73.         & "tblL.ID = tblA.ID"
  74.  
  75.     If tempFilter <> "" Then
  76.             qry = qry & " WHERE " & tempFilter & ";"
  77.     Else
  78.             qry = qry & ";"
  79.     End If
  80.  
  81.     ' Update the form's recordset
  82.     Set Form.recordset = db.OpenRecordset(qry)
  83.  
  84. Exit_updateFilter:
  85.     Exit Sub
  86.  
  87. Err_updateFilter:
  88.     MsgBox Err.Description
  89.     Resume Exit_updateFilter
  90.  
  91. End Sub
  92.  
If I get a chance this evening, I'll have a good look at the code. Please stay in touch.
Aug 9 '07 #4

ADezii
Expert 5K+
P: 8,638
My form's code:
Expand|Select|Wrap|Line Numbers
  1.  
  2. ' one of these for all other combo boxes, text boxes, etc...
  3. Private Sub cmbSupplier_AfterUpdate()
  4.     updateFilter
  5. End Sub
  6.  
  7. ' A separate button that clears all filters
  8. Private Sub cmdRemoveFilter_Click()
  9. On Error GoTo Err_cmdRemoveFilter_Click
  10.  
  11.     Dim db As DAO.Database
  12.     Dim formQry As DAO.QueryDef
  13.  
  14.     ' Remove the filter on the subform
  15.     'Form.FilterOn = False
  16.     Form.filter = ""
  17.  
  18.     ' Set all filter selection boxes back to empty state
  19.     cmbSupplier.Value = ""
  20.     cmbMicro.Value = ""
  21.     cmbCompiler.Value = ""
  22.  
  23.     Set db = Access.CurrentDb
  24.     Set formQry = db.QueryDefs("qryInfo") ' set the initial query to the default form query
  25.     Set Form.recordset = formQry.OpenRecordset()
  26.  
  27. Exit_cmdRemoveFilter_Click:
  28.     Exit Sub
  29.  
  30. Err_cmdRemoveFilter_Click:
  31.     MsgBox Err.Description
  32.     Resume Exit_cmdRemoveFilter_Click
  33.  
  34. End Sub
  35.  
  36. ' Function that concatenates all combo box values into one filter string
  37. Private Sub updateFilter()
  38. On Error GoTo Err_updateFilter
  39.  
  40.     Dim tempFilter As String
  41.     Dim qry As String
  42.  
  43.     Dim db As DAO.Database
  44.     Dim rst As DAO.recordset
  45.  
  46.     Set db = Access.CurrentDb
  47.     tempFilter = ""
  48.  
  49.     If cmbSupplier.Value <> "" Then
  50.         tempFilter = translateFilter("txtblSuppliers", "Supplier", cmbSupplier.Value)
  51.     End If
  52.     If cmbMicro.Value <> "" Then
  53.         If Len(tempFilter) > 0 Then
  54.             tempFilter = tempFilter & " AND " & _
  55.                 translateFilter("txtblMicros", "Micro", cmbMicro.Value)
  56.         Else
  57.             tempFilter = translateFilter("txtblMicros", "Micro", cmbMicro.Value)
  58.         End If
  59.     End If
  60.     If cmbCompiler.Value <> "" Then
  61.         If Len(tempFilter) > 0 Then
  62.             tempFilter = tempFilter & " AND " & _
  63.                 translateFilter("txtblCompilers", "Compiler", cmbCompiler.Value)
  64.         Else
  65.             tempFilter = translateFilter("txtblCompilers", "Compiler", cmbCompiler.Value)
  66.         End If
  67.     End If
  68.  
  69.  
  70.     qry = "SELECT tblL.Supplier, " _
  71.         & "tblL.Micro, tblL.Compiler, tblA.ID " _
  72.         & "FROM tblL INNER JOIN tblA ON " _
  73.         & "tblL.ID = tblA.ID"
  74.  
  75.     If tempFilter <> "" Then
  76.             qry = qry & " WHERE " & tempFilter & ";"
  77.     Else
  78.             qry = qry & ";"
  79.     End If
  80.  
  81.     ' Update the form's recordset
  82.     Set Form.recordset = db.OpenRecordset(qry)
  83.  
  84. Exit_updateFilter:
  85.     Exit Sub
  86.  
  87. Err_updateFilter:
  88.     MsgBox Err.Description
  89.     Resume Exit_updateFilter
  90.  
  91. End Sub
  92.  
I'm taking the code into work and having a look at it tomorrow. I'm assuming translateFilter is either a Private Function (returning a String) in the Form's Class Module, or a Public Function in a Standard Code Module. Which one is it, and please post the code for it. Will be seeing you.
Aug 9 '07 #5

P: 3
I'm taking the code into work and having a look at it tomorrow. I'm assuming translateFilter is either a Private Function (returning a String) in the Form's Class Module, or a Public Function in a Standard Code Module. Which one is it, and please post the code for it. Will be seeing you.
Sorry about that - I am not able to read your replies, except when I reply and they are quoted in the response. I don't know whether you had other questions that I haven't addressed, but here is the code for the translateFilter function.

It is a Public Function in a Standard Code Module that can make the filter less restrictive by adding more options for the filtered fields based on entries in a database "translation" table. Ex: A is equivalent to B, C, D according to the DB translation table, so if user filters for A, also allow records with B, C, and D to pass the filter.

If my memory serves me right, however, this problem was evident prior to the addition of the translateFilter function. I think the code will still work after removing these function calls, but the text still disappears.

Thanks for taking the time to help me with this!

Expand|Select|Wrap|Line Numbers
  1. Public Function translateFilter(txTable As String, txName As String, txValue As String) As String
  2.     ' Look up the txValue in the txTable and return a string
  3.     ' that includes all matches in the filter
  4.     Dim db As DAO.Database
  5.     Dim qry As String
  6.     Dim rst As DAO.recordset
  7.     Dim tempString As String
  8.  
  9.     Set db = Access.CurrentDb
  10.     qry = "SELECT " & txName & " FROM " & txTable & " WHERE " & txTable & ".Group = " & _
  11.     "(SELECT Group FROM " & txTable & " WHERE " & txName & " = '" & txValue & "');"
  12.  
  13.     Set rst = db.OpenRecordset(qry)
  14.     If Not rst.BOF And Not rst.EOF Then
  15.         rst.MoveFirst
  16.         tempString = "("
  17.         While Not rst.BOF And Not rst.EOF
  18.             tempString = tempString & txName & " LIKE """ & rst.Fields(txName) & """"
  19.  
  20.             rst.MoveNext
  21.             If Not rst.EOF Then
  22.                 tempString = tempString & " OR "
  23.             Else
  24.                 tempString = tempString & ")"
  25.             End If
  26.         Wend
  27.     End If
  28.     translateFilter = tempString
  29.     Set rst = Nothing
  30.     Set db = Nothing
  31. End Function
  32.  
Aug 10 '07 #6

ADezii
Expert 5K+
P: 8,638
Sorry about that - I am not able to read your replies, except when I reply and they are quoted in the response. I don't know whether you had other questions that I haven't addressed, but here is the code for the translateFilter function.

It is a Public Function in a Standard Code Module that can make the filter less restrictive by adding more options for the filtered fields based on entries in a database "translation" table. Ex: A is equivalent to B, C, D according to the DB translation table, so if user filters for A, also allow records with B, C, and D to pass the filter.

If my memory serves me right, however, this problem was evident prior to the addition of the translateFilter function. I think the code will still work after removing these function calls, but the text still disappears.

Thanks for taking the time to help me with this!

Expand|Select|Wrap|Line Numbers
  1. Public Function translateFilter(txTable As String, txName As String, txValue As String) As String
  2.     ' Look up the txValue in the txTable and return a string
  3.     ' that includes all matches in the filter
  4.     Dim db As DAO.Database
  5.     Dim qry As String
  6.     Dim rst As DAO.recordset
  7.     Dim tempString As String
  8.  
  9.     Set db = Access.CurrentDb
  10.     qry = "SELECT " & txName & " FROM " & txTable & " WHERE " & txTable & ".Group = " & _
  11.     "(SELECT Group FROM " & txTable & " WHERE " & txName & " = '" & txValue & "');"
  12.  
  13.     Set rst = db.OpenRecordset(qry)
  14.     If Not rst.BOF And Not rst.EOF Then
  15.         rst.MoveFirst
  16.         tempString = "("
  17.         While Not rst.BOF And Not rst.EOF
  18.             tempString = tempString & txName & " LIKE """ & rst.Fields(txName) & """"
  19.  
  20.             rst.MoveNext
  21.             If Not rst.EOF Then
  22.                 tempString = tempString & " OR "
  23.             Else
  24.                 tempString = tempString & ")"
  25.             End If
  26.         Wend
  27.     End If
  28.     translateFilter = tempString
  29.     Set rst = Nothing
  30.     Set db = Nothing
  31. End Function
  32.  
How about trying this Temporary Fix until, hopefully, we can resolve this issue. Enter code, similar to that posted below, to all Combo Boxes which are involved in the Filter Creation Process and let me know how you make out:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cbmSupplier_AfterUpdate()
  2. Dim varFilterValue As Variant
  3.  
  4. varFilterValue = Me![cbmSupplier]
  5.   upDateFilter
  6. 'restore original Filter Value after updating the Filter
  7. Me![cbmSupplier] = varFilterValue
  8. End Sub
Aug 10 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.