473,598 Members | 3,298 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Filtering form by combo box

Greetings all,

I am attempting to make a form that will filter through several tables
that
(I believe) have refretial integrity. I am pulling data from several
tables into the form and i would like to eventually be able to filter
down through the
tables untill i can reach one unique record. I am creating a datbase
to keep track of registered accounts for a stae program. Each
account is registered into the program through a two diferent parties,
local and state wide. Accounts are unique. State wide parties are
unique. Local parties register accounts with either uniqie IDs or
aggregate Accounts under a unique ID. Each Local party may have one or
many names, which may or may not be unique for each account. Each
account registers information once a season (or more, sometimes people
resubmit becuase they want to make changes on their data). to enforce
refrential integrity, there is a multiple primary key index placed on
the accounts based on Dem_Y, Dem_S, Account, and Sub_date.
My tables look like this:

tblState;
StParty_ID (primary)
StParty_nm
....info

tblState_Local
LcParty_Id (primary)
StParty_ID (foreign key)
....info

tblLocal_Acct
LcParty_ID (foreign key)
LcParty_nm
Account (Primary key)

tblAccount_Dem_ Y_S_Sub
Dem_y (multi- field index primary key)
Dem_S (multi- field index primary key)
Account (multi- field index primary key)
Sub_date (multi- field index primary key)
....unique data
The refrential integrity is working fine, i know this because there is
excellent heirarchy through tblState. i can "tree" down to the unique
data for an account perfectly. I would like to create a form, where i
can use combo boxes to navigate through the hierarchy as easy as i can
do in datasheet view of tblState. I am currently using one form with
several combo boxes which use the After update event to change the
rowsource of the next combo box that would correspond to the heirarchy
of the tables.

I can select a unique State party ID, and a label box will display the
State party's name. I have succesfully limited the rowsource of the
Local Party ID combo box so that it will only display the local IDs
that are registered through the selected State party_ID when i click
the drop down window. My problem is that i can not select one of the
Local ID's to continue my heirarchial form filtering. I can see them
in the dropdown window, but when i click on them i get an error sound
(Dong or Ding..whatever. .). here is my code for changing the SQL
statement of the Rowsourse.
Private Sub cbxState_AfterU pdate()
Dim strSQL As String
strSQL = "Select LcParty_ID, StParty_Id "
strSQL = strSQL & "From tblState_Local "
strSQL = strSQL & "Where StParty_ID = " & Me!cbxState.Val ue
Me!cbxLocal.Row Source = strSQL
Me!cbxLocal.Req uery
End Sub

I used this same format for changing the rowsource of the Accounts
combo box after updating the Local combo box.

I also used the Form wizard to select the specific fields from the
tables which would allow for the heirarchy to work ( so i think??)
Here is the record source for the form

SELECT tblState.StPart y_nm, tblState_Local. StParty_ID,
tblLocal_Acct.L ocal_ID, tblLocal_Acct.L cParty_nm,
tblLocal_Acct.A ccount
FROM (tblState INNER JOIN tblState_Local ON tblState.StPart y_ID =
tblState_Local. StParty_ID) INNER JOIN tblLocal_Acct ON
tblState_Local. LcParty_ID = tblLocal_Acct.L cParty_ID;
How do i get my combo boxes to work? should they be unbound? should
i be passing the combo boxes with multiple columns? any help would be
much appreciated. thank you in advance.
Nov 13 '05 #1
2 5439
I've done something similar before, and I may be a bit rusty (I don't
have that DB here, I'll have to send a code snippet tomorrow), but
it's something like this pseudo-code:

