424,054 Members | 1,055 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,054 IT Pros & Developers. It's quick & easy.

Inserting data into related tables via ado recordset

P: 3
we've 2 tbls [Invoice Details] & [Accs Sale Details] in a 1 to many relationship with cascade update option. data from a third table is to be inserted in above 2 tables but it is not successful uptil now.

the error message i got is "U can't change or add a record because a related record is required in table 'Invoice Details'

did extensive search to find the answer but failed plz help. code is below

Expand|Select|Wrap|Line Numbers
  1. Private Sub Enter_Click()
  2. Dim cn As New ADODB.Connection, rsSalesRep, rsMd, rsAcc, 
  3. Dim strSQL, Msg As String
  4.  
  5.     cn.Open CurrentProject.Connection    
  6.     Set rsSalesRep = New ADODB.Recordset 
  7.     Set rsAcc = New ADODB.Recordset    
  8.  
  9.     strSQL = "SELECT [Sales Orders].[SalesRep ID], [Sales Orders].[Md-ID], [Sales Orders].[Acl-ID], [Sales Orders].Brand, Sales Orders].[Qty Issued], [Sales Orders].[Dt Issued], [Sales Orders].[Unit Cost], [Sales Orders].[Qty Returned], [Sales Orders].[Dt Returned], ([Qty Issued]-[Qty Returned])*[Unit Cost] AS [Extended Price], [Sales Orders].[Ledger Entry] FROM SalesRep LEFT JOIN [Sales Orders] ON SalesRep.ID = [Sales Orders].[SalesRep ID] WHERE ((([Sales Orders].[Ledger Entry])=No));"
  10.  
  11.     rsSalesRep.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic
  12.  
  13.     strSQL = "SELECT [Accs Sale Details].[Inv-ID], [Accs Sale Details].[Acl-ID], [Accs Sale Details].[Md-ID], Accs Sale Details].Qty , [Accs Sale Details].[Unit Cost], [Accs Sale Details].Brand, Invoice Details].[Inv#] , [Invoice Details].InvDt, [Invoice Details].[SalesRep ID] FROM Invoice Details] INNER JOIN [Accs Sale Details] ON [Invoice Details].[Inv-ID] = [Accs Sale Details].[Inv-ID];"
  14.  
  15.  
  16.     rsAcc.Open strSQL, cn, adOpenKeyset, adLockOptimistic
  17.  
  18.         Do While Not rsSalesRep.EOF
  19.  
  20.             If (IsNull(rsSalesRep![Extended Price].Value) = False Or rsSalesRep![Extended Price].Value <> 0) Then
  21.  
  22.                If (rsSalesRep![Acl-ID].Value <> 27) Then
  23.                   With rsAcc
  24.                   .AddNew
  25.                   ![SalesRep ID] = rsSalesRep![SalesRep ID]
  26.                   ![Inv#] = rsSalesRep![SalesRep ID]
  27.                   !InvDt = rsSalesRep![Dt Returned]
  28.                   !IV = rsSalesRep![SalesRep ID]
  29.                   ![Acl-ID] = rsSalesRep![Acl-ID]
  30.                   ![Md-ID] = rsSalesRep![Md-ID]
  31.                   !Qty = rsSalesRep![S-Qty]
  32.                   ![Unit Cost] = rsSalesRep![Unit Cost]
  33.                   !Brand = rsSalesRep!Brand
  34.                   .Update
  35.                   End With
  36.                   rsSalesRep![Ledger Entry].Value = True 
  37.                End If
  38.             End If
  39.         'Exit Sub
  40.  
  41. '        Me.Requery: Me.Repaint
  42.          rsSalesRep.MoveNext
  43.          Loop
  44.  
  45.  
  46.     rsSalesRep.Close:    rsMd.Close:   rsAcc.Close:    cn.Close
  47.     Set rsSalesRep = Nothing:    Set rsMd = Nothing:     Set rsAcc = Nothing:    Set cn = Nothing
  48.  
  49.        Me.Sale_sbf_Sales_Rep_Orders.Requery
Apr 16 '14 #1

✓ answered by zmbd

2 tbls [Invoice Details] & [Accs Sale Details] in a 1 to many relationship
-- Please read > Before Posting (VBA or SQL) Code and perform the basic trouble shooting steps as outlined therein

"U can't change or add a record because a related record is required in table 'Invoice Details'
-- Please post the EXACT title, number, and text of the error message. Please do not alter, omit, nor abbreviate the information provided in the error message

-- what this is saying is that the child table is attempting to create a record wherein the related data from the parent table either has not been entered or does not exist.

For example, you have a table with customer names with record ids 1 thru 10 as the primary-key and child table that has addresses that uses the customer name record id as a foreign-key...

if you attempted to create the address record for customer record id 11 you would get this type of error because that record ([customer_names]![record_id]=11) doesn't exist in the customer names table - yet.

--- insert a STOP command at line 4 and start your code. Once you enter the debug state at line 4 use [F8] to step thru your code: take a very careful look at how your code is creating the new record…. You'll see where your error is!

Expand|Select|Wrap|Line Numbers
  1.  rsSalesRep.Close:    rsMd.Close:   rsAcc.Close:    cn.Close
  2.     Set rsSalesRep = Nothing:    Set rsMd = Nothing:     Set rsAcc = Nothing:    Set cn = Nothing
This is a horrible coding practice.
Having multiple commands on a single line, although allowed, does not improve your code execution speed, is harder to maintain, is easily missed by anyone that must come behind you to maintain the code (they will bless your name greatly using many four-letter-english-words), and is @ near impossible to trouble shoot should there be an error on that line as the parser will error flag the entire set of commands on that line.

Even Micosoft:
----------
Statements in Visual Basic (Visual Studio 2013)
Putting Multiple Statements on One Line
--------------------------------------------------------------------------------

You can have multiple statements on a single line separated by the colon (:) character. The following example illustrates this.

VBCopy
Dim sampleString As String = "Hello World" : MsgBox(sampleString)

Though occasionally convenient, this form of syntax makes your code hard to read and maintain. Thus, it is recommended that you keep one statement to a line.
Note the underscoring and highlight emphasis are mine
----------

Share this Question
Share on Google+
4 Replies


Seth Schrock
Expert 2.5K+
P: 2,911
I've never used ADO so I can't help you there, but I think that it would be a lot simpler to just do an INSERT query. You already have the SELECT portion created so all you need to do is add the INSERT portion in front of that and then run the SQL code using
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute strSQL, dbFailOnError
Apr 16 '14 #2

zmbd
Expert Mod 5K+
P: 5,285
2 tbls [Invoice Details] & [Accs Sale Details] in a 1 to many relationship
-- Please read > Before Posting (VBA or SQL) Code and perform the basic trouble shooting steps as outlined therein

"U can't change or add a record because a related record is required in table 'Invoice Details'
-- Please post the EXACT title, number, and text of the error message. Please do not alter, omit, nor abbreviate the information provided in the error message

-- what this is saying is that the child table is attempting to create a record wherein the related data from the parent table either has not been entered or does not exist.

For example, you have a table with customer names with record ids 1 thru 10 as the primary-key and child table that has addresses that uses the customer name record id as a foreign-key...

if you attempted to create the address record for customer record id 11 you would get this type of error because that record ([customer_names]![record_id]=11) doesn't exist in the customer names table - yet.

--- insert a STOP command at line 4 and start your code. Once you enter the debug state at line 4 use [F8] to step thru your code: take a very careful look at how your code is creating the new record…. You'll see where your error is!

Expand|Select|Wrap|Line Numbers
  1.  rsSalesRep.Close:    rsMd.Close:   rsAcc.Close:    cn.Close
  2.     Set rsSalesRep = Nothing:    Set rsMd = Nothing:     Set rsAcc = Nothing:    Set cn = Nothing
This is a horrible coding practice.
Having multiple commands on a single line, although allowed, does not improve your code execution speed, is harder to maintain, is easily missed by anyone that must come behind you to maintain the code (they will bless your name greatly using many four-letter-english-words), and is @ near impossible to trouble shoot should there be an error on that line as the parser will error flag the entire set of commands on that line.

Even Micosoft:
----------
Statements in Visual Basic (Visual Studio 2013)
Putting Multiple Statements on One Line
--------------------------------------------------------------------------------

You can have multiple statements on a single line separated by the colon (:) character. The following example illustrates this.

VBCopy
Dim sampleString As String = "Hello World" : MsgBox(sampleString)

Though occasionally convenient, this form of syntax makes your code hard to read and maintain. Thus, it is recommended that you keep one statement to a line.
Note the underscoring and highlight emphasis are mine
----------
Apr 17 '14 #3

P: 3
dear Seth Schrock
i replaced the SELECT statement with INSERT statement but still i get the same message. i m re checking the code as advised by moderator.
regards
Apr 17 '14 #4

P: 3
many many thanks to zmbd. problem solved
regards
Apr 23 '14 #5

Post your reply

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