Form structure and layout

[zozew]

Hi guys,

Ive been on the VBA thread, DB structure thread and gotten invaluable help...now that im closer to actually restarting my development i need some guidance in how to best use Access forms and what they can do. I come from a Flash developer background and i could basically do as i pleased with design elements there but i understand Access has a different structure.

Basically I'm building a DB for a jail. I already built one but i got advised to revise to better resemble the real life of the movements inside a jail.

Well i have come so far as to set up all relations between my tables, now though as I'm used to working quite linear (one master table with a few one to many relations tables) I'm a little lost how to create what i want to display and what to base the forms on as before i always just based the masterForm on the masterTable and a few subforms for the linked tables.

Quick explanation of how i want it to be*:

*Its basically a HR database but instead of employees its inmates.


A person can be committed and released multiple times, he is committed as an Inmate. A part from a persons physical attributes (Height, weight etc) i track the inmates security classification (RDC, MIN, MED, MAX) that changes a few times normally and i track the squad the inmate belongs to as well as any other movements like hospital or Quarantine visits or being a jail Aide.

My problem now is how to make a lets call it master form where you can add/edit persons/inmates and also use it for lookups. The master form should display the Classification history as well as the squad history and any other fields im tracking (see relations for ""history tables")

im having trouble displaying these "history" tables correctly and at the same time be able to edit/add information in them.

I want the master form to display one record and its corresponding history tables as well. I have never had these many to many relationships with an intermittent table connecting the two tables before....now i have four??
And i want the master form to display the latest history record from each "history" table as security classification and squads are part of the booking process of an inmate

So to simplify all: how do you best display these "history" tables in a master form where they also can be edited ? (think HR personnel profile page )

Any advice here would be invaluable


Please see attached file for my table relations..

 

[spikepl]

Just to clarify: a person can be an inmate multiple times, but each times carries it's own four histories, independent from the prior histories of that person as previous inmate?

I would have a form displaying the person's personal details bound to tblPerson

On that form I would have a subform showing Inmate periods bound to tblInmate, to select an inmate period

I would then have a subform for each of the 4 histories, linked such that when you select inmate period the corresponding histories turn up.

To set it all up, setup
- form to show personla data in form view
- subform to show inmate periods in datasheet view
- subform to show one hisotry in datasheet view

drag the two subforms onto the main form

to link these two you need to fiddle with a hidden field: http://www.fmsinc.com/microsoftaccess/forms/synchronize/linkedsubforms.asp

same method for remaining 3 histories

 

[zozew]

Just to clarify: a person can be an inmate multiple times, but each times carries it's own four histories, independent from the prior histories of that person as previous inmate?

Yes that is correct, just like in real life you can be committed again after you have been released. And each time an inmate is "in jail" they have different histories, movements and so on.. So the the multiple inmate function i have added will also help with the booking as most personal information is already in the system.

I've been playing around a lot trying to make it function and look ok as well. From the users point of view they want to easily book, look up information and get some stats like how many inmates per squad etc etc...

Im going to try and see if i can make it work with the person table as base...i was thinking more of using the inmate table (Active inmates) as base as the jail uses that information daily..maybe im wrong..? Thought i just filter out all none active inmates as there can only be one active inmate/ person (obviously hehe) then if you like to see/search the incarceration history you click and open/tab to it for that person...?? and then use basically the same structure you suggested with the sub forms..

 

[zozew]

Update: Ive uploaded a DB with a few of the subforms working

...although im having a bit of a UI problem Ive added text boxes on the main form that show information from the subForms...very simple... I would somehow want to show the latest records information from some subform on the main form so you dont "have" to look in each subform for the latest information for example the Security Classification and the Squad. As i mentioned I have added them now but not the right way...

If you get a chance download the DB and run the form frmMaster2 its the latest update i have made..

This forms recordsource is now a mix...maybe im totally wrong...but it seems to work..

thx for any input

*update: i uploaded a new DB where most things work just that it feels messy...

 

[spikepl]

You've done quite well, me thinks!

Functionality-wise I do not see any errors, so if you have any specific gripe then please be specific about it.

Layout-wise I agree it looks a bit messy, but that is a matter of personal taste. The subforms do not in principle need to be hidden under Tabs - but having them in the open would also lead to a messy screen.

Beautify it at your convenience.

 

[zozew]

You've done quite well, me thinks!

Thanks

Well i think i mentioned before I wanted to display the last record of the Security Class and Squad names in the main record, right now its just a textbox with a function pointing to the subform fields...if i click in another record in the subform it changes to that value ...so not very good hehe rather i would like to have a way of telling the textbox in the main form to always show the last records value for those fields...am i making any sense??

Maybe..is there a "cool way" of setting fields (squad, sec class) by a combobox in the main field and they add a record to the subforms and maybe you click on a button to open the history subforms...? maybe a small popup...??

the texboxes im talking about are the redlabeled ones Class temp and squad temp..

 

[zozew]

*update

I think i have found a solution to my textbox displaying a field of the last records..i found this

DlookUp("Name", "Employess", "IDEmployee=" & Dmax("IDEmployee", "Employess", "") )

So im now trying to use DlookUp with the Dmax to see a specific field of the last record in my class history table for a specific inmateID...hmmm having trouble syntaxing the Dmax part...trying though hehe


**update

I've got this working giving me the record with the most recent date for that inmate:

DMax("reclassificationDate", "tblClassificationHistory", "inmateID =" & Forms!frmMaster2!inmateID.Value)

But.... i need the classificationHistoryID or the classificationID from that record not the date...any ideas?

 

[zozew]

***Update

I think i have solved it...not sure how well though...please look at the onCurrent event of the form frmMaster2 to see how i solved the above problem i described..

attached is a new version of my DB