The first [I'll use cBox as a shortcut for comboBox] cBox I had was
bound, and all others, cascading down like you want, were unbound. (at
least, this is what I think I recall)

1st cBox, 2 columns, first column hidden with ID, the other column to
show data that the user selects (don't try to have the user memorize
ID values!) Mine was set with no default choice, so the user had to
pick something -- but it may not matter as long as the form requeries
the second cBox based on a default value when the form is opened. This
will also need an OnChange event to requery the 2nd cBox values when a
choice is made.

2nd cBox, unbound, also has 2 columns, with the second column having
selectable data too. This one, however, will have its RowSource
property set with a [join] query that has a WHERE clause equal to the
1st cBox's ID value.
This will need the OnChange event also to requery the 3rd cBox values
after a choice is made.

3rd cBox, unbound, again 2 columns, the second column with data...
....needs the RowSource set with a query with a WHERE clause equal to
the 2nd box... needs the OnChange event...

Do you see the 'water-fall' yet? Let me know if you need more info. or
clarification, and I'll post the code snippet tomorrow (if I can find
it, that is!) -- I'll let you know either way.

-PT

sp******@hotmai l.com (Sean) wrote in message news:<2d******* *************** ***@posting.goo gle.com>...
Greetings all,

I am attempting to make a form that will filter through several tables
that
(I believe) have refretial integrity. I am pulling data from several
tables into the form and i would like to eventually be able to filter
down through the
tables untill i can reach one unique record. I am creating a datbase
to keep track of registered accounts for a stae program. Each
account is registered into the program through a two diferent parties,
local and state wide. Accounts are unique. State wide parties are
unique. Local parties register accounts with either uniqie IDs or
aggregate Accounts under a unique ID. Each Local party may have one or
many names, which may or may not be unique for each account. Each
account registers information once a season (or more, sometimes people
resubmit becuase they want to make changes on their data). to enforce
refrential integrity, there is a multiple primary key index placed on
the accounts based on Dem_Y, Dem_S, Account, and Sub_date.
My tables look like this:

tblState;
StParty_ID (primary)
StParty_nm
...info

tblState_Local
LcParty_Id (primary)
StParty_ID (foreign key)
...info

tblLocal_Acct
LcParty_ID (foreign key)
LcParty_nm
Account (Primary key)

tblAccount_Dem_ Y_S_Sub
Dem_y (multi- field index primary key)
Dem_S (multi- field index primary key)
Account (multi- field index primary key)
Sub_date (multi- field index primary key)
...unique data
The refrential integrity is working fine, i know this because there is
excellent heirarchy through tblState. i can "tree" down to the unique
data for an account perfectly. I would like to create a form, where i
can use combo boxes to navigate through the hierarchy as easy as i can
do in datasheet view of tblState. I am currently using one form with
several combo boxes which use the After update event to change the
rowsource of the next combo box that would correspond to the heirarchy
of the tables.

I can select a unique State party ID, and a label box will display the
State party's name. I have succesfully limited the rowsource of the
Local Party ID combo box so that it will only display the local IDs
that are registered through the selected State party_ID when i click
the drop down window. My problem is that i can not select one of the
Local ID's to continue my heirarchial form filtering. I can see them
in the dropdown window, but when i click on them i get an error sound
(Dong or Ding..whatever. .). here is my code for changing the SQL
statement of the Rowsourse.
Private Sub cbxState_AfterU pdate()
Dim strSQL As String
strSQL = "Select LcParty_ID, StParty_Id "
strSQL = strSQL & "From tblState_Local "
strSQL = strSQL & "Where StParty_ID = " & Me!cbxState.Val ue
Me!cbxLocal.Row Source = strSQL
Me!cbxLocal.Req uery
End Sub

I used this same format for changing the rowsource of the Accounts
combo box after updating the Local combo box.

I also used the Form wizard to select the specific fields from the
tables which would allow for the heirarchy to work ( so i think??)
Here is the record source for the form

SELECT tblState.StPart y_nm, tblState_Local. StParty_ID,
tblLocal_Acct.L ocal_ID, tblLocal_Acct.L cParty_nm,
tblLocal_Acct.A ccount
FROM (tblState INNER JOIN tblState_Local ON tblState.StPart y_ID =
tblState_Local. StParty_ID) INNER JOIN tblLocal_Acct ON
tblState_Local. LcParty_ID = tblLocal_Acct.L cParty_ID;
How do i get my combo boxes to work? should they be unbound? should
i be passing the combo boxes with multiple columns? any help would be
much appreciated. thank you in advance.

