Question How To Go About Building The Application

[HighXplosive]

For the benefit of clarity, I would suggest anyone who reads this thread to download the entity relationship model I have produced of the database in order to understand what the hell I'm talking about.

I have points to make before I present my questions:

* The database is intended to be used to log calls and emails. These are known as cases, hence the entity Case. Each case MAY have Clients (sometimes no details of the person calling are acquired, hence the Client entity being optional).

* Case_Reference is a reference table to link unique Cases with unique Clients. It is absolutely necessary in order to keep Clients as seperate entities from their Cases.

* You'll note that the Client entity is built up using many other optional entities. These entities could otherwise be fields of the Client entity itself, but I have made a design decision.

My difficulty comes when designing the forms for this database. I have full referential integrity, the tables are fully normalised as reasonably as possible. I have ruthlessly prevented any null entries of any sort.

However, after much reading up (internet and this book I have called Access 2003 Bible), I'm still unsure as to the power and capabilities of Access of achieving EXACTLY what I think is best for the client when it comes to user interface issues. Here is my difficulty:

Noting that Client has many optional related tables, and that there are many levels of optionality in my design: when designing forms, I'm finding it tricky.

Obviously, I would like my clients to be able to enter their data, check it over, take their time, then save the data, but only once they have added everything they want to a "case". The problem is referential integrity, and the user interface.

I'm aware of subforms, but the nature of forms is quite a thorn. I want to construct a form so that the data can be entered onto it and only when a "SAVE ALL" button is clicked does the data on screen transfer onto the tables IN THE ORDER I WANT. Not one table at a time.

* I also want to determine the Primary Key value that is used for each and every entry. My database does not use autonumber fields: they are unreliable and prone to....horrible things. Thus, all my tables have a unique NUMBER type primary key. How would I go about programming some sort of "counter" per table: I'm assuming I have to make the application check the largest number (i.e. latest entry) and save the current case as the next number. Question is how?

* Additionally, the actual user interface I have designed seems impossible using Access forms. A typical scenario is this:

Someone calls. This is a Case. He gives his name. He is a Client. He gives someone elses name. This is also a Client. Thus this Case has 2 Clients. The data entry person opens a new Case. He adds these 2 Clients, enters the Case details and saves the Case.

My question is, can I implement a form whereby a list of the Clients currently attached to the current Case being entered (thus it doesn't actually exist yet!! Hence referential integrity issues) is shown in a list box (I reiterate, they don't exist yet, thus I can't query them from the database, because they don't exist).

What I mean is the data entry person makes a case. He chooses to add a client, enters the details for that client, saves that client, and repeats for the next. After all this, he clicks 'save case' and only then is all this data logged. What is the simplest way to do this.

* One more thing: all Cases have Topics. The Topics table I have constructed has a list of hundreds of topics, and each has a Type and a Name plus their IDs.

From a user interface POV, I intended to have to lists boxes: one of all existing Topics, and one of all assigned Topics on the current case being entered. The user would drag topics from the existing list to the Case list. The topics need to be removed from the existing list (to prevent the same topic being added twice) and appear in the Case list. Can this be implemented in a form: noting that nothing is saved until the data entry person clicks a "save the whole case and all other things that it needs" button.

Summary: How do I instantiate every Case until all data is entered related to it and in it before recording it onto the Access database using forms...if it is possible?

Clarification: By Application as a whole, I am refering to the external level of ANSI/SPARC architecture and various similar models. Additionally, don't be afraid to tell me I'll need to program this nonsense into Access if I have to!

 

[Pat Hartman]

The major strength of Access is it's bound forms and subforms. To use bound forms, you need to allow the records to be saved in their natural sequence. That means that the one-side record (the case) is saved, then the client record is saved, then the relation record (client to case) is saved. That leaves you with the possibility of an incomplete transaction. If this is a problem, I would handle it by adding a "complete" flag to the case table. When the user is finished, he would check the complete flag. your app would do whatever checking is necessary to ensure completeness and then check or refuse to check the complete flag. Using this method, you need to be able to allow users to delete "incomplete" cases and you also need to include the complete flag in your selection criteria for reporting, etc. so that only complete cases are included. And finally, to ensure that incomplete cases don't hang around without being completed or deleted, run a query each time the db is opened to look for incomplete cases for the current user. If any are found, open a form to show them.

You are making too much of the autonumber issues. I always use autonumbers and in 15 years have only had one database with an autonumber corruption issue and it was my own fault.

