Subforms

[AndyPandy]

Im trying to design a database and think subforms make a form look untidy, so do you need to have subforms or if i was to use web database instead would subforms go out the window here what i have so far all on one form but the gryed out boxes do not auto populate.

 

[GinaWhipp]

Well, I don't see any Subforms on your Forms, so can't give an opinion based on it. I can give an opinion on Subforms. Here are some Forms with one or more Subforms...

http://www.access-diva.com/ffe1.html
http://www.access-diva.com/bai1.html
http://www.access-diva.com/al1.html

No ugliness, so IMHO, it depends on how you layout the Form. So, if you don't like them just don't use them.

Now, if you are referring to the Tab Control, again it's how you lay it out, in your case I would have probably elected to use Subforms and put the *History* section on a Command Button (seperate Form) or change the swap the *Notes* and *History* fields so Users have more room to enter notes.

As for your second issue, Combo Boxes, Text Boxes, etc... will not auto-populate if ENabled is set to *No*. As for the other Controls, what are they to auto-popuate to? And, based on what?

 

[AndyPandy]

basicaly the form i have created is based onn one form and various table no sub forms, the greyed out boxes when you click dispatch you fill in the dispatch form details and it autmaticaly shows them on the main form but are greyed out to stop you from altering them. Anyhow i keep getting told that you have to use subforms so i re-created the database with 3 subforms. could you take a look please.

I have a few problems. 1stly for testing purpose i have typed in information in the forms, that has appeared in the tables but when you go back to those forms some of the data is missing. mainly in the dispatch and histtory tab and the drop down boxes.

2ndly for each new job i need the datetime field to automaticaly store the date and time it was created and the date and time it wa dispatched.

Also the history notes box they are two fields , what i want to happen is when you enter the note you press enter key and it then transfers to the history tab with date and time and the note. etc

 

[gemma-the-husky]

screen real estate is valuable

how you manipulate it to best use this asset is a matter of taste. tab controls are very useful in this respect

a subform is a different concept. a form/subform enable you to view an entity, together with appropriate related entities. It's hard to see an alternative.

 

[GinaWhipp]

Well, I think there is an underlying problem, your Table structure.

CaseID does not belong in the Customers table, since a Customer can have more than one Case you just need a CustomerID field in table Cases.

If you only plan to enter one Case per Customer per Call then you only need a Subform to show old Cases. However, if you only want to enter one Case per Call you can do that with one Form.

I would use a Subform for Notes and History because one or more Notes caould be attached to one Case.

You cannot Link Form/Subform by the Autonumber field because that is not how they are linked in Relationships. Customer ID will never match up to Case ID because they are numbering independently and in conjunction with each other. As, I look I see you are using the ID field to Link everything and it just won't work.

I'm not sure what the Calls table is as initially I thought it was to handle calls received but the fields names make no sense. Also, you don't need CustomerID in that table as a Call should be attached to a Case which will be attached to a Customer. So, no need to have it there.

Case Notes should be in it's own table or do you really want everyone to type ALL the Notes into one field?

Once, you get the Tables and the Relationships worked out I believe that will solve some of you issues.

 

[AndyPandy]

Sorry Gina

The calls table should be call type.

So basicaly at the moment looking to enter 1 call per customer but sometime that customer may require a return visit so one customer can have many call or one call.

I am a little new to Database and relational

Show how would it be best to link the subforms or would you be so kind in correcting the mistake and ammending accordingly.

I would be most greatefull.

or explain write it down and i will follow your instructions

 

[GinaWhipp]

Hmm, I think you missed the point... First the Tables must be fixed before anything can be done with the Forms/Subforms. Think of it like building a house... you can't stat hanging the drapes until the foundation has been laid. And, without a proper foundation the house will fall, drapes and all.

So, I'm not going to *fix* the Forms/Subforms until the Tables have been adjusted. Now, would you like to take a crack at it?

 

[AndyPandy]

Can you advise as to what is wrong with the tables as i have been working on this for over a week lol and what I need to add/delete. I am all up for learning but I learn better through doing/following instructions.

 

[GinaWhipp]

Hmm, okay but the balance of the fields are up to you...

tblCustomers
cCustomerID (PK)
cCustomerName
etc...

tblCases
cCaseID (PK)
cCustomerID (FK - link to tblCustomers/cCustomerID)
cEmployeeID (FK - link ot tblEmployees/eEmployeeID)
cCase
cCategoryID (FK - link to tblCategories/cCategoryID)
cHot (Yes/No Data Type) Used to determine if this is an emergency call
etc...

tblCategories
cCategoryID (PK)
cCategory
cSortOrder
cActive

tblEmployees
eEmployeeID (PK)
eFirstName
eLastName
etc...

***This is the table that would store your Case History and Notes
tblActivities
aActivityID (PK)
aActivityTypeID (FK - link to tblActivityTypes/atActivityTypeID)
aCaseID (FK - link to tblCases/cCaseID)
aDate
aNotes
aCompleted

