Hehe... and since me and my girlfriend just broke up, that seems applicaple both in the dbase and the real world ;)

I have one Access Database. For now containing three Tables:

KlantNAW (customer adress data) with one primary key, Klantnummer (clientnumber) which is an Autonumber Data Type, Long Int, Increment, Indexed: Yes (No dupes).

CaseDateTimeInfoTable with one primary key, Casenumber which is an Autonumber type, Long Int, Incremental, Indexed: Yes (No dupes).
Within this table I have included the Klantnummer field from KlantNAW as a Number, Indexed: Yes (dupes OK).

CaseTechInfoTable with no primary keys but with both the KlantNummer field and the CaseNumber field included. Both have a direct relationship to the tables in which those two values are created. Both values are now of type Number, both are Indexed, but Klantnummer is set to Duplicates OK and Casenumber is set to No Duplicates. I don't know if it might be wiser to just not index those values in this table, since they are already indexed in the tables in which they are created, if someone could tell me which is better in this case, please do.

I have created a one-to-many relationship (well actually, access seems to decide whether it becomes a one-to-many or one-to-one relationship) between KlantNAW.Klantnummer and CaseDateTimeInfoTable.Klantnummer with Join Type 1 and I've created a Join Type 1, one-to-many relationship between KlantNAW.Klantnummer and CaseTechInfoTable.Klantnummer.
There is only one extra relationship left, which is a one-to-one, Join Type 1 CaseDateTimeInfoTable.CaseNumber with CaseTechInfoTable.CaseNumber.

So ehrm.... a recap:
KlantNAW.Klantnummer (P-key) with CaseDateTimeInfoTable.Klantnummer
KlantNAW.Klantnummer (P-key) with CaseTechInfoTable.Klantnummer
CaseDateTimeInfoTable.CaseNumber (P-key) with CaseTechInfoTable.Casenumber

If I leave it like that the dbase works perfectly except for the fact that when I delete a customer the related case date/time info and the case tech info don't delete with the client account accordingly.

So, I thought I'd "Enforce Referential Integrity" and "Cascade Delete Related Records". I do not know if it would be wise to also enable "Cascade Update Related Fields". Could someone please tell me if that would be wise to do or not? And maybe even why... I have some beginner and more advanced books but can't be sure, probably because of lack of experience (or maybe lack of brains ;) ).

Anyway, back to the problem: when I make all the relationships to Enforce Rererential Integrity and I add a customer through a form (KlantNAW_InvoerForm) and click the "Create new case for this customer" button, the form I use for entering the case date info pops up nicely, but when I try to close that form I get an Error stating: "You cannot add or change a record because a related record is required in table "KlantNAW"". So, I click ok, then get a messagebox stating I can't save the record at this time. Do I want to close anyway? .... well, ok. Let's do that. And now the strange thing is that after that, when I do absolutely nothing other than pressing the create new case button again, enter the date/time info into the form and close it again, no errors or messages come up. Wel... that should happen the first time around right? What's wrong?

Now... I'm really lost. I'm new at this. I made some tables, queries and forms, set all options for all values, that which I didn't understand mostly tried solving or finding out on my own but now I'm hitting a brick wall.

If you guys and gals could help me out I would really be very grateful for that.

EDIT: I even tried making it more simple by removing all relationships and creating just one new one between KlantNAW.KlantNummer and CaseDateTimeInfoTable.Klantnummer (type 1, enforce referential integrity and cascade deleted records) but still, the same problem arises.

EDIT2: made sure it was not a form thing by just putting in data in the table data views directly. Same thing occurs.

Okay

1) Where you have the two tables linking to a single table on the 1 to many the two fields in the third table should be a combined primary key. This will then be indexed as a Primary key (No Dupes for the combination pairs.

2) Yes you should have referential integrity enforced

3Two camps on cascade update and cascade delete
I switch them on and do not allow users anywhere near the tables cos deleting a value that is at the one end will cascade the delete to the many end. One of the fastest ways to get rid of records...provinding of course that is what you want to do !!!

Cascade update means that if you update the value of a field that is a PK then the change will be cascaded to related Foreign key values.

Basically both of these prevent orphan records

The error message you are getting basically means you are trying to enter data in the third table into fields that are associated wuith the other two tables, Referential integrity means the value must exist on teh 1 side before it can be used at the many side

This is actually simpler to understand that the female of the species.

