Can someone explain why a field on the many side of a one-to-many relationship, with referential integrity enforced, is not automatically updating?
I have 5 tables in my database with the following relationships:
Transactions (1-Many) Transaction Details (1-Many) Deliveries
Order Codes (1-Many) Transaction Details
Order Codes (1-Many) Inventory (1-Many) Deliveries
Order Codes (1-Many) Deliveries
All relationships are set to enforce referential integrity
I have one form with a subform to enter new transactions, based on Transactions & Transaction Details
I have another form for deliveries with 2 subforms, based on Transactions, Transaction Details & Deliveries
Both forms work as expected except that the OrderCodeID field in the Deliveries table is not being populated.
Deliveries Table has the following structure:
DeliveriesID
TransactionDeta ilsID
OrderCodeID
InventoryID
DeliveryDate
ConsignmentNumb er
TransactionDeta ilsID & InventoryID are correctly populated with the respective ID number but OrderCodeID remains blank
6  2402 
Hi Richard. Referential integrity does not of itself mean that values for the field on the many side of a relationship are set when the table is written to. It is only taken care of automatically when using a mainform-subform combination to implement the one-to-many data entry, as long as the Link Child Fields and Link Master Fields properties of the subform are correctly set to the fields involved.
In your case, if you are using a form-subform combination and a many-side foreign key is not being set, it suggests that the field concerned is missing from one of these properties on the embedded form.
If you are not using a subform to update your data you will need to build in a means to set up the value of the key field accordingly; as I have said, this is not done automatically except when using a form-subform structure, nor is it a feature of the referential integrity links you have correctly set.
-Stewart
I am using form-subform to automatically complete the many side data entry. The fields on the form I create using the forms wizard, with editing, look like this:
Deliveries Form::
Main Form (Transactions Table):
TransactionID (Deleted)
CustomerID (Deleted - Replaced with 'CustomerShortN ame' Text box sourced from 'Customers' Table)
TransactionType ID (Deleted - Replaced with 'Transaction' Text box sourced from 'Transaction Type' Table)
TransactionRefe rence
StartDate
RequestedDate
TransactionStat usID (Converted to Combo Box) Row source = TransactionStat us.TransactionS tatusID, TransactionStat us.Status
LinkToOrder
Comments
Details Subform (Transaction Details Table):
TransactionDeta ilsID (Hidden)
TransactionID (Hidden)
OrderCodeID (Hidden)
OrderCode (sourced from 'Order Codes' Table)
QTYOrdered
CommittedDate (Deleted)
Deliveries Subform (Deliveries Table & Inventory Table):
DeliveriesID (Hidden)
TransactionDeta ilsID (Hidden)
OrderCodeID (Hidden) [Tried visible]
InventoryID (Converted to Combo Box), RowSource = Inventory.Inven toryID, Inventory.Produ ctCode, Inventory.Seria lNumber, InventoryStatus .Status
ProductCode (from Inventory Table)
SerialNumber (from Inventory Table)
InventoryStatus (from Inventory Table), Converted to Combo Box - RowSource = InventoryStatus .ID, InventoryStatus .Status
DeliveryDate
ConsignmentNumb er
LinkToDN
Transaction and Transaction details data are entered via a separate Data Entry Main/Sub form. The intention is to use the Deliveries form to select product from inventory then enter delivery details to complete the transaction. This all works fine except that I need to keep track of what has been delivered on multiple product transactions (especially if there are back-orders). For this I need 'Deliveries.Ord erCodeID' field to be populated from 'Order Codes.OrderCode ID', so that I can count the contents.
I have been reading up on Link Master Field & Link Child field and it seems I have at least 2 issues to deal with. The controls on my forms are all named the same as the table fields; and, I may have an incorrect master/child link configuration. The first is easy I can rename all controlls on my forms. The second I am going to need some help with - assuming that's where the problem lies.
How do I determine if my Link Fields are correctly established?
If all other fields are being automatically populated with correct data, what do I change that will not cause these links to break?
Additionally, my previous post listed all the controlls on the main form and both sub forms, showing how these were modified to provide the form view I wanted - Is there an issue with the way I have configured these controlls that is stopping the OrderCodeID field from populating automatically?
The control names ARE normally the same as the names of the underlying fields - this is not a problem unless two copies of a control are placed on a form and the same name used for both.
I commend the amount of detail you have provided, but unfortunately it is not what we need to know to solve this one.
The main question you need to consider is where does the value for the OrderCodeID field you want to set in the many-side form come from? If it is from an existing control on your form you can use that control as the master in the master-child links settings on the main form. You would need to add the name of the control manually to the property, as it will not be available to the wizard you used to set up the forms - the wizard looks at the fields in the recordsource of the forms, not unbound controls. Adding the name of a second or third master or child control is straightforward - you just separate the first name from the second using a semicolon
[like this]; [second control]
If, however, your OrderCodeID value is not on the form you will need to pass a copy of the value to an unbound control somewhere, or lookup the value and set the OrderCodeID field accordingly.
Another approach is simply in code to set the default value of the OrderCodeID field to whatever the current OrderCodeID happens to be, using the DefaultValue property of the control concerned. Defaults are always text values - regardless of the type of the underlying field - but setting them from code in a form event is straightforward : - Me.YourControlName.DefaultValue = "'" & [some control name] & "'"
-Stewart
The value of the OrderCodeID that I want is populated into the Transaction Details table when I first create the transaction data entry. This is exposed in a control on the Details subform. From my reading so far it seems I cannot link to a subform from another subform - I must link to a control on the main from.
My options are therefore:
A. Create an unbound control on the main form where control source = OrderDetailsID value from the Details subform. Then establish Master/child links with this control from the Deliveries subform.
B. Add code to one of the event procedures of the OrderCodeID control on the Deliveries subform to copy the value (as text) from OrderCodeID control on the Details subform.
I will give each of these a go to see if I can get one to work. I have not had much luck so far getting anything to work using event procedures but I am willing to try.
Just one question - The OrderCode ID fields in each of the tables is a number field (one-to-many relationship with Order Codes table autonumber ID field) will option B work when I copy the data as text?
I have tried both options with mixed results. Details as follows...
Option (A)
Created unbound control Text25 on the main form.
Set Control Source of Text25 to:: = [Deliveries Transaction Subform].[Form]![OrderCodeID]
Added Link Fields as follows:
LinkMasterField s:: [Deliveries Transaction Subform].Form![TransactionDeta ilsID];Text25
LinkChildFields :: TransactionDeta ilsID;OrderCode ID
This does populate the Deliveries.Orde rCodeID field with a product code ID. Unfortunately, only the ID of the currently active record in the Details subform
A customer may order 5 off one product (OrderCodeID = 81) and 3 of another product (OrderCodeID = 91). This creates 2 records.
When I select the item from inventory I need the appropriate OrderCodeID to populate the field
Option (B)
Set property of Inventory.Order CodeID on Deliveries subform to visible.
Opened properties of Deliveries.Orde rCodeID control and set event.
Private Sub OrderCodeID_Exi t(Cancel As Integer) - Also tried On Click & On Enter events
Me.OrderCodeID. DefaultValue = "'" & [OrderCodeID_Inv entory] & "'"
End Sub
When I add a product, Nothing Happens?
The database behaves as if the code did not exist
I seem to get this whenever I try to use an event - I am completely lost when it comes to these properties.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Jesse Sheidlower |
last post by:
I'm struggling with speed issues on some queries that
I would have expected to be relatively fast. Perhaps
even more frustratingly, when I've tried to break
these down into their components, they still execute
very slowly. I've looked over all the relevant suggestions
for optimization and so forth, and there's nothing I can
tell that I'm missing.
An example of a query is to get all the words (the cg.cw
field) in a particular...
|
by: Jim S. |
last post by:
I'm having a horrible time simply inserting the date in a MySQL
database through a Visual Basic program. I have a table, called
"ORDERS"; "ID" is the primary key. I'm trying the insert the date,
into the MySQL database, which is a DATETIME datatype. I must keep it
DATETIME so it can be accessed via Microsoft Access.
Here are two versions of the program. The first one fails, with the
"Row cannot be located for updating. Some values may...
|
by: teddysnips |
last post by:
This from a SQL Server manual:
"Complex queries, however, such as those in decision support systems,
can reference large numbers of rows in base tables and aggregate large
amounts of information into relatively concise aggregates (such as sums
or averages). SQL Server 2000 supports creating a clustered index on a
view that implements such a complex
query. When the CREATE INDEX statement is executed, the result set of
the view SELECT is...
|
by: dixie |
last post by:
I have a table called "tblParticipants" with a field called "ID", which must
be indexed with No duplicates allowed. I am looking for a way of doing in
vba a small if ..then ..else .. routine that looks at that field in that
table and if it is not indexed, indexes it, and if it is indexed, jumps to a
label. I have a query called "qryAddIndex" that will index the field, what
I really need is the vba to test if the field is indexed or not.
...
|
by: liko81 |
last post by:
I have an Invoice class that must know, directly or indirectly, how to
do anything associated with creating, reading, or otherwise processing
an invoice to a customer. It is an uber-DAO object that combines the
business logic and DAO layers. The invoice it handles can have one to
many lines. The most logical implementation is an Invoice object with
an array of InvoiceLine objects. We of course want to prevent Joe
Programmer from directly...
| | |
by: solar |
last post by:
Indexed.Yes (Duplicates OK).
In my code for remote control i wanted to remove the property Indexed
of a field, but i receive the date type conversion error.How can i
change these properties by code ?
At the end of the list of the properties of the field,just below
Required, it is written Indexed.Yes (Duplicates OK). i want to turn it
to No and i write False, but it says property is unkown. What is the
exact property and the command to...
|
by: David C. Ullrich |
last post by:
Having a hard time phrasing this in the form
of a question...
The other day I saw a thread where someone asked
about overrideable properties and nobody offered
the advice that properties are Bad. So maybe we've
got over that. I suppose properties could have
Bad consequences if a user doesn't know they exist
and think that a certain property of an object is
just an ordinary attribute. But that applies to
|
by: Rick Collard |
last post by:
Using DAO 3.6 on an Access 2002 database, I'm getting unexpected
results with the FindFirst method. Here's the simple code to test:
Public Sub FindIt()
Dim db As Database, rs As Recordset
Dim sCriteria As String
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("tblTest", dbOpenDynaset)
|
by: Mike |
last post by:
So here's the situation (.NET 2.0 btw):
I have a form, and on this form is a textbox among many other
databound controls. The textbox is bound to a field in a data table
via the Text property. In this table there are multiple columns that
cannot be NULL, which, are bound to other controls (but they're not
really important at this time).
I create a new row via the currency manager like so:
_currencyManager.AddNew()
_currentRow =...
|
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: 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: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
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...
| |
