Problem setting tabledef from a form's RecordSource

[Bruce Dodds]

I'm trying to set a tabledef object from a form object's RecordSource in
Access 97. This is the code:

Public Function EditSubjectForm (ByVal frm As Form) As Boolean

Dim db As Database, tbl As TableDef
Set db = CurrentDb
Set tbl = db!TableDefs(fr m.RecordSource)

END OF CODE

When I run this I get the message "Run-time error '3265'. Item not found
in this collection".

I can display frm.RecordSourc e in the Immediate window, and cut and
paste it there into the following statement which works:

set tbl = db.TableDefs("t blSubjectOSSP")

Does anyone know what I should do to get this to work? Thanks!

[Larry Linson]

I've just tested almost identical code in Access 2003 that worked just fine
located in a Standard Module, called from both the Open or Load events of a
Form.

Public Function SetTDF(calledfr om As String, ByVal frm As Form) As Boolean
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
MsgBox calledfrom & " -- " & frm.Name & ": " & frm.RecordSourc e
Set tdf = db.TableDefs(fr m.RecordSource)
MsgBox calledfrom & " -- TableDef is " & tdf.Name
Set tdf = Nothing
Set db = Nothing
SetTDF = True
End Function

The code calling this function in the Form's Module was:

Private Sub Form_Load()
Dim retval As Boolean
retval = SetTDF("Load", Me)
MsgBox "Open SetTDF returned " & retval
End Sub

Private Sub Form_Open(Cance l As Integer)
Dim retval As Boolean
retval = SetTDF("Open", Me)
MsgBox "Open SetTDF returned " & retval
End Sub

So, you are going to need to do some debugging:

Just for "grins", qualify your Database and TableDefs definitions as
DAO.Database and DAO.TableDefs. Next, see if you are really getting what you
expect in the argument "frm", by inserting the following

MsgBox "Form name is " & frm.Name

before you use it. If that looks OK, add similar MsgBox statements to mine
in the code above to see what values you actually have.

The error message you get certainly leads me to believe that you are not
getting a valid table name from "frm.RecordSour ce" when you run your code.

Larry Linson
Microsoft Access MVP

"Bruce Dodds" <br********@com cast.net> wrote in message
news:0JbOc.1797 05$%_6.17383@at tbi_s01...

I'm trying to set a tabledef object from a form object's RecordSource in
Access 97. This is the code:

Public Function EditSubjectForm (ByVal frm As Form) As Boolean

Dim db As Database, tbl As TableDef
Set db = CurrentDb
Set tbl = db!TableDefs(fr m.RecordSource)

END OF CODE

When I run this I get the message "Run-time error '3265'. Item not found
in this collection".

I can display frm.RecordSourc e in the Immediate window, and cut and
paste it there into the following statement which works:

set tbl = db.TableDefs("t blSubjectOSSP")

Does anyone know what I should do to get this to work? Thanks!

[Sherwood Wang]

"Bruce Dodds" <br********@com cast.net> wrote in message
news:0JbOc.1797 05$%_6.17383@at tbi_s01...

I'm trying to set a tabledef object from a form object's RecordSource in
Access 97. This is the code:

Public Function EditSubjectForm (ByVal frm As Form) As Boolean

Dim db As Database, tbl As TableDef
Set db = CurrentDb
Set tbl = db!TableDefs(fr m.RecordSource)

END OF CODE

When I run this I get the message "Run-time error '3265'. Item not found
in this collection".

I can display frm.RecordSourc e in the Immediate window, and cut and
paste it there into the following statement which works:

set tbl = db.TableDefs("t blSubjectOSSP")

Does anyone know what I should do to get this to work? Thanks!

frm is object no string!

*Sherwood Wang MVP*

[Bruce Dodds]

Larry Linson wrote:

I've just tested almost identical code in Access 2003 that worked just fine
located in a Standard Module, called from both the Open or Load events of a
Form.

Public Function SetTDF(calledfr om As String, ByVal frm As Form) As Boolean
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
MsgBox calledfrom & " -- " & frm.Name & ": " & frm.RecordSourc e
Set tdf = db.TableDefs(fr m.RecordSource)
MsgBox calledfrom & " -- TableDef is " & tdf.Name
Set tdf = Nothing
Set db = Nothing
SetTDF = True
End Function

The code calling this function in the Form's Module was:

Private Sub Form_Load()
Dim retval As Boolean
retval = SetTDF("Load", Me)
MsgBox "Open SetTDF returned " & retval
End Sub

Private Sub Form_Open(Cance l As Integer)
Dim retval As Boolean
retval = SetTDF("Open", Me)
MsgBox "Open SetTDF returned " & retval
End Sub

So, you are going to need to do some debugging:

Just for "grins", qualify your Database and TableDefs definitions as
DAO.Database and DAO.TableDefs. Next, see if you are really getting what you
expect in the argument "frm", by inserting the following

MsgBox "Form name is " & frm.Name

before you use it. If that looks OK, add similar MsgBox statements to mine
in the code above to see what values you actually have.

The error message you get certainly leads me to believe that you are not
getting a valid table name from "frm.RecordSour ce" when you run your code.

Larry Linson
Microsoft Access MVP

"Bruce Dodds" <br********@com cast.net> wrote in message
news:0JbOc.1797 05$%_6.17383@at tbi_s01...

I'm trying to set a tabledef object from a form object's RecordSource in
Access 97. This is the code:

Public Function EditSubjectForm (ByVal frm As Form) As Boolean

Dim db As Database, tbl As TableDef
Set db = CurrentDb
Set tbl = db!TableDefs(fr m.RecordSource)

END OF CODE

When I run this I get the message "Run-time error '3265'. Item not found
in this collection".

I can display frm.RecordSourc e in the Immediate window, and cut and
paste it there into the following statement which works:

set tbl = db.TableDefs("t blSubjectOSSP")

Does anyone know what I should do to get this to work? Thanks!

Thanks for the tip. I did paste your code into my module, and it ran
fine. It was kind of baffling since it was so similar to my code.

I had been getting inconsistent results with "Compile and Save All" in
this database. Sometimes after running that the menu Compile options
would be greyed out; sometimes not. So I deleted the lines that I posted
above, compacted the database, and retyped the code. Now it works. I
just hope that the database is uncorrupted as it stands - I created it
by importing from a corrupted db.

"O rose, thou art sick . . . "