Problem of copying records
Rex
if I enter a value in one of the fileds of this table I want it to be
copied in all the records belonging to this particluar ID. for example
ID Name
--------------------------
1 xyz
1
1
1
2
2
2
When I enter "xyz" for any one of the record of ID# 1.. I want all the
other records (with ID# 1) to have the name field as "xyz" so after
this.. the table would look like this
ID Name
--------------------------
1 xyz
1 xyz
1 xyz
1 xyz
2
2
2
I know this is not possible in table itself.. However I presume it
would be possible to do this in a form. But I dont know HOW
cheers
Rex
pietl...@hotmail.com
const cQuote="""" 'Thats two quotes
me!Control.DefaultValue = cQuote & me!Control.Value & cQuote
'******** Code End **********
salad
You could have a button on a form to call and update query. Let's say
your table name is "Table1". You want to update "NameFld" to the value
of "One" for id = 1.
Dim strSQL As String
Dim strText As String
Dim lngID As Long
Dim dbs As Database
strTest = "One" 'value to update
lngID = 1 'key to look for
'create dynamic sql statement
strSQL = "UPDATE Table1 SET Table1.NameFld = '" & _
strTest & & "' " & _
"WHERE Table1.ID = " & lngID
set dbs = Currentdb
With dbs
.Execute strSQL
msgbox "Updated " & .RecordsAffected & " records"
End WIth
set dbs = Nothing
Granny Spitz via AccessMonster.com
> I know this is not possible in table itself.. However I presume it
> would be possible to do this in a form. But I dont know HOW
You *don't* want to do this in a form. Why not? Look at your schema.
Whether you have 1 or 4 or 50 records with an ID of 1, the Name is *always*
going to be xyz, or whatever you edit it to be in the future. (Name is not a
good name for a column by the way, as it's a reserved keyword.)
That's redundant data. You have a 1:1 relationship between ID and Name,
meaning it can go into a separate lookup table (the parent for this child
table) and either value can be placed in the current table as the foreign key.
That is, if you like to use surrogate keys. Otherwise, you can drop the
numeric ID column and keep the Name column natural key in the current table
and forget about creating a separate parent table, unless there are
additional attributes that need to be moved from the current table to the
parent table to normalize it.
If OTH you are in the middle of normalizing an imported spreadsheet and the
form is just one of the steps in transforming the data, then you could
instead use a single update query to set the corresponding values in all
records with an equijoin on the current table like this:
UPDATE tblCompanies AS C1 INNER JOIN
tblCompanies AS C2 ON C1.ID = C2.ID
SET C1.CoName = C2.CoName
WHERE (ISNULL(C2.CoName) = FALSE);
And you could then extract the records to create the lookup/parent table with
a make table query like this:
SELECT DISTINCT ID, CoName INTO tblCoNames
FROM tblCompanies;
But my recommendation is to transform an imported spreadsheet with this
structure by cutting out the intermediate step and just create the
lookup/parent table with a make table query like this (without first
assigning values to the empty columns):
SELECT DISTINCT ID, CoName INTO tblCoNames
FROM tblCompanies
WHERE (ISNULL(CoName) = FALSE);
If you like to use surrogate keys, that is. Skip the ID column if you prefer
natural keys.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200610/1
salad
> Rex wrote:
>
> You *don't* want to do this in a form. Why not? Look at your schema.
> Whether you have 1 or 4 or 50 records with an ID of 1, the Name is *always*
> going to be xyz, or whatever you edit it to be in the future. (Name is not a
> good name for a column by the way, as it's a reserved keyword.)
Which version? I looked at the reserved words for A97 and Name was not
a reserved word. In A2003 its not in the list of SQL reserved words.
Granny Spitz via AccessMonster.com
> Which version? I looked at the reserved words for A97 and Name was not
> a reserved word. In A2003 its not in the list of SQL reserved words.
It's on the list of reserved words for Access 97, 2000, 2002, and 2003.
http://support.microsoft.com/kb/109312/en-us and
http://support.microsoft.com/kb/209187/ and
http://support.microsoft.com/kb/286335/
But even if it's not a reserved word in an earlier version of Access, if you
ever plan to upgrade to a newer version, or have another application built in
a newer version link to these tables, or use ADO or DAO to connect to these
tables or queries, or use remote queries, then don't use Name as a column
name if you want to avoid bugs.
--
Message posted via http://www.accessmonster.com
salad
nor 2003. Both referred to Jet SQL reserved words.
Funny they don't include the reserved words in the help files.
Granny Spitz via AccessMonster.com
> Thanks. In A97 help, the reserved words help file doesn't contain it,
> nor 2003. Both referred to Jet SQL reserved words.
>
> Funny they don't include the reserved words in the help files.
There's two lists of reserved words, one for Access and one for Jet SQL.
Beats me why Microsoft can't combine the two into one list so that we only
have to check one list for what's off limits.
RoyVidar
<67529f3e9364b@uwe>:
> salad wrote:
>> Thanks. In A97 help, the reserved words help file doesn't contain
>> it, nor 2003. Both referred to Jet SQL reserved words.
>>
>> Funny they don't include the reserved words in the help files.
>
> There's two lists of reserved words, one for Access and one for Jet
> SQL. Beats me why Microsoft can't combine the two into one list so
> that we only have to check one list for what's off limits.
For those only using Jet, the list of reserved words in Access
wouldn't be very interesting, I guess.
--
Roy-Vidar
Bas Cost Budde
Hell breaks loose as soon as you try an expression with that field on a
report...
salad schreef:
--
Bas Cost Budde
Holland
www.heuveltop.nl/BasCB/msac_index.html
Rex
explain the problem in a simpler way. But thanks for the suggestion.. I
will definately keep this in mind and would check the list of reserve
words before I design my tables..
cheers!
RoyVidar
<eg3eac$plt$1...@localhost.localdomain>:
> Believe me salad... using "name" as a field name is not a good idea.
> Hell breaks loose as soon as you try an expression with that field on
> a report...
Not that I'd ever use it, but I'm not sure I follow. In a small test
here, if I refer to it in an expression, I'd usually refer to the
control, which with my usual naming conventions would be called
txtName, and that seems to work OK.
Of course, if one does not rename the control, and use [Name] in an
expression, Access would return the name of the report, but in my
eyes that's expected.
In this test, even using
=Count([Name])
as controlsource of a control works.
When testing code, both
Debug.Print Me.Name ' and
Debug.Print Me!Name
gives the expected result, one returning the report name, the other
the contents of the field in the recordsource/content of the control.
Are there other sircumstances where using Name as field name makes
"Hell breaks loose" in reports? When called in to fix stuff, I've
usually only used the "band aid" of [bracketing] reserved words and
disambiguating controlsource/controls by prefixing control names of
the controls referred to in code or expressions, which usually does
the trick.
--
Roy-Vidar
Bas Cost Budde
And, for me the situation is different, because I use a national
language version. When I name a field Naam, the dutch word for 'name',
Access recognizes this as the Name property on some occasions (when I
use the field in an expression AFAICR) and subsequently refuses to
understand what that means.
[] does no good in that case. In other cases it works fine. I decided to
not use any word that may be associated with a database property for
field names, maybe too rigorously, but for me workable and safe.
RoyVidar schreef:
--
salad
>
>>Thanks. In A97 help, the reserved words help file doesn't contain it,
>>nor 2003. Both referred to Jet SQL reserved words.
>>
>>Funny they don't include the reserved words in the help files.
>
>
> There's two lists of reserved words, one for Access and one for Jet SQL.
> Beats me why Microsoft can't combine the two into one list so that we only
> have to check one list for what's off limits.
>
for Jet SQL. I wonder where they hid the second set in the help files.
I guess one needs to put on a new hat and think "How would MS define
Access reserved words?" If one can get into MS-think, a solution may be
found.
Like "specifications". I guess I'd think "limitations" are what people
look for more often.
David W. Fenton
> salad wrote:
>> Thanks. In A97 help, the reserved words help file doesn't
>> contain it, nor 2003. Both referred to Jet SQL reserved words.
>>
>> Funny they don't include the reserved words in the help files.
>
> There's two lists of reserved words, one for Access and one for
> Jet SQL. Beats me why Microsoft can't combine the two into one
> list so that we only have to check one list for what's off limits.
Um, why would you do that? That would obscure the important
differences between *where* you can use them.
And Access and Jet are distinct products.
Now, in the Access help file, sure, why not have a single page with
the Access list followed by the Jet SQL list.
But combining them into one single list would be a huge mistake, as
you'd lose a lot of important information.
And some people use Jet SQL without using Access.
And others use Access without using Jet SQL.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Granny Spitz via AccessMonster.com
> I wonder where they hid the second set in the help files.
I don't think they intended to put it in there, but I do think they put it on
the web because so many people were asking about the other list of reserved
words that people run into trouble with so often.
> If one can get into MS-think, a solution may be
> found.
You must be hearing the munchkins singing, "Follow the yellow brick road!"
> Like "specifications". I guess I'd think "limitations" are what people
> look for more often.
They're using the horse of a different color. You just have to look for the
horse of the *right* color and you'll find it, right where they put it. : )
Granny Spitz via AccessMonster.com
> Um, why would you do that? That would obscure the important
> differences between *where* you can use them.
Oh, my! You mean we're supposed to remember *when* it's safe to use the ones
that are on one list but not the other?! That's expecting a lot from people
who have a hard time finding their car keys in the morning! I've got most of
the reserved words memorized so I rarely have to look any of them up, but if
I had to remember *which* list each one came from so I could use it safely
I'd feel like I'd high dived into a shallow pool. My head would hurt! And I
don't want anyone else to feel that pain, either! I'm looking out for them.
salad
failing often.
David W. Fenton
> David W. Fenton wrote:
>> Um, why would you do that? That would obscure the important
>> differences between *where* you can use them.
>
> Oh, my! You mean we're supposed to remember *when* it's safe to
> use the ones that are on one list but not the other?! That's
> expecting a lot from people who have a hard time finding their car
> keys in the morning! I've got most of the reserved words
> memorized so I rarely have to look any of them up, but if I had to
> remember *which* list each one came from so I could use it safely
> I'd feel like I'd high dived into a shallow pool. My head would
> hurt! And I don't want anyone else to feel that pain, either!
> I'm looking out for them.
You're an idiot.
<PLONK>
rkc
> You're an idiot.
>
> <PLONK>
You're just sad.
Focused.
But sad.
Lyle Fairfield
> "Granny Spitz via AccessMonster.com" <u26473@uwe> wrote in
> news:6760aec11f3b8@uwe:
> > Oh, my!
>
> You're an idiot.
>
> <PLONK>
Lyle sings the old Connie Francis tune to Granny:
Who's sorry now?
Who's sorry now?
Whose heart is aching for breaking each vow?
Who's sad and blue?
Who's crying too?
Just like I cried over you?
Right to the end
Just like a friend
I tried to warn you somehow
You had your way,
Now you must pay
I'm glad that you're sorry now.
Just to try to make her feel better.
Granny Spitz via AccessMonster.com
> Lyle sings the old Connie Francis tune to Granny:
> Just to try to make her feel better.
That's so sweet of you, Lyle! I'm unhappy about being plonked, but you've
made me feel better. Thank you so much!
Granny Spitz via AccessMonster.com
>> Oh, my! You mean we're supposed to remember *when* it's safe to
>> use the ones that are on one list but not the other?!
> You're an idiot.
>
> <PLONK>
I'm so glad David sugar coated that remark. I'd hate to hear what he
*really* thinks of me.
(Note to self: Don't quit day job in favor of that gig on the comedy circuit.
)
Lyle Fairfield
> >> Oh, my! You mean we're supposed to remember *when* it's safe to
> >> use the ones that are on one list but not the other?!
> (snip)
> > You're an idiot.
> >
> > <PLONK>
>
> I'm so glad David sugar coated that remark. I'd hate to hear what he
> *really* thinks of me.
Take the last train to Plonksville
And I'll meet you at the station.
You can be here by four thirty,
David made your reservation.
Don't be slow, oh, no, no, no!
Oh, no, no, no!
Cause I'm stuck way out in limbo,
David put me here as well.
Cruelly and without mercy
He just sent me to this hell
And I must go, oh, no, no, no!
Oh, no, no, no!
And I don't know if I'm ever coming back.
Take the last train to Plonksville.
I'll be waiting at the station.
We can talk VBA Idents
And some JET word reservations
Oh... oh, no, no, no!
Oh, no, no, no!
Take the last train to Plonksville,
Now I must hang up the phone.
I can't hear you in this noisy
Newsgroup all alone.
I'm feelin' low, oh, no, no, no!
Oh, no, no, no!
And I don't know if I'm ever coming back.
Take the last train to Plonksville,
Take the last train to Plonksville.
Granny Spitz via AccessMonster.com
LOL! Thank you, Lyle! You made my day!
Larry Linson
> LOL! Thank you, Lyle! You made my day!
You two DO know that you are taking all the sting that David intended right
out of his <PLONK>, don't you? :-) Why, you seem to be having fun with it!
Granny Spitz via AccessMonster.com
> You two DO know that you are taking all the sting that David intended right
> out of his <PLONK>, don't you?
That's one of Lyle's many talents!
> Why, you seem to be having fun with it!
Yes indeed! But David will never know what's going on. He's plonked us both.
Lyle Fairfield
> Yes indeed! But David will never know what's going on. He's plonked us both.
Of course, he won't. Suggesting that he does know or will know would
imply that he uses <PLONK!> only for effect, to demonstrate his
capacity for incisive action. I'm sure David is much too genuine for
that, and I should know. After all he's plonked me several times ...
hmmmm ... ?
Larry Linson
"Lyle Fairfield" <lylefa...@aim.com> wrote in message
news:1160274027....@i3g2000cwc.googlegroups.com...
Ah, ha! Term-limited plonks?
There's a fellow in a private newsgroup who does either term-limited plonks
or "virtual" plonks. After a while, he'll respond to something I post...
then I know he's reading my posts (again?). I know it doesn't mean that I'm
"out of the doghouse" with him, because it won't be long until he plonks me
again. See you in Plonksville, probably sooner than later.
Larry
Granny Spitz via AccessMonster.com
> I'm sure David is much too genuine for
> that
I'm sure he's not bluffing either. I really am in Plonk City, at the corner
of Walk and Don't Walk.
> After all he's plonked me several times ...
> hmmmm ... ?
You're posts are worthy of a second, third, fourth,... chance. David's not
interested in anything I have to say. I won't be getting a second chance.
Lyle Fairfield
> David's not interested in anything I have to say. I won't be getting
> a second chance.
Your posts give good advice. There's no need to be modest about them.
David seldom takes advice, so from the point of view of being helpful his
plonking you or anyone else is irrelevant.
I think we have exhausted the usefulness of this thread and should let it
fade away. I plan to do so.
--
Lyle Fairfield
Granny Spitz via AccessMonster.com
> Your posts give good advice. There's no need to be modest about them.
The advice I give is at a different level than David's on. He doesn't need
it. It's the discussions between CDMA posters that are valuable, so cutting
off communication between certain posters cuts off some of the exchange of
ideas and points of view.
> I think we have exhausted the usefulness of this thread and should let it
> fade away. I plan to do so.
Very wise.
David W. Fenton
news:7IYVg.401$Gp4.5@trnddc08:
How could they be accomplishing that if I've plonked both of them,
and won't be seeing their posts?
David W. Fenton
> "Lyle Fairfield" <lylefa...@aim.com> wrote in message
> news:1160274027....@i3g2000cwc.googlegroups.com...
>> Granny Spitz via AccessMonster.com wrote:
>>
>>> Yes indeed! But David will never know what's going on. He's
>>> plonked us both.
>>
>> Of course, he won't. Suggesting that he does know or will know
>> would imply that he uses <PLONK!> only for effect, to demonstrate
>> his capacity for incisive action. I'm sure David is much too
>> genuine for that, and I should know. After all he's plonked me
>> several times ... hmmmm ... ?
When people quote you, Lyle, I see your posts.
> Ah, ha! Term-limited plonks?
My newsreader allows me to set my kill settings for a limited number
of days (the default is 30) or permanently. Lyle and Granny are
permanent members of my killfile. I'll occasionally retrieve one of
Lyle's posts and reply to it when he says something interesting that
is quoted by someone else.
The reason for the time-limited plonk is that it allows one to cool
off for a while, and to give someone a second chance.
I generally don't use it, since I don't plonk people until they've
shown themselves to be completely impossible.
Lyle Fairfield
> "Larry Linson" <bou...@localhost.not> wrote in
> news:7IYVg.401$Gp4.5@trnddc08:
>
> > "Granny Spitz via AccessMonster.com" wrote
> >
> > > LOL! Thank you, Lyle! You made my day!
> >
> > You two DO know that you are taking all the sting that David
> > intended right out of his <PLONK>, don't you? :-) Why, you seem
> > to be having fun with it!
>
> How could they be accomplishing that if I've plonked both of them,
> and won't be seeing their posts?
It's really too bad if you don't get the chance to show how vulnerable
DiscountAsp.Net SQL Hosting is. I know you won't see this but maybe
some one will alert you to the opportunity.
Larry Linson
> How could they be accomplishing that
> if I've plonked both of them, and won't
> be seeing their posts?
But, now that you know they are having fun with it, actually enjoying it.
IMNSHO, that, in itself, diminishes the value of a PLONKing. Even though a
PLONK is primarily for protecting the plonker from seeing posts; it's also a
"statement" to the plonkee.
Maybe you should take a look at Lyle's responses to the same post I
referenced here, as he's talking about your opportunity to demonstrate the
vulnerability of that website. I don't "have a dog in that fight," but,
well, I thought this was a rather interesting thread for a while.
Larry
Arno R
"David W. Fenton" <XXXu...@dfenton.com.invalid> schreef in bericht news:Xns98569A3ABBFD0f9...@127.0.0.1...
>
> I generally don't use it, since I don't plonk people until they've
> shown themselves to be completely impossible.
Sometimes you are just plain wrong in your judgement.
But I guess you plonk yourself also once in a while ??
Arno R
Granny Spitz via AccessMonster.com
>> > LOL! Thank you, Lyle! You made my day!
>>
>> You two DO know that you are taking all the sting that David
>> intended right out of his <PLONK>, don't you? :-) Why, you seem
>> to be having fun with it!
>
> How could they be accomplishing that if I've plonked both of them,
> and won't be seeing their posts?
Does that mean Lyle can't laugh behind David's back, because David won't
*see* him laughing? Does that rule apply to everybody, even to Canadians?
Larry Linson
> David W. Fenton wrote:
"If a tree laughs in the woods, and nobody hears it, did it make any sound?"
I can't think of another answer to this question that isn't likely to get me
added to somebody's "bad list." :-)
Larry