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... -
-
Dim tempFilter As String
-
tempFilter = "A LIKE 'B'"
-
Form.filter = tempFilter
-
Form.FilterOn = True
-
-
Thanks for reading!
6 4184 ADezii 8,834
Recognized Expert Expert
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... -
-
Dim tempFilter As String
-
tempFilter = "A LIKE 'B'"
-
Form.filter = tempFilter
-
Form.FilterOn = True
-
-
Thanks for reading!
Please post all the relevant code.
My form's code: -
-
' one of these for all other combo boxes, text boxes, etc...
-
Private Sub cmbSupplier_AfterUpdate()
-
updateFilter
-
End Sub
-
-
' A separate button that clears all filters
-
Private Sub cmdRemoveFilter_Click()
-
On Error GoTo Err_cmdRemoveFilter_Click
-
-
Dim db As DAO.Database
-
Dim formQry As DAO.QueryDef
-
-
' Remove the filter on the subform
-
'Form.FilterOn = False
-
Form.filter = ""
-
-
' Set all filter selection boxes back to empty state
-
cmbSupplier.Value = ""
-
cmbMicro.Value = ""
-
cmbCompiler.Value = ""
-
-
Set db = Access.CurrentDb
-
Set formQry = db.QueryDefs("qryInfo") ' set the initial query to the default form query
-
Set Form.recordset = formQry.OpenRecordset()
-
-
Exit_cmdRemoveFilter_Click:
-
Exit Sub
-
-
Err_cmdRemoveFilter_Click:
-
MsgBox Err.Description
-
Resume Exit_cmdRemoveFilter_Click
-
-
End Sub
-
-
' Function that concatenates all combo box values into one filter string
-
Private Sub updateFilter()
-
On Error GoTo Err_updateFilter
-
-
Dim tempFilter As String
-
Dim qry As String
-
-
Dim db As DAO.Database
-
Dim rst As DAO.recordset
-
-
Set db = Access.CurrentDb
-
tempFilter = ""
-
-
If cmbSupplier.Value <> "" Then
-
tempFilter = translateFilter("txtblSuppliers", "Supplier", cmbSupplier.Value)
-
End If
-
If cmbMicro.Value <> "" Then
-
If Len(tempFilter) > 0 Then
-
tempFilter = tempFilter & " AND " & _
-
translateFilter("txtblMicros", "Micro", cmbMicro.Value)
-
Else
-
tempFilter = translateFilter("txtblMicros", "Micro", cmbMicro.Value)
-
End If
-
End If
-
If cmbCompiler.Value <> "" Then
-
If Len(tempFilter) > 0 Then
-
tempFilter = tempFilter & " AND " & _
-
translateFilter("txtblCompilers", "Compiler", cmbCompiler.Value)
-
Else
-
tempFilter = translateFilter("txtblCompilers", "Compiler", cmbCompiler.Value)
-
End If
-
End If
-
-
-
qry = "SELECT tblL.Supplier, " _
-
& "tblL.Micro, tblL.Compiler, tblA.ID " _
-
& "FROM tblL INNER JOIN tblA ON " _
-
& "tblL.ID = tblA.ID"
-
-
If tempFilter <> "" Then
-
qry = qry & " WHERE " & tempFilter & ";"
-
Else
-
qry = qry & ";"
-
End If
-
-
' Update the form's recordset
-
Set Form.recordset = db.OpenRecordset(qry)
-
-
Exit_updateFilter:
-
Exit Sub
-
-
Err_updateFilter:
-
MsgBox Err.Description
-
Resume Exit_updateFilter
-
-
End Sub
-
ADezii 8,834
Recognized Expert Expert
My form's code: -
-
' one of these for all other combo boxes, text boxes, etc...
-
Private Sub cmbSupplier_AfterUpdate()
-
updateFilter
-
End Sub
-
-
' A separate button that clears all filters
-
Private Sub cmdRemoveFilter_Click()
-
On Error GoTo Err_cmdRemoveFilter_Click
-
-
Dim db As DAO.Database
-
Dim formQry As DAO.QueryDef
-
-
' Remove the filter on the subform
-
'Form.FilterOn = False
-
Form.filter = ""
-
-
' Set all filter selection boxes back to empty state
-
cmbSupplier.Value = ""
-
cmbMicro.Value = ""
-
cmbCompiler.Value = ""
-
-
Set db = Access.CurrentDb
-
Set formQry = db.QueryDefs("qryInfo") ' set the initial query to the default form query
-
Set Form.recordset = formQry.OpenRecordset()
-
-
Exit_cmdRemoveFilter_Click:
-
Exit Sub
-
-
Err_cmdRemoveFilter_Click:
-
MsgBox Err.Description
-
Resume Exit_cmdRemoveFilter_Click
-
-
End Sub
-
-
' Function that concatenates all combo box values into one filter string
-
Private Sub updateFilter()
-
On Error GoTo Err_updateFilter
-
-
Dim tempFilter As String
-
Dim qry As String
-
-
Dim db As DAO.Database
-
Dim rst As DAO.recordset
-
-
Set db = Access.CurrentDb
-
tempFilter = ""
-
-
If cmbSupplier.Value <> "" Then
-
tempFilter = translateFilter("txtblSuppliers", "Supplier", cmbSupplier.Value)
-
End If
-
If cmbMicro.Value <> "" Then
-
If Len(tempFilter) > 0 Then
-
tempFilter = tempFilter & " AND " & _
-
translateFilter("txtblMicros", "Micro", cmbMicro.Value)
-
Else
-
tempFilter = translateFilter("txtblMicros", "Micro", cmbMicro.Value)
-
End If
-
End If
-
If cmbCompiler.Value <> "" Then
-
If Len(tempFilter) > 0 Then
-
tempFilter = tempFilter & " AND " & _
-
translateFilter("txtblCompilers", "Compiler", cmbCompiler.Value)
-
Else
-
tempFilter = translateFilter("txtblCompilers", "Compiler", cmbCompiler.Value)
-
End If
-
End If
-
-
-
qry = "SELECT tblL.Supplier, " _
-
& "tblL.Micro, tblL.Compiler, tblA.ID " _
-
& "FROM tblL INNER JOIN tblA ON " _
-
& "tblL.ID = tblA.ID"
-
-
If tempFilter <> "" Then
-
qry = qry & " WHERE " & tempFilter & ";"
-
Else
-
qry = qry & ";"
-
End If
-
-
' Update the form's recordset
-
Set Form.recordset = db.OpenRecordset(qry)
-
-
Exit_updateFilter:
-
Exit Sub
-
-
Err_updateFilter:
-
MsgBox Err.Description
-
Resume Exit_updateFilter
-
-
End Sub
-
If I get a chance this evening, I'll have a good look at the code. Please stay in touch.
ADezii 8,834
Recognized Expert Expert
My form's code: -
-
' one of these for all other combo boxes, text boxes, etc...
-
Private Sub cmbSupplier_AfterUpdate()
-
updateFilter
-
End Sub
-
-
' A separate button that clears all filters
-
Private Sub cmdRemoveFilter_Click()
-
On Error GoTo Err_cmdRemoveFilter_Click
-
-
Dim db As DAO.Database
-
Dim formQry As DAO.QueryDef
-
-
' Remove the filter on the subform
-
'Form.FilterOn = False
-
Form.filter = ""
-
-
' Set all filter selection boxes back to empty state
-
cmbSupplier.Value = ""
-
cmbMicro.Value = ""
-
cmbCompiler.Value = ""
-
-
Set db = Access.CurrentDb
-
Set formQry = db.QueryDefs("qryInfo") ' set the initial query to the default form query
-
Set Form.recordset = formQry.OpenRecordset()
-
-
Exit_cmdRemoveFilter_Click:
-
Exit Sub
-
-
Err_cmdRemoveFilter_Click:
-
MsgBox Err.Description
-
Resume Exit_cmdRemoveFilter_Click
-
-
End Sub
-
-
' Function that concatenates all combo box values into one filter string
-
Private Sub updateFilter()
-
On Error GoTo Err_updateFilter
-
-
Dim tempFilter As String
-
Dim qry As String
-
-
Dim db As DAO.Database
-
Dim rst As DAO.recordset
-
-
Set db = Access.CurrentDb
-
tempFilter = ""
-
-
If cmbSupplier.Value <> "" Then
-
tempFilter = translateFilter("txtblSuppliers", "Supplier", cmbSupplier.Value)
-
End If
-
If cmbMicro.Value <> "" Then
-
If Len(tempFilter) > 0 Then
-
tempFilter = tempFilter & " AND " & _
-
translateFilter("txtblMicros", "Micro", cmbMicro.Value)
-
Else
-
tempFilter = translateFilter("txtblMicros", "Micro", cmbMicro.Value)
-
End If
-
End If
-
If cmbCompiler.Value <> "" Then
-
If Len(tempFilter) > 0 Then
-
tempFilter = tempFilter & " AND " & _
-
translateFilter("txtblCompilers", "Compiler", cmbCompiler.Value)
-
Else
-
tempFilter = translateFilter("txtblCompilers", "Compiler", cmbCompiler.Value)
-
End If
-
End If
-
-
-
qry = "SELECT tblL.Supplier, " _
-
& "tblL.Micro, tblL.Compiler, tblA.ID " _
-
& "FROM tblL INNER JOIN tblA ON " _
-
& "tblL.ID = tblA.ID"
-
-
If tempFilter <> "" Then
-
qry = qry & " WHERE " & tempFilter & ";"
-
Else
-
qry = qry & ";"
-
End If
-
-
' Update the form's recordset
-
Set Form.recordset = db.OpenRecordset(qry)
-
-
Exit_updateFilter:
-
Exit Sub
-
-
Err_updateFilter:
-
MsgBox Err.Description
-
Resume Exit_updateFilter
-
-
End Sub
-
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.
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 "translatio n" 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! -
Public Function translateFilter(txTable As String, txName As String, txValue As String) As String
-
' Look up the txValue in the txTable and return a string
-
' that includes all matches in the filter
-
Dim db As DAO.Database
-
Dim qry As String
-
Dim rst As DAO.recordset
-
Dim tempString As String
-
-
Set db = Access.CurrentDb
-
qry = "SELECT " & txName & " FROM " & txTable & " WHERE " & txTable & ".Group = " & _
-
"(SELECT Group FROM " & txTable & " WHERE " & txName & " = '" & txValue & "');"
-
-
Set rst = db.OpenRecordset(qry)
-
If Not rst.BOF And Not rst.EOF Then
-
rst.MoveFirst
-
tempString = "("
-
While Not rst.BOF And Not rst.EOF
-
tempString = tempString & txName & " LIKE """ & rst.Fields(txName) & """"
-
-
rst.MoveNext
-
If Not rst.EOF Then
-
tempString = tempString & " OR "
-
Else
-
tempString = tempString & ")"
-
End If
-
Wend
-
End If
-
translateFilter = tempString
-
Set rst = Nothing
-
Set db = Nothing
-
End Function
-
ADezii 8,834
Recognized Expert Expert
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 "translatio n" 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! -
Public Function translateFilter(txTable As String, txName As String, txValue As String) As String
-
' Look up the txValue in the txTable and return a string
-
' that includes all matches in the filter
-
Dim db As DAO.Database
-
Dim qry As String
-
Dim rst As DAO.recordset
-
Dim tempString As String
-
-
Set db = Access.CurrentDb
-
qry = "SELECT " & txName & " FROM " & txTable & " WHERE " & txTable & ".Group = " & _
-
"(SELECT Group FROM " & txTable & " WHERE " & txName & " = '" & txValue & "');"
-
-
Set rst = db.OpenRecordset(qry)
-
If Not rst.BOF And Not rst.EOF Then
-
rst.MoveFirst
-
tempString = "("
-
While Not rst.BOF And Not rst.EOF
-
tempString = tempString & txName & " LIKE """ & rst.Fields(txName) & """"
-
-
rst.MoveNext
-
If Not rst.EOF Then
-
tempString = tempString & " OR "
-
Else
-
tempString = tempString & ")"
-
End If
-
Wend
-
End If
-
translateFilter = tempString
-
Set rst = Nothing
-
Set db = Nothing
-
End Function
-
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: -
Private Sub cbmSupplier_AfterUpdate()
-
Dim varFilterValue As Variant
-
-
varFilterValue = Me![cbmSupplier]
-
upDateFilter
-
'restore original Filter Value after updating the Filter
-
Me![cbmSupplier] = varFilterValue
-
End Sub
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Bruce Dodds |
last post by:
In an Access 2003 application, the selected value in a combo box
disappears after the afterupdate event of the combo box applies a filter
to the form.
The combo box is unbound (Control Source is blank), and its recordsource
is not related to the form recordsource.
If anyone has any information on how to fix this, I'd appreciate it.
|
by: Midiman69 |
last post by:
Hi
I am trying to use a unbound combo box on a menu form to open another Form
and filter records according to the combo box selection and failing
miserably!
I am using the following - this opens the form but does not apply the
|
by: Stewart |
last post by:
Hi all! My (relatively small) database holds data on staff members and
the projects (services) that they are assigned to.
In my form frmStaff, I have a list of staff members - it is a
continuous form. Each staff member is linked to a Service through a
many-to-many relationship, using a junction table called
jctStaffServices. I would like to place a Combo Box in frmStaff where
you can 'filter' staff by the Service (i.e. ServiceName)...
|
by: MLH |
last post by:
I have a form (xxxxxxxxxxxxxx) with a combo-box control
(yyyyyyyyyyyyyy). The rowsource property for that combo box is as
follows:
SELECT DISTINCTROW .,
. FROM ;
The SQL for qryVehicleList is ...
SELECT & " " & & " " & & " & "]" AS Vehicle, tblVehicleJobs.VehicleJobID,
tblVehicleJobs.OwnerID, tblVehicleJobs.AuthID, tblVehicleJobs.TowJob,
|
by: jjyconsulting |
last post by:
Newbie needing some help. I have a tblParticipants. The fields
include gender, education_level, income, occupation etc., I'm trying to
create a form where a user can run a query from the form and just
choose the appropriate criterias from the combo boxes to get the
results. I also want the query to run even if there is not a value in
all the combo boxes ie., i want just all males with income level of
over $100,000...Any insights or help...
| |
by: salad |
last post by:
I was wondering how you handle active/inactive elements in a combo box.
Let's say you have a combo box to select an employee. Joe Blow has
been selected for many record however Joe has left the company and has
been flagged inactive. If you have a filter on the rowsource like
Where Active = True
then Joe's name would not show up in the combo list. This would be fine
if the combo is associated with a field that is null/0. It would not be...
|
by: jonosborne |
last post by:
hi, i hope someone can help, i have read a lot of tutorials relating to this matter but im afraid VBA isnt my strong point and i just need a touch of guidance.
I have one table called which has three fields , and . On a form i have three combo boxes which show data from each field on the table along with an extra 'All Records'. The following code enabling this is in the 'Row Source' of each combo box:
. AS Filter, FROM UNION SELECT "*"...
|
by: banderson |
last post by:
Hello,
I have a data entry form for a table with information about buildings and am having a problem making a combo box do what I want. I would like to make the combo box show a list of unique bldg mgmt company names and then to open a building management company form to show all records with this name, so the user can find the correct branch location to select. Then, upon closing the building management company form, the active/selected record...
|
by: novoselent |
last post by:
This seems like it should be an easy thing, but I think I'm missing something simple here...or I'm just going about it all wrong...
Using Access 2003
I have a form that lists vehicle service dates for different company terminals.
The form loads showing all records.
In the form header I have a combo box named "CSCFilter" that is unbound, and uses the table/query option to list the 16 terminals in our company.
The field I'm trying to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |