A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

table relationships and subdatasheets



 
 
Thread Tools Display Modes
  #1  
Old January 29th, 2010, 06:40 PM posted to microsoft.public.access.gettingstarted
Billy11
external usenet poster
 
Posts: 3
Default table relationships and subdatasheets

I'm very new with Access, so I appreciate your patience and help. I created a
database with 4 tables in it and later found that I didn't need one, so I
deleted it. Let's call that Table B. I'm left with Tables A, C, & D. (I had
established a relationship between Table A & B before I deleted it.) Now,
when I attempt to create a subdatasheet in Table A, I get a dialogue box that
says "The table or query name 'Table B' you entered in either the property
sheet or macro is misspelled or refers to a table or query that doesn't
exist." Well, I didn't create a query or a macro. How do I correct this so I
can proceed with the subdatasheets.
--
Billy
  #2  
Old January 29th, 2010, 07:58 PM posted to microsoft.public.access.gettingstarted
ruralguy via AccessMonster.com
external usenet poster
 
Posts: 1,172
Default table relationships and subdatasheets

SubData sheets impact the performance of the system. I turn them off.
http://allenbrowne.com/bug-09.html

Billy11 wrote:
I'm very new with Access, so I appreciate your patience and help. I created a
database with 4 tables in it and later found that I didn't need one, so I
deleted it. Let's call that Table B. I'm left with Tables A, C, & D. (I had
established a relationship between Table A & B before I deleted it.) Now,
when I attempt to create a subdatasheet in Table A, I get a dialogue box that
says "The table or query name 'Table B' you entered in either the property
sheet or macro is misspelled or refers to a table or query that doesn't
exist." Well, I didn't create a query or a macro. How do I correct this so I
can proceed with the subdatasheets.


--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201001/1

  #3  
Old January 29th, 2010, 09:54 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default table relationships and subdatasheets

On Fri, 29 Jan 2010 09:40:07 -0800, Billy11
wrote:

I'm very new with Access, so I appreciate your patience and help. I created a
database with 4 tables in it and later found that I didn't need one, so I
deleted it. Let's call that Table B. I'm left with Tables A, C, & D. (I had
established a relationship between Table A & B before I deleted it.) Now,
when I attempt to create a subdatasheet in Table A, I get a dialogue box that
says "The table or query name 'Table B' you entered in either the property
sheet or macro is misspelled or refers to a table or query that doesn't
exist." Well, I didn't create a query or a macro. How do I correct this so I
can proceed with the subdatasheets.


Open TableA in design view and view its Properties. I suspect that the table's
Subdatasheet property references TableB. Set this property to [None] and you
should be back in business.

You should also view the Database's Options and turn *off* Track Name
Autocorrect - this feature has richly earned the nickname "Name Autocorrupt".
--

John W. Vinson [MVP]
  #4  
Old January 31st, 2010, 02:55 AM posted to microsoft.public.access.gettingstarted
David W. Fenton
external usenet poster
 
Posts: 3,373
Default table relationships and subdatasheets

John W. Vinson wrote in
:

You should also view the Database's Options and turn *off* Track
Name Autocorrect - this feature has richly earned the nickname
"Name Autocorrupt".


Not meaning to dispute the recommendation that it should probably be
OFF in most cases, I'm finding it useful in a project I'm working on
right now, where all the table fields are prefixed with the prefixes
I only use for variable names. Every text field is named
txtSomething. This is highly annoying to me, as you it's impossible
to navigate a field list by typing the first letter of the field
you're looking for.