Nov 13 '05 #2
Thank you for your speedy reply. I was working on it late into the day
yesterday, and i figured out a way using unbound comboboxes like you
said. Thanks again. Now i need to go figure out more about access.
you were a good help. I used pretty much the same psuedocode that you
did, except minor adjustments due to my table construction. thanks a
lot.


pt******@spsu.e du (PaulT) wrote in message news:<81******* *************** **@posting.goog le.com>...
I've done something similar before, and I may be a bit rusty (I don't
have that DB here, I'll have to send a code snippet tomorrow), but
it's something like this pseudo-code:

The first [I'll use cBox as a shortcut for comboBox] cBox I had was
bound, and all others, cascading down like you want, were unbound. (at
least, this is what I think I recall)

1st cBox, 2 columns, first column hidden with ID, the other column to
show data that the user selects (don't try to have the user memorize
ID values!) Mine was set with no default choice, so the user had to
pick something -- but it may not matter as long as the form requeries
the second cBox based on a default value when the form is opened. This
will also need an OnChange event to requery the 2nd cBox values when a
choice is made.

2nd cBox, unbound, also has 2 columns, with the second column having
selectable data too. This one, however, will have its RowSource
property set with a [join] query that has a WHERE clause equal to the
1st cBox's ID value.
This will need the OnChange event also to requery the 3rd cBox values
after a choice is made.

3rd cBox, unbound, again 2 columns, the second column with data...
...needs the RowSource set with a query with a WHERE clause equal to
the 2nd box... needs the OnChange event...

Do you see the 'water-fall' yet? Let me know if you need more info. or
clarification, and I'll post the code snippet tomorrow (if I can find
it, that is!) -- I'll let you know either way.

-PT

sp******@hotmai l.com (Sean) wrote in message news:<2d******* *************** ***@posting.goo gle.com>...
Greetings all,

I am attempting to make a form that will filter through several tables
that
(I believe) have refretial integrity. I am pulling data from several
tables into the form and i would like to eventually be able to filter
down through the
tables untill i can reach one unique record. I am creating a datbase
to keep track of registered accounts for a stae program. Each
account is registered into the program through a two diferent parties,
local and state wide. Accounts are unique. State wide parties are
unique. Local parties register accounts with either uniqie IDs or
aggregate Accounts under a unique ID. Each Local party may have one or
many names, which may or may not be unique for each account. Each
account registers information once a season (or more, sometimes people
resubmit becuase they want to make changes on their data). to enforce
refrential integrity, there is a multiple primary key index placed on
the accounts based on Dem_Y, Dem_S, Account, and Sub_date.
My tables look like this:

tblState;
StParty_ID (primary)
StParty_nm
...info

tblState_Local
LcParty_Id (primary)
StParty_ID (foreign key)
...info

tblLocal_Acct
LcParty_ID (foreign key)
LcParty_nm
Account (Primary key)

tblAccount_Dem_ Y_S_Sub
Dem_y (multi- field index primary key)
Dem_S (multi- field index primary key)
Account (multi- field index primary key)
Sub_date (multi- field index primary key)
...unique data
The refrential integrity is working fine, i know this because there is
excellent heirarchy through tblState. i can "tree" down to the unique
data for an account perfectly. I would like to create a form, where i
can use combo boxes to navigate through the hierarchy as easy as i can
do in datasheet view of tblState. I am currently using one form with
several combo boxes which use the After update event to change the
rowsource of the next combo box that would correspond to the heirarchy
of the tables.

