Duplicate Records in Main Form and Subform

[accessonly11]

hi members,

i want to duplicate form and its subform record where invoice number matched, but

i am facing an error 3201, you cannot add or change a record because a related record is required in table tblcustomer.....
while this code

rstSale.Update

got highlighted yellow.
my code behind click button is as under

Private Sub cmdLoad_Click()
    Dim strSql As String
    Dim rstSale As Recordset
    Dim rstSaleDetails As Recordset
    Dim lngNewSaleID As Long

'Get the invoice number from the text box
    Dim strInvoiceNo As Long
    strInvoiceNo = Me.InvoiceNo.Value
   
'Get the existing record for that particular invoice from the Saledata (main table)
    strSql = "Select * From tblSale where SINo = " & strInvoiceNo & ""
    Set rstSale = CurrentDb.OpenRecordset(strSql)
   
'Create a new record in the tblSale table with the same data as the existing record
rstSale.AddNew
    rstSale("SINo").Value = rstSale("SINo")
    rstSale("CustomerID").Value = rstSale("CustomerID")
    rstSale("PatientName").Value = rstSale("PatientName")
    rstSale("DateTime").Value = rstSale("DateTime")
    rstSale("SaleStatus").Value = rstSale("SaleStatus")
    rstSale("TRetail").Value = rstSale("TRetail")
    rstSale("TDiscount").Value = rstSale("TDiscount")
    rstSale("TSale").Value = rstSale("TSale")
    rstSale("CashReceived").Value = rstSale("CashReceived")
    rstSale("IsPaid").Value = rstSale("IsPaid")
    rstSale("DateSettle").Value = rstSale("DateSettle")
    rstSale("CreatedBy").Value = rstSale("CreatedBy")
   
    rstSale.Update
    lngNewSaleID = rstSale("SaleID").Value

'Get the Sale Details for the existing invoice
strSql = "SELECT * FROM tblSaleDetails where SaleID = " & rstSale("SaleID")
Set rstSaleDetails = CurrentDb.OpenRecordset(strSql)

'Create new sale detail records for the new invoice
rstSaleDetails.MoveFirst
Do Until rstSaleDetails.EOF
    rstSaleDetails.AddNew
    rstSaleDetails("SaleID").Value = lngNewSaleID
    rstSaleDetails("ProductID").Value = rstSaleDetails("ProductID")
    rstSaleDetails("Qty").Value = rstSaleDetails("Qty")
    rstSaleDetails("Retail").Value = rstSaleDetails("Retail")
    rstSaleDetails("Discount").Value = rstSaleDetails("Discount")
    rstSaleDetails("Sale").Value = rstSaleDetails("Sale")
    rstSaleDetails("TRetail").Value = rstSaleDetails("TRetail")
    rstSaleDetails("TDiscount").Value = rstSaleDetails("TDiscount")
    rstSaleDetails("TSale").Value = rstSaleDetails("TSale")
    rstSaleDetails.Update
    rstSaleDetails.MoveNext
  Loop
   
    'Refresh the main form and subform to show the new record
    Me.Refresh
    Me.frmSaleDetails1.Form.Refresh
   
End Sub

any suggestions please,
please tell me for further info about my db

 

[mike60smart]

hi members,

i want to duplicate form and its subform record where invoice number matched, but

i am facing an error 3201, you cannot add or change a record because a related record is required in table tblcustomer.....
while this code

rstSale.Update

got highlighted yellow.
my code behind click button is as under

Private Sub cmdLoad_Click()
    Dim strSql As String
    Dim rstSale As Recordset
    Dim rstSaleDetails As Recordset
    Dim lngNewSaleID As Long

'Get the invoice number from the text box
    Dim strInvoiceNo As Long
    strInvoiceNo = Me.InvoiceNo.Value
  
'Get the existing record for that particular invoice from the Saledata (main table)
    strSql = "Select * From tblSale where SINo = " & strInvoiceNo & ""
    Set rstSale = CurrentDb.OpenRecordset(strSql)
  