To create your own autonumbers, use a table that contains a row for each table that requires an autonumber. These records will hold the last assigned autonumber for that particular table. To use them, run an update query in the BeforeInsert event of each form to acquire the next autonumber value and commit it to the log table. This method will have the same issue with gaps as the autonumber does because you will not be able to recover an autonumber that was issued but ultimately not used. In a multi-user environment, I would use a date/time field populated with Now() to use as a comparison. That way you make sure that the record has not changed between the time you read it and need to update it. Include error checking and a loop to handle conflicts.

 

[Banana]

Regarding the subform and saving of records; someone asked a similar question. Read up for some ideas in addition to Pat's excellent proposal.

 

[Pat Hartman]

I forgot to add that you also don't need to be pedantic about null values. Some fields are simply optional and it is perfectly acceptable for them to exist in the main table. By the looks of your schema, you have some tables that hold single fields. I would never do that. One-to-One relationships should only be made when you need to sub-class an entity. For example, if your customer table may contain both companies and individuals and you keep different information for each, you would create a customer table with the common fields along with a customer type field and two other tables, one to hold customer company attributes and the other to hold customer individual attributes. The proscription against nulls, applies to this multi-class situation. You also seem to have created a separate table for close date which is a value that all cases will contain at some point in time. Just because it starts out null doesn't mean that it should be put in a separate table.

Since you have a situation where client is not required, use left joins to the relation table. That will take care of instances when you do not have a relation record because you do not have a client. Do not separate out all the attributes that will be present when you really have a client.

 

[HighXplosive]

@ Banana - Thanks for that link, I've done a bit of reading of it. I have only a month to implement my solution as some real life issues have pinned my project time between going back to my studies at university and visiting loved ones at hospital. That means you're suggestions are, while fascinating and good knowledge, I think are beyond my reach at this moment in time. Cheers though!

@ Pat

I had the same idea in mind Pat, or a similar idea, of a sequential process for entering the data. Considering what you have said, I would raise a few issues that jump out at me.

- How would this sequence work given that, as my model shows, there CAN be two Clients per Case. Perhaps a seperate interface for storing Clients to a Case, bearing in mind Cases can still exist without Clients.

Scenario:
User creates a Case.
User creates and adds a Client to Case. Repeat if necessary?

- How would I implement this in a form that I may allow the user to associate (with or without their direct intervention) a Client entry they are making to a particular Case (that is, how to set the Primary Key of Client into the Case_Reference entity and the Primary Key of Case into the Case_Reference entity, as Foreign Keys thereby creating the relationship).

You also mentioned using an update query to acquire the 'artificial' Autonumbers from a table, and update them. How then would I associate the updated number to a particular record after the update query? Would this require some VBA code mixed in with SQL?

I think what I'm really asking is "how do I create the relationships in my database using Access forms". Obviously the act of creating relationships is done by using key values: I just can't get my head around how to do it in Access.