I can select a unique State party ID, and a label box will display the
State party's name. I have succesfully limited the rowsource of the
Local Party ID combo box so that it will only display the local IDs
that are registered through the selected State party_ID when i click
the drop down window. My problem is that i can not select one of the
Local ID's to continue my heirarchial form filtering. I can see them
in the dropdown window, but when i click on them i get an error sound
(Dong or Ding..whatever. .). here is my code for changing the SQL
statement of the Rowsourse.
Private Sub cbxState_AfterU pdate()
Dim strSQL As String
strSQL = "Select LcParty_ID, StParty_Id "
strSQL = strSQL & "From tblState_Local "
strSQL = strSQL & "Where StParty_ID = " & Me!cbxState.Val ue
Me!cbxLocal.Row Source = strSQL
Me!cbxLocal.Req uery
End Sub

I used this same format for changing the rowsource of the Accounts
combo box after updating the Local combo box.

I also used the Form wizard to select the specific fields from the
tables which would allow for the heirarchy to work ( so i think??)
Here is the record source for the form

SELECT tblState.StPart y_nm, tblState_Local. StParty_ID,
tblLocal_Acct.L ocal_ID, tblLocal_Acct.L cParty_nm,
tblLocal_Acct.A ccount
FROM (tblState INNER JOIN tblState_Local ON tblState.StPart y_ID =
tblState_Local. StParty_ID) INNER JOIN tblLocal_Acct ON
tblState_Local. LcParty_ID = tblLocal_Acct.L cParty_ID;
How do i get my combo boxes to work? should they be unbound? should
i be passing the combo boxes with multiple columns? any help would be
much appreciated. thank you in advance.

Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3639
by: Dalan | last post by:
Okay, I have worked on this and then some, but cannot seem to crack it. So if someone can straighten my code out, or suggest a new approach, then I'm all ears. Here goes: I have two tables - one (tblReports) with all of the fields appearing on a report selection form (frmReports). The other one (tblGroup) is only use for the eight group types that I'm trying to use as a filter. The tblGroup is hooked to the tblReports Group field...
3
11095
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 have a primary form named TestResults, which is connected to data in a table named TestResults. There are basically two other tables that are related to the TestResults table (and the primary form) named Names-Normalized and SiteAddresses. The...
0
1730
by: Scott Loupin | last post by:
I've got two databases with similar data in them (WestSide and EastSide). I've set up two identical reports that is filtered by date and the client name. I'm using one form to do the filtering. Currently, I've used queries to combine the two client lists, so when I open the combo box, I have many names I need to scroll through until I find the one I want. How can I use filtering to only see clients from one database or the other,...
1
1864
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,
1
3862
by: hackerslacker | last post by:
I have an ordering form that use two combo boxes to filter down the records of a Products table. This worked fine with the after_Update of the first filtering the records and creating the rowsource of the second combo. The second combo's control source is the Product ID field (foriegn key) on the Orders table. Everything worked well until I decided to enhance the form by changing the second combo to an option group. Now I don't know...
2
2605
by: Dev1 | last post by:
All- I'm new to this forum and i've been working with acces for about 2 days now. I have a form in which I have two combo boxes and depending on what is selected on the first dropdown the second one gets populated accordingly, which is great! There are two problems; First the when I select the value from the second drop down the value in the first and second combo box go away!! Second when I remove the filter the values for all the...
4
27777
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...
3
1806
by: flymo | last post by:
Hello All, I've bee trying out access 2007 and have a weird issue and would like to see if I'm issing something really basic. I have a form based on a query, I create a combo to look for records on my form based on Org_ID. Wgen I create all is fine - I type e, combo goes to e and starts filtering etc just like normal......I save the form, close the database and when I re-open the combo doesn't respond to any input - I can select a...
5
5047
by: RHooper | last post by:
Hi, I'm new to Access, so I apologize if this question is trivial. I am trying to set-up a quick filter for users to define on a form bound to a table. I have a combo box called cboSearchCriteria from which users can select the field they wish to filter by and a text box called txtSearch into which they can specify criteria. The user then clicks a button to filter: Private Sub cmdFilter_Click() Dim strWhere As String ...
0
7992
marktang
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...
0
7904
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,...
0
8398
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8400
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8267
tracyyun
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...
1
5850
isladogs
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...
0
5438
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
2414
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1505
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.