'Create a new record in the tblSale table with the same data as the existing record
rstSale.AddNew
    rstSale("SINo").Value = rstSale("SINo")
    rstSale("CustomerID").Value = rstSale("CustomerID")
    rstSale("PatientName").Value = rstSale("PatientName")
    rstSale("DateTime").Value = rstSale("DateTime")
    rstSale("SaleStatus").Value = rstSale("SaleStatus")
    rstSale("TRetail").Value = rstSale("TRetail")
    rstSale("TDiscount").Value = rstSale("TDiscount")
    rstSale("TSale").Value = rstSale("TSale")
    rstSale("CashReceived").Value = rstSale("CashReceived")
    rstSale("IsPaid").Value = rstSale("IsPaid")
    rstSale("DateSettle").Value = rstSale("DateSettle")
    rstSale("CreatedBy").Value = rstSale("CreatedBy")
  
    rstSale.Update
    lngNewSaleID = rstSale("SaleID").Value

'Get the Sale Details for the existing invoice
strSql = "SELECT * FROM tblSaleDetails where SaleID = " & rstSale("SaleID")
Set rstSaleDetails = CurrentDb.OpenRecordset(strSql)

'Create new sale detail records for the new invoice
rstSaleDetails.MoveFirst
Do Until rstSaleDetails.EOF
    rstSaleDetails.AddNew
    rstSaleDetails("SaleID").Value = lngNewSaleID
    rstSaleDetails("ProductID").Value = rstSaleDetails("ProductID")
    rstSaleDetails("Qty").Value = rstSaleDetails("Qty")
    rstSaleDetails("Retail").Value = rstSaleDetails("Retail")
    rstSaleDetails("Discount").Value = rstSaleDetails("Discount")
    rstSaleDetails("Sale").Value = rstSaleDetails("Sale")
    rstSaleDetails("TRetail").Value = rstSaleDetails("TRetail")
    rstSaleDetails("TDiscount").Value = rstSaleDetails("TDiscount")
    rstSaleDetails("TSale").Value = rstSaleDetails("TSale")
    rstSaleDetails.Update
    rstSaleDetails.MoveNext
  Loop
  
    'Refresh the main form and subform to show the new record
    Me.Refresh
    Me.frmSaleDetails1.Form.Refresh
  
End Sub

any suggestions please,
please tell me for further info about my db

Hi
Not really understanding your process.

If you have a Main Form based on your tblSale
With a Subform based on your tblSaleDetails
If you want to add the last SaleDetail record again then all you need is a process to Insert the Current Record as a New Record

 

[Pat Hartman]

You can't use the same recordset object to both read and write. It cannot be positioned on two records at the same time.

Do NOT reposition the recordset on the main form. Go to the record you want to copy. then the button can open open the recordset clone to write to. copy from the form controls to the opened recordset. Save the generated ID. Then run an append query for the child records. It needs two arguments. It needs the Old SaleID to select the rows to copy and it needs the New SaleID as the FK to insert.

Here is working code from an application. It is more complicated since it does a THREE level copy. You want the "top" and "bottom" code. Ignore the "middle".

Copy order from TMPM application

Private Sub cmdCopyQuote_Click()
On Error GoTo Err_Handler
'Purpose:   Duplicate the main form record and related records in the subform.
    Dim str2Sql          As String
    Dim strSQL           As String 
    Dim NewQuoteID       As Long       'FK value of the new record.
    Dim OldQuoteID       As Long
    Dim NewQuoteDetailID As Long
    Dim db               As DAO.Database
    Dim qd               As DAO.QueryDef
    Dim FromQD           As DAO.QueryDef
    Dim FromRS           As DAO.Recordset
    Dim ToTD             As DAO.TableDef
    Dim ToRS             As DAO.Recordset

    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If

    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
        Exit Sub
    End If 

    'Duplicate the main record: add to form's clone.
    OldQuoteID = Me.QuoteID
    With Me.RecordsetClone
        .AddNew
        !CustID = Me.cboCustID
        !JobName = Me.JobName
        !Notes = Me.Notes
        !TermsID = Me.TermsID
        !ShippingID = Me.ShippingID
        !CustConID = Me.cboCustConID
        !CustLocID = Me.cboCustLocID
        !QuoteNumber = Nz(DMax("QuoteNumber", "tblQuotes"), 0) + 1
        !QuoteDate = Date
        !Expires = Date + 30
        .Update

        'Save the primary key value, to use as the foreign key for the related records.
        .Bookmark = .LastModified
        NewQuoteID = !QuoteID
        'Display the new duplicate.  This repositions the form to the new Order
        Me.Bookmark = .LastModified
    End With
'''''middle table of three
        'Duplicate the related Item records using DAO recordset.  Append Accessories with  append query inside loop
        Set db = CurrentDb()
        Set ToTD = db!tblquoteDetails
        Set ToRS = ToTD.OpenRecordset
        Set FromQD = db.QueryDefs!qQuoteCopyDetails 'just a select query with a where
            FromQD.Parameters!EnterOldQuoteID = OldQuoteID
        Set FromRS = FromQD.OpenRecordset(dbOpenDynaset, dbSeeChanges)
        With FromRS
            .MoveFirst
            Do Until .EOF = True
                ToRS.AddNew
                ToRS!ItemNo = !ItemNo
                ToRS!EstID = !EstID
                ToRS!ModelNo = !ModelNo
                ToRS!Description = !Description
                ToRS!Qty = !Qty
                ToRS!Price = !Price
                ToRS!AccessPrice = !AccessPrice
                ToRS!QuoteID = NewQuoteID
                NewQuoteDetailID = ToRS!QuoteDetailID
                ToRS.Update             

'''''bottom table uses append query.              
                ''  copy accessories
                Set qd = db.QueryDefs!qCopyAppendQuoteAcc
                    qd.Parameters!EnterOldQuoteDetailID = !QuoteDetailID
                    qd.Parameters!EnterNewQuoteDetailID = NewQuoteDetailID
                    qd.Execute dbSeeChanges
          
                .MoveNext
            Loop
        End With

        Me.SfrmQuoteDetails.Requery  '' Show the newly copied records
        ToRS.Close
        FromRS.Close
        Set ToRS = Nothing
        Set FromRS = Nothing
        Set db = Nothing

Exit_Handler:
    Exit Sub

Err_Handler:
     Select Case Err.Number
        Case 3021, 2501   ' update cancelled
            Resume Exit_Handler
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume Exit_Handler
    End Select

End Sub


--------------------------------------------------
The query:

INSERT INTO tblquoteacc ( QuoteDetailID, ItemNo, EstID, ModelNo, Description, Qty, Price )
SELECT [EnterNewQuoteDetailID] AS Expr1, tblQuoteAcc.ItemNo, tblQuoteAcc.EstID, tblQuoteAcc.ModelNo, tblQuoteAcc.Description, tblQuoteAcc.Qty, tblQuoteAcc.Price
FROM tblQuoteAcc
WHERE (tblQuoteAcc.QuoteDetailID = [EnterOldQuoteDetailID];

 

[accessonly11]

dear i tried to modify this code according to my database, but still unsuccessfull. the modified code is

Private Sub Command597_Click()
'Code Received Via Mr Pat Hartman, Access-programmers.co.uk

On Error GoTo Err_Handler
'Purpose:   Duplicate the main form record and related records in the subform.
    Dim str2Sql          As String
    Dim strSQL           As String
    Dim NewQuoteID       As Long       'FK value of the new record.
    Dim OldQuoteID       As Long
    Dim NewQuoteDetailID As Long
    Dim qd               As DAO.QueryDef
    Dim db               As DAO.Database
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If

    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
        Exit Sub
    End If

    'Duplicate the main record: add to form's clone.
    OldQuoteID = Me.InvoiceNo
    With Me.RecordsetClone
        .AddNew
        !SINo = Me.SINo
        !CustomerID = Me.CustomerID
        !PatientName = Me.PatientName
        !DateTime = Me.DateTime
        !SaleStatus = Me.SaleStatus
        !TRetail = Me.TRetail
        !TDiscount = Me.TDiscount
        !TSale = Me.TSale
        !CreatedBy = Me.CreatedBy
        .Update

        'Save the primary key value, to use as the foreign key for the related records.
        .Bookmark = .LastModified
        NewQuoteID = !SaleID
        'Display the new duplicate.  This repositions the form to the new Order
        Me.Bookmark = .LastModified
    End With

Set db = CurrentDb()
'''''bottom table uses append query.
                ''  copy accessories
                Set qd = db.QueryDefs!QRepeat
                    'qd.Parameters!EnterOldQuoteDetailID = !QuoteDetailID
                    'qd.Parameters!EnterNewQuoteDetailID = NewQuoteDetailID
                    qd.Execute dbSeeChanges
          
                .MoveNext
            Loop
        End With

        Me.SfrmQuoteDetails.Requery  '' Show the newly copied records
        ToRS.Close
        FromRS.Close
        Set ToRS = Nothing
        Set FromRS = Nothing
        Set db = Nothing

Exit_Handler:
    Exit Sub

Err_Handler:
     Select Case Err.Number
        Case 3021, 2501   ' update cancelled
            Resume Exit_Handler
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume Exit_Handler
    End Select

End Sub

 

[accessonly11]

here i will try to simplify my db condition.

i have two table
tblSales, with these fields (screenshots)

tblSaleDetails, with these fields

i designed main form with its subform
>main form is based on tblSale via Query
>subform is also based on tblSaleDetails via Query
>i need to repeat mainform and its related subform record where SIN (sale Invoice No, a field of main table tblSales) is equall to number value which i provided in unbound textbox of main form "InvoiceNo", saving new SaleID into tblSales as well as tblSaleDetails to make relateable.

i hope these info will be enough, but will provide more if any missing

 

[Gasman]

Well you commented out the ID fields, so what do you expect?
Not sure why you have a MoveNext.?
I would expect one parent record and possible multiple child records.

 

[accessonly11]

i do some more changes in upper code provided by mr. Pat Hartman,

Private Sub Command597_Click()
'Code Received Via Mr Pat Hartman, Access-programmers.co.uk

On Error GoTo Err_Handler
'Purpose:   Duplicate the main form record and related records in the subform.
    Dim str2Sql          As String
    Dim strSQL           As String
    Dim NewQuoteID       As Long       'FK value of the new record.
    Dim OldQuoteID       As Long
    Dim NewQuoteDetailID As Long
    Dim qd               As DAO.QueryDef
    Dim db               As DAO.Database
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If

    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
        Exit Sub
    End If

    'Duplicate the main record: add to form's clone.
    OldQuoteID = Me.InvoiceNo
    With Me.RecordsetClone
        .AddNew
        !SINo = Me.SINo
        !CustomerID = Me.CustomerID
        !PatientName = Me.PatientName
        !DateTime = Me.DateTime
        !SaleStatus = Me.SaleStatus
        !TRetail = Me.TRetail
        !TDiscount = Me.TDiscount
        !TSale = Me.TSale
        !CreatedBy = Me.CreatedBy
        .Update

        'Save the primary key value, to use as the foreign key for the related records.
        .Bookmark = .LastModified
        NewQuoteID = !SaleID
        'Display the new duplicate.  This repositions the form to the new Order
        Me.Bookmark = .LastModified
    End With

Set db = CurrentDb()
'''''bottom table uses append query.
                ''  copy accessories
                Set qd = db.QueryDefs!QRepeat
                    'qd.Parameters!EnterOldQuoteDetailID = !QuoteDetailID
                    'qd.Parameters!EnterNewQuoteDetailID = NewQuoteDetailID
                    qd.Execute dbSeeChanges
        
                '.MoveNext
            'Loop
        'End With

        Me.frmSaleDetails1.Requery '' Show the newly copied records
        'ToRS.Close
        'FromRS.Close
        'Set ToRS = Nothing
        'Set FromRS = Nothing
        Set db = Nothing

Exit_Handler:
    Exit Sub

Err_Handler:
     Select Case Err.Number
        Case 3021, 2501   ' update cancelled
            Resume Exit_Handler
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume Exit_Handler
    End Select

End Sub

partially working, adding new records but without where criteria
while appending records to subform not working, may be problem with append Query?

 

[Gasman]

I would expect you would need to supply the old ID and new ID to the query that appends the child records?
Yet you have commented out what appears to be those exact parameters?

When you are given code, you need to take the time to understand it if you need to amend it in anyway.

 

[accessonly11]

I would expect you would need to supply the old ID and new ID to the query that appends the child records?
Yet you have commented out what appears to be those exact parameters?

When you are given code, you need to take the time to understand it if you need to amend it in anyway.

yes i need to use append query to supply old and new ids, using parameters, but it is very difficult for me, how to use append query using parameters as it is mention in the bottom of the code provided by mr. Pat Hartman
-------------------------------------------------- The query: INSERT INTO tblquoteacc ( QuoteDetailID, ItemNo, EstID, ModelNo, Description, Qty, Price ) SELECT [EnterNewQuoteDetailID] AS Expr1, tblQuoteAcc.ItemNo, tblQuoteAcc.EstID, tblQuoteAcc.ModelNo, tblQuoteAcc.Description, tblQuoteAcc.Qty, tblQuoteAcc.Price FROM tblQuoteAcc WHERE (tblQuoteAcc.QuoteDetailID = [EnterOldQuoteDetailID];

 

[Pat Hartman]

I also posted the query so you could see the parameters. I named them [Enter........] You just have to use the same names in your VBA and use the .parameters property to set them.

 

[The_Doc_Man]

how to use append query using parameters as it is mention in the bottom of the code provided by mr. Pat Hartman

This is just FYI and Pat never makes a big deal of it, but it is Ms. Hartman, not Mr. Hartman.

 

[Gasman]

I also posted the query so you could see the parameters. I named them [Enter........] You just have to use the same names in your VBA and use the .parameters property to set them.

And do not comment them out (as you have already done )

 

[accessonly11]

And do not comment them out (as you have already done )

dear i am unable to understand this sentence, sorry for that,
using append query with paraments, is very difficult for me, so did not used it and cannot comment out the result.

This is just FYI and Pat never makes a big deal of it, but it is Ms. Hartman, not Mr. Hartman.

thanks for correcting me.

 

[accessonly11]

I found another code in a database which is published in public drive, i modify and added some more code in it according to my conditions.

the code is as under;

Private Sub Command602_Click()
    Dim strSQL          As String
    Dim NewSaleID       As Long
    Dim OldSaleID       As Long
    Dim NewSaleDetailID As Long
    Dim InvoiceNo       As Long
    Dim rstSaleDetails  As Recordset
   
    'get invoice number to be repeat
    InvoiceNo = InputBox("Enter Previous Sale Incoice No# to repeat", "Enter Invoice")
   
 
    'get next/New SaleID value
    NewSaleID = DMax("SaleID", "tblSale") + 1
   
    'insert new row into tblSale
    strSQL = "INSERT INTO tblSale (SaleID, SINo, CustomerID, PatientName, [DateTime], SaleStatus, TRetail, TDiscount, TSale, CreatedBy) " & _
            "SELECT " & NewSaleID & ", tblSale.SINo, tblSale.CustomerID, tblSale.PatientName, tblSale.DateTime, tblSale.SaleStatus, tblSale.TRetail, tblSale.TDiscount, tblSale.TSale, tblSale.CreatedBy " & _
            "FROM tblSale " & _
            "WHERE tblSale.SaleID = " & InvoiceNo
    CurrentDb.Execute strSQL, dbFailOnError
   
    OldSaleID = DLookup("[SaleID]", "tblSale", "SINo= " & InvoiceNo)
       
   
    NewSaleDetailID = DMax("SaleDetailsID", "tblSaleDetails")
    strSQL = "SELECT * FROM tblSaleDetails WHERE SaleID = " & OldSaleID
    Set rstSaleDetails = CurrentDb.OpenRecordset(strSQL)
   
    ' loop through set of SaleDetails values in Sale being copied
    ' and insert a new row into SaleDetails, incementing value of SaleDetailsID by 1
    ' at each iteration of the loop
    With rstSaleDetails
        .MoveLast
        .MoveFirst
        Do While Not .EOF
            NewSaleDetailID = NewSaleDetailID + 1
            strSQL = "INSERT INTO tblSaleDetails(SaleDetailsID,ProductID,SaleID,Qty,Retail,Discount,Sale,TRetail,TDiscount,TSale) " & _
                "VALUES(" & NewSaleDetailID & ",""" & .Fields("ProductID") & """," & NewSaleID & ",""" & .Fields("Qty") & """,""" & .Fields("Retail") & """,""" & .Fields("Discount") & """,""" & .Fields("Sale") & """,""" & .Fields("TRetail") & """,""" & .Fields("TDiscount") & """,""" & .Fields("TSale") & """)"
            CurrentDb.Execute strSQL, dbFailOnError
           .MoveNext
        Loop
    End With
       
End Sub

can you someone please guide me the problem i am facing in it,
the problem is, error 3201, you cannot add or change a record because a related record is required in table 'tblSale'.
and this line get yellow background, CurrentDb.Execute strSQL, dbFailOnError under with rstSaleDetails section.

while i already added the primary key in main table tblSale by using this code

'get next/New SaleID value
    NewSaleID = DMax("SaleID", "tblSale") + 1
   
    'insert new row into tblSale
    strSQL = "INSERT INTO tblSale (SaleID, SINo, CustomerID, PatientName, [DateTime], SaleStatus, TRetail, TDiscount, TSale, CreatedBy) " & _
            "SELECT " & NewSaleID & ", tblSale.SINo, tblSale.CustomerID, tblSale.PatientName, tblSale.DateTime, tblSale.SaleStatus, tblSale.TRetail, tblSale.TDiscount, tblSale.TSale, tblSale.CreatedBy " & _
            "FROM tblSale " & _
            "WHERE tblSale.SaleID = " & InvoiceNo
    CurrentDb.Execute strSQL, dbFailOnError [ICODE]
where is fault left, please guide

 

[Gasman]

Not sure you can add a SaleID if it is an autonumber in tblSales.
Why do you need oldSaleID if it was the InvoiceNumber?

I would be adding a debug.print of the strSQL before weven trying to use it and commenst out the execute until you get it correct.
What is the PK/FK for the tables.?

Your error is telling you you need a parent record in tblSales before you can add any child records.

Also start walking through your code with F8 and inspect what you have, not what you *think* you have.
I'd start with
OldSaleID = DLookup("[SaleID]", "tblSale", "SINo= " & InvoiceNo)

Your SaleID is meant to equal InvoiceNumber from the first sql, noq we are talking about SiNo ?

 

[accessonly11]

I saw in db from where I took this code, that it is used to add incremental value in PK field SaleID.

here is the design view of tblSale

my PK field is SaleID, and SINo field is for Invoice numbering . I need oldSaleID variable as I have to store SaleID value where InvoiceNo will be equal to my required Sales Invoice Number provided by me ( which need to be repeated).

here is the design view of my second table, where SaleDetailsID is PK and SaleID is FK.

I hope these screenshots will clear the scenario, please ask me if need to clarify more

 

[Gasman]

I do not believe you do.
If you copy the parent record, you will get a new SaleID.
That is the value you need to put into the child records that you are copying.

 

[accessonly11]

firstly I will get invoice number via Inputbox, to be repeated

InvoiceNo = InputBox("Enter Previous Sale Incoice No# to repeat", "Enter Invoice")

here I will get next/New PK SaleID value for parent record

NewSaleID = DMax("SaleID", "tblSale") + 1

here I will insert new record into parent table tblSale using new SaleID

     strSQL = "INSERT INTO tblSale (SaleID, SINo, CustomerID, PatientName, [DateTime], SaleStatus, TRetail, TDiscount, TSale, CreatedBy) " & _
            "SELECT " & NewSaleID & ", tblSale.SINo, tblSale.CustomerID, tblSale.PatientName, tblSale.DateTime, tblSale.SaleStatus, tblSale.TRetail, tblSale.TDiscount, tblSale.TSale, tblSale.CreatedBy " & _
            "FROM tblSale " & _
            "WHERE tblSale.SaleID = " & InvoiceNo
    CurrentDb.Execute strSQL, dbFailOnError

at this stage, i think code is not working properly, and new SaleID is not appending into table, thats why i got error 3201 when I try to add records in child table.

but i see it working in this database (attached) link is : https://onedrive.live.com/?cid=44CC...42912!9068&parId=44CC60D7FEA42912!169&o=OneUp

----
here i am posting my main form record source:
SELECT tblSale.SaleID, tblSale.SINo, tblSale.CustomerID, tblSale.PatientName, tblSale.DateTime, tblSale.SaleStatus, tblSale.TRetail, tblSale.TDiscount, tblSale.TSale, tblSale.CreatedBy, tblEmployees.EmpName
FROM tblSale LEFT JOIN tblEmployees ON tblSale.CreatedBy = tblEmployees.EmpID;

and what i want to append through this sql
strSQL = "INSERT INTO tblSale (SaleID, SINo, CustomerID, PatientName, [DateTime], SaleStatus, TRetail, TDiscount, TSale, CreatedBy) " & _
"SELECT " & NewSaleID & ", tblSale.SINo, tblSale.CustomerID, tblSale.PatientName, tblSale.DateTime, tblSale.SaleStatus, tblSale.TRetail, tblSale.TDiscount, tblSale.TSale, tblSale.CreatedBy " & _
"FROM tblSale " & _
"WHERE tblSale.SaleID = " & InvoiceNo.
is there a mistake in one of these sqls?

 

[GK in the UK]

I think there is confusion about NewSaleID and what it is. It isn't your new invoice number, it's the Access auto-generated ID of the new parent record. Access provides it automatically when you .Update the parent record.

At that point, you can put that new ID into your child record. It has nothing to do with your invoice number field.

It's confusing because you have SaleID in both tables. It would have been better to name it SaleFK in the child table.

Whether you use a recordset OR an update query to add your child records you MUST put NewSaleID into SaleFK. Without it, you get the error you experienced.
I won't repeat the whole query but it might start with:
strSQL = "INSERT INTO tblSaleDetails(SaleFK, ..................
"VALUES(" & NewSaleID, << which is the auto-generated SaleID from the parent

You more or less had it your post #1:
rstSale.Update
lngNewSaleID = rstSale("SaleID").Value << this is your FK that the child record needs

But when you add the child records, you *do not* supply the autonumber ID, Access does it. But you *must* supply the FK which you have called SaleID.

So you need rstSaleDetails.SaleID = rstSale.SaleID (on your naming schema)

But if you had named it as I suggest it becomes clearer what is happening

rstSaleDetails.SaleFK = rstSale.SaleID

Incidentally you don't need the .Value part because it's the default property

Also don't forget as Pat said you actually need 2 recordsets on each table because the rs pointer can't be on two records at once. But you can have 2 recordsets on the same table.

 

[Gasman]

i am facing an error 3201, you cannot add or change a record because a related record is required in table tblcustomer.....

OK, apologies here, but all your code does nothing for tblCustomer?
The error is complaining that you fo not have a parent record in tblCustomer.

SO, walk through your code and inspect the data you are processing.
IF? you were copying an already correct invoice, then the customerID must already exist. This error is saying that customerID is not there, hence you cannot save a child record for Customer without a CustomerID in the customer table.