Going from flash to Access...headache ;) combo box and populating....

[zozew]

Hi there all, as a fairly veteran flash designer/programmer i am now attempting my first Access project.

I'm attempting to create a jail management system to keep track of inmates, print different certificates (discharge, transfer, manifestation and so on)

I've done what i think is an okey job of the database structure (used to have a great PHP programmer for the backend before ) but i have some problems with the data fetching process and how to get certain thing to update when i want them to...

DB structure:

InmateProfile: table (InmateID, FirstName, etc...... all personal information about the inmate)

InmateCases: table (CaseNumberID, CrimeID, InmateID CaseNumber, CourtBranch, AllegedCrime, CaseStatus, ImposablePenalty)

InmateHearings: table (hearingID, caseNumberID, Hearings)

CrimesImpPenalties: table (CrimesID, Crime, ImposablePenalty)


Form structure:

I have a form InmateProfileForm (main form for inmate information input)
I also created a InmateCases subform and InmateHearings subform in InmatesProfileForm. InmateCases table is linked to InmateHearings table, its displayed as one data sheet for the InmatesCases table and one data sheet for the InmateHearings so you can input the information CaseNumber, CourtBranch, AllegedCrime, CaseStatus, ImposablePenalty and for each InmateCase record you can add multiple Hearings in the InmateHearings subForm

and it works great!

Question:
In my InmatesCases subFrom ( view as datasheet) the AllegedCrime you choose from a field combo box who gets its values from a look up from the CrimesImpPenalty table....now when i choose a crime from the combo box i would like the field ImposablePenalty in the same table as AllegedCrime (InmateCases table) to update with the corresponding ImposablePenalty field from the CrimesImpPenalty table. Because one crime has only one imposable penalty as from the CrimeImpPenalty table.

As you input the information in the inmateCases subform (view datasheet) CaseNumber, CourtBranch etc its all nice and logical but then you come to AllegedCrimes combo box, you choose a crime then the next field is ImposablePenalty i'd love to get it to automaticaly get the value from the previous field or maybe just put a text box on the main inmatesProfileForm displaying the ImposablePenalty value. But it would have to update when you click on the different caseNumbers in the InmateCases subForm just like the InmateHearings subForm do....


Sorry for allt the lengthy explenations...but more info is better i hope...

thanks for all help..

Zozew : Access Newbeee...

 

[John Big Booty]

You could populate your combo with a query that collects all the AllegedCrime(s) with their corresponding ImposablePenalty. You can then populate an unbound text box with;

=ComboName.Column([B][COLOR="Red"]x[/COLOR][/B])

Where x represents the column that holds ImposablePenalty. Remember that the columns in a combo and a list box are numbered from zero up, the first column (0) is usually hidden and bound to the control source.

 

[zozew]

OK sounds graet i will try it, ill let you know how it goes....

thx
Z