By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,489 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,489 IT Pros & Developers. It's quick & easy.

Indexed field not updating

P: 24
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:

TransactionDetailsID & InventoryID are correctly populated with the respective ID number but OrderCodeID remains blank
May 19 '09 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 2.5K+
P: 2,545
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.

May 19 '09 #2

P: 24
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 'CustomerShortName' Text box sourced from 'Customers' Table)
TransactionTypeID (Deleted - Replaced with 'Transaction' Text box sourced from 'Transaction Type' Table)
TransactionStatusID (Converted to Combo Box) Row source = TransactionStatus.TransactionStatusID, TransactionStatus.Status

Details Subform (Transaction Details Table):
TransactionDetailsID (Hidden)
TransactionID (Hidden)
OrderCodeID (Hidden)
OrderCode (sourced from 'Order Codes' Table)
CommittedDate (Deleted)

Deliveries Subform (Deliveries Table & Inventory Table):
DeliveriesID (Hidden)
TransactionDetailsID (Hidden)
OrderCodeID (Hidden) [Tried visible]
InventoryID (Converted to Combo Box), RowSource = Inventory.InventoryID, Inventory.ProductCode, Inventory.SerialNumber, InventoryStatus.Status
ProductCode (from Inventory Table)
SerialNumber (from Inventory Table)
InventoryStatus (from Inventory Table), Converted to Combo Box - RowSource = InventoryStatus.ID, InventoryStatus.Status

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.OrderCodeID' field to be populated from 'Order Codes.OrderCodeID', so that I can count the contents.
May 20 '09 #3

P: 24
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?
May 20 '09 #4

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. Me.YourControlName.DefaultValue = "'" & [some control name] & "'"
May 20 '09 #5

P: 24
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?
May 20 '09 #6

P: 24
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:
LinkMasterFields:: [Deliveries Transaction Subform].Form![TransactionDetailsID];Text25
LinkChildFields:: TransactionDetailsID;OrderCodeID
This does populate the Deliveries.OrderCodeID 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.OrderCodeID on Deliveries subform to visible.
Opened properties of Deliveries.OrderCodeID control and set event.
Private Sub OrderCodeID_Exit(Cancel As Integer) - Also tried On Click & On Enter events
Me.OrderCodeID.DefaultValue = "'" & [OrderCodeID_Inventory] & "'"
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.
May 21 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.