Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2013
    Posts
    22

    Question Unanswered: Add an existing record from a mainform to a subform when you click on the save button

    Hey there good day can anyone help as i am struggling to finish the end result:
    I got a unbound mainform
    got a bound subform
    got two tables Masterplant and PlantTransaction
    When I edit a record it shows on the mainform and when i save the record it must duplicate an existing record in the subform which works but the trick is that the Opening Hours in the new record must become my the Closing Hours of the previous record, everything works its just the Opening Hours does not show from the Closing hours previous record and the TransactionID is a number field that must auto increment with a different TransactionID number any help will be appreciated thanks in advance!!!
    Code below:


    Private Sub cmdSave_Click()
    Dim strSQL As String
    Dim rst As DAO.Recordset
    If IsNull(txtOpeningHRS) Then
    Set rst = Me.RecordsetClone
    If rst.RecordCount > 0 Then
    If Me.NewRecord Then
    rst.MoveLast
    Else
    rst.Bookmark = Me.Bookmark
    rst.MovePrevious
    End If
    txtOpeningHRS = rst!CloseHrs
    End If
    End If
    If IsNull(Me.TransactionID) Or Me.TransactionID = 0 Then
    Me.TransactionID = Nz(DMax("TransactionID", "PlantTransaction") + 1, 1234)
    End If
    strSQL = "INSERT INTO PlantTransaction(TransactionID,[Plant Number],Opening_Hours,[TransactionDate],[FuelConsumption],[Hour Meter Replaced],Comments,[Hours Worked]) " & _
    strSQL & "VALUES(" & Me.txtTranID & ",'" & Me.txtPlantNo & "','" & Me.txtOpeningHRS & "',#" & Me.txtTransDate & "#,'" & Me.txtFuelConsFuelHr & "','" & Me.txtHrMtrRep & "','" & Me.txtComments & "','" & Me.txtHrsWorked & "');"

    CurrentDb.Execute strSQL
    Me.PlantTransactionQuery.Form.Requery
    cmdNew_Click
    End Sub

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    First, you should not be manually auto-incrementing a field, set that field to be an autonumber field and it will increment automatically.

    If I understand you, you want your new record Opening Hours to be the same as the last records Closing Hours? Set the default value for Opening Hours to be Dlast("[Closing Hours]","PlantTransaction")

    The same would hold true for any other value you want to use based on the previous records values.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •