ByRef

[Bill H.]

I get a ByRef argument type mismatch error when I try to compile. The code
runs and works, but if I manually tell it to complie, I get an error.

Here is a segment of the code on a form:

dim MtablenameTo as string
If fExistTable(MtablenameTo) Then


The ByRef problem is on the parameter of fExistTable(), a function in a
module:

Function fExistTable(strTableName As String) As Integer
Dim db As Database
Dim i As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
Exit For
End If
Next i
db.Close
Set db = Nothing
End Function


Thanks.

 

[Ken Snell \(MVP\)]

Change this line:
If fExistTable(MtablenameTo) Then

to this:
If fExistTable(MtablenameTo.Name) Then

The above assumes that MtablenameTo is an object that is pointing to the
table object.

 

[Dirk Goldgar]

I get a ByRef argument type mismatch error when I try to compile. The code
runs and works, but if I manually tell it to complie, I get an error.

Here is a segment of the code on a form:

dim MtablenameTo as string
If fExistTable(MtablenameTo) Then


The ByRef problem is on the parameter of fExistTable(), a function in a
module:

Function fExistTable(strTableName As String) As Integer
Dim db As Database
Dim i As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
Exit For
End If
Next i
db.Close
Set db = Nothing
End Function

I don't see why you would get an argument-type mismatch with that code. Are
you sure you have reported the declaration of MtablenameTo correctly? I
note that the capitalization of the statement indicates that you didn't just
copy and paste it. If, for example, you declared MtablenameTo as Variant,
then you'd get the error you reported.

 

[Ken Snell \(MVP\)]

Sorry, I misread your post's contents. My suggestion is incorrect.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Change this line:
If fExistTable(MtablenameTo) Then

to this:
If fExistTable(MtablenameTo.Name) Then

The above assumes that MtablenameTo is an object that is pointing to the
table object.

 

[Bill H.]

But I did just copy and paste to here.

I don't get it either. That same function code worked in another database
and I just copied/pasted from that program into this new program.

--Bill

 

[Dirk Goldgar]

But I did just copy and paste to here.

I don't get it either. That same function code worked in another database
and I just copied/pasted from that program into this new program.

You may have pasted the function definition, but I find it hard to believe
that you pasted this line:

If you had actually entered that line in a module -- and it had been
understood -- then the words "dim", "as", and "string" would all have had
their first letters capitalized.

So if you really did copy and paste that line, something is seriously wrong.
Please post the entire procedure containing that line.

 

[Bill H.]

Oh, now I see what was meant by "you didn't copy."

cut/paste from the form's module:

Public MtablenameFrom, MtablenameTo, MtablenameWorkingCopy As String

but the values don't get set until the Form_Current() runs:

MtablenameFrom = Me.ImportFilename
MtablenameTo = MtablenameFrom & "a"

ImportFilename is a field on the form where I enter the table I'm working
with.

Then, I have these actions on the ImportFilename field:

Private Sub ImportFilename_AfterUpdate()
Me.ImportFilename = UCase(Me.ImportFilename)
MtablenameTo = Me.ImportFilename & "a"
MtablenameWorkingCopy = MtablenameTo & "-working copy"
End Sub

Private Sub ImportFilename_BeforeUpdate(Cancel As Integer)
MtablenameFrom = Me.ImportFilename
End Sub

Then, I have this fragment as part of the code that runs when I click on a
command button on the form:

If fExistTable(MtablenameTo) Then
DoCmd.DeleteObject acTable, MtablenameTo
End If

And that is where I get the ByRef error.

--Bill

 

[dymondjack]

Public MtablenameFrom, MtablenameTo, MtablenameWorkingCopy As String

For the record, the first three variables here are being dimmed as Variants.
I'm not sure if that's whats causing the issue (although its probably
likely), but to use a statement that way and have them all be Strings, you
need:

Public MtablenameFrom As String, _
MtablenameTo As String, _
MtablenameWorkingCopy As String

(I threw the vb line breaks in, but they aren't required)

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[dymondjack]

For the record, the first three variables here are being dimmed as Variants.

First two records... sorry


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Bill H.]

Well, certainly worth a try.

So, must all Dim and Public statements be separated like that? I mean, each
one with its own declaration?

Thx.

--Bill

 

[Bill H.]

Hmpht.

That worked, but changing those particular variables to string as you
suggested caused another problem when the variables were Null. Fixed that
by making them all variant.



Hope it holds...

--Bill

 

[dymondjack]

Don't change them all to variants, just use the Nz wrapper.

Nz(Variablename, "")

That returns a zero length string if the value is null (see the help file on
it).


Variant datatypes are very difficult to predict. When you use a variant,
its like throwing someing at access and saying "you figure out what it is".
And access's ideas on what things could be usually vary greatly from ours.

Variant datatypes should only be used if there is a specific reason, and
those reasons are generally few and far between. My latest project has over
3000 lines of code and I think there are two, or maybe three variants.


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[dymondjack]

The variables declarations do not need to be on separate lines (I did that
for ease of reading), but each variable needs a datatype, otherwise they are
variants.


Dim var1 As String, var2 As String, var3 as String

Dim var1, var2, var3 As String


In the top line, vars 1 and 2 both get dimmed as Strings, but in the bottom
one there is no specification of datatype, so they are variants.

You *might* want to do yourself a favor and disregard using that particular
shortcut in your code, and instead go with

Dim var1 As String
Dim var2 As String
Dim var3 As String

For the extra six characters and two lines, this will probably save you some
trouble further down the road (as it certainly would have here).


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain