Results 1 to 5 of 5
  1. #1
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: fill from previous record (A2003)

    The problem may be where you're calling it from. BeforeInsert fires when you start to enter data in a new record. Just moving to a new record doesn't first BeforeInsert.
    Charlotte

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    fill from previous record (A2003)

    (Edited by HansV - inserted <!t>[tab]<!/t> tags to preserve indentation in code.)

    I've tried a number of solutions for filling the current record with values from the previous and can't get any to work on a subform located on a tabbed form. I've included one solution's code here (courtesy of Allen Browne) in the hopes that someone can identify why it doesn't work on my subform. I don't get an error message, it just doesn't fill in the values.

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Call CarryOver (Me)
    End Sub
    --------------------------------------------------------------------------------
    This is basCarryOver

    Sub CarryOver(frm As Form)
    On Error GoTo Err_CarryOver
    ' Purpose: Carry the values over from the last record to a new one.
    ' Usage: In a form's BeforeInsert event procedure, enter:
    ' Call CarryOver(Me)
    ' Notes: This example limited to text boxes and combo boxes.
    ' Text/combo boxes must have same Name as the fields they represent.
    Dim rst As DAO.Recordset
    Dim ctl As Control
    Dim i As Integer

    Set rst = frm.RecordsetClone
    If rst.RecordCount > 0 Then
    rst.MoveLast
    For i = 0 To frm.count - 1
    Set ctl = frm(i)
    If TypeOf ctl Is TextBox Then
    If Not IsNull(rst(ctl.Name)) Then
    ctl = rst(ctl.Name)
    End If
    ElseIf TypeOf ctl Is ComboBox Then
    If Not IsNull(rst(ctl.Name)) Then
    ctl = rst(ctl.Name)
    End If
    End If
    Next
    End If

    Exit_CarryOver:
    Set rst = Nothing
    Exit Sub

    Err_CarryOver:
    Select Case Err
    Case 2448 'Cannot assign a value
    Debug.Print "Value cannot be assigned to " & ctl.Name
    Resume Next
    Case 3265 'Name not found in this collection.
    Debug.Print "No matching field name found for " & ctl.Name
    Resume Next
    Case Else
    MsgBox "Carry-over values were not assigned, from " & ctl.Name & _
    ". Error #" & Err.Number & ": " & Err.Description, vbExclamation, "CarryOver()"
    Resume Exit_CarryOver
    End Select
    End Sub

    Thanks for your assistance,
    E

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: fill from previous record (A2003)

    Elizabeth,

    I copied the code into a module. As Charlotte notes, the code only fires when you start to enter something in the new record, not when you move to a new record. If you want CarryOver to fire the moment you move to a new record, you could put it in the On Current event:

    Private Sub Form_Current()
    ' Test if we are entering a new record.
    If Me.NewRecord Then
    CarryOver Me
    End If
    End Sub

    Note: CarryOver will not work correctly if you have text boxes or combo boxes on the form whose control source is a calculated field.

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill from previous record (A2003)

    It doesn't pull the values even after I start entering something in the new record. Could it have something to do with the combo boxes and which column shows in the control? Where would you start troubleshooting? What are the most likely errors for a newbie to screw up?

    Thanks for your ongoing help!!
    Elizabeth

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: fill from previous record (A2003)

    Click in the first line of the event procedure (Form_BeforeInsert or Form_Current, whichever one you're using now), and press F9 to set a breakpoint. The line will be highlighted in dark red, and a bullet will appear in the margin (you can also toggle a breakpoint by clicking in the margin.)
    Now switch to Access, open the form and start entering something in a new record. The code should pause in the line with the breakpoint. You can now execute the code one step at a time by pressing F8. Does the code actually go into CarryOver? If so, can you find out if the controls actually are being set? If not, does the code skip CarryOver, or don't you get to the breakpoint at all?

Posting Permissions

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