Apologies to all the Ladies who may read this ...it was said in a moment of male weakness

Len

Well.... this sucks:
----------------------------------------------------------------------
You cannot add or change a record because a related record is required in table <name>. (Error 3201)
You tried to perform an operation that would have violated referential integrity rules for related tables. For example, this error occurs if you try to change or insert a record in the "many" table in a one-to-many relationship, and that record does not have a related record in the table on the "one" side.

If you want to add or change the record, first add a record to the "one" table that contains the same value for the matching field.
--------------------------------------------------------------------------

So... how am I supposed to delete a customer and all related data from that customer throughout all tables? I thought that is wat referential integrity is all about. Enforcing data across tables within relations (keys).

Right?

Thank you very much for your input.

1. I just found out about multifield keys :) So by now table KlantNAW has one key (Klantnumber), table CaseDateTimeInfo has a multifield key consisting of KlantNAW.Klantnumber and CaseDateTimeInfo.Casenumber and table CaseTechInfo has a mulitifield key consisting of those same two.

I now only have two relations:
KlantNAW.KlantNumber with CaseDateTimeInfo.KlantNumber
and
CaseDateTimeInfo.CaseNumber with CaseTechInfo.CaseNumber

This seems a bit strange to me, why I cannot explain. My gut tells me there should also be a relation for KlantNAW.KlantNumber with CaseTechInfo.KlantNumber. Should I do this or never listen to my gut? ;)

In the third table (CaseTechInfo) the Indexing is set to Yes, but duplicates are allowed (since customernr. 11 can have casenr. 11).

2. Ok, I will do that then. But when I do, that error keeps on coming up and I can't get rid of it. In this way it is not possible to enter data into the form without having to close it after it's been filled, accepting the loss of data and then reopening the same form and reentering the same data again. Because when its done the second time, the error doesn't occur.

For now I will be the only one working with this DB, but that might change. Anyway, when a client needs to be deleted, the casedatetimeinfo and casetechinfo also need to be deleted. When a client doesn't need to be deleted but the case does, the casedatetimeinfo and casetechinfo must be deleted. That's what the referential integrity is supposed to accomplish. Right. (?)

So, although I understand a bit more now, and have adjusted accordingly, I still am presented with one of the problems, the error mentioned earlier.

This is what happens:
Click create new customer on the switchboard - form to type in customer adress data appears - so type in adress data etc. - click create new case for this customer button on the "type in customer adress data" form - form used to input dates, times, appointments, agreements is displayed and filled in - when that form is closed by clicking the red cross in the top corner of the formwindow the error appears - click ok - message saying data will be lost appears - click yes - form to type in adress data was still open so it gets focus again - click create new case for this customer again - fill in form - close with red cross of the window and this time no error occurs.

That last part shouldn't happen... ;) I just want to be able to close the window without getting the error.

Notice the casetechinfo part is not yet in play at this moment in time, but it would seem to me I will be having the same problem there.

As for the women are hard to understand part... that really wasn't the issue between the two of us. We'll probably stay friends if possible. Just conflicting parts of our character which appear to be to difficult to get over, oh well....
It's strange... still loving each other a whole lot, but having to say goodbye to the relationship because it wouldn't work out in the long run. Maybe it would have been easier if we'd be banging doors (ehrr.... :s don't know how to else put it in correct english). Certainly not better, but maybe easier. Still, we lasted over 2 years....

Click create new customer on the switchboard - form to type in customer adress data appears - so type in adress data etc. - click create new case for this customer button on the "type in customer adress data" form - form used to input dates, times, appointments, agreements is displayed and filled in - when that form is closed by clicking the red cross in the top corner of the formwindow the error appears - click ok - message saying data will be lost appears - click yes - form to type in adress data was still open so it gets focus again - click create new case for this customer again - fill in form - close with red cross of the window and this time no error occurs.

Okay read this bit and believe that the creation of the new customer is not actually completed when you are trying to add related data.

When you add a new ciustomer make sure you use the tab key and go through all the fields. You may well find that the form then clears of all the data you have entered...thats good probably as it indicates the record has been saved.

Why has the form not got a proper Close Form button rather than using the x

Referential integrity prevents orphan records therefore if the parent is not there it will not let you create an orphan. believe me any relational database I see without referential integrity enforced loses 9 points out of a possible top score of 10 to start with

L

