Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    78

    Unanswered: Macro to VBA Query

    Hi All,
    I have a macro that basically checks for certain values in a textbox on the form and based on that hides or displays a button. It works very well as a macro, but then I used Access' facility to convert from Macro to VBA, it generated the code and when I replaced the macro with the corresponding function name, it gives me an error, which I cant seem to figure out. Below I am attaching both the macro and converted VBA code.

    Macro Code:
    Condition 1: ([cboLastRevd].[Value]<>[txtTruncRev].[Value]) And ([cboLastRevd] Is Not Null) And ([txtTruncRev] Is Not Null)

    Action 1: Set Value ([cmdChangeRev].[Visible] = True)

    Condition 2: ([cboLastRevd] Is Null) Or ([cboLastRevd]=[txtTruncRev])

    Action 2: Set Value ([cmdChangeRev].[Visible] = False)

    VBA Code:
    Function mcrRevButtonVisible()
    On Error GoTo mcrRevButtonVisible_Err

    With CodeContextObject
    If (Eval("([cboLastRevd].[Value]<>[txtTruncRev].[Value]) And ([cboLastRevd] Is Not Null) And ([txtTruncRev] Is Not Null)")) Then
    .cmdChangeRev.Visible = True
    End If
    If (Eval("([cboLastRevd] Is Null) Or ([cboLastRevd]=[txtTruncRev])")) Then
    .cmdChangeRev.Visible = False
    End If
    End With


    mcrRevButtonVisible_Exit:
    Exit Function

    mcrRevButtonVisible_Err:
    MsgBox Error$
    Resume mcrRevButtonVisible_Exit

    End Function

    [cboLastRevd] & [txtTruncRev] are text boxes on the form and cmcChangeRev is the name of the button. Based on the values compared between the two text boxes, the button will either be visible or hidden. This macro is placed in the OnCurrent event of the form. I am using Access 2002 version. The error that I get is "MS Access cant find the name 'cboLastRevd' you entered in the expression". I am having similar problems with other macros/VBA code too, but I guess once I figure it out here, that should be able to solve the other ones too. Also, at some places the code does not generate the "With CodeContextObject" statement too. I cant seem to figure it out.

    Thanks to all in advance for all your time and efforts.

    Regards:
    Prathmesh

  2. #2
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    One thing you might try is to use the full form reference in the macro, that is, [Forms]![YourForm]![cboLastRevd] etc and then convert.

    As a side note, I use Access 95 and Visible and Invisible is Yes or No

  3. #3
    Join Date
    Oct 2003
    Posts
    78
    Hi Mike,
    Tks for the input. However, the reason I am not using the full reference is that I want to use it as a global function. Most of my forms need this capability to hide and display the button based on compared values from the two textboxes. So if I can make a sort of generic function then all I need to do is just call that one function wherever I want. This thing works fine with macros. I am using one macro for most of my forms, taking care to name the controls appropriately. I dont know if that would be a problem with VBA code and only macros would be able to handle this functionality.

    Regards.

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by Prathmesh
    Hi Mike,
    Tks for the input. However, the reason I am not using the full reference is that I want to use it as a global function. Most of my forms need this capability to hide and display the button based on compared values from the two textboxes. So if I can make a sort of generic function then all I need to do is just call that one function wherever I want. This thing works fine with macros. I am using one macro for most of my forms, taking care to name the controls appropriately. I dont know if that would be a problem with VBA code and only macros would be able to handle this functionality.

    Regards.
    I have had little success with the conversion process myself. My data base is about 100 meg and has around 2000 macros some of which are very long. Quite sometime ago someone looked at the data base and hearly had a heart attack as the only code I have is to open Word docs and insert data from Access fields to Word Bookmarks. This person had a go at changing some of my macros and it all ended in grief

    Mike

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    some of the autocode you posted has no meaning for me, so my version is just different!

    'Function mcrRevButtonVisible()
    'you are not returning a value, so why a function?

    private sub mcrRevButtonVisible()

    On Error GoTo mcrRevButtonVisible_Err

    'With CodeContextObject
    'If (Eval("([cboLastRevd].[Value]<>[txtTruncRev].[Value]) And
    '([cboLastRevd] Is Not Null) And ([txtTruncRev] Is Not Null)")) Then
    '.cmdChangeRev.Visible = True
    'If (Eval("([cboLastRevd] Is Null) Or ([cboLastRevd]=[txtTruncRev])")) Then
    '.cmdChangeRev.Visible = False
    'End If
    'End With
    'what ????


    cmdChangeRev.Visible = ((nz([cboLastRevd],0) <> nz([txtTruncRev],0)) AND (not(isnull([cboLastRevd]))) AND (not(isnull([txtTruncRev]))))


    mcrRevButtonVisible_Exit:
    'Exit Function ooops, this should have been:
    Exit Sub


    mcrRevButtonVisible_Err:
    MsgBox Error$
    Resume mcrRevButtonVisible_Exit

    End Sub

    izy
    Last edited by izyrider; 07-09-04 at 04:20.
    currently using SS 2008R2

Posting Permissions

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