I am really starting to hate access, its inconsistencies and its b

[Guest]

Ok I am really frustrated but here goes.

I have a Macro which I have managed to read a select list of records based
on a query. Simple: Read a record, change the values of 2 fields (boolean);
one to YES and one to NO. No biggie! The problem is that the macro runs
thru all the records (IT DOES cause I "STEPPED" through it with the MACRO
SINGLE STEP function,) but ONLY the 1st record is recorded with the changes!
WHAT THE FUDGE IS THE PROBLEM WITH ACCESS?

Some points of info.
1) I am obliged to change the value of the field (SETVALUE) through the
variable on the form bound to the macro because when I do SETVALUE ITEM to
the query variable it gives me an error! WHY? I DUNNO! (ERROR -> THE OBJECT
DOESN'T CONTAIN THE AUTOMATION OBJECT...<query name>)

2) I tried adding in a REQUERY and it still only updates the 1st record.

3) I removed the REQUERY and put in 3 statements
a)CLOSE QUERY
b)OPEN QUERY
c)GOTO RECORD FIRST
Only 1st record gets updated.

4) Tried putting in a GOTO NEXT RECORD after the SETVALUE statements in case
it required going to a next record for it force an update. ONLY 1ST RECORD
GETS UPDATED.

ANYONE NOTICING A FREAKIN PATTERN????

WHy is ACCESS SUCH A PAIN IN THE BUTT???

Anyone know why ONLY the 1st record gets updated and how to fix this
problem???

 

[Rick Brandt]

Ok I am really frustrated but here goes.

I have a Macro which I have managed to read a select list of records
based on a query. Simple: Read a record, change the values of 2
fields (boolean); one to YES and one to NO. No biggie! The problem
is that the macro runs thru all the records (IT DOES cause I
"STEPPED" through it with the MACRO SINGLE STEP function,) but ONLY
the 1st record is recorded with the changes! WHAT THE FUDGE IS THE
PROBLEM WITH ACCESS?

Some points of info.
1) I am obliged to change the value of the field (SETVALUE) through
the variable on the form bound to the macro because when I do
SETVALUE ITEM to the query variable it gives me an error! WHY? I
DUNNO! (ERROR -> THE OBJECT DOESN'T CONTAIN THE AUTOMATION
OBJECT...<query name>)

2) I tried adding in a REQUERY and it still only updates the 1st
record.

3) I removed the REQUERY and put in 3 statements
a)CLOSE QUERY
b)OPEN QUERY
c)GOTO RECORD FIRST
Only 1st record gets updated.

4) Tried putting in a GOTO NEXT RECORD after the SETVALUE statements
in case it required going to a next record for it force an update.
ONLY 1ST RECORD GETS UPDATED.

ANYONE NOTICING A FREAKIN PATTERN????

WHy is ACCESS SUCH A PAIN IN THE BUTT???

Anyone know why ONLY the 1st record gets updated and how to fix this
problem???

There is no problem with Access. Only that you don't know how to use it.

Stop trying to update records with a macro first off. You need an appropriate
update query that will update all records at once not something that traverses
the records one at a time. When you open your query you might be SELECTING all
records in the query, but you will still be positioned only to the first row.
Opening any datasheet in the GUI clearly demonstrates this.

UPDATE TableName
SET FieldName1 = Forms!NameOfForm!NameOfControl1,
FieldName2 = Forms!NameOfForm!NameOfControl2

 

[Guest]

Sorry for my frustration but I guess, as a programmer, I am used to the
traditional form of updating records by looping thru the data and changing
them 1 record at a time.

Ok So if I have a regular a select query with 6 out of about 20 fields of a
master table, how do I go about updating the fields? 2 boolean fields for
which one field must be changed from YES to NO and the other one, the reverse.

Example of query

Contract, To PRINT, Printed

I want to set "TO PRINT" to NO and "PRINTED" to yes on all the records that
the query produces. Keep in mind that the query selects records based on
specific criteria. In this case it looks for all records that have a YES in
"PRINTED". THOSE are the ones I want to update. Example: 3 out of 415
recorsds,

 

[Guest]

Ok well good news!
I looked up how to setup an update macro in Access help. DAMN don't I feel
like a moron!!! Took about 5 minutes to make and even faster to run!

Thanks for pointing me in the right direction Rick!

Suddenly my Maco is about 1/4 the size it used to be: Simpler, faster and
more effective now.

Thanks again Rick!

Crazy AL

 

[Duane Hookom]

Crazy Al,
"as a programmer", you should learn to write some VBA. Update queries are
much more efficient and easier than creating and stepping through
recordsets. However, you may have situations where ADO or DAO recordsets are
necessary. I'm sure once you start using VBA rather than macros, you will be
hooked.

 

[Granny Spitz via AccessMonster.com]

ANYONE NOTICING A FREAKIN PATTERN????

WHy is ACCESS SUCH A PAIN IN THE BUTT???

Yes, it's Wild Bill Hickock's "How To Be A Top Gunfighter" pattern. People
would come to see his Wild West Show and watch him do tricks, like shoot a
bullet through a silver dollar thrown into the air. He'd get letters from
widows of men who bought six-shooters but bled to death from six bullet holes
in their legs and feet when they threw their own silver dollars up into the
air. The widows complained the six-shooters and the silver dollars were
defective, because we all know that the act of purchasing a tool will
instantly give us expert skills to use that tool just like Wild Bill, without
any training or practice. Wild Bill wrote the widows with this tip: "If you
leave the pistol in the holster, make sure you place the silver dollar on top
of the toes of your boot instead of throwing it in the air. And keep your
eyes wide open while you take aim." Wild Bill was always curious why the
widows never wrote him again for more gunfighter tips.

If you feel pain in your butt, Wild Bill would advise you to take the silver
dollar out of your back pocket and place it on the toes of your boot.

 

[Granny Spitz via AccessMonster.com]

as a programmer, I am used to the
traditional form of updating records by looping thru the data and changing
them 1 record at a time.

Hon, for future reference non-programmers have to use macros because they
don't know how to write code or how to use the wizards to write code for them.
New programmers don't use macros, but they loop through (cursor through)
recordsets to update one record at a time, even if it's millions of records.
Smart and experienced programmers don't waste their or the user's time. They
just write an update query to update the recordset as a group. The Autokeys
macro, which would take longer to duplicate with VBA code, is the only
exception.

I want to set "TO PRINT" to NO and "PRINTED" to yes on all the records that
the query produces. Keep in mind that the query selects records based on
specific criteria. In this case it looks for all records that have a YES in
"PRINTED".

UPDATE TableA
SET TO_PRINT = False
WHERE PRINTED = True;

Smart and experienced programmers only use alphanumerics and the underscore
for column names. They use the column's caption property to show spaces and
other illegal characters to make it easier for users (non-programmers) to
read.

 

[David W. Fenton]

Sorry for my frustration but I guess, as a programmer, I am used
to the traditional form of updating records by looping thru the
data and changing them 1 record at a time.

That "traditional form of updating records by looping" went out in
about 1990. SQL is the way to update data, and is *always* faster
than looping through a recordset.

 

[iliace]

How about....

with frmMyForm
DoCmd.RunSQL("UPDATE " & strMyTableName & " SET " & _
strFieldname1 & "=" & _
.txtTextField1.Value & ", " & _
strFieldname2 & "=" & _
.txtTextField2.Value)
end with

You could have a nice user-system table with your field name and
constant name values, and loop through them programmatically - but not
to update each record, but rather to generate your query on the fly.


"""Duane Hookom ÐÉÓÁÌ(Á):
"""