Copy from one related table to another using VBA SQL incrementing date.

[MackMan]

Hi guys. Last week I submitted the thread ...
http://www.access-programmers.co.uk/forums/showthread.php?t=277992 and from this I wanted to copy one table to another, incrementing the date by any given value. With a lot of help, the desired outcome was reached.

After looking and not finding on Google and so on, I'm trying to copy the tables Related table to another table (again, I'm not worried about DB size, as these will eventually be used as entry at some point) but I keep coming up with the same thing...; Error 3201 - You cannot add or change a record because a related record is required in Table tblBILLSeriesTopLines

This is a little confusing to try and explain, so please bare with me.

Two Tables each ( four in all )

Table One contains the main records, and I can create (and copy as many as I need to table three which hold ALL those records.
Table two contains any sub records of table one where necessary, and I'd like to be able to do the same... Create as many that are necessary as per the main table, and create this in table four (which is related to table three - See diagram.. I hope I've explained better there)

If possible, I'd be grateful if someone could take a look at my SQL's and advise.

The whole WHERE / FROM and ID's are confusing me., and I've no doubt not incuded an ".update" or something, or not set something that I should.

Private Sub cmdCreate_Click()
 Dim strSQL As String
Dim strSQLSub As String
Dim db As Database
Dim i As Integer
 Dim NewID As Long
Dim StartDate As Date
Dim z As Double
Dim Intv As String
Dim Every As Double
Dim SubID As Long
 
 If Me.Dirty Then
    Me.Dirty = False
End If
  
 Set db = CurrentDb()
 
NewID = Me.BILLID
SubID = [Forms]![frmBILLEnterDetails]![frmRegisterSplitsPopup].[Form]![BILLSplitID]
StartDate = Me.TransDate
z = Me.RecurCount
Intv = Me.PeriodTypeID
Every = Me.PeriodFreq
  
 
For i = 1 To z
  
 strSQL = "INSERT INTO [tblBillSeriesTopLines] ( BillID, AccountID, PayeeID, ChequeNo, TransDate, Category, SubCategory, Credit, Debit, Amount, FixedOrEstimateID, " & _
         "TransactionStatus, Flagged, FlagReasonID, TransferToAccountID, TransferFromAccountID, IsTransferYN) " & _
         "SELECT " & NewID & " AS NewID, AccountID, PayeeID, ChequeNo," & Format(DateAdd(Intv, (i * Every) - 1, StartDate), "\#mm\/dd\/yyyy\#") & " AS TransDate, Category, " & _
         "SubCategory, Credit, Debit, Amount, FixedOrEstimateID, TransactionStatus, Flagged, FlagReasonID, TransferToAccountID, TransferFromAccountID, IsTransferYN " & _
         "FROM [tblBILLDetails] WHERE BillID = " & Me.BILLID & ";"
                  DBEngine(0)(0).Execute strSQL, dbFailOnError
 Next i
 db.Close
 Set db = CurrentDb()
       
          
            SubID = Me.BILLID
 For i = 1 To z
 strSQLSub = "INSERT INTO [tblBILLSeriesSplits] ( BillTopLineID, TransDate, Payee, Category, SubCategory, Credit, Debit, Amount, Memo) " & _
            "SELECT " & SubID & " AS NewID," & Format(DateAdd(Intv, (i * Every) - 1, StartDate), "\#mm\/dd\/yyyy\#") & " AS TransDate, Payee, Category, SubCategory, Credit, Debit, Amount, Memo " & _
            "FROM [TblBillDETAILSSplit] WHERE BillTopLineID = " & NewID & ";"
                    DBEngine(0)(0).Execute strSQLSub, dbFailOnError
                    
Next i
db.Close

My thought process behind the above was..

Create the necessary records in the primary table, and then create the necessary records in the related table.

As always... I appreciate all your comments and advise. More so, your patience.

 

[Cronk]

On what line do you get the error? Because that code is not doing anything other than to generate SQL strings which are not executed.

Try putting

db.execute strSQL

in your loop

 

[MackMan]

Thanks for the reply Cronk.

It's happening in the second sql string StrSqlsub
Dbengine(0)(0).execute strsqlsub dbfailonerror

 

[pbaldy]

This debugging method may help:

http://www.baldyweb.com/ImmediateWindow.htm

 

[MackMan]

Hi PB, thanks for your reply. I've been using this method, and only had a chance to get back to it now...

It seems, I need to save the new ID as the primary key value to use as the foreign key for the related records.. so I've had to change the code to the following...

Private Sub cmdCreate_Click()
 Dim strSQL As String
Dim strSQLSub As String
Dim db As Database
Dim i As Integer
 Dim NewID As Long
Dim StartDate As Date
Dim z As Double
Dim Intv As String
Dim Every As Double
Dim SubID As Long
 
 If Me.Dirty Then
    Me.Dirty = False
End If
  
 Set db = CurrentDb()
 
NewID = Me.BILLID
StartDate = Me.TransDate
z = Me.RecurCount
Intv = Me.PeriodTypeID
Every = Me.PeriodFreq
  
 
For i = 1 To z
  
 strSQL = "INSERT INTO [tblBillSeriesTopLines] ( BillID, AccountID, PayeeID, ChequeNo, TransDate, Category, SubCategory, Credit, Debit, Amount, FixedOrEstimateID, " & _
         "TransactionStatus, Flagged, FlagReasonID, TransferToAccountID, TransferFromAccountID, IsTransferYN) " & _
         "SELECT " & NewID & " AS NewID, AccountID, PayeeID, ChequeNo," & Format(DateAdd(Intv, (i * Every) - 1, StartDate), "\#mm\/dd\/yyyy\#") & " AS TransDate, Category, " & _
         "SubCategory, Credit, Debit, Amount, FixedOrEstimateID, TransactionStatus, Flagged, FlagReasonID, TransferToAccountID, TransferFromAccountID, IsTransferYN " & _
         "FROM [tblBILLDetails] WHERE BillID = " & Me.BILLID & ";"
                  DBEngine(0)(0).Execute strSQL, dbFailOnError
[COLOR=darkred]***Somewhere here I need to Save the primary key value, and use as the foreign key for the related records...  But how??[/COLOR]
 [COLOR=darkred]When I do a with me.recordsetclone[/COLOR]
 [COLOR=darkred].addnew it's on this line that I'd [/COLOR]
 [COLOR=darkred].bookmark = .lastmodified[/COLOR]
 [COLOR=darkred]lngID =!TopLineID [/COLOR]
 [COLOR=darkred]or appropriate for that record...[/COLOR][COLOR=darkred]***[/COLOR]
          
         strSQLSub = "INSERT INTO [tblBILLSeriesSplits] ( BillTopLineID, TransDate, Payee, Category, SubCategory, Credit, Debit, Amount, Memo) " & _
            "SELECT " & SubID & " AS NewID," & Format(DateAdd(Intv, (i * Every) - 1, StartDate), "\#mm\/dd\/yyyy\#") & " AS TransDate, Payee, Category, SubCategory, Credit, Debit, Amount, Memo " & _
            "FROM [TblBillDETAILSSplit] WHERE BillTopLineID = " & NewID & ";"
                    DBEngine(0)(0).Execute strSQLSub, dbFailOnError
                    
Next i
db.Close

When I do a with me.recordsetclone and .addnew it's on the highlighted line that I'd
.bookmark = .lastmodified
lngID =!TopLineID
or appropriate for that record...

How is this done using a copy to table whilst creating at the same time.?

Am I right In thinking, that if I requery the sub form that's showing the newly created record, and focus on that ID, I can use that as the primary key value ?

EDIT -- Ok, I admit, I thought this was going to be easy...

setting the value of "I" in the first instance is simple enough.

I need to then do a count in regards to the number of related records in the second instance, and then reassign a value to say..."ir", and then assign the PK value, from the first instance.

This'll be interesting... wish me luck...

Unless there is an easier way someone can recommend?

I haven't tried an append query,,, if this is even possible.

 

[pbaldy]

Are you saying that the strSQL append can append multiple records, and for each one you need the new ID for the strSQLSub? If so, I suspect you'll have to append the strSQL records using a recordset loop one-by-one. In that loop you can use the method you mentioned to get the new ID and insert the strSQLSub records using that SQL.

 

[MackMan]

Hi Paul. Yep Exactly that. I've already started on the code.

What I thought was a relatively simple task is slightly more difficult, but I think I can do it.

Well, at least.. I can give it a try!

Thanks for your guidance... I shall let you know if I'm successful

 

[pbaldy]

No problem, post back if you get stuck.

 

[MackMan]

Nailed it! Took a little time, but managed it in the end
Basically, once the Main form has been counted I needed to then count the Related forms entries, and based the incrementing date on the number of entries on the related form...

If anyone else needs this (which I doubt) then the code I used is here...

Private Sub cmdCreate_Click()
 Dim strSQL As String
Dim strSQLSub As String
 Dim db As Database
Dim i As Integer
Dim ir As Integer
Dim NewID As Long
Dim SplitID As Long
Dim StartDate As Date
Dim z As Double
Dim Intv As String
Dim Every As Double
 If Me.Dirty Then
    Me.Dirty = False
End If
 Set db = CurrentDb()
 
NewID = Me.BILLDETAILID
StartDate = Me.TransDate
z = Me.RecurCount
x = Me.SPLITCount
Intv = Me.PeriodTypeID
Every = Me.PeriodFreq
  
 For i = 1 To z
     strSQL = "INSERT INTO [tblBillSeriesTopLines] ( BilldetailID, AccountID, PayeeID, ChequeNo, TransDate, Category, SubCategory, Credit, Debit, Amount, FixedOrEstimateID, " & _
             "TransactionStatus, Flagged, FlagReasonID, TransferToAccountID, TransferFromAccountID, IsTransferYN) " & _
             "SELECT BILLDETAILID, AccountID, PayeeID, ChequeNo, " & Format(DateAdd(Intv, (i * Every) - 1, StartDate), "\#mm\/dd\/yyyy\#") & " AS TransDate, Category, " & _
             "SubCategory, Credit, Debit, Amount, FixedOrEstimateID, TransactionStatus, Flagged, FlagReasonID, TransferToAccountID, TransferFromAccountID, IsTransferYN " & _
             "FROM [tblBILLDetails] WHERE BillDetailID = " & Me.BILLDETAILID & ";"
             DBEngine(0)(0).Execute strSQL, dbFailOnError
             
             Forms!frmBILLEnterDetails!frmBillDETAILSSeriesLIST.Requery
             Forms!frmBILLEnterDetails!frmBillDETAILSSeriesLIST.SetFocus
             RunCommand acCmdRecordsGoToLast
             
        Forms!frmBILLEnterDetails.SetFocus
             
SplitID = Me.BillSERIESID
    'Debug.Print strSQL
    
            Forms!frmBILLEnterDetails!frmDetailSplits.SetFocus
            RunCommand acCmdRecordsGoToFirst
            
    For ir = 1 To x
    
strSQLSub = "INSERT INTO [tblBILLSeriesSplits] (BILLSERIESID, BillDetailID, BILLDetailsplitID, TransDate, Category, SubCategory, Credit, Debit, Amount, Memo, " & _
            "AccountID, PayeeID, ChequeNo, Flagged, FlagReasonID, TransferToAccountID, TransferFromAccountID, IsTransferYN, LastPaidOn) " & _
             "SELECT " & Me.BillSERIESID & " AS BILLSERIESID, " & Me.BILLDETAILID & " AS BILLDETAILID, BILLDetailSPLITID, " & Format(DateAdd(Intv, (i * Every) - 1, StartDate), "\#mm\/dd\/yyyy\#") & " AS TransDate, Category, SubCategory, Credit, Debit, Amount, memo,  " & _
             "AccountID, PayeeID, ChequeNo, Flagged, FlagReasonID, TransferToAccountID, TransferFromAccountID, IsTransferYN, LastPaidOn " & _
             "FROM [tblBILLDETAILSSplit] WHERE BILLDETAILsplitID = " & Me.DETAILSplitID & ";"
             'Debug.Print strSQLSub
         DBEngine(0)(0).Execute strSQLSub, dbFailOnError
    
    Forms!frmBILLEnterDetails!frmDetailSplits.SetFocus
            RunCommand acCmdRecordsGoToNext
        
        
        Next ir
    Next i
db.Close
MsgBox "The Bill Series has been created." & vbCrLf & vbCrLf & "There has been " & DCount("BillSeriesID", "tblBILLSeriesTopLines", "BillDetailID =" & NewID) & " Main Occurences" & _
" and " & DCount("Billseriessplitid", "tblBILLSeriesSplits", "BillDetailID =" & NewID) & " instances of Splits created." & vbCrLf & vbCrLf & "The Series will end on " & Me.txtEndDate, vbInformation, "Regular Payment Series has been Created"
 If CurrentProject.AllForms("frmBILLSMain").IsLoaded Then
    Forms!frmBILLSMain!frmBILLSMainEventDatesSUMMARY30Days.Requery
End If
 Forms!frmBILLEnterDetails!frmBillDETAILSSeriesLIST.Requery
 If CurrentProject.AllForms("frmHOME").IsLoaded Then
    Forms!frmHOME!frmHOME_BILLSUMMARY15Days.Requery
End If
 End Sub

I purposely didn't use line breaks so I could see the code more clearly.

Works exactly how I needed!
As always I really appreciate your guidance.

 

[pbaldy]

Glad you sorted it out!