CODEWITHSUNDEEP
ms-accessvba
vuyy1182
vuyy1182

Reputation: 1686

vba Byref argument type mismatch when calling the function

I have a method to rename the table column name.

Public Function Rename_Column(tablename As String, oldcolumn As String, newcolumn As String)
  Dim dbs As Database, tdf As TableDef
  Set dbs = CurrentDb

  For Each tdf In dbs.TableDefs
    If tdf.Name = tablename Then
      For Each fld In tdf.Fields
        If fld.Name = oldcolumn Then
          fld.Name = newcolumn
        End If
      Next
    End If
  Next
  dbs.Close
End Function

And i'm calling this function in other procedure 

Public Sub querylistboxitems()
  Dim strTableName As String

  strTableName = "Table1"
  Call Rename_Column(strTableName, "old", "New")
End Sub

But it is giving an error "Byref argument type mismatch"

Upvotes: 2

Views: 30097

Answers (2)

MathKid
MathKid

Reputation: 2122

There are other tricky situations where this problem may occur. For example when declaring two (or more) variables on one line:

Dim firstSubMenu, secondSubMenu As CommandBarPopup

Now firstSubMenu is of type Variant, while secondSubMenu is of type CommandBarPopup. This may not be what you intended and can also be a cause for the above mentioned error when passing them by reference to a function that expects a parameter of type CommandBarPopup. In this case a solution is to declare them on two lines:

Dim firstSubMenu As CommandBarPopup
Dim secondSubMenu As CommandBarPopup

Or, if you really want to squeeze it on one line, it can be done like this:

Dim firstSubMenu As CommandBarPopup, secondSubMenu As CommandBarPopup

Note that you'll have to repeat the 'As' type declaration for each variable.

Upvotes: 22

Wayne G. Dunn
Wayne G. Dunn

Reputation: 4312

It failed for me because you did not define "fld". The following is a lot more direct than looping thru all tables / fields:

Public Function Rename_Column(tablename As String, oldcolumn As String, newcolumn As String)
    Dim dbs As Database
    Dim tdf As TableDef
    Dim fld As Field

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(tablename)
    Set fld = tdf.Fields(oldcolumn)
    Set fld = Nothing
    Set tdf = Nothing
    dbs.Close
    Set dbs = Nothing
End Function

Upvotes: 2

Related Questions