help Creating Multiple records from one form with 1 field different (due date)

[russ0670]

I have table which holds payments on a client account. If the client pays on a repeating contract I need a form to basically create a record for each future transaction. IE if the client pays monthly on a year contract it would create 12 records in the transactions table with the due date set to the first of each month.

The fields in the transactions table.

TransactionID(autonumber); Client id(number); Amount(currency); Datedue (short date) ; Paid(y/n box);

The fields in the repeattransactions form

Client id; Start Date; Amount; Frequency (weekly, monthly, quarterly, annually)


The only thoughts I have had is that I can get this form to open another form which pulls the data from my current form and calculates the date on the value in my current form then creates the record.

I don't know whether this will work or whether there is a better, quicker way to do this.

could anyone help me with this or forward some literature about repeating record creation i would be very grateful.

[RuralGuy]

Have you ever looked at this link? http://allenbrowne.com/AppRecur.html

[russ0670]

Thank you this is just what i was looking for. The best thing is the guys site explains it so will help in future.

Thanks again.

[RuralGuy]

You are very welcome. Thanks for posting back.

[russ0670]

This app is great and is exactly what i want, however i dont understand all the coding and so cannot create my own version. instead i want to use this as a template and just adjust and add a few field. i need two of these in the same db (one for payment and one for appointments).

What I did was change the table, form names and all references so that everywhere event was it was changed to trans so tblevent was changed to tbltrans. I went through everything changing all references from event to trans. Tables, queries, vba and forms.

Everything works perfect apart from the exceptions form when you create a new exception.

When I go to open frmtransexception in get a debug saying "compile error method or data member not found." and it highlights Me.transID.DefaultValue = lngtransID in the formload sub.

Also when I go to close the form i get the following error message
error 2465: Microsoft Access cant find the field frmtranssub' referred to in your expression

I have attached the DB (had to zip otherwise it was 2mb) and entered the code from the frmtransexception below (highlighting the section brought up for debug on the first error in bold. If you or any other mod can have a look at this and see where i am going wrong. im sure it is just one reference somewhere I am missing.

Option Compare Database
Option Explicit

Private mbHasChanged As Boolean
Private Const conMod = "frmtransException"

Private Sub cmdCancel_Click()
On Error GoTo Err_Handler
    'Purpose:   Undo and close
    
    If Me.Dirty Then
        Me.Undo
    End If
    
    DoCmd.Close acForm, Me.Name
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
    Call LogError(Err.Number, Err.Description, conMod & ".cmdCancel_Click")
    Resume Exit_Handler
End Sub

Private Sub cmdDelete_Click()
On Error GoTo Err_Handler
    'Purpose:   Cancel new entry, or delete existing entry, and close form.
    
    If Me.Dirty Then
        Me.Undo
    End If
    
    If Not Me.NewRecord Then
        RunCommand acCmdDeleteRecord
    End If
    
    DoCmd.Close acForm, Me.Name
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
    Call LogError(Err.Number, Err.Description, conMod & ".cmdDelete_Click")
    Resume Exit_Handler
End Sub

Private Sub cmdOk_Click()
On Error GoTo Err_Handler
    'Purpose:   Save and close.
    
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    DoCmd.Close acForm, Me.Name
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
    Call LogError(Err.Number, Err.Description, conMod & ".cmdOk_Click")
    Resume Exit_Handler
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
    If Status = acDeleteOK Then
        mbHasChanged = True
    End If
End Sub

Private Sub Form_AfterUpdate()
    mbHasChanged = True
End Sub

Private Sub Form_Load()
On Error GoTo Err_Handler
    'Purpose:
    Dim lngtransID As Long
    
    If Me.FilterOn Then
        lngtransID = ParseLongFromFilter(Me.Filter, "transID")
        If lngtransID <> 0& Then
            Me.transID.DefaultValue = lngtransID
        End If
        Me.InstanceID.DefaultValue = ParseLongFromFilter(Me.Filter, "InstanceID")
    End If
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
    Call LogError(Err.Number, Err.Description, conMod & ".Form_Load")
    Resume Exit_Handler
End Sub

Private Function ParseLongFromFilter(strWhere As String, strField As String) As Long
    Dim lngPos As Long
    Const strcCharList = ")] ="
    
    lngPos = InStr(strWhere, strField)
    If lngPos > 0& Then
        ParseLongFromFilter = Val(StripChar(Mid$(strWhere, lngPos + Len(strField) + 1&), strcCharList))
    End If
End Function

Private Function StripChar(ByVal strSource, strCharList) As String
    Dim i As Long
    
    For i = 1 To Len(strCharList)
        strSource = Replace(strSource, Mid(strCharList, i, 1), vbNullString)
    Next
    StripChar = strSource
End Function

Private Sub Form_Unload(Cancel As Integer)
On Error GoTo Err_Handler
    'Purpose:   Requery the previous form, and return the the same record.
    Dim strWhere As String
    Dim frm As Form
    Dim rs As DAO.Recordset
    
    If CurrentProject.AllForms("frmtrans").IsLoaded Then
        Set frm = Forms!frmtrans!frmtransSub.Form
        If Not frm.NewRecord Then
            strWhere = "(transID = " & frm!transID & ") AND (InstanceID = " & frm!InstanceID & ")"
            frm.Requery
            Set rs = frm.RecordsetClone
            rs.FindFirst strWhere
            If Not rs.NoMatch Then
                frm.Bookmark = rs.Bookmark
            End If
        End If
    End If
    
Exit_Handler:
    Set rs = Nothing
    Set frm = Nothing
    Exit Sub
    
Err_Handler:
    Call LogError(Err.Number, Err.Description, conMod & ".Form_Unload")
    Resume Exit_Handler
End Sub