tblActivityTypes
atActivityTypeID (PK)
atActivityType (i.e. Call, Visit, etc...)
atSortOrder
atActive

While taking a closer look at your tables I also see you have used Reserved Words which is I name my field names the way I do. Your way will cause you problems later on. For more information see...

http://allenbrowne.com/AppIssueBadWord.html

For more information on Naming Conventions see...

http://www.granite.ab.ca/access/tablefieldnaming.htm
http://www.access-diva.com/d1.html

For more information on Relational Databases see...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

UtterAccess Newcomer's Reading List
http://www.utteraccess.com/forum/Newcomer-s-Reading-List-t1998783.html

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/

Other helpful tips…

Setting up a Model Database
http://www.access-diva.com/d11.html

My Database Standards...
http://regina-whipp.com/blog/?p=102

This should keep you busy for a few moments!

 

[AndyPandy]

The only one bit I do not get cCase il do the tables and then post them back here to see if they are write., some of them may change names but thank you.

 

[GinaWhipp]

Hmm, that should have been *cCaseTitle*. That's what I get for typing so fast!

No problem, glad to help!

 

[AndyPandy]

Ok Tables are done see attached. Just bit unfamilar with some of the table name you have used so i will describe the process for you encase you need to modify table design.

customer calls in and they do not have a contract so we take there name address details etc

Date and time of call is logged

Descripton of the fault is taken along with purchase order number if given and customer agrees to the non contracted call out charges.

We then assign the work or job to an engineer and verbaly dispatch it to the engineer.

we then fill in who it was dispatched to who dispatched it date time.

notes filled which populate a history field with what happened with the job etc.

i trying to create a better version of call tracker template in access with one form with subforms for one data enry form over all.

hope this helps forms up to you now pls.

 

[GinaWhipp]

Okay, I am reviewing now and I have some comments...

1. Doesn't the Activities table hold more than just Calls? So, the way I would handle it is...

Activity - Call from customer
Activity - Dispatch Engineer

Each Activity would be a seperate record. I would not try to combine everything into one record. Going to be hard to track turn around times, what's left open, etc... if I do it you way.

2. Contacts stored in the Customers table not a good idea. Suppose you have more than one Contact per Customer. Are you going to duplicate the Customer?

3. Spaces in Field names, very annoying when you have to start coding this database. You'll have to bracket those field names each and every time because Access doesn't know those words belong together. I don't know a Developer who doesn't use CamelCase.

4. Tracking History in one long Memo field is never a good idea. You could end up with A LOT of notes and you'll have to read thru everything every time to find what you are looking for. Using my suggestion of seperate Records makes it much easier to get to what you want quicker.

5. Be consistent in how you name your Tables... tbl or TBL or table just creates confusion later on. Come up with a naming convention and stick with it.

I've actually written a database for Service Calls (http://www.access-diva.com/gtc4.html), I had a lot more tables, so I do understand what you are doing. Side note... on Form shown there has 3 Subforms and the other has 4 Subforms. As I said it's all in how you format it.

 

[AndyPandy]

cals would always come from the customer dispatch to engineer would like it to be a command button that opens up dispatch form to select who dispatched it time and date and to which engineer.

then it reopens the same form with the dispatch information displayed after you have filled it in manualy

the calls that come in may come in from one contactee at site but sometimes the same customer may require a return visit which may be logged by a different contactee at site.

dont need catagory as its either going to be non contracted call out or mannual call out

non contracted
mannual contract cancelled by mistake
manual system down.

ive sort of altered the template a bit on this attachment give you some idea of what i want the main form to look like. SEE CALL DETAILS BUT WHAT EVER YOU THINK ID BEST.

 

[GinaWhipp]

1. Yes, that is possible
2. Yes, that is possible
3. So, you would not be logging all the activity?
4. Okay, then remove it but I would use an Option Group if thise are the three choices. Then default the most likely one.
5. Well, I always dislike those Templates and now even more so. Teaching people to name fields like that. :banghead: Well, okay, if that is what you want altered I can't because I would start from scratch rather then use that thing... sorry.

 

[AndyPandy]

Hi.

I attached the template to show you the main call details form in order to show you what I was tryint to create myself not that i want the template altering. lol so please feel free to start from scratch on how you would do it.

 

[AndyPandy]

Hi I have re-done the table again lol calldetails would be like your acivity table etc.

hope that helps, easier to use my tablenames as i know then what each table is for, i have tried to put descriptions in as well.

the only think i cant figure out is the sub forms the relationships and how to make it so one company may have one or many call, jobdetails etc so i think it will be a many to many lol

 

[AndyPandy]

hello gina r u there

 

[GinaWhipp]

So, sorry! I was having troubles with my eMails this AM and yours must have gotten lost along the way. I'll look at your new sample now...

 

[GinaWhipp]

Question (and there could be more...

Why is EmployeeID in tblBranch? Won't there be more than one Employee at any given Branch?

To answer your other question... You're missing what I call Junction tables which is why you can't figure out to display one-to-many.