help.... table record has a "+" sign beside each record?

G

Guest

good day all,

I have a tblinventory that I have been working with, and I just went in to
the tbl to double check some data and noticed a box with a "+". when I click
on the box it drops down to information about my invoices, I don't know who
the "+" got there or how to get rid of it? Can anyone help?

thanks,

Brook
 
D

Dirk Goldgar

Brook said:
good day all,

I have a tblinventory that I have been working with, and I just
went in to the tbl to double check some data and noticed a box with
a "+". when I click on the box it drops down to information about my
invoices, I don't know who the "+" got there or how to get rid of it?
Can anyone help?

Access will put in the "subdatasheet" automatically and by default if
you've established a one-to-many relationship between this table and
another. It can slow things down sometimes, so if you want to get rid
of it, open the table in design view, open its property sheet, and set
the Subdatasheet Name property to "[None]".
 
A

Allen Browne

Open the table in design view.
Open the Properties box (View menu).
Set the Subdatasheet Name property to:
[None]

This thing got there in Access 2000, because MS thought it might be useful.
They did not bother documenting how slow it makes everything, or the lack of
events for these things, and they turned in on by default, so you now have
to turn it off every time you create a table.

If you want to turn it off programmatically for all tables in your database,
this should do it:

Function TurnOffSubDataSh()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
Const conPropName = "SubdatasheetName"
Const conPropValue = "[None]"

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.CreateProperty(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
Next

Set prp = Nothing
Set tdf = Nothing
Set db = Nothing
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
 
T

tina

If you want to turn it off programmatically for all tables in your
database,
this should do it:

nice, Allen, very handy. it's going right into my module of "default code
for all my dbs" :)

Allen Browne said:
Open the table in design view.
Open the Properties box (View menu).
Set the Subdatasheet Name property to:
[None]

This thing got there in Access 2000, because MS thought it might be useful.
They did not bother documenting how slow it makes everything, or the lack of
events for these things, and they turned in on by default, so you now have
to turn it off every time you create a table.

If you want to turn it off programmatically for all tables in your database,
this should do it:

Function TurnOffSubDataSh()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
Const conPropName = "SubdatasheetName"
Const conPropValue = "[None]"

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.CreateProperty(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
Next

Set prp = Nothing
Set tdf = Nothing
Set db = Nothing
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Brook said:
good day all,

I have a tblinventory that I have been working with, and I just went in
to
the tbl to double check some data and noticed a box with a "+". when I
click
on the box it drops down to information about my invoices, I don't know
who
the "+" got there or how to get rid of it? Can anyone help?

thanks,

Brook
 
G

Guest

Hello Dirk,

thanks so much for the reply!

that was exactly what I was looking for!

thanks again..

Just one other question... will this affect any of my data?

Brook

Dirk Goldgar said:
Brook said:
good day all,

I have a tblinventory that I have been working with, and I just
went in to the tbl to double check some data and noticed a box with
a "+". when I click on the box it drops down to information about my
invoices, I don't know who the "+" got there or how to get rid of it?
Can anyone help?

Access will put in the "subdatasheet" automatically and by default if
you've established a one-to-many relationship between this table and
another. It can slow things down sometimes, so if you want to get rid
of it, open the table in design view, open its property sheet, and set
the Subdatasheet Name property to "[None]".

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

(please reply to the newsgroup)
 
D

Dirk Goldgar

Brook said:
Hello Dirk,

thanks so much for the reply!

that was exactly what I was looking for!

thanks again..

Just one other question... will this affect any of my data?

No, not in the least. It's a user-interface feature, and its presence
or absence has no effect on the data itself.
 
B

BruceM

Where would that function be placed? Several times I've seen code that
applies to the whole database, but I've never been quite sure where to put
it. This code seems especially handy, so I would like to implement it.

Allen Browne said:
Open the table in design view.
Open the Properties box (View menu).
Set the Subdatasheet Name property to:
[None]

This thing got there in Access 2000, because MS thought it might be
useful. They did not bother documenting how slow it makes everything, or
the lack of events for these things, and they turned in on by default, so
you now have to turn it off every time you create a table.

If you want to turn it off programmatically for all tables in your
database, this should do it:

Function TurnOffSubDataSh()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
Const conPropName = "SubdatasheetName"
Const conPropValue = "[None]"

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.CreateProperty(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
Next

Set prp = Nothing
Set tdf = Nothing
Set db = Nothing
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Brook said:
good day all,

I have a tblinventory that I have been working with, and I just went in
to
the tbl to double check some data and noticed a box with a "+". when I
click
on the box it drops down to information about my invoices, I don't know
who
the "+" got there or how to get rid of it? Can anyone help?

thanks,

Brook
 
A

Allen Browne

Click the Modules tab of the Database window, and click New.
Access opens a new code window.
Paste the code in there, and save it with a name such as Module1.

To run it, press Ctl+G to open the Immediate window, and enter:
? TurnOffSubDataSh()

Mind the line wrap, i.e. there were a couple of lines that spilled over onto
the next one in the post.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

BruceM said:
Where would that function be placed? Several times I've seen code that
applies to the whole database, but I've never been quite sure where to put
it. This code seems especially handy, so I would like to implement it.

Allen Browne said:
Open the table in design view.
Open the Properties box (View menu).
Set the Subdatasheet Name property to:
[None]

This thing got there in Access 2000, because MS thought it might be
useful. They did not bother documenting how slow it makes everything, or
the lack of events for these things, and they turned in on by default, so
you now have to turn it off every time you create a table.

If you want to turn it off programmatically for all tables in your
database, this should do it:

Function TurnOffSubDataSh()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
Const conPropName = "SubdatasheetName"
Const conPropValue = "[None]"

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.CreateProperty(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
Next

Set prp = Nothing
Set tdf = Nothing
Set db = Nothing
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

Brook said:
good day all,

I have a tblinventory that I have been working with, and I just went in
to
the tbl to double check some data and noticed a box with a "+". when I
click
on the box it drops down to information about my invoices, I don't know
who
the "+" got there or how to get rid of it? Can anyone help?
 
B

BruceM

I tried it, and received a "User-defined type not defined" error message at
Dim db As DAO.Database. I think I will not spend a lot of time pursuing
this particular issue, but it is useful to know how to run code in this
manner. I will just turn off sub-datasheet manually as needed. It only
involves a few tables in each database. Thanks for your suggestion and
reply.

Allen Browne said:
Click the Modules tab of the Database window, and click New.
Access opens a new code window.
Paste the code in there, and save it with a name such as Module1.

To run it, press Ctl+G to open the Immediate window, and enter:
? TurnOffSubDataSh()

Mind the line wrap, i.e. there were a couple of lines that spilled over
onto the next one in the post.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

BruceM said:
Where would that function be placed? Several times I've seen code that
applies to the whole database, but I've never been quite sure where to
put it. This code seems especially handy, so I would like to implement
it.

Allen Browne said:
Open the table in design view.
Open the Properties box (View menu).
Set the Subdatasheet Name property to:
[None]

This thing got there in Access 2000, because MS thought it might be
useful. They did not bother documenting how slow it makes everything, or
the lack of events for these things, and they turned in on by default,
so you now have to turn it off every time you create a table.

If you want to turn it off programmatically for all tables in your
database, this should do it:

Function TurnOffSubDataSh()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
Const conPropName = "SubdatasheetName"
Const conPropValue = "[None]"

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.CreateProperty(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
Next

Set prp = Nothing
Set tdf = Nothing
Set db = Nothing
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

good day all,

I have a tblinventory that I have been working with, and I just went
in to
the tbl to double check some data and noticed a box with a "+". when I
click
on the box it drops down to information about my invoices, I don't know
who
the "+" got there or how to get rid of it? Can anyone help?
 
A

Allen Browne

If you are using Access 2000 or 2002, you may need to add a reference to the
DAO library (which is referenced by default in all other version of Access.)

Open any code window.
Choose References on the Tools menu
Check the box beside:
Microsoft DAO 3.6 Library

If you do want to know more about references, see:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

BruceM said:
I tried it, and received a "User-defined type not defined" error message at
Dim db As DAO.Database. I think I will not spend a lot of time pursuing
this particular issue, but it is useful to know how to run code in this
manner. I will just turn off sub-datasheet manually as needed. It only
involves a few tables in each database. Thanks for your suggestion and
reply.

Allen Browne said:
Click the Modules tab of the Database window, and click New.
Access opens a new code window.
Paste the code in there, and save it with a name such as Module1.

To run it, press Ctl+G to open the Immediate window, and enter:
? TurnOffSubDataSh()

Mind the line wrap, i.e. there were a couple of lines that spilled over
onto the next one in the post.


BruceM said:
Where would that function be placed? Several times I've seen code that
applies to the whole database, but I've never been quite sure where to
put it. This code seems especially handy, so I would like to implement
it.

Open the table in design view.
Open the Properties box (View menu).
Set the Subdatasheet Name property to:
[None]

This thing got there in Access 2000, because MS thought it might be
useful. They did not bother documenting how slow it makes everything,
or the lack of events for these things, and they turned in on by
default, so you now have to turn it off every time you create a table.

If you want to turn it off programmatically for all tables in your
database, this should do it:

Function TurnOffSubDataSh()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
Const conPropName = "SubdatasheetName"
Const conPropValue = "[None]"

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.CreateProperty(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
Next

Set prp = Nothing
Set tdf = Nothing
Set db = Nothing
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

good day all,

I have a tblinventory that I have been working with, and I just went
in to
the tbl to double check some data and noticed a box with a "+". when
I click
on the box it drops down to information about my invoices, I don't
know who
the "+" got there or how to get rid of it? Can anyone help?
 
B

BruceM

Of course, the References! As soon as I read what you wrote I realized I
should have thought of that. It now works as intended, and the subdatasheet
is turned off. I actually have rare occasions to use that feature, so I can
turn it on if need be. I know I'm not supposd to work directly in tables,
but on occasion I need to do something like delete a record from a junction
table that contains only the EmployeeID. I know I could make a form to take
care of that occasional chore, but for the once or twice a year that it is
necessary the priority is pretty low.
I have already bookmarked your site, but now I will visit that particular
page to learn more about references. Thanks again.

Allen Browne said:
If you are using Access 2000 or 2002, you may need to add a reference to
the DAO library (which is referenced by default in all other version of
Access.)

Open any code window.
Choose References on the Tools menu
Check the box beside:
Microsoft DAO 3.6 Library

If you do want to know more about references, see:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

BruceM said:
I tried it, and received a "User-defined type not defined" error message
at Dim db As DAO.Database. I think I will not spend a lot of time
pursuing this particular issue, but it is useful to know how to run code
in this manner. I will just turn off sub-datasheet manually as needed.
It only involves a few tables in each database. Thanks for your
suggestion and reply.

Allen Browne said:
Click the Modules tab of the Database window, and click New.
Access opens a new code window.
Paste the code in there, and save it with a name such as Module1.

To run it, press Ctl+G to open the Immediate window, and enter:
? TurnOffSubDataSh()

Mind the line wrap, i.e. there were a couple of lines that spilled over
onto the next one in the post.


Where would that function be placed? Several times I've seen code that
applies to the whole database, but I've never been quite sure where to
put it. This code seems especially handy, so I would like to implement
it.

Open the table in design view.
Open the Properties box (View menu).
Set the Subdatasheet Name property to:
[None]

This thing got there in Access 2000, because MS thought it might be
useful. They did not bother documenting how slow it makes everything,
or the lack of events for these things, and they turned in on by
default, so you now have to turn it off every time you create a table.

If you want to turn it off programmatically for all tables in your
database, this should do it:

Function TurnOffSubDataSh()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
Const conPropName = "SubdatasheetName"
Const conPropValue = "[None]"

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.CreateProperty(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
Next

Set prp = Nothing
Set tdf = Nothing
Set db = Nothing
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

good day all,

I have a tblinventory that I have been working with, and I just went
in to
the tbl to double check some data and noticed a box with a "+". when
I click
on the box it drops down to information about my invoices, I don't
know who
the "+" got there or how to get rid of it? Can anyone help?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top