form design for end users - best practices 2

[misscrf]

I have a main form. Then I have 3 pages on a tab control ( 4 if the type of entry validates that is is to be shown) Each page has a subform. The subforms can be either single or continuous, I think I am still deciding what I want to lock down this entry form to make entry easy.

The form is for entering applicants for jobs to our company. The main form is just for name and email and candidate type. If the candidate type is one of a few types a page becomes visible for specific info, otherwise, there are just 3 pages. 1 is for addresses. An applicant can have multiple addresses (home, work, school etc), another is for phone numbers. An applicant can have multiple phone numbers.

The last page is for the applications for that candidate. A candidate could apply more than once, so they could have more than one application. This will get tricky because the application form has a subform, for the activities relevant to that application. This is like a logical history. Activity - interview scheduled, interview held, offer extended, rejection letter sent, etc.

Anyway, I am looking for advice on many issues. How to best control the subforms, how to make it so that when the user is tabbing it goes from the last field on one subform to the first field on the next subform, and more. My hestitation is that I think I need to make sure that there aren't more address, phone numbers, applications or activities needed to be added at that time. My next hesitation is that I don't want to be stopping the user with messages every 5 seconds.

I have screenshots available to send out, but there is no way to post them here. I can also email the db if need be.

Thanks.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[TheAceMan1]

OK misscrf . . . . .

Well! . . . Well! . . . Well! . . . . focus all to yourself! . . . how about that! ;-)

