473,324 Members | 2,541 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Indexed field not updating

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
TransactionDetailsID
OrderCodeID
InventoryID
DeliveryDate
ConsignmentNumber

TransactionDetailsID & InventoryID are correctly populated with the respective ID number but OrderCodeID remains blank
May 19 '09 #1
6 2386
Stewart Ross
2,545 Expert Mod 2GB
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
May 19 '09 #2
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)
TransactionReference
StartDate
RequestedDate
TransactionStatusID (Converted to Combo Box) Row source = TransactionStatus.TransactionStatusID, TransactionStatus.Status
LinkToOrder
Comments

Details Subform (Transaction Details Table):
TransactionDetailsID (Hidden)
TransactionID (Hidden)
OrderCodeID (Hidden)
OrderCode (sourced from 'Order Codes' Table)
QTYOrdered
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
DeliveryDate
ConsignmentNumber
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.OrderCodeID' field to be populated from 'Order Codes.OrderCodeID', so that I can count the contents.
May 20 '09 #3
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
Stewart Ross
2,545 Expert Mod 2GB
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] & "'"
-Stewart
May 20 '09 #5
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
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

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

Similar topics

0
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...
0
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,...
3
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...
4
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...
0
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...
1
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...
17
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...
25
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...
0
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. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.