Strange Type Mismatch Error

[GarstonMassive]

Hi,

I'm using A97 and getting the above error message on the following line:

Set tdf = "tblTableName" & CStr(x)

Access highlights the ampersand symbol.

X has been declared as an integer and runs from 1 to 10 in a loop.

tdf has been declared as a TableDef.

Anyone shed any light on it? Something to do with References perhaps?

 

[Remou]

Perhaps:
Set tdf = CurrentDb.TableDefs("tblTableName" & CStr(X))

 

[MajP]

Do you already have tables named?

tblTableName1
tblTableName2
....

Then

set tdf = currentdb.tabledefs("tblTableName" & CStr(x))

If you are trying to create a table def with the above name look at "currentdb.createtabledef" method.

 

[MajP]

Remou beat me.

 

[GarstonMassive]

That's great chaps! Thanks a lot.

Also I'm now trying to open a recordset on the table name but when I try I get a "Object invalid or no longer set" error message.

The code I'm using:

Set rsRecordset = CurrentDb.OpenRecordset(tdf.Name, dbOpenSnapshot)

TIA.

 

[Golom]

Need to see more of your code.

That message means that the tabledef that you defined (tdf) has either been set to Nothing, has gone out of scope or has not yet been set at the point where you are issuing this command.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[GarstonMassive]

Golom as you requested:

For x = 1 To 12

Set tdf = CurrentDb.TableDefs("tblTableName" & CStr(x))
Set rsRecordset = CurrentDb.OpenRecordset(tdf, dbOpenSnapshot)

debug.print tdf

Next

The error message has changed now to "Type Mismatch" and the OpenRecordset command is highlighted.

This seems to me to be a References issue???

 

[Golom]

I don't see (from this code snippet at least) why you need the TableDef at all. You are getting a "Type Mismatch" because you are referencing "tdf" and not "tdf.Name" in the "OpenRecordset" call.

For x = 1 To 12

   Set rsRecordset = CurrentDb.OpenRecordset("tblTableName" & CStr(x), dbOpenSnapshot)

Next

At the end of the For loop however, you will have a recordset that contains data from "tblTableName12" ... the others having been replaced as you proceed through the loop.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[GarstonMassive]

Golom I changed it to "tdf.Name" and I got my original error "Object invalid or no longer set".

As I loop through the tables I do some data manipulation but I haven't included it here as it's not important.

Any ideas?

 

[Golom]

Do you possibly have an On Error Resume Next somewhere before this?

It is possible that the

Set tdf = CurrentDb.TableDefs("tblTableName" & CStr(x))

Is raising an error and "tdf" is not being set.
Try referencing tdf before you use it in the OpenRecordset as in

Set tdf = CurrentDb.TableDefs("tblTableName" & CStr(x))
Debug.Print tdf.Name

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[GarstonMassive]

No there's no Error handling in there at all.

This is very strange and leads me to think it's something to do with refs.

If I try your code above it says the same error message. When I hover over the "tdf.Name" syntax the message pops up in a tooltip.

 

[GarstonMassive]

...fixed it. I changed "CurrentDB" to "DBEngine(0)(0)" and it worked.

Think it maybe a A97 thing or something to do with the fact that DAO 2.5 is referenced.

Dont go there - I know.......

Thanks for your assistance fellas. This forum is great.

 

[Golom]

Now we're getting somewhere. Try this

Set tdf = CurrentDb.TableDefs("tblTableName" & CStr(x))
 
MsgBox "tblTableName" & CStr(x) & vbCrLf & _
       TypeName(tdf) & vbCrLf & _
       Err.Number & " - " & Err.Description

It may be that you have turned off error messages in Access. It is clear however that the "Set" statement is failing and that may be due to "tdf" not being the correct object type or to the fact that "tblTableName" & CStr(x) is a non-existent table name.



[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]