You pretty much have the idea of what you need (and I wouldn't change it). But before anything else, [purple]your tables & relationships have to be right[/purple] ([blue]or your already headed for trouble[/blue]), so lets start there:
[ol][li]You should have [blue]6 tables[/blue]:
[ol a][li][blue]Applicants[/blue][/li]
[li][blue]Addresses[/blue][/li]
[li][blue]PhoneNumbers[/blue][/li]
[li][blue]Applications[/blue][/li]
[li][blue]Activities[/blue][/li]
[li][blue]TypeCandidate[/blue][/li][/ol][/li]
[li]Each table should have a [purple]PrimaryKey[/purple][/li]
[li]There will be a [purple]One to Many Relationship[/purple] between [blue]Applicants[/blue] and the following tables. As such these tables should have a [purple]Foreignkey Field[purple] with same [blue]Name & DataType[/blue] as the [blue]PrimaryKey of Applicants (if PrimaryKey is Long Integer, so is ForeignKey):[/blue]
[ol a][li][blue]Addresses[/blue][/li]
[li][blue]PhoneNumbers[/blue][/li]
[li][blue]Applications[/blue][/li]
[li][blue]TypeCandidate[/blue][/li][/ol][/li]
[li]An additional [purple]One to Many Relationship[/purple] between Applications & Activities. Activities should have a [purple]Foreignkey Field[/purple] with same [blue]Name & DataType[/blue] as the [purple]PrimaryKey[/purple] of [blue]Applications[/blue] (if PrimaryKey is Long Integer, so is ForeignKey).[/li]
[li]When you make you [blue]relationships[/blue] (in the relationships window) be sure you put a checkmark in:
[ol a][li][blue]Enforce Referential Integrity[/blue][/li]
[li][blue]Cascade Update Related Fields[/blue][/li]
[li][blue]Cascade Delete Related Fields[/blue][/li][/ol][/li]
[li]All you subforms should be [blue]linked[/blue] via [purple]Master/Child Links[/purple] by their respective [purple]PrimaryKey/ForeignKey[/purple] fields.[/li]
[li]If everything is done correctly, [blue]change the applicant in the mainform and all subforms tag along![/blue][/li][/ol]

Now there's the issue of the [blue]Tab Control[/purple]. Personally I wouldn't use it as [purple]it hides info the user needs to see[/purple], and [purple]it takes those extra clicks to view the data[/purple]. You can show/structure all your subforms in an [blue]all encompassing view[/blue] without it (no chance of missing data to fill in here). Wether you use the Tab Control is your choice, but if ya really wanna know, watch your user handle the database then ask them! I'd bet you get a minimum 75% No . . . .

With the present setup you can add data at any time (forgetting shouldn't be a problem!). Only thing you need to make sure of is: [purple]Parent Forms get data before subForms![/purple]

[blue]There's more I could tell ya but lets see how this works![/blue]

See Ya! . . . . . .

 

[misscrf]

Ok, I see that table definitions are need here. I have created my table list and table details. I put it into html and posted to my geocities site.

Please have a look and let me know if you are comfortable discussing form set up or if you disagree with any of my table setup.

http://www.geocities.com/misscrf/tabledefinitions.html

That is the link to my table definitions.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[TheAceMan1]

misscrf . . . .

Thats a bit much too go through on Tek-Tips, but what do you mean by [blue]Foreign identifier?[/blue]

See Ya! . . . . . .

 

[misscrf]

fk, a look up to another table's pk.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[TheAceMan1]

misscrf . . . . .

If you could . . . . in your [blue]Is PK[/blue] column:
[ol][li] If its a [purple]PrimaryKey[/purple] enter [purple]PK[/purple].[/li]
[li]If its the [purple]ForeignKey[/purple] to a [blue]BaseTable[/blue] enter [purple]FK[/purple].[/li]
[li]If its the [purple]ForeignKey[/purple] to a [blue]LookUpTable[/blue] enter [purple]LT[/purple].[/li]
[li]All others blank![/li][/ol]

See Ya! . . . . . .

 

[misscrf]

OK. I made those changes.

http://www.geocities.com/misscrf/tabledefinitions.html

Hope that helps.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[mph1]

Please excuse my intrusion. Without going into detail, which AceMan is more than capable, a thought.

You indicated the following:

The form is for entering applicants for jobs to our company. ........ A candidate could apply more than once, so they could have more than one application.

Since you can have an applicant apply more than once, you may want to put a lookup filed to see when etc.

Good Luck!

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[TheAceMan1]

How are ya mph1 . . . . . .

Stop by anytime! Your input is always welcome. ;-)

If you check, this is covered by a [blue]one to many[/blue] relationship between [blue]Applicants[/blue] & [blue]Applications[/blue] . . .

See Ya! . . . . . .

 

[misscrf]

Yep, you got it Ace. Thank you very much for going through all of this.

I am trying to create a very solid foundation and understanding for this application.

It is a candidate database for tracking applicants to the lawfirm I work for. I have created this based on what the HR department has indicated as a field that they want to enter data in for, and report on.

This is the main concept, as I have created it:

Candidate entry is a place to enter the main candidate info and specify their type, if the type qualifies as an attorney type, an attorney page will show with a subform for legal info.

This will include law school and bar admittance.

There will be tabs indicative to all candidates as well.
Each candidate will have a tab for their addresses, for which they can have multiple. A candidate can have a home residence, mailing, school, etc.

Next, there will be a Phone tab. Similar to addresses, a candidate may have a home phone, mobile, etc.

Last, there will be applications. A candidate may apply to us, simply sending their resume in, with no reference to a specific job lisiting. In a while, such as 6 months, this same candidate may apply again with a new resume but in reference to a job listing, and maybe through an agency.

For each application, there will be a history or multiple activities. A letter may be sent to the candidate, the candidate my call us 3 times in a week. We may schedule an interview, an offer may be extended, materials may be sent to the office that the job posting or candidate is applying for.

Reports will come later. I want to get the forms working well. I will work on getting screenshots posted now.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Here we go, Form screen shots:

http://www.geocities.com/misscrf/images/mainmenu.jpg

http://www.geocities.com/misscrf/images/mainentryformaddtabshows.jpg

http://www.geocities.com/misscrf/images/mainentrywith4thpage.jpg

http://www.geocities.com/misscrf/images/phonetab.jpg

http://www.geocities.com/misscrf/images/applicationandactivitytab.jpg

I can explain how they work so far if you have any questions.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[TheAceMan1]

misscrf . . . . .

A shot of the relationships window would be great!

See Ya! . . . . . .

 

[misscrf]

u got it:

http://www.geocities.com/misscrf/images/tablerelationships.jpg

Let me know if it would help to move the tables around. I tried to make the tables show in a logical view...

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[mph1]

Hi AceMan 1,

My reason for commenting about a search was to give misscrf something to think about.

I also appreciate your comment. Sometimes I think my grandchildren know more about Access than I do. It seems to come so easily to them.

Maurie

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[misscrf]

One other thing to explain is that I plan to implement a search form for the entry form, which will allow users to search on none, any or all fields. then from the results, they can select the record to go to, and it closes the search form bringing them back to the candidate form at that candidate...

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[TheAceMan1]

misscrf . . . . .

One thing at a time . . . . .

[blue]Good Job![/blue] on [blue]normalizing you tables[/blue] (appears to be [purple]3rd normal form[/purple] as we call it). Relationships appear to integrated well also. You really don't need to touch anything.

However, here's some insight on LookUp tables. The Idea here is really to make your [purple]Relationships Window[/purple] easier to decipher on 1st view, [purple]by eliinating unecessary LookUp Tables[/purple] (besides saving table resources). Many times you'll haveto go back and look at it. Each time (if there's enough tables) you'll have to focus, concentrate, sort out, and decipher what you have and are looking for. Imagine what your [purple]Relationships Window[/purple] would look like when most (if not all) of your LookUp Tables were gone! Bear in mind [blue]this is a design issue mainly geared toward making things easier for yourself . . . so its an option you may/may not adopt.[/blue] Me! . . . . personally . . . . I'd rather sit down and have that bud, than sit down and pull my hair out! To explain:

If a lookup table is going to have say . . . 10 or less Static items, such as your Salutations Table (with Mr., Miss, Mrs.) you can reduce [blue]table resources[/blue] your using and [blue]eliminate those tables[/blue] from the relationships window. You do this as follows:
[ol][li]Remove the Relationship & Table from the relationships window.[/li]
[li]Rename the field the lookup table was linked to, to something more suitable (if necessary).[/li]
[li]Open the form where its used in design view. Remove the proper field from the details area and make sure the new field is included in the RecordSource of the form.[/li]
[li]Make a Combobox using the [purple]I'll type in vaules option[/purple] (enter them in order), and put it in place where the old control was.[/li][/ol]
I believe you get the Idea! If you decide to try this (and you should) . . . don't get crazy! Be discrete about it.

Wether you keep the tables or not, it always good to have a printout of the relationships window handy . . .

See Ya! . . . . . .

 

[misscrf]

I agree that this might work, but I think I may end up dealing with users that will want the control to add and delete a type as they wish. The aim is to make this completely user controlled with out them seeing that they are completely controlled.

ie. I have taken all of the maintenance buttons off of the entry form. Why? Because each combo has code in it. Let me give you an example, as they are all the same:

Private Sub cmbSalutation_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cmbSalutation_NotInList
Dim Msg, Style, Title
Msg = "The salutation type you entered is not in the Salutation list, would you like to add it now?"
Style = vbYesNo
Title = "Type or listing must be maintained"
Response = MsgBox(Msg, Style, Title)


If Response = vbYes Then
Me.Undo
DoCmd.OpenForm "frmSalutationType", acNormal, , , , acDialog
Me.Requery
Me.cmbSalutation.Requery

End If
Exit_cmbSalutation_NotInList:
Exit Sub

Err_cmbSalutation_NotInList:
MsgBox Err.Description
Resume Exit_cmbSalutation_NotInList
End Sub

This says if its not in the list, ask them if they want to add it, if yes open the maintenance form, if not, then the cursor goes back to the combo with the values shown.

I put a button on the main menu to open a maintenance form, and now that is a form with every button for every maintenance form. I think I need to do it this way, so that the user feels like they have control over all of the types and lists. I get that impression from the group.
See what I mean?


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I am having a specific issue, so I thought I would post it.

Since I moved that around, it made a complication for me, and I am trying to figure out how to handle it. The maintenance forms can open from 2 places now. They can open as response to notinlist code but they can also go from the main menu, to the main maintenance form, and open from there.

Here is the thing. If a user answers yes to not in list, the maintenance form for that combo opens, it has a command button to close. In this instance, the entry form stays open in the background. For the maintenance form, the main maintenance form with all of the buttons closes. It does not stay in the background, because that would look messy.

I thought of putting 2 command buttons on the maintenance forms. Then on the notinlist code, I would hide the button that I dont want to get clicked (the close button that opens the maintenance menu ) and keeps the other one visible. The I would do the same code to the command buttons on the maintenance menu form. I would code the on open of each specific maintenace form to hide the command button that is really for the notinlist exiting, and keep the close button for the maintenance form exiting showing.

I will wait for someone to tell me that I am stupid and give me a better method for this...


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[TheAceMan1]

misscrf . . . . .

My last post was just an option as I said. Now:

[blue]Since I moved that around, . . .[/blue]

Move What?

See Ya! . . . . . .

 

[misscrf]

I have taken all of the maintenance buttons off of the entry form" and "I put a button on the main menu to open a maintenance form, and now that is a form with every button for every maintenance form."

misscrf

It is never too late to become what you could have been ~ George Eliot