Need Help With Cascading Combo Boxes

[emassey0411]

Hello everybody. I'm new to this forum however I have used others and I am stuck. I have some coding experience and do really well with hands on learning or reviewing what others have coded that will work with what I am trying to do. However, I did that and I'm still not getting what I need.

I am doing a somewhat basic database for my mom's church. They do work with prisons all over the states by mentoring to the prisoners and she asked me to convert the Excel spreadsheets that they use (and take forever to do) to a database. I have 4 main tables that I want to pull information in from - Inmates, Mentors, Prisons & Inmate Verification.

I am trying to do the cascading boxes for the Inmates Verifications that need to be done. So based on the State the prison is in and based on the type of prison (Federal, State, County) I want to eventually bring in the website hyperlink so the users can just click on that to do their verification. I could also match on the Prison name but will still need Prison Type and County to be part of the requirement.

I found one post on this site that I copied exactly (using my tables and fields) but all I get is a blank drop down box and I don't know what I'm doing wrong (probably missing a comma or something ).

So what I want is, when the user selects the prison it populates the address. Next they will select if it is a Federal, State or County prison/jail and based on that selection and the State in the address I need County to populate with the data that is associated (and yes county can be blank if State is selected - it is like that in the table). When they continue they will then need to select the Name of the office that the verification came from (example: Maricopa County Sheriff Office) which will then populate the hyperlink (in the final field) that they will select to do the actual verification. I stuck at County.

Right now my Combo box RowSource is:

SELECT [Prison Info].[County] FROM [Prison Info] WHERE (((Prison Info.Type}=Forms!Inmates!Type) AND ((Prison Info.State)=Forms!Inmates!State))) GROUP BY [Prison Info].[County]

also attach is the database that I am working on. Any help somebody can give me will be greatly appreciated. I know I can make it really simple for the users but I do want to this to give them some time back and not take 8 hours to do what they do - as volunteers.

Here is a screen shot of the form with the cascading fields highlighted



Mentor Database.zip

Again, thank you for any help you can give me. It's been a while since I've had to work with access.

[pbaldy]

You've got a curly brace in there, plus the query name has to be bracketed because of the inadvisable space:

SELECT [Prison Info].[County] FROM [Prison Info] WHERE [Prison Info].Type=Forms!Inmates!Type AND [Prison Info].State=Forms!Inmates!State GROUP BY [Prison Info].[County]

[Welshgasman]

I hope that is not real data?

[emassey0411]

Ok - got it to work but now it won't "reset" or clear when I change the Prison name which changes the state. This should change the "County" but isn't. I have the [Event Procedure] set on After Update. Does it need to be set on Before Update too?

[pbaldy]

The after update event of which control? It would need to be the prison name, the update events of the state control won't fire if the value isn't changed by the user.

[emassey0411]

I put the After Update event on the Prision Name and Prison Type since those can be changed by the user but the County still isn't updating when I make changes to either the Prison Name or Prison Type

[pbaldy]

What's the code you have there? Or can you attach the current db with fake data?

[emassey0411]

Here is the database with fake inmates but real prisons

[pbaldy]

You have it behind a control that doesn't exist. It works behind the combo where you select prisons:

Private Sub p_list1_AfterUpdate()
  cmbCounty.Requery
End Sub

[emassey0411]

that worked. Thank you

one more question - with the Prisons list I'm getting duplicate names because of the counties. However I just want to show the names only once for them to select. I did a GROUP BY and then my list went blank. Any suggestions? you need to scroll down the list till you get to AZ or CA and you will see the duplicates. Thanks again for your help

[emassey0411]

Is there a way to get rid of all this space in the combo box. Because I'm bringing in the prison address but not showing it with the name combo box I get a LOT of blank space that I'd like to have not show.
Thank you

[pbaldy]

I don't understand the relationship between the prisons table and the inmate verification table (or the purpose of the second).

The list width property controls the width of the dropped down combo. You have it at 10".

[emassey0411]

Prison table has the list of prisons and the Inmate Verification table has the website they need to go to so they can verify the inmates information. I joined them in the Prison Info query joining on the State Abbreviation so that when they populate the prison they will get a choice of what the county that prison is in so it will bring in the Office name and the website. That way they just need to click the link in the data entry form to do their verifications. I was under the impression that to do the cascading of multiple combo boxes you needed to have it in a query. Is that not correct?

example: Prison=Apache Co Jail, Type=County, and County=MCSO then Verification Office=County Sheriff Office and the website will pull into the form

Thank you for the help on the drop down box

[pbaldy]

I would expect the county field to be in the prison table, just like all the other address info. Maybe I'm not understanding the data though. I've got to dive back into a project so I may be dark for a while, but there are plenty of people here smarter than I am who will probably understand where you're headed better than I.

[ssanfu]

Welcome to the forum.

Having looked at your dB, I have some questions and thoughts.

I am trying to do the cascading boxes for the Inmates Verifications that need to be done. So based on the State the prison is in and based on the type of prison (Federal, State, County) I want to eventually bring in the website hyperlink so the users can just click on that to do their verification. I could also match on the Prison name but will still need Prison Type and County to be part of the requirement.

What does this mean? Or, how does happen?


Q: Can one inmate be in prison multiple times?
Q: Can/does an inmate get transferred to different prisons?
Q: Can an inmate have more than one mentor? At the same time or over time that you track?



What are these??
NIV-E
LASB-E
RNA-E
NIV-S
LASB-S
RNA-S

VL



I would suggest you stop and review your table designs and relationships.
This is not the correct way to link tables. Should be from PK to FK.



In the Inmate table, there are a lot of fields that should not be in the table. Since it is an Inmate table, only the Inmate attributes should be in the table.
I used the first dB you posted - the Inmate table does not have the Mentor field.


These apply (are attributes) to an Inmate:
InmateID_PK
GenderID_FK
FirstName
MiddleName
LastName
Suffix
Age <<-- Note: I would have DOB and calculate the age when needed.

ADC* (I changes to "ADC_Number" because of the hash) Does this number change each time a person goes to prison??


PrisonName
Unit
PrisonAddress <<-- Why is the prison info being stored in the Inmate table??
City <<-- Why is the prison info being stored in the Inmate table??
State <<-- Why is the prison info being stored in the Inmate table??
Zip <<-- Why is the prison info being stored in the Inmate table??
Verified
PrisonType <<-- Why is the prison info being stored in the Inmate table??
County <<-- Why is the prison info being stored in the Inmate table??
VerifiedAt
VerificationSite
VL

The following are not an attribute of a person/inmate - they belong in a different table:
ParoleEligibilityDate
ReleaseDate
ABA_Form
NIV-E <<-- ??
LASB-E <<-- ??
RNA-E <<-- ??
NIV-S <<-- ??
LASB-S<<-- ??
RNA-S<<-- ??
Description <<--- of What??
ProcessedBy
OrderDate
OrderNumber
PostmarkDate
Comment
NotesToOffice
NotesToMentor




I went through your tables and removed all spaces in object names. I replaces special characters ("#" and "-" ) and I renamed a field "Type". "Type" is a reserved word in Access and shouldn't be used for object names. Plus it is not very descriptive.
All caps is considered SHOUTING - I changed the field names to proper case.

I renamed the tables,query and form (see arrows)
All tables have a PK field that is an autonumber type AND is not "ID"!





These are my thoughts and observations.... and how I would design the dB, even I know very, very little about your objective.

Good luck with your project......