If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Field relationships
Is there a way to have a delete record command button on a subform only
delete the field(s) that are linked to the parent forms table? For example: I have a table (misc_donations) with 3 fields: donation_type, donation_amnt, donor; and another table (donations_types) that has just 1 filed : donation_type. On the form to enter misc_donations I include a button to open a subform to enter/edit donations_type. The donation_type on misc_donation table is selected from a pull down list. I can get it to update changes to donation_type but if I delete a donation type from the donation_type subform it deletes all of the records in the misc_donations which have that particular donation_type, I just want it to clear out or change the donation_type field. Thanks, Kris |
#2
|
|||
|
|||
Field relationships
It sounds likely you have cascade deletes setup. It also sound like you are
enforced referential integrity here. Really, all you have is a lookup. Lets say a table of favourite colors. If you delete a few records of bob's favourite colors...you don't care, or need any relation stuff to the "colors" table. It is not really a formal relationship..but just a list you use for looking up colors to save typing. So, go to your relationship window..and double click on the join link between those two tables...you want to un-check the cascade deletes. You can leave in the referential integrity (enforce)...but you don't really have to. Fact is, in our color example...you are free to add new colors to the table...and again many customers may not yet have chosen their favourite color. So, you code of want to distinguish between real relations like a customer who has many orders. If you delete that customer, then you most certainly want all orders that belong to that customer removed also. And, you also don't want to be able to add an order UNLESS the customer exists. However, with our color example...we have a lot of freedom..and we can add new colors to our color list, and we can delete customers..but we never delete colors from that simple list of colors. So, it is rather more of a lookup list..then a relation. In fact, you can get away not even drawing any join lines in the relationship window in this case. However, it is handy to leave them there...as it helps you see that some tables do have a lookup. If you take look at the following screen shot..you can see that when tables do NOT have the sideways 8 (omega), then we are talking only about a lookup. And, if you look even closer...you will note that some lines have an arrow head..but some don't These are enforced relations..but what we call left joins. for example...we might set the database up so that all customers MUST have a order. But, likely that is too restrictive of a relation (so, we use a left join...or what the prompt says: Include all records from customers and only those records from table orders. If you set the relationship to where joined fields are both equal..then in effect you are saying that customers MUST have a order for the database to function. As a result, looking at the following relations picture...90% of all my relations are left joins (yours should be also). http://www.attcanada.net/%7ekallal.m...Appendex2.html So, for example, tblBgroup (booking Group), is linked to table payments...note the right arrow. That means, as a developer I accept the fact that no payments may not yet be made. However, look at how tblBgroup is joined to tblBooking. So, I do NOT allow a "group of people" to be booked until a booking record is made. Thus, all my code will assume when you create a booking..you MUST ADD people (the booking group). So, setting up your relationships diagram can result is HUGE amount of documentation for the project. In fact, I have as consultant walked into companies and had developers fired for lack for setting up relationships..and using ER tools. It is the first thing I look for when evalaution any projects..or skills of the develoeprs involved. -- Albert D. Kallal (MVP) Edmonton, Alberta Canada http://www.attcanada.net/~kallal.msn |
Thread Tools | |
Display Modes | |
|
|