Unable to set subdatasheet to None - Microsoft Access / VBA

karen scheu via AccessMonster.com

I am having trouble lately opening my local tables. It is taking a a few
seconds to open the table that has no records in it. I read that there is a
bug with the AutoCorrect feature and so I set that option off in my DB which
is an Access 2002-2003 database, then tried to set the subdatasheet to none,
but every time I get back into it, it goes back to Auto. How can I set it to
none and have it stay. I also tried creating a new MDB and setting the
option first then importing the table and setting the subdatasheet to None
and it still does not save.

Please help. I don't really even know if this will resolve my problem yet.

Thanks.
--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200509/1

Nov 13 '05 #1

Subscribe Reply

2077

Barry Edmund Wright

Karen,
The code below will iterate through all your tables and turn the [auto] to
none. I believe I got the code from Allan Brown's site.
Maybe this will help you.
Regards,
Barry

Public Function TurnOffSubDataS h()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
Dim vCnt As Long
Const conPropName = "SubdatasheetNa me"
Const conPropValue = "[None]"

vStarted = Timer
HourGlass_On

Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
If tdf.Connect = vbNullString And Asc(tdf.Name) <> 126 Then 'Not
attached, or temp.
If Not HasProperty(tdf , conPropName) Then
Set prp = tdf.CreatePrope rty(conPropName , dbText, conPropValue)
tdf.Properties. Append prp
Else
If tdf.Properties( conPropName) <> conPropValue Then
tdf.Properties( conPropName) = conPropValue
End If
End If
End If
End If
vCnt = vCnt + 1
Next tdf

Set prp = Nothing
Set tdf = Nothing
Set db = Nothing

vFinished = Timer
HourGlass_Off

MsgBox "Procedure Complete! " & vCnt, vbInformation, "Time: " &
Format((vFinish ed - vStarted), "fixed") & " Seconds"

End Function
Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties( strPropName)
HasProperty = (Err.Number = 0)
End Function

"karen scheu via AccessMonster.c om" <fo***@AccessMo nster.com> wrote in
message news:54******** ***@AccessMonst er.com...
I am having trouble lately opening my local tables. It is taking a a few
seconds to open the table that has no records in it. I read that there is a
bug with the AutoCorrect feature and so I set that option off in my DB which
is an Access 2002-2003 database, then tried to set the subdatasheet to none,
but every time I get back into it, it goes back to Auto. How can I set it
to
none and have it stay. I also tried creating a new MDB and setting the
option first then importing the table and setting the subdatasheet to None
and it still does not save.

Please help. I don't really even know if this will resolve my problem yet.

Thanks.
--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200509/1

Nov 13 '05 #2

karen scheu via AccessMonster.com

Thank you so much for this code.

Regards,
Karen

Barry Edmund Wright wrote:

Karen,
The code below will iterate through all your tables and turn the [auto] to
none. I believe I got the code from Allan Brown's site.
Maybe this will help you.
Regards,
Barry

Public Function TurnOffSubDataS h()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
Dim vCnt As Long
Const conPropName = "SubdatasheetNa me"
Const conPropValue = "[None]"

vStarted = Timer
HourGlass_On

Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
If tdf.Connect = vbNullString And Asc(tdf.Name) <> 126 Then 'Not
attached, or temp.
If Not HasProperty(tdf , conPropName) Then
Set prp = tdf.CreatePrope rty(conPropName , dbText, conPropValue)
tdf.Properties. Append prp
Else
If tdf.Properties( conPropName) <> conPropValue Then
tdf.Properties( conPropName) = conPropValue
End If
End If
End If
End If
vCnt = vCnt + 1
Next tdf

Set prp = Nothing
Set tdf = Nothing
Set db = Nothing

vFinished = Timer
HourGlass_Off

MsgBox "Procedure Complete! " & vCnt, vbInformation, "Time: " &
Format((vFinis hed - vStarted), "fixed") & " Seconds"

End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties( strPropName)
HasProperty = (Err.Number = 0)
End Function