- I've done some further reading and essentially I think I initially wanted to create an Unbound Form. I've read elsewhere on this forum that is a daunting task (or time consuming to say the least, something I'm short of). Would I be correct in assuming that you would advise me NOT to try and unbound form in 1 month given that my BASIC is very rusty.

- Pardon my pedantic crusade against NULL values Pat! I'm merely aiming to create a fully normalised design: in the event I wish to simplify the design I would merely, as you quite correctly suggested, stick those single attribute tables into their parent tables: then put some other validation in, etc.

Always better to break a problem into the smallest pieces first!

- On the issue of Autonumbers: good suggestion you make. The reason I'm not too fond of the Autonumber issues is that, normally, I would prefer my Application to dig up the the last value of a key and compute the next one rather than the database itself. Of course, Access does blur/mix the database and the application level quite closely.

 

[Banana]

I think it bears making clear that if you work against Access's natural behavior, you would be actually creating more headaches and bugs along the way than if you took it to your advantage.

For example, I would think it far more easier for you to use bound form/subform populating the Case and Clients, respectively. Because you want to commit it all at once, or none at all, I would probably use a local table on front-end, and have a status label notifying that this data has been not committed. They would have to then click a button to save the data to backend, and I can use transactions because I'd be executing append query in code using those information. Likewise, if a case needs to be edited, use another append query to fetch the data from backend to local temporary table and requery the form.

As for autonumber, the idea was that if the table is on backend, you just need to create new record, fetch the last autonumber and insert it into the field used for your primary key. This way, nobody will have same ID because this comes from a common table. If you don't want to write too much VBA, you can just use a invisible field for your PK and insert the number into that field, so it's also included in the append query to backend when your user click Save All button.

FYI, other RDBMS, AFAIK, doesn't give out autonumber preemptively, so Access isn't really that different from other RDBMS (though, Access will try to assign a key early; as soon as a new record is dirtied, whereas other RDBMS will assign key only when record has actually been saved to the storage. Beyond that, they're quite similar)

Regarding your question about how you would associate clients with case, if I'm reading you right, I think what you have is actually a many-many relationship. Therefore, the form that manages Cases would have a subform that query the junction table so you can then select Client from a combobox, or if you want to use a listbox, use it instead of subform (on understanding that there's lot more code involved) with a mean to add new client if necessary.

I hope that helps.

 

[HighXplosive]

@ Banana

Your advice has been very useful Banana, thanks. I think I'm starting to grasp how I should go about constructing these forms.

Perhaps the biggest plus is confirming with my client that they do not mind entering the key values themselves manually (that is, Case ID, Client ID, etc). This simplifies things immensely, as I will merely have to implement some robust validation and verification.

FYI, other RDBMS, AFAIK, doesn't give out autonumber preemptively, so Access isn't really that different from other RDBMS (though, Access will try to assign a key early; as soon as a new record is dirtied, whereas other RDBMS will assign key only when record has actually been saved to the storage. Beyond that, they're quite similar)

True, but Access physically creates the record once that key value is assigned and to undo or stop this automatic numbering requires an unecessary procedure.

Anyway, regardless, because the data entry persons will enter the details there are noissues with autonumbers and front-end back-end tables and other confusing extra levels of complexity.

You are correct, Client to Case is a many-to-many relationship. However, I would disagree with your suggestion for the following reason: correct me if I'm wrong!

Client always has a Case, thus it would make sense to stick the Case Reference subform into Client's entry form, not Case. Instead, I've stuck the Topic Reference subform into the Case form.

The attached files shot and shot2; shot is the semi-complete Case entry form, with the attached Topic Reference subform at the edge (continuous form). shot2 shows a very,very WIP Client entry form. You can see the very, very skeleton subform for Case Reference.

What do you think chief?

 

[Banana]

While it's nice that your clients won't mind entering the keys, I'd have to be quite empathic that they *don't*. This just opens up a can of worm regarding operator error. Something as simple as transposing '71' for '17' can end up referencing client to wrong case, and those are quite insidious because it may be not be clear until it's too late then you've got to spend many hours clean up the incorrect references, even if they can be figured at a later time.

For this reason alone, it is far preferable that your data entry operators deal with combobox or listbox to select from existing records (with a button to create a new record, if so desired).

That said, I'm not seeing how you will relate the Case to Client? I see you relating Case to Topic, Client to Case & Reference (is that also "Topic" or something entirely separately?). My confusion is whether client are actually related to references, rather than a case which can contain many references, which means a entirely different kind of relationship.

Regardless, if you want to stipulate that there must be a child for Case record, you can enforce this with my earlier suggestion by doing a validation check prior to copying the data from local tables to the back end/Pat's suggestion of using complete flag to send the data to the backend and thus become visible to others for editing/viewing.

HTH.

 

[boblarson]

Users can, and should, enter data. Access should handle the keys. The whole process should be handled by the system and not by the user. This is to ensure data integrity. There is a whole bunch of arguments on the natural versus surrogate key issue, but I never use natural keys - I only use surrogate (autonumber) keys just from the standpoint that I do not have to worry about any potential for error when keys are created and used.

 

[HighXplosive]

@ Banana

I'm aware of all these issues, but if I were to use some form of automatic key numbering it would have to be as simple as possible, bearing in mind I have about 13 + entities, most of which have unique keys.

Pat's idea was good. Your idea sounds good but slightly more complicated to implement. But despite these good suggestions, I'm still unsure what you mean by "copying" the data from one table to another.

What I mean is, I know its done via SQL code, I just can't find where you're supposed to put this "code" into. I'm assuming you mean using the events of the form and the VBA editor: therefore you are suggesting I use the VBA editor to code some SQL procedures to certain events. If so, I am not sure if I can accomplish that in my given timeframe.

 

[gemma-the-husky]

I'm not trying to be controversial, but the trouble is, you appear to be trying to design quite a high end application, without having a full grasp of exactly what Access does

Are you competent with spreadsheets

Access is not a spreadsheet, but in many ways Access tables present data as if it was a series of spreadsheets. It thens adds to this automatic referencing from sheet to sheet, datatyping of spreadsheet columns to prevent nonsensical entries, and blinding speed in comparison to spreadsheets (ie effectively in functions like lookup, vlookup etc, and in presentation capability)

If you can envisage managing your data in spreadsheets, then it is a start.

Instead of using a single spreadsheet, you break down the spreadsheet into its consituent parts - so that you dont store eg, a client address on every row of the spreadsheet, you just store the clientID, and the clientaddress is stored separately, linked to the clientid.

Now, the functionality of the database is something you determine

You have cases, and clients, and you link these together

If a case can have just one client, then this indicates a certain structure. If more than one, then a different structure. If a client can only be involved in one case (a different point), then again, this implies one structure - if more than case another structure.

Now, its hard to see exactly what you mean, but you talk about topics per case. This implies another table, to store the topic details per case. This assumes that topics are dependent on the cases, but NOT dependent on the clients. However if a case has say 3clients, but certain topics apply to some clients, but not others, then its a different structure.

Therefore, you need to thoroughly analyses the data requirements before doing anything. as the initial design needs to be as good as you can get it. Fixing design errors down the line is much harder.

-------
Now, to take this further - when you implement the solution - it doesnt matter really whether the SYSTEM gives a case a number (an autonumber) or whether you type in a new number manually (as long as that number is unique). However, once a number is allocated, the point is that you shouldn't have to re-enter that number ever, in order to allocate a client to the case - you should merely pick the client, and have Access use the case number it already knows to provide the link. Hence autonumbers are generally no less reliable than manual numbers for this purpose - Bear in mind that a number is always more efficient than a non-number (ex text) If you actually need a specific case reference as well, then I would be inclined to use the autonmuber, and also store your case reference. Once the link is made, Access can easily retrieve the linked items, and OTHER data associated with the linked items. You can then modify the case reference if required without any issues at all.

Thats what the above information already given to you by others means, and its these features that enable Access (and other databases) to manage enormous volumes of data at rapid processing speeds.

All you have to do is (effectively) determine the enterprise rules, and make sure that data entry maintains its integrity

This is by no means trivial, and much coding work is taken up in making sure that systems are robust, that appropriate data has to be entered where required, and that exceptions are handled properly - eg entries out of a normal range, entries not consistent with other data, and so on

hope this helps

 

[neileg]

To add my two penn'orth:

HighXplosive, you are missing an important point about key values. In my applications, users don't see the key value ever. That is part of the mechanics of the data management, not the user interface, and I always use a surrogate (autonumber key). When the user needs to see an ID number, this is an additional field, and while it may well be unique and indexed, it is not a key value.

 

[HighXplosive]

Please bear with me folks. You do not need to tell me about data integrity issues, the importance of key referencing and how all these things work. I can design a database.

The difficulty I'm having is producing a working solution using Access with its powerful but, for what I'm use to, wholly restrictive model for the application level. I'm use to programming my applications to do what I want it to do.

I do not like how Access forces you to use this powerful, but also restrictive model of Forms that are bound to specific tables. Most of my problems are because I've not used Access before and I am also short on time to learn it, and learn VBA. Originally I was thinking of using Java and a ODBC to link a Java application with the .mdb file, but that would be far too time consuming.

Now heres the latest problem I have. You will not understand this question unless you look at my entity relationship model, Post No. 1 of this thread. It uses crows foot notation. The notation is exceptionally important.

I'm still tackling this issue of linking Clients to Cases using a subform that is also user friendly. How do I go about doing this?

Bare in mind this database could eventually contain 1000+ records. I cannot use a Combo Box to allow the data entry person to select a particular Case to attach to a particular Client, as there could be hundreds of each, and none of them have ANY identifying details except their primary keys.

That is why the users must see the primary key values, because they are the only attribute guaranteed to be present in any given Case entry or Client entry. The database is for a helpline: the helpline rarely ever gets a callers full details, just bits and pieces.

Again, I reiterate, look at the ER model I have produced: the important relationship here is Case > Case Reference < Client

I've also attached an attribute listing to this post as I've realised how difficult it is to understand what the bloody hell I'm talking about without it.

 

[neileg]

You are making unsupported assertions!

Why do users have to see the PK? As I said you can assign an ID for users but it doesn't have to be (and in my applications will never be) the PK.

It is true that Access provides a very powerful bound form model, but it's perfectly possible to use unbound forms.

You don't have to use a combo box as a pick list. It's native behaviour includes 'zoom' functionality, i.e. it moves to matching data as you type.

Further comments

I've looked at your documents. I don't understand how CaseRefID is an FK. I presume you mean a PK or is part of a compound key. It's probably redundant if the client/case combination is unique because you can just make the two FKs into a compound key.
It looks like your CLIENT/RELATIONSHIP relationship is the wrong way round. I would have expected Relationship to have been the many side of this join, but I may have misunderstood.

It's clear you are feeling a bit frustrated. I don't think any of us consider Access to be the best thing since sliced bread. I'm sure we could bore you to death with things we'd change if we could, but as a rapid development environmnet it's pretty good, which is why people use it.

Good Luck!

 

[HighXplosive]

A new problem!

This once again concerns the relationship Client > Case Reference < Case.

I have a subform that is bound to Case Reference. This subform is placed in another subform that is bound to Case. The purpose of this subform is to cycle through the Clients that are related to the Case that is currently being viewed. This is working so far.

I have then tried to place some text box controls into the Case Reference subform that I just mentioned. I cannot bind the controls to display a specific Clients details, such as Forename and Surname.

The structure is:

Case bound subform
Case Reference bound subform

Case Reference subform should display information about a Client. Client has a foreign key in Case Reference.

I tried to solve this by placing ANOTHER subform beneath Case Reference called ClientReferenceClientDetails. This worked until I tried to bind text box to display text from a table called Relationship, which has a foreign key in Client.

The problem is, if I display the RelationshipID (the foreign key) in my sub-sub-subform called ClientReferenceClientDetails, it displays as a number 1 or 2 or 3 depending what Relationship it is looking up. I cannot figure out how to get it to use the RelationName field in the Relationship table.

So I tried the following code in place of the Control Source of the text box in the ClientReferenceClientDetails subform used to display the Relationship (of the current Client of the current Case Reference, of the current Case.)

=DLookUp("[RelationName]","[Relationship]","[RelationshipID]=" & Forms!ClientReferenceClientDetails!RelationshipID)

This worked for the subform, but the subforms above it, i.e. CaseReference subform, Case subform, all display a #Name? string.

I'm presuming all these sub-sub-sub-sub-sub-sub forms is unnecessary and could have been avoided by the proper expressions used in the initial CaseReference subform, but my luck with foreign keys and using them to reference their parent tables is.....lacking.

 

[Pat Hartman]

You need to examine a working application to see how the pieces are connected. Take a look at Northwinds. It will be on your computer if you installed the sample files. If not, you can find it by getting it from your installation CD or downloading it from the Microsoft download site. The customer order form shows you how to implement a many-to-many relationship. Order details to products since you seem to be hung up on that.

Think of subforms as bound grid controls that you use in VB.Net and other languages. The access subform is somewhat limited visually compared to the standard grid control but it requires no programming and is not limited in other functionality.

To effectively use bound forms, you need to understand and know how to use the following Form level events. Some of these events also occur at the control level for an individual control:
Current - fired whenever the recordsource is moved to a new record. Use the Me.NewRecord property to identify when you are on a "new" record.
BeforeUpdate - the buck stops here. This is the single most important event in the entire form event model. This is the event to use to do your final edits. All edits for null values or edits that involve field dependencies must be placed here. When an edit fails, use the following code to cancel the update to prevent Access from automatically saving the data assuming it gets past any declarative RI.

Cancel = True
Msgbox "some meaningful error message", vbOKOnly
Exit Sub

In some cases, you may want to undo the changes to the form - Me.Undo or a particular control - Me.controlname.Undo

BeforeInsert - fires immediately after the first character is typed into a form. You could use this event to populate the foreign key if you are using a popup form rather than a subform (on subforms, the master/child links cause the FK of the subform to be filled with the PK from the parent form)

There are charts in help that show the order in which forms fire. Subforms confuse the issue because their events fire before the events of the main form.

One final word of advice to someone familiar with other languages but not VBA - the default property of a control on a form or report is the Value property. You may omit it when referencing the control so Me.somecontrol is functionally equivalent to Me.somecontrol.Value. In VB the default property is Text. VBA has a Text property but it is only available when a control has the focus so to use the Text property, you would first need to set focus to the control. This trips up many experienced programmers. In VBA, the only time the Text property is used is in a control’s Change event so that you can examine each keystroke as it is typed. Once the control’s BeforeUpdate event or the form’s BeforeUpdate event is fired, the complete entry is contained in the Value property.

I might also add that you are doing a lot of arguing with people who are trying to help you. We are the Access experts. We are not always right and you don’t get good advice from everyone but if all the responses you do get run to the same general advice – use bound forms, don’t fight the system, use autonumbers, you’ve gone over the deep end with your over normalization, you might want to listen and learn.