Thanks again, i'll try the tab thing in a bit. But I already (well, think I did) made sure it was not a form thing by just putting in data in the table data views directly. Same thing occurs. So indeed, it would seem that the creation of the new customer is not yet complete. Nor should it be necessary. In my case, customers call on a certain date and time, leave a voicemail with name and number. I listen to my voicemail, create customer based on those two or three pieces of data, call them and ask for the rest. But not all fields will always be filled in, nor will that always be necessary.

Maybe, just maybe... when I can get a piece of code to work that does something like:
If I am a new customer and the create new case button is clicked, save me, close me and open me again, but give focus to casedatetimeinfo form?

About the Enforce integrity: that's exactly what i would think now i'm starting to understand this.

There is no proper close button yet since it is in its early stage of development still. Lots of stuff still needs to be added to be able to make calculations on time/date/ time spent / products bought/sold etc.

Most likely the whole thing will be redone in the future, but for now i'll have to make do, and school myself in this stuff. These really are just the basics and I need to get those to work to see if i'm able to get the big picture done myself over time.

Well, you were spot on with the creation of the customer is not yet completed...

Why? And why do all the fields need to be "tabbed through" before the error doesn't appear anymore? Before Referential integrity wasn't enabled that wasn't an issue.

Reading a bit from one of your previous posts I believe you have already explained the why somewhat.

But now, how can I make it so that the "tabbing through part" is done automatically? In other words, finish creating the new client when the "create new case for this customer" button is clicked even though some fields are left blank?

Haha... Me starting up a small business and in the process finding out I might like doing this (DBase design) more. :D

Well, you were spot on with the creation of the customer is not yet completed...
?

Yup .. favourite target


Why? And why do all the fields need to be "tabbed through" before the error doesn't appear anymore? Before Referential integrity wasn't enabled that wasn't an issue.

Even when you enter data into the table directly if you hit escape before moving from that line then the data is not saved, Only when you exit the row... just like tabbing through to the end

before referential integrity was enforced you could enter any old rubbish and completely fill up the database with unrelated crap. Now you cannot


Reading a bit from one of your previous posts I believe you have already explained the why somewhat.

But now, how can I make it so that the "tabbing through part" is done automatically? In other words, finish creating the new client when the "create new case for this customer" button is clicked even though some fields are left blank?

Make all the fields required fields is one that... then the user has to enter something for every field or put a note on the display.. and make the command button invisible until the last field gets the focus and then also make a note visible saying Press Tab key before ... etc or put a Close form command on the button to close the form immediately after opening the next form. That will save the record.

Or use any combination. I like the adding of the close form command to fire immediately after opening the new form best.

Frustrating it may be but you end up with something that works properly or something that is going to be a pain in the butt

L

can i help

if you are in a new (unsaved) customer the attribute me.new will be true.

if you are in an edited existing record the atribute me.dirty wil be true

therefore if you click add new case, test the above

if you are in a new or dirty record, run

runcommand accmdsaverecord, which will save the new record first. If there are any mandatory fields missingm the save will fail so you should trap for this.

hope this helps

How do I "test" for that?

I'm a newbie. Have read something about the dirty property in a book (programming by example with vba from wordware). But i'm also using VBA programming for the absolute beginning, which seems more appropriate since I do not even grasp all of the non programming stuff within access. Most of the basics and by now some stuff beyond that I think (well, hope actually ;) ).

Does something like "me.save" take care of this problem? I know it should work like Len says, and I agree with him, so eventually, if my company becomes bigger and users need to be steered in the right direction, such limitations will be necessary. But for now they are only bothering me.

If there is no way around that, might a default value for all fields (like N/A for example) solve the issue? Since then there is data in every field and using queries... well, the N/A doesn't have to be a problem to filter that out for reports etc.

But back to the code part.... where to place it? In the onclick event of the button I presume?

And then what?
If Me.New = True then DoCmd.RunCommand acCmdSaveRecord Else Exit?

At the moment there are no mandatory fields, nor will there be for quite some time. One of the most annoying things is working with a dbase that will not allow you to do what you need to do. Often times that means going about (for example) the company guidelines/protocols differently. What matters in the end is that all data necessary for a certain purpose is available. And since it is my own company (thus my own *ss) on the line here, I'll make sure that will be the case.

Hehe... I tried getting away with:

Private Sub NieuweCase_Button_KlantNAW_InvoerForm_Click()
On Error GoTo Err_NieuweCase_Button_KlantNAW_InvoerForm_Click

DoCmd.Save acForm, "KlantNAW_InvoerForm"
DoCmd.OpenForm "CaseDateTimeInfoTable_Form", acNormal, "", "", , acNormal
DoCmd.GoToRecord , "", acNewRec

Exit_NieuweCase_Button_KlantNAW_InvoerForm_Click:
Exit Sub

Err_NieuweCase_Button_KlantNAW_InvoerForm_Click:
MsgBox Err.Description
Resume Exit_NieuweCase_Button_KlantNAW_InvoerForm_Click
End Sub

Doing a save of the adress input form before opening the appointment form but sadly... no cigar.

EDIT:
DoCmd.RunCommand acCmdSaveRecord
did the trick... just like you said. I'm still having problems figuring out and remembering the syntax of the code/commands I need to write. But with a bit of experimenting... it works.

So, thank you both very much! This afternoon and part of the evening have been very useful(l?) to me. :)

One of the most annoying things is working with a dbase that will not allow you to do what you need to do. Often times that means going about (for example) the company guidelines/protocols differently. What matters in the end is that all data necessary for a certain purpose is available. And since it is my own company (thus my own *ss) on the line here, I'll make sure that will be the case.

If the database does not do what you want then the design/interface is wrong so put it right.

It may be your *ss on the line as it is your company but when you make a wrong decision because your database was not set up properly roasting your own *ss will not recover the situation

So blindly putting in defaults will not solve the problem cos you still need to save the record

On the button that opens the new form ypou want something like

DoCmd.Openform "New Form Name" ,acnormal
DoCmd.maximise
Docmd.close acform,"Current form name"

Now sit back, relax and do the job as it should be done

If you think that 30 mins and youhave Access licked think again. There are guys on this forum who have been designing and developing databases for many years and they will tell you they are still learning.

Me I am a beginner only started 10 years ago

L

I do understand and agree with you. The data needs to be put in the records correctly. But as I sad before, often that data is not yet available to me at the moment of creating a new client -record. Tabbing through the 25 fields is a solution to the problem, but I would like to have the application behave the way I want, not the other way around ;) And the "DoCmd.RunCommand acCmdSaveRecord" luckily solved that issue for me in this case.

I do appreciate the help of the people on the forum very much and I really do not think that learning an application, especially in combination with programming, is done in 30 minutes. Sam's tries to do it in 24 hours... well I've tried that with Visual C++ once and it didn't work ;)

I'm just glad there are so many people out there who are willing to help, and I praise google and the awesome amount of books available. I have the utmost respect for you people who have been at it for such a long time.

I did not mean to... how do I say that in English "demean" (?) anything, anyone or any method. As you might have noticed I am not a native English speaker, so if I created such an idea, I apologize for that.

Just this afternoon while I was making dinner I wondered: when a new product (like for example access was some 10 (?) years ago) is introduced, almost immediately there are tons of books available for the public. How do the people who create those books do that so incredibly quickly? I mean, the amount of things you can do, and explain in a book are so vast... It's unbelievable.

But enough of my rambling for now ;)

Once again, thank you very much!

Okay

No offence taken at all.

Was concerned that you did not drive to hard to achieve a short term goal and the expense of a satisfactory and equitable solution.

Yup you may not have all data available so you have to do the best with what you have. that's fine. Do not compromise the quality just because the quantity is a bit lacking. That was my concern.

helpful bunch here

they have helped may many times

L

Just a thought

you dont really want you or users to HAVE to populate every field in your record. Many may be left blank. All you have to decide is which fields are essential to carry some info and set the properties for those to non null.

These will be some things like a name and perhaps one or two dates, but you will know. At this point the record will save, and will save automatically if you close the form.

Assuming you are now opening the "Add New Cases Form" with a command button, you will have an on click event. In design mode, right-click the property, in the events tab, click the ellipsis (...) on the on click line which should say "event procedure", and this will take you there.

You are now at the place where you execute the click command.

you can now put on the first line

if me.new then (me refers to this form, and new is because access knows this is a new record)
---- do what you want - save the record, or tell the user he has not comleted entry of a new record etc - that is up to you


if me.dirty (me refers to this form, and dirty refers to the changed property)
then
---- again do what you want - save the record, or tell the user he has made changes and to save them first before continuing - that is up to you