In any event, when I'm done doing the renaming and checking all the
dependencies, I'll turn Name AutoCorrect OFF. I think the problems
with it likely come from it being turned on for production use
(where it's just not needed).

It's certainly making my life easier for this preliminary work on
this new project.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #5  
Old January 31st, 2010, 07:43 AM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default table relationships and subdatasheets

On 31 Jan 2010 01:55:52 GMT, "David W. Fenton"
wrote:

Not meaning to dispute the recommendation that it should probably be
OFF in most cases, I'm finding it useful in a project I'm working on
right now, where all the table fields are prefixed with the prefixes
I only use for variable names. Every text field is named
txtSomething. This is highly annoying to me, as you it's impossible
to navigate a field list by typing the first letter of the field
you're looking for.

In any event, when I'm done doing the renaming and checking all the
dependencies, I'll turn Name AutoCorrect OFF. I think the problems
with it likely come from it being turned on for production use
(where it's just not needed).


Good point David - it's been a couple of years but I've done the same: turn it
on, make specific changes, and turn it back off. Thanks!
--

John W. Vinson [MVP]
  #6  
Old January 31st, 2010, 03:00 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default table relationships and subdatasheets

I prefer using the shareware product Find and Replace product for this. It is
inexpensive product and is pretty thorough. It can fix the name in the table,
in all queries, in references on forms and reports, in macros, and in my VBA
code. Plus it allows me to make the decision on a case by case basis or
accept change all if I wish.

Find and Replace: http://www.rickworld.com
Has versions to support up to Access 2007 (Beta version only for 2007) -
Shareware (Approx $40-$60 depending on version)

I have no association with the product other than I use it and have paid for
two copies (one personal and one at the office)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John W. Vinson wrote:
On 31 Jan 2010 01:55:52 GMT, "David W. Fenton"
wrote:

Not meaning to dispute the recommendation that it should probably be
OFF in most cases, I'm finding it useful in a project I'm working on
right now, where all the table fields are prefixed with the prefixes
I only use for variable names. Every text field is named
txtSomething. This is highly annoying to me, as you it's impossible
to navigate a field list by typing the first letter of the field
you're looking for.

In any event, when I'm done doing the renaming and checking all the
dependencies, I'll turn Name AutoCorrect OFF. I think the problems
with it likely come from it being turned on for production use
(where it's just not needed).


Good point David - it's been a couple of years but I've done the same: turn it
on, make specific changes, and turn it back off. Thanks!

  #7  
Old January 31st, 2010, 10:35 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default table relationships and subdatasheets

On Sun, 31 Jan 2010 09:00:51 -0500, John Spencer wrote:

I prefer using the shareware product Find and Replace product for this. It is
inexpensive product and is pretty thorough.


I've used Speed Ferret (http://www.moshannon.com) for the same purpose. I
believe it's still version limited: it works with .mdb format files and you
must have Access 2000 or 2002 installed to use it.
--

John W. Vinson [MVP]
  #8  
Old February 1st, 2010, 09:40 PM posted to microsoft.public.access.gettingstarted
David W. Fenton
external usenet poster
 
Posts: 3,373
Default table relationships and subdatasheets

John Spencer wrote in
:

I prefer using the shareware product Find and Replace product for
this. It is inexpensive product and is pretty thorough.


But it can't do the same things. For instance, all the integer
fields (long or short) are prefixed with "int". If I use Find and
Replace, I end up with problems in MakeTable queries (this app has
them -- I didn't create them) because of SELECT...INTO.

Speed Ferret could have done what I need, but I only have the A97
version (and because they haven't updated it since A2002 and it
requires A2002 to be able to use it in A2003, I won't upgrade).

It can fix the name in the table,
in all queries, in references on forms and reports, in macros, and
in my VBA code. Plus it allows me to make the decision on a case
by case basis or accept change all if I wish.


None of the search and replace tools I have are smart enough to do
this without causing damage. Sure, I have to change each field name
manually, but I can count on being able to trace the dependencies
and check that everything's been renamed properly. The only
exception is code, but this particular app has very little code in
it, so it's not a big deal.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #9  
Old February 3rd, 2010, 03:42 AM posted to microsoft.public.access.gettingstarted
Billy11
external usenet poster
 
Posts: 3
Default table relationships and subdatasheets

Thanks, John. I checked the Properties. There are no Subdatasheet properties
listed. There wouldn't be, would there, since I've never created any
subdatasheets? That's where I'm stuck right now.
--
Billy


"John W. Vinson" wrote:

On Fri, 29 Jan 2010 09:40:07 -0800, Billy11
wrote:

I'm very new with Access, so I appreciate your patience and help. I created a
database with 4 tables in it and later found that I didn't need one, so I
deleted it. Let's call that Table B. I'm left with Tables A, C, & D. (I had
established a relationship between Table A & B before I deleted it.) Now,
when I attempt to create a subdatasheet in Table A, I get a dialogue box that
says "The table or query name 'Table B' you entered in either the property
sheet or macro is misspelled or refers to a table or query that doesn't
exist." Well, I didn't create a query or a macro. How do I correct this so I
can proceed with the subdatasheets.


Open TableA in design view and view its Properties. I suspect that the table's
Subdatasheet property references TableB. Set this property to [None] and you
should be back in business.

You should also view the Database's Options and turn *off* Track Name
Autocorrect - this feature has richly earned the nickname "Name Autocorrupt".
--

John W. Vinson [MVP]
.

  #10  
Old February 3rd, 2010, 05:56 AM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default table relationships and subdatasheets

On Tue, 2 Feb 2010 18:42:01 -0800, Billy11
wrote:

Thanks, John. I checked the Properties. There are no Subdatasheet properties
listed. There wouldn't be, would there, since I've never created any
subdatasheets? That's where I'm stuck right now.


Access will give you subdatasheets even if you DON'T want them (e.g. if you
define a relationship or a lookup field). Every table has a Subdatasheet
property (open the table in design view and select view... properties); what
you want is an explicit

[None]

in the Subdatasheet property of the table. Is that what's there?
--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:10 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.