2003->2010: Setting recordsource of some subforms leads to 'object is closed' error

[AvaisShuaib]

Hi,


I have a form which has a list of different table names and another list of tabs. When you select a table name and a tab and then click on a button, the following code is executed:

Dim objSubform      As Object

Select Case strTabName
    Case "Tab 1"
        Set objSubform = Me.tabTableData.Pages(0).Controls(0)
        intPageIndex = 0
    Case "Tab 2"
        Set objSubform = Me.tabTableData.Pages(1).Controls(0)
        intPageIndex = 1
    Case "Tab 3"
        Set objSubform = Me.tabTableData.Pages(2).Controls(0)
        intPageIndex = 2
    Case "Tab 4"
        Set objSubform = Me.tabTableData.Pages(3).Controls(0)
        intPageIndex = 3
    End Select

'Make Visible False to load data then to show
    objSubform.Visible = False
        
    mstrTableName = Me.lstDBTable.Value
        
    'Reload Subform Query
    objSubform.SourceObject = mstrTableName & " subform"
    MsgBox objSubform.SourceObject
    objSubform.Form.RecordSource = "SELECT * FROM " & mstrTableName
    MsgBox objSubform.Form.RecordSource
    objSubform.Requery

This code works completely in Access 2003 but in 2010 the majority of the forms work but there are a few that don't.

For the forms that work, the Sourceobject property is set in less than a second whilst for the ones that do not work, it takes a good few seconds. Once that is done, when attempting to set the record source, i get the following error:

"The expression you entered refers to an object that is either closed or doesn't exist."

All of the tables are in the same database and i have tried creating new subforms linked to the same tables that fail, and these new subforms also fail.


Does anyone have any ideas where i could be going wrong?

[sierra7]

Hi
Access 2003 used ADO as the default library and Access 2010 has reverted to DAO (as per Access 97), so if you are using ADO you have to register an ADO dll.

I might be up the wrong creek, but you could start by looking at your References(from a code window Tools>Reference s) then add something like Microsoft ActiveX Data Objects 2.8 Library.

Good luck!
S7

[AvaisShuaib]

Hi S7,

I changed the ref. To activex 2.8 library but I still get the same error. thanks for the effort though

[sierra7]

Hi Avais
Sorry that did not work but you said the code ran OK in Access 2003 and as you are referring to some type of object model that was the first thing to suspect.

Trying to figure out your code, I don't like the space before " subform"

   objSubform.SourceObject = mstrTableName & " subform"

If I understand you code correctly, you have a number of Forms (to be used as subforms), whose name is a TableName + space + 'subform'
I wonder if you have extra spaces in your form names? Which is why Access can't find them.
Obviously, spaces are allowed but they can cause problems, so personally I avoid them.
S7

[TheSmileyCoder]

First off I do not have AC2010, so I am truly guessing in the dark here.

That said, what I would try is this:
Create a copy of the form without any spaces or special charecters in the name, and see if this helps. However since you say its working for some forms, and not for others, I don't actually put to much faith into this idea, but worth a shot.

The other thing I would try, since you say that it takes a while for the "trouble" forms to load, is to see if it could be a timing issue of sorts. To test this out, basicly put a DoEvents between setting the sourceobject, and setting the recordsource.

Does the subform have any linked subforms of its own?

Why do you even need to set the recordsource of the form? Is the same subform used to display different tables?

[AvaisShuaib]

@TheSmileyCoder ,

When i do not set the recordosurce, the only change that occurs now is that the same error is thrown but now when trying to requery the form on line 28.

And no, different subforms dispaly different tables.

And no, subforms do not have any linked subforms of their own.

Also, for the DoEvents, to i merely put 'DoEvents' between the two? as this is what i have done and nothing spectacular happens, it runs the code and then goes onto the setting of the recordsource, which fails.

@S7

I have tried it without spaces and still no luck. I have named the form different things with only 1 or 2 columns from the table and still no connection.

Furthermore, when i jut have a subform in my form, as in no tab controls, and I link it to one of the problem tables, i get a blank subform at runtime but with the good tables, i get the data. And as i understand, if a subform is blank, then there was a problem connecting to the data. but how can that be for one table and not the other when they are in the same database? and all the columns in GMIS are varchars so nothing exotic.

[TheSmileyCoder]

Again speaking from a AC2003 perspective, I can tell you that setting the recordsource of a form, causes an automatic requery (thus your line 28 requery is not necessary). I still don't see why you dont "simply" have the recordsource set before loading the form.

You say your field types are varchar, am I to understand that your backend is not a Access database, or is varchar new in 2010? Or do you simply mean Text, when you say varchar?

Maybe a fieldname in the affected tables conflicts with a reserved word? Could you take the form/table (one thats not working of course) with the least amount of fields, and type out here, field names and field types, as well as any controls used on the form?

[AvaisShuaib]

My backend is an SQL Server 2000.

I have tried setting the source first like below:

 'Reload Subform Query
    objSubform.Form.RecordSource = "SELECT * FROM " & mstrTableName
    objSubform.SourceObject = mstrTableName & " subform"

The above results in the good tables giving the same error when setting the recordsource so on the first line.


Field Names from the sql 200 db:
RXM nvarchar(255)
Name nvarchar(255)
Rating nvarchar(255)
CPSName nvarchar(255)
CPSCode nvarchar(255)
GroupCode nvarchar(255)
GroupRating nvarchar(255)
GROUP_NAME varchar(255)
PARTNER_REVIEW_ DATE varchar(25)

I am not too sure on how to get details of controls on the form. so any insights would be good.



Just realised when i updated the activex reference to 2.8, i get compile errors (note above testing has be done with activex 2.7), i am haivng trouble updating some sql statements to be in comploiance with activex 2.8 for example not sure how to modify the following:

Dim lrstCurr        As Recordset
Dim lrstFX          As Recordset
Dim lstrSQL         As String
 lstrSQL = "Select * from tableName where name ='" & "X." & UMap(lobjCn, "K", something, 1) & ".Y.V" & "'"
    lrstCurr.Open lstrSQL, lobjCn, adOpenStatic, adLockReadOnly

I know i need to use openRecordset but not too sure on how.

[TheSmileyCoder]

In general using NAME as a field name can cause trouble, and should be avoided. Whether or not it has any effect on the issue at hand I really can't tell.

From what you have told, it seems like there is a trouble loading the recordset. Since its only affecting some tables, I am again guessing that it could be related to field names. It must be something quite special since its working in 2003, but not in 2010.

If you can, try to change the NAME to something else and see if that solves anything. If its doesn't I will try to see if I can fine someone who has experience with SQL server and AC2010.

[AvaisShuaib]

There are other tables where NAME is not present and they do not appear, although what i have noticed is:

I have 3 radio buttons, and when you click on a aprticular button, a different list of tables are shown in a lsit box, you then select these tables and a tab and click on another button which will execute the code above. Now all of the talbes in 2 of that radio set work fine, the tables in 1 of the radio buttons do not work except the tables that are also come under the other radio buttons. However, i have earlier tried creating a new radio button but the same tables giv eproblems, also tried hardcoding certian tables into the other 2 working radio buttons, but they also still give problems. So this probably is jsut a coincidence but thought i would mention it just in case.


Thanks for all your help, its most likely i am not writing down some important piece of info that would enable you to figure out the problem

[TheSmileyCoder]

Well there could be other fields in those tables that use other reserved words.

I will admit its hard, since you say its something that works in 2003 (Which I am also using), but not in 2010(Which I have never tried), but from what you tell me, its atleast consistent. Thats means it MUST be possible to work out whats special about the "problem" tables. They must have something in common, that the other tables dont have. You need to try to focus on what they have in common. Do they use similar field names? Is there special data in any of them?

[sierra7]

Hi again,
I'm running Access 2010 front end against a SQL 2008R2 backend. I find that if you use any reserved words as fieldnames, then SQLServer puts square brackets around them, but I avoid them if possible.

In your sample code I would defind the recordsets explicitly as either ADO or DAO.

Dim lrstCurr        As ADO.Recordset 
Dim lrstFX          As ADO.Recordset 
Dim lstrSQL         As String 
or
Dim lrstCurr        As DAO.Recordset 
Dim lrstFX          As DAO.Recordset 
Dim lstrSQL         As String

As you say the system runs ok from Access 2003 I would start with ADO. See if it compiles then.

As for your SQL string

lstrSQL = "Select * from tableName where name ='" & "X." & UMap(lobjCn, "K", something, 1) & ".Y.V" & "'"

First I don't understand the UMap() function, but hope it makes sense to you. (I could not find it in VB or T-SQL). You seem to be concatonating four elements to make a value for the field 'name', so try putting square brackes around it [name].
I usually put a semicolon at the end of an SQL statement in Access but I'm not sure whether it is still essential. (MS-SQL doesn't but Oracle does. Very confusing!)
S7

[AvaisShuaib]

I have managed to fix one of my problem tables by changing the type to nvarchar(255) from varchar(25). I guess i have to now check all the others and hope this is the case for all of them.

Thanks for your help.



PS. if you know of any other dodgy database types, it would be most welcome as i have quite a few of these tables and not all of them have varchars in them

[AvaisShuaib]

HI All, actually my above comment is wrong,

as i have mentioned, the tables are linked to an SQL Server database.

What i did not mention is that the access 2003 ran on windows xp and the access 2010 runs on win 7. and the win7 is on a different network and atm there are some dns problems, as such some of the tables were updated to include the new dns server name of the sql server but some were not. and the ones that were not were giving the errors.

[sierra7]

Avais,
My understanding is that nvarchar is required if you are storing 'non-Latin' characters such as Arabic, Greek, Hebrew, Russian, Thai,Chinese etc. Normal varchar can cope with umlauts, cedillas and circumflex etc. I believe that nvarchars take up more storage (double?) so are less efficient if not needed.

I also wondered why the field-widths were mostly 255. As someone who learned database design using dBase where you would discuss at length whether a field should be 10 or 15 characters long, I always feel uncomfortable when I see this. i.e. indicative of sloppy design.

Another point I have read, is not to mix varchar and nvarchar. I doubt this is because the software can't handle it (perhaps it is!) but I would imagine that if ever the application was to run in,say Russian script, there could be a problem if nvarchar-data was to be put into a varchar field. (Is this what your UMap() function was doing?)

The fact that the recent changes you made have had a beneficial effect surprises me. It leads me to believe that the problem is not just a difference between Access 2003 and 2010 as you suggest.

I have never been involved with Unicode but you may need to investigate issues relating to different versions of SQL Server Native Client when connecting to your backend.

Best of luck!
S7