How do I maintain referential integrity between a main form and a subform, each based upon different (but joined with integrity enforced) table?
Here's the situation:
I have two tables: tblContracts and tblPayments. tblContracts has an autonumber field called IDKey as its primary key. tblPayments also has an IDKey field (Integer datatype). The two tables are linked in a one-to-many relationship on the field IDKey with referential integrity enforced.
I have a main form based upon tblContracts (the "one" side of the relationship) that has an embedded subform based upon tblPayments. the two forms are linked Parent/Child on the IDKey field.
Here's the problem: If a user goes to a new record in the main form, it allows them to enter information in the subform without entering information in the main form. This means that a new record (and its corresponding autonumber IDKey field value) does not get generated in tblContracts and I have an orphan record in tblPayments that is not linked to any record in tblContracts - which violates the referential integrity that is supposedly enforced between the two tables.
Any guidance on how to deal with this would be greatly appreciated.
TIA,
Carolyn
6  4502 
Everything seems to be set up ok. Check to make sure you are including the IDKEY on both forms. You can even hide those fields if you want, but I'm pretty sure they should be there. Since the IDKEY on the main form is an autonumber, it should then create that same IDKEY on the subform, due to the parent/child link.
comteck
That's exactly what I would have thought, but it does not appear to work that way. The tables are linked in the relationships window with referential integrity enforced for both cascade update and cascade delete. The IDKey field appears on both forms and is used to link the main form to the sub form. IDKey is the primary key for the table on which the main form is based.
Anyway, the workaround I am using is to make the subform invisible until something is entered on the main form. Not my first choice for usability, but at least it keeps the integrity of the table relationships.
Any further thoughts would be welcome.
Carolyn
In your "tblPayment s" table, is the datatype a Number or an Autonumber? It should be Number.
I'm assuming you don't get a message that says "Index or Primary Key cannot contain a null value."
Like you, I'm baffled with this one.
comteck
Hi, again.
The data type of the IDKey field in tblPayments is Integer.
No error message. (Actually, the error message you indicate is the error message I would have expected.)
I've tried setting this up in a bitsy test DB as well as my larger application just to make sure there wasn't something else weird going on.
The test DB has two tables: tblContracts (with two fields: IDKey as primary key, autonumber, and Contracts as a text field) and tblPayments (also with two fields: IDKey as Integer and Payments as a text field.) The tables are set up in the Relationships window as a one (tblContracts) to many (tblPayments) relationship with referential integrity enforced.
Then I created two forms: frmContracts with the two fields from the Contracts table and a subform, frmPayments with the two fields from the Payments table and linked the two together on IDKey.
If you go to a new record in frmContracts and only enter information in the Payments subform, and then close the form, you'll wind up with an orphan record in the Payments table.
It's a puzzlement...
Integer is not a data type. It is selected under "Field Size". The data type can be either "Autonumber ", or "Number", and Integer can be selected for either.
While in the table design view, you select a "Field Name", and directly to the right of that, you select the datatype. For the main form, the datatype should be Autonumber, and for the sub-table, the datatype should be Number.
As well, I've always selected "Long Integer", as opposed to "Integer". It shouldn't make a difference though.
comteck
MMcCarthy 14,534
Recognized Expert Moderator MVP
IDKey should be a foreign key in tblPayments not a primary key. There should be a separate primary key in tblPayments unique to it. Try this and see if it makes any difference
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Steve |
last post by:
In AccessXP in Access2000 mode ---
TblCustomer: CustomerID, CustomerName
TblOrder: OrderID, CustomerID, OrderDate
TblOrderDetail: OrderDetailID, OrderID, Detail
Appropriate relationships are established and referential integrity is enforced.
FrmCustomer/SFrmOrder is a form/subform based on the respective tables.
|
by: Steve |
last post by:
For all who might have read my previous posts ----
AccessXP in 2000 mode and I presume in 2002 mode has a bug you might not know
about. If you try and delete a record in a subform where referential integrity
is enforced and cascade deltete is not set and then in the next operation try to
move to another record in the main form, you will get the error message
"Operation Not Supported In Transactions". This occurs because the link to the...
|
by: Don Seckler |
last post by:
I am building an application to track the distribution and returns of
copies of magazines.
Copies of a magazine that are unsold are returned by the retailer to
the wholesaler. They are destroyed by the wholesaler and the
wholesaler supplies the publisher (me) with a report (affidavit)about
which copies are destroyed.
The report (affidavit)from the wholesaler is dated and numbered and
says how many copies of each issue were returned...
|
by: Bob Rogers |
last post by:
I seem to have gotten myself into a bit of a pickle. I am trying to
delete a record from a table attached to a form, and it complains
about not being able to delete a record in a table because "the record
cannot be deleted because table sales includes related records"
Cascade deletes and referential integrity right?
Well no. The table I am deleting out of has NO referential integrity
with ANY other table. I tried renaming and deleting...
|
by: Dave |
last post by:
I have always taken it for granted that once RI is in place, no orphan
records can be created, and that RI can't be put in place while orphans
exist, but today I came across a situation where that is not true. I am
having a lot of trouble believing my eyes, so I would be very grateful for
anyone's feedback on similar issues.
The situation is the usual Invoice/Invoice Details one: the db has RI in
place on the relatioinship between...
| | |
by: Grant McLean |
last post by:
Hi
First a simple question ...
I have a table "access_log" that has foreign keys "app_id" and
"app_user_id" that reference the "application_type" and "app_user"
tables. When I insert into "access_log", the referential integrity
triggers generate these queries:
SELECT 1 FROM ONLY "public"."application_type" x
|
by: Andrew R |
last post by:
Hi
I'm creating a series of forms, each with with around 15-20 text boxes.
The text boxes will show data from tables, but are unbound to make them
more flexible.
I want the form to be used for both adding new data and modifying
existing data. I have created a save button on the form.
When the user clicks the save button, the code checks to see if there
|
by: AndyM |
last post by:
Hi,
I have a curious problem that is causing me large amounts of grief and is steadily turning me grey. Hopefully you guys can help.
I have a Master table that contains a CustomerID (as well as customer name etc), this is used as the Primary key to all the tables relating to that customer. I've been using 1 to Many relationships for most of the tables (e.g A customer can have many addresses) with Referential Integrity and cascading turned...
|
by: ApexData |
last post by:
Access2000, using a continuous form.
I’m getting a message that say “you cannot add or change a record
because a related record is required in table Employee”.
This occurs in all my combobox fields that have referential integrity
linked to another table. I do not have the field in the table set as
required, and I do not want to require the entry. If the record is
filled out completely and added to the table, you can go back to the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
