Calling a function from a command button

[kdnichols]

Hello Everyone,

I have been more involved with Excel VBA and I am getting back into Access vba.

I have the following code:

Private Sub Command2_Click()
Call TableInfo

DoCmd.OpenTable "DBdata", acViewNormal, acReadOnly
End Sub
Function TableInfo(strTableName As String)
On Error GoTo TableInfoErr
' Purpose: Display the field names, types, sizes and descriptions for a table.
' Argument: Name of a table in the current database.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs(strTableName)
Debug.Print "FIELD NAME", "FIELD TYPE", "SIZE", "DESCRIPTION"
Debug.Print "==========", "==========", "====", "==========="
For Each fld In tdf.Fields
Debug.Print fld.Name,
Debug.Print FieldTypeName(fld),
Debug.Print fld.Size,
Debug.Print GetDescrip(fld)
Next
Debug.Print "==========", "==========", "====", "==========="
TableInfoExit:
Set db = Nothing
Exit Function
TableInfoErr:
Select Case Err
Case 3265& 'Table name invalid
MsgBox strTableName & " table doesn't exist"
Case Else
Debug.Print "TableInfo() Error " & Err & ": " & Error
End Select
Resume TableInfoExit
End Function

When I try to call the function from the command button I get a Compile Error: Argument not optional.

What am I doing wrong in my syntax here. Sorry I know this is probably a dumb easy question. Any help is greatly appreciated.

Thanks,

Kurt

 

[DJkarl]

You need to pass the table name...

Hello Everyone,

I have been more involved with Excel VBA and I am getting back into Access vba.

I have the following code:

Private Sub Command2_Click()
Call TableInfo

DoCmd.OpenTable "DBdata", acViewNormal, acReadOnly
End Sub
Function TableInfo(strTableName As String)
On Error GoTo TableInfoErr
' Purpose: Display the field names, types, sizes and descriptions for a table.
' Argument: Name of a table in the current database.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs(strTableName)
Debug.Print "FIELD NAME", "FIELD TYPE", "SIZE", "DESCRIPTION"
Debug.Print "==========", "==========", "====", "==========="
For Each fld In tdf.Fields
Debug.Print fld.Name,
Debug.Print FieldTypeName(fld),
Debug.Print fld.Size,
Debug.Print GetDescrip(fld)
Next
Debug.Print "==========", "==========", "====", "==========="
TableInfoExit:
Set db = Nothing
Exit Function
TableInfoErr:
Select Case Err
Case 3265& 'Table name invalid
MsgBox strTableName & " table doesn't exist"
Case Else
Debug.Print "TableInfo() Error " & Err & ": " & Error
End Select
Resume TableInfoExit
End Function

When I try to call the function from the command button I get a Compile Error: Argument not optional.

What am I doing wrong in my syntax here. Sorry I know this is probably a dumb easy question. Any help is greatly appreciated.

Thanks,

Kurt

 

[fredalina]

Your function is expecting argument(s) passed to it. i.e. TableInfo(xxxx)

 

[kdnichols]

Thanks,

So how do I correct this. I do believe this is trying to work in the Immediate Window.

How do I change it to display the info in a table or what is the best way to rewrite this?

Thanks,

Kurt