Indexed field not updating

**Stewart Ross** · May 19 '09, 03:34 PM

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

**Richard Penfold** · May 20 '09, 12:51 AM

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.

**Richard Penfold** · May 20 '09, 12:49 PM

Is This right?

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?

**Stewart Ross** · May 20 '09, 08:07 PM

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 :

Code:

Me.YourControlName.DefaultValue = "'" & [some control name] & "'"

-Stewart

**Richard Penfold** · May 20 '09, 11:20 PM

Clarifying my options

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?

**Richard Penfold** · May 21 '09, 04:17 AM

Results of testing.

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.