I have a problem filtering records in my subform using a ComboBox in the Main Form. I have tried options proferred in other forums and have still not git the desired result. I know I must be missing something as this looks quite simple.
I have attached a screenshot to help.
What I need to for the records to be filtered by the Section Field using either of the three options in the ComboBox. The ComboBox looksup data from a Sections table (SectionID, Section).
Main Table Name: Orders (Master Link: Order ID)
SubForm Table Name: Order Details (Child Link: Order ID)
ComboBox Object Name: CboFilter
I will appreciate any help.
Programmatically setting the Filter Property for a Sub-Form from a Main Form can be a little problematic since you must know exactly when to turn the Filter ON and when to turn it OFF. That being said, here are a few, simple, assumptions: - The 2 Tables involved are [Orders] and [Order Details].
- These Tables are Linked by the [Order ID] Field via a 1 to MANY Relationship, namely:
[Orders].[Order ID]{1} ==> [Order Details].[Order ID]{MANY}. - The [Order Details] Table contains a Field named [Section]{TEXT}.
- The Name of the Order Details Sub-Form is Order Details.
- The Source Object for the Order Details Sub-Form is subfOrderDetails.
- The Name of the Filtering Combo Box is cboFilter.
- cboFilter draws its Data from a Sections Table consisting of [SectionID] and [Section] Fields.
- cboFilter is a Single Column Combo Box consisting of the [Section Field] only. If this is not the case, the code must be modified, namely substitute Me![cboFilter].Column(1) for Me![cboFilter].
- Here is the code which must reside in the AfterUpdate() Event of cboFilter:
- Private Sub cboFilter_AfterUpdate()
-
Dim intNumOfDetails As Integer
-
Dim strMsg As String
-
-
'Will the Filter (Order ID/Section) actually produce any Records?)
-
intNumOfDetails = DCount("*", "[Order Details]", "[Order ID] = " & Me![Order ID] & _
-
" And [Section] = '" & Me![cboFilter] & "'")
-
-
If Not IsNull(Me![cboFilter]) Then 'must be an entry
-
If intNumOfDetails > 0 Then 'Filter produces Records
-
Me![Order Details].Form.Filter = "[Section] = '" & Me![cboFilter] & "'"
-
Me![Order Details].Form.FilterOn = True
-
Else
-
strMsg = "No Order Details exist for Order ID [" & Me![Order ID] & "] and " & _
-
"Section [" & Me![cboFilter] & "]!"
-
MsgBox strMsg, vbExclamation, "No Order Details"
-
End If
-
End If
-
End Sub
- Any problems, just let us know.
5 14573
Programmatically setting the Filter Property for a Sub-Form from a Main Form can be a little problematic since you must know exactly when to turn the Filter ON and when to turn it OFF. That being said, here are a few, simple, assumptions: - The 2 Tables involved are [Orders] and [Order Details].
- These Tables are Linked by the [Order ID] Field via a 1 to MANY Relationship, namely:
[Orders].[Order ID]{1} ==> [Order Details].[Order ID]{MANY}. - The [Order Details] Table contains a Field named [Section]{TEXT}.
- The Name of the Order Details Sub-Form is Order Details.
- The Source Object for the Order Details Sub-Form is subfOrderDetails.
- The Name of the Filtering Combo Box is cboFilter.
- cboFilter draws its Data from a Sections Table consisting of [SectionID] and [Section] Fields.
- cboFilter is a Single Column Combo Box consisting of the [Section Field] only. If this is not the case, the code must be modified, namely substitute Me![cboFilter].Column(1) for Me![cboFilter].
- Here is the code which must reside in the AfterUpdate() Event of cboFilter:
- Private Sub cboFilter_AfterUpdate()
-
Dim intNumOfDetails As Integer
-
Dim strMsg As String
-
-
'Will the Filter (Order ID/Section) actually produce any Records?)
-
intNumOfDetails = DCount("*", "[Order Details]", "[Order ID] = " & Me![Order ID] & _
-
" And [Section] = '" & Me![cboFilter] & "'")
-
-
If Not IsNull(Me![cboFilter]) Then 'must be an entry
-
If intNumOfDetails > 0 Then 'Filter produces Records
-
Me![Order Details].Form.Filter = "[Section] = '" & Me![cboFilter] & "'"
-
Me![Order Details].Form.FilterOn = True
-
Else
-
strMsg = "No Order Details exist for Order ID [" & Me![Order ID] & "] and " & _
-
"Section [" & Me![cboFilter] & "]!"
-
MsgBox strMsg, vbExclamation, "No Order Details"
-
End If
-
End If
-
End Sub
- Any problems, just let us know.
Used your code and got an error stop on this line:
intNumOfDetails = DCount("*", "[Order Details]", "[Order ID] = " & Me![Order ID] & _
" And [Section] = '" & Me![CboFilter] & "'")
Meanwhile, your assumptions were right except for nos 4 and 5 which I adjusted accordingly.
Any ideas?
@shiwawa - What is the Error Message that you are receiving?
- I know some questions are redundant, but just to be sure...
- Is your Table Name [Order Details]?
- Is the [Order ID] Field named 'exactly' that, namely [Order ID], and not OrderID?
- Is the [Order ID] Field NUMERIC in nature?
- Does the [Order Details] Table have a Field named Section?
- Is the Section Field a TEXT Data Type?
- Is your Filter Combo Box named cboFilter?
- If all the above Response are Yes, would you be willing to mail me a Copy of the Database with some Sample Data?
NeoPa 32,556
Expert Mod 16PB
A new thread ( Add Button) has been created from a new post in this thread. Please visit if you think you can be of help.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jason |
last post by:
I am trying to filter records in a primary form based on records in
related tables. The data in the related tables is being displayed in
the primary form
through subforms. To be more specific, I...
|
by: Colin |
last post by:
In access 2000 I need to filter records in a Subform by pushing a
button on a command button located on the Main form.
The Main form is blank. Its only purpose is to contain the subform
which is...
|
by: Dave Boyd |
last post by:
Hi,
I have two very similar forms each with a subform. The main form gets
a few fields from the user and passes this back to a query that the
subform is bound to. The requery is done when the...
|
by: New2Access |
last post by:
Hi,
I have a form thats used to enter records into a table. The table and
form have fields for Name, week, project, and hours. To assist users,
I wish to add a subform that will show them how...
|
by: origin197511 |
last post by:
Hello all...
I'm having an issue with MSAccess 2000. I have a Form that holds
records of my cartridge loads for a rifle and a subform that lists all
groups that have been fired with that load. ...
|
by: Susan Bricker |
last post by:
Greetings.
I am having trouble populating text data that represents data in my
table. Here's the setup:
There is a People Table (name, address, phone, ...)
peopleID = autonumber key
There...
|
by: Stu |
last post by:
Hi,
I have a combobox who's values change the recordsource of the form.
Within this form, there is a subform, whos records also need to change
pending the value in the combobox. I am able to get...
|
by: OllyJ |
last post by:
Hope you can help guys.
I have a scheduling database, the main schedule table contains the following fields:
AddedDate
Day/Night
Machine
Arm/Head
StockCode
ToolNumber
|
by: bkberg05 |
last post by:
Hi - I have a form called Vendor (tied to table with same name). Each vendor_id can belong to more than one 'category'. So there's a table called Vendor_Category which contains just vendor_id and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |