Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Converted Macro Giving Fits (Access 2003/SP 1)

    I converted a macro, that fills in a bunch of information, into VBA. I've only done that once or twice before and I'm having problems. This is what the code is after I worked with it a little. Now if I 'filter for' a specific item in the 'Sent' field, it will filter, but if I try to 'Remove Filter/Sort', it won't remove the filter. It just puts the record into edit mode.

    Private Sub Sent_Exit(Cancel As Integer)

    If (Me.Sent = "2 tongs, 2 spoons, af 1-4") Then
    Me.[Item Totals] = 3700
    Me.[Order type] = "m"
    Me.[Packages] = 1
    Me.[Large Labels] = 2
    Me.[Small Labels] = 1
    Me.[Box Size] = 3640
    End If

    BoxSizes_Exit:
    Exit Sub

    BoxSizes_Err:
    MsgBox Error$
    Resume BoxSizes_Exit
    End Sub

    Here is the code that VBA made when I converted the macro.

    Option Compare Database

    '------------------------------------------------------------
    ' BoxSizes
    '
    '------------------------------------------------------------
    Function BoxSizes()
    On Error GoTo BoxSizes_Err

    With CodeContextObject
    If (.Sent = "2 tongs, 2 spoons, af 1-4") Then
    .[Box Size] = 3640
    End If
    End With


    BoxSizes_Exit:
    Exit Function

    BoxSizes_Err:
    MsgBox Error$
    Resume BoxSizes_Exit

    End Function

    I've done a Compact and Repair Database hoping it was just a minor fluke with no luck. If it's too complicated, I may just go back to the macro.

    Further information, this only happens if my cursor is in the 'Sent' field.

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

    Re: Converted Macro Giving Fits (Access 2003/SP 1)

    Try using the After Update event of the Sent text box instead of the On Exit event:

    Private Sub Sent_AfterUpdate()
    ...
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Converted Macro Giving Fits (Access 2003/SP 1)

    That did it, thanks. I was just starting to work with that after I realized that OnExit wasn't such a good option. It would filter records from a couple years ago and then on exit it would update fields like the code said to do.

Posting Permissions

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