I am having trouble lately opening my local tables. It is taking a a few
seconds to open the table that has no records in it. I read that there is a
bug with the AutoCorrect feature and so I set that option off in my DB which
is an Access 2002-2003 database, then tried to set the subdatasheet to none,
but every time I get back into it, it goes back to Auto. How can I set it
to
none and have it stay. I also tried creating a new MDB and setting the
option first then importing the table and setting the subdatasheet to None
and it still does not save.

Please help. I don't really even know if this will resolve my problem yet.

Thanks.

--
Message posted via http://www.accessmonster.com

Nov 13 '05 #3

Similar topics

1910

subDatasheet setup on query behind form not being remembered

by: astro | last post by:

I'm trying to get a subdataSheet embedded on a form which will be displayed in datasheet view. I setup the 'record source' for the form to a query on a table. I go into the query properties and setup a subdatasheet. I test this and it works. I close the query builder - get prompted to save - reply yes. I reopen the 'record source' and the subdatasheet setting are gone. Any ideas?

Microsoft Access / VBA

1397

Unable to set .SubDateSheetName=[None]

by: (PeteCresswell) | last post by:

Same DB, same table, two different PCs. -------------------------------------------------------------- PC #1, I'm able to do it, no problem. PC #2, will let me do it and close the table with no error messages, but when I open it again, it has reverted to . -------------------------------------------------------------- Something about a service pack?

Microsoft Access / VBA

11112

Change table property 'subdatasheet name' via code

by: Jim M | last post by:

After learning that the 'subdatasheet name' property set to "" can adversely effect database speed, I wanted to send a small code snippent to my end users to reset the SubdatasheetName Property so they don't have to manually cycle through 35 or 40 tables and change the property. VBA help says: "To set the SubdatasheetName property by using Visual Basic, you must first either: -Set the property in table Design view by pointing to...

Microsoft Access / VBA

1373

change Subdatasheet to None in ADO

by: ray | last post by:

Hi all, I have seen and tried some code posted here to reset the Subdatasheet property of Tables to NONE. It works fine in DAO, but I am using an ADP. So that means that the code won't work, even with a reference set to the DAO 3.6 Object library. It falls over as soon as I try to execute the line Set MyDB = CurrentDb()

Microsoft Access / VBA

4987

Subdatasheet property

by: ray | last post by:

I am at the moment manually setting Subdatasheet to in all the tables in this adp. If anybody can put me out of my misery by suggesting some automated way to do it, I would be most grateful. I can't work out how to get ADO or the ADOX catalog to do it, and DAO doesn't connect to projects. I am now up to table 75 out of 425 in Systest, and I will have to do it again in UAT and Prod. Ewwwww. This is the thirty-eighth most boring thing I...

Microsoft Access / VBA

1364

displaying subdatasheet in a form? can it be done

by: burningthemidniteoil | last post by:

I have access 2002. I have a query that diplays a subdatasheet ( +/- subtrees are shown on the record), but when i make a datasheet form out of that query , it does not display the plus (+) and minus(-) trees. Q. How do you display a subdatasheet in a datasheet form. Note, i do not want to display a subform i want to display a subdatasheet. There are subdatasheet properties on the form under format but they don't do anything??

Microsoft Access / VBA

2301

subdatasheet = [none]

by: Fred Zuckerman | last post by:

I read in this group, that setting the subdatasheet property to for linked tables is recommended. In my database I have 4 linked tables. They are all ODBC links to views on a SQL server. I change the table property from to and save. But when I re-open they are reset to . Can someone help? I also read that this occurs in Access97, but my database is Access2000 on W98. TIA, Fred Zuckerman

Microsoft Access / VBA

4218

Table properties Subdatasheet name [Auto]/[None]

by: Simon | last post by:

Dear reader, By default the "Subdatasheet name" of a table is set to . If I change the setting to and save the changes the table property Subdatasheet name is still set to .

Microsoft Access / VBA

1663

Default Subdatasheet Name in new tables

by: Bob Darlington | last post by:

I have a routine which creates a new table using "SELECT INTO ....." Is there anyway to set the default Subdatasheet name property to 'None' for new tables? Or do I need to run a separate routine to set it each time? I'm using Access 2002, and I do have the code to reset this value if necessary. -- Bob Darlington Brisbane

Microsoft Access / VBA

8440

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8866

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8781

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8550

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

7381

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6191

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5662

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4365

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1769

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General