Filtering form by combo box

[Sean]

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_AfterUpdate()
Dim strSQL As String
strSQL = "Select LcParty_ID, StParty_Id "
strSQL = strSQL & "From tblState_Local "
strSQL = strSQL & "Where StParty_ID = " & Me!cbxState.Value
Me!cbxLocal.RowSource = strSQL
Me!cbxLocal.Requery
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.StParty_nm, tblState_Local.StParty_ID,
tblLocal_Acct.Local_ID, tblLocal_Acct.LcParty_nm,
tblLocal_Acct.Account
FROM (tblState INNER JOIN tblState_Local ON tblState.StParty_ID =
tblState_Local.StParty_ID) INNER JOIN tblLocal_Acct ON
tblState_Local.LcParty_ID = tblLocal_Acct.LcParty_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.

[PaulT]

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******@hotmail.com (Sean) wrote in message news:<2d*************************@posting.google.c om>...

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_AfterUpdate()
Dim strSQL As String
strSQL = "Select LcParty_ID, StParty_Id "
strSQL = strSQL & "From tblState_Local "
strSQL = strSQL & "Where StParty_ID = " & Me!cbxState.Value
Me!cbxLocal.RowSource = strSQL
Me!cbxLocal.Requery
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.StParty_nm, tblState_Local.StParty_ID,
tblLocal_Acct.Local_ID, tblLocal_Acct.LcParty_nm,
tblLocal_Acct.Account
FROM (tblState INNER JOIN tblState_Local ON tblState.StParty_ID =
tblState_Local.StParty_ID) INNER JOIN tblLocal_Acct ON
tblState_Local.LcParty_ID = tblLocal_Acct.LcParty_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.

[Sean]

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.edu (PaulT) wrote in message news:<81************************@posting.google.co m>...

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******@hotmail.com (Sean) wrote in message news:<2d*************************@posting.google.c om>...

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_AfterUpdate()
Dim strSQL As String
strSQL = "Select LcParty_ID, StParty_Id "
strSQL = strSQL & "From tblState_Local "
strSQL = strSQL & "Where StParty_ID = " & Me!cbxState.Value
Me!cbxLocal.RowSource = strSQL
Me!cbxLocal.Requery
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.StParty_nm, tblState_Local.StParty_ID,
tblLocal_Acct.Local_ID, tblLocal_Acct.LcParty_nm,
tblLocal_Acct.Account
FROM (tblState INNER JOIN tblState_Local ON tblState.StParty_ID =
tblState_Local.StParty_ID) INNER JOIN tblLocal_Acct ON
tblState_Local.LcParty_ID = tblLocal_Acct.LcParty_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.