HighXplosive
New member
- Local time
- Today, 02:33
- Joined
- Aug 25, 2008
- Messages
- 9
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!

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!
Attachments
Last edited: