HELP Please -Deadline / Referential Integrity error

  • Thread starter Thread starter jwrnana
  • Start date Start date
J

jwrnana

I have an order entry database (Template from access).

1. Create customer info in form from customer table.
2. Make certain products are in product table/form.
3. Open orders form.
4. Select customer.
5. Go to subform, orders by customer.
6. Enter info in another subform/ order details.

I can enter all of this information -- but when I get to the end of the
orders details subform (entered product, quantity) errors occurs:

You cannot add or change a record because a
related
record is required in table "Orders".

I have entered previous customers with all pertinent information without a
problem. Now I am getting this error. When I select HELP, I get message
about referential integrity/one-to-many relationship. I have not changed
anything. What do I need to do? I have linked the tables products and
orders together. Everything looks okay and I haven't changed anything.

Code Follows:

Private Sub Form_AfterDelConfirm(Status As Integer)
Forms![Orders]![Order Details Subform].Requery
End Sub
Private Sub ProductID_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub
Private Sub ProductID_AfterUpdate()
Me![UnitPrice] = Me![ProductID].Column(2)
End Sub
Private Sub ProductID_DblClick(Cancel As Integer)
On Error GoTo Err_ProductID_DblClick
Dim lngProductID As Long

If IsNull(Me![ProductID]) Then
Me![ProductID].Text = ""
Else
lngProductID = Me![ProductID]
Me![ProductID] = Null
End If
DoCmd.OpenForm "Products", , , , , acDialog, "GotoNew"
Me!ProductID.Requery
If lngProductID <> 0 Then Me![ProductID] = lngProductID

Exit_ProductID_DblClick:
Exit Sub

Err_ProductID_DblClick:
MsgBox Err.Description
Resume Exit_ProductID_DblClick
End Sub



I am not very good with codes. Thank you in advance.

My deadline is quickly approaching and I am LOST.
 
I have an order entry database (Template from access).

1. Create customer info in form from customer table.
2. Make certain products are in product table/form.
3. Open orders form.
4. Select customer.
5. Go to subform, orders by customer.
6. Enter info in another subform/ order details.

I can enter all of this information -- but when I get to the end of the
orders details subform (entered product, quantity) errors occurs:

You cannot add or change a record because a
related
record is required in table "Orders".
Click to expand...

It sounds like you're entering a nonexistant OrderID - perhaps a
default 0. What is the Master Link Field/Child Link Field of the
OrderDetails subform? Is it a subform of the Orders form?
I have entered previous customers with all pertinent information without a
problem. Now I am getting this error. When I select HELP, I get message
about referential integrity/one-to-many relationship. I have not changed
anything. What do I need to do? I have linked the tables products and
orders together. Everything looks okay and I haven't changed anything.
Click to expand...

What are the relationships between Customers, Orders, OrderDetails,
Products? What are the Recordsources of the relevant forms? What are
the Master/Child Link Fields of the forms? Have you compared your
database with the Orders application in the Northwind sample database?
Code Follows:

Private Sub Form_AfterDelConfirm(Status As Integer)
Forms![Orders]![Order Details Subform].Requery
End Sub
Private Sub ProductID_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub
Private Sub ProductID_AfterUpdate()
Me![UnitPrice] = Me![ProductID].Column(2)
End Sub
Click to expand...

well, these don't have any chance of causing this error...
Private Sub ProductID_DblClick(Cancel As Integer)
Click to expand...

but what's the intention of this sub? Let's walk through it...
On Error GoTo Err_ProductID_DblClick
Dim lngProductID As Long

If IsNull(Me![ProductID]) Then
Click to expand...
If the ProductID textbox (textbox? or combo box?) is blank...
Me![ProductID].Text = ""
Click to expand...
set it to blank... i.e. do nothing whatsoever...
Else
lngProductID = Me![ProductID]
Me![ProductID] = Null
Click to expand...
and if it's not blank, *set it to blank*??
End If
DoCmd.OpenForm "Products", , , , , acDialog, "GotoNew"
Me!ProductID.Requery
If lngProductID <> 0 Then Me![ProductID] = lngProductID
Click to expand...

and then set it back to the previous value!?

I don't get it.
Exit_ProductID_DblClick:
Exit Sub

Err_ProductID_DblClick:
MsgBox Err.Description
Resume Exit_ProductID_DblClick
End Sub



I am not very good with codes. Thank you in advance.

My deadline is quickly approaching and I am LOST.
Click to expand...

Good luck...

John W. Vinson[MVP]
 
Orders details form is a subform of the orders form.



Relationships: Customer to Orders = Customer ID (Primary Key) to Customer
ID in Orders (1 to many)
Orders to Orders Details = Order ID (Primary
Key) to Order ID in Orders Details (1 to many)
Order Details to Products = Product ID to
Product ID (Primary Key) (many to 1)



Record Source of Orders form: SELECT DISTINCTROW Orders.*,
Customers.CompanyName FROM Customers INNER JOIN Orders ON
Customers.CustomerID=Orders.CustomerID WHERE
((Orders.CustomerID=forms![Orders by Customer]!CustomerID));


Record Source of Orders by Customer Subform: SELECT DISTINCTROW
Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.FreightCharge,
Orders.SalesTaxRate,
Sum(Clng(nz([Quantity]*[UnitPrice]*(1-[Discount]))*100)/100) AS LineTotal,
Orders.ShipDate, [Sum Of Payments Query].[Total Payments] AS [Total
Payments] FROM (Orders LEFT JOIN [Sum Of Payments Query] ON
Orders.OrderID=[Sum Of Payments Query].OrderID) LEFT JOIN [Order Details] ON
Orders.OrderID=[Order Details].OrderID GROUP BY Orders.OrderID,
Orders.CustomerID, Orders.OrderDate, Orders.FreightCharge,
Orders.SalesTaxRate, Orders.ShipDate, [Sum Of Payments Query].[Total
Payments];


Record Source of Order Detail Subform: SELECT DISTINCTROW [Order
Details].OrderDetailID, [Order Details].OrderID, [Order Details].LineItem,
[Order Details].ProductID, [Order Details].SerialNum, [Order
Details].Quantity, [Order Details].UnitPrice, [Order Details].Discount,
Round([Quantity]*[Order Details].UnitPrice*(1-[Discount]),2) AS [Line
Total], Products.ProductCode, [HandlingPct]*Round([Quantity]*[Order
Details].UnitPrice*(1-[Discount]),2) AS HandlingChg FROM Products INNER JOIN
[Order Details] ON Products.ProductID=[Order Details].ProductID ORDER BY
[Order Details].LineItem;

Record Source of Products form: SELECT Products.ProductID,
Products.ProductName, Products.UnitPrice, Products.ProductCode,
Products.UnitWeight, Products.HandlingPct, SIN.SIN, Contracts.ContractNum
FROM SIN INNER JOIN (Contracts INNER JOIN Products ON
Contracts.ContractID=Products.ContractID) ON SIN.SINID=Products.SINID;


Your question "What are the Master/Child Link Fields of the forms?" Are you
asking what is the event procedure? Please tell me how to get information
you need to look at.

I have not compared with Northwind, but will.

Thanks in advance!



John Vinson said:
I have an order entry database (Template from access).

1. Create customer info in form from customer table.
2. Make certain products are in product table/form.
3. Open orders form.
4. Select customer.
5. Go to subform, orders by customer.
6. Enter info in another subform/ order details.

I can enter all of this information -- but when I get to the end of the
orders details subform (entered product, quantity) errors occurs:

You cannot add or change a record because a
related
record is required in table "Orders".
Click to expand...

It sounds like you're entering a nonexistant OrderID - perhaps a
default 0. What is the Master Link Field/Child Link Field of the
OrderDetails subform? Is it a subform of the Orders form?
I have entered previous customers with all pertinent information without a
problem. Now I am getting this error. When I select HELP, I get message
about referential integrity/one-to-many relationship. I have not changed
anything. What do I need to do? I have linked the tables products and
orders together. Everything looks okay and I haven't changed anything.
Click to expand...

What are the relationships between Customers, Orders, OrderDetails,
Products? What are the Recordsources of the relevant forms? What are
the Master/Child Link Fields of the forms? Have you compared your
database with the Orders application in the Northwind sample database?
Code Follows:

Private Sub Form_AfterDelConfirm(Status As Integer)
Forms![Orders]![Order Details Subform].Requery
End Sub
Private Sub ProductID_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub
Private Sub ProductID_AfterUpdate()
Me![UnitPrice] = Me![ProductID].Column(2)
End Sub
Click to expand...

well, these don't have any chance of causing this error...
Private Sub ProductID_DblClick(Cancel As Integer)
Click to expand...

but what's the intention of this sub? Let's walk through it...
On Error GoTo Err_ProductID_DblClick
Dim lngProductID As Long

If IsNull(Me![ProductID]) Then
Click to expand...
If the ProductID textbox (textbox? or combo box?) is blank...
Me![ProductID].Text = ""
Click to expand...
set it to blank... i.e. do nothing whatsoever...
Else
lngProductID = Me![ProductID]
Me![ProductID] = Null
Click to expand...
and if it's not blank, *set it to blank*??
End If
DoCmd.OpenForm "Products", , , , , acDialog, "GotoNew"
Me!ProductID.Requery
If lngProductID <> 0 Then Me![ProductID] = lngProductID
Click to expand...

and then set it back to the previous value!?

I don't get it.
Exit_ProductID_DblClick:
Exit Sub

Err_ProductID_DblClick:
MsgBox Err.Description
Resume Exit_ProductID_DblClick
End Sub



I am not very good with codes. Thank you in advance.

My deadline is quickly approaching and I am LOST.
Click to expand...

Good luck...

John W. Vinson[MVP]
Click to expand...
 
Back
Top