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.

--
Bill

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.

--

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


"Bill H." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.
>
> --
> Bill
>
>

"Bill H." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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

--

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


"Ken Snell (MVP)" <(E-Mail Removed)> wrote in message
news:evuf$(E-Mail Removed)...
> 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.
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
> http://www.accessmvp.com/KDSnell/
>
>
> "Bill H." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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.
>>
>> --
>> Bill
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "Bill H." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>

"Bill H." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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:

>>> dim MtablenameTo as string

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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


"Dirk Goldgar" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> "Bill H." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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:
>
>>>> dim MtablenameTo as string
>
> 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>

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


"Bill H." wrote:

> 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
>
>
> "Dirk Goldgar" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > "Bill H." <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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:
> >
> >>>> dim MtablenameTo as string
> >
> > 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.
> >
> > --
> > Dirk Goldgar, MS Access MVP
> > www.datagnostics.com
> >
> > (please reply to the newsgroup)
> >
>
>
>

> 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


"dymondjack" wrote:

> 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
>
>
> "Bill H." wrote:
>
> > 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
> >
> >
> > "Dirk Goldgar" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> > > "Bill H." <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > >> 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:
> > >
> > >>>> dim MtablenameTo as string
> > >
> > > 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.
> > >
> > > --
> > > Dirk Goldgar, MS Access MVP
> > > www.datagnostics.com
> > >
> > > (please reply to the newsgroup)
> > >
> >
> >
> >

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

"dymondjack" <dymondjack at hot mail dot com> wrote in message
news:6722834B-8DF9-4049-92E9-(E-Mail Removed)...
> 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
>
>
> "Bill H." wrote:
>
>> 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
>>
>>
>> "Dirk Goldgar" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> > "Bill H." <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> 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:
>> >
>> >>>> dim MtablenameTo as string
>> >
>> > 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.
>> >
>> > --
>> > Dirk Goldgar, MS Access MVP
>> > www.datagnostics.com
>> >
>> > (please reply to the newsgroup)
>> >
>>
>>
>>