473,324 Members | 2,511 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

problem with updating database

in access i have two tables (tblplayer, tblpoints)
in tblplayer i have field totalpoint (number)
in tlbpoints i have field pointgame (number)
the two files are related via idplayer
now i made a query "qrybegan" and in that query i calculate
a field sumpointgame which is the sum of all the field pointgame (option sum
in the query)
i have put in the query also the field totalpoint from the tblplayer

now i want to put the sumpointgame in the field totalpoint of the tblplayer
(i need this for a certain reason)

i made this

dim rsartikelskl as recordset
set huidigedB= currentdB
set rsartikelskl=huidigedB.openrecordset("qrybegan, dbopendynaset)
rsartikelskl.Movefirst
Do until rsartikelskl.EOF()
rsartikelskl.edit
rsartikelskl!totalpoint=rsartikelskl!sumpointgame
update
rsartikelskl.MoveNext
loop
rsartikelskl.Close

Now i thaught that the calculated field sumpointgame was put in the
totalpoint field of the tblplayer
it does nothing................. the field sumpointgame stays at 0

is there a specialist who can tell me why this dont work???
is there someone who has a routine which has the same result (maybe in
sql??)

ths for reply

greetings

--

Nov 12 '05 #1
7 2270

Looking at your code, there are a couple of errors: it won't run as it
is. It should be:

dim rsartikelskl as recordset

set huidigedB= currentdB

set rsartikelskl=huidigedB.openrecordset("qrybegan", dbopendynaset)

rsartikelskl.Movefirst

Do until rsartikelskl.EOF()

rsartikelskl.edit

rsartikelskl!totalpoint=rsartikelskl!sumpointgame

rsartikelskl.update

rsartikelskl.MoveNext

loop

rsartikelskl.Close

You should also add these lines to release the object variables and
reclaim memory space:

Set rsartikelskl = Nothing

Set huidigedB = Nothing

This may not be the cause of your problem, but it's a start. Also, are
you sure that your query is an updateable recordset? Try entering data
manually into your query, because, if you can't, your code won't be able
to either.

The SQL to do the same thing would be "UPDATE QryBegan SET TotalPoint =
SumPointGame".

Hope this helps.
--
Andy Briggs
Elmhurst Solutions Limited
http://www.elmhurstsolutions.com
Posted via http://dbforums.com
Nov 12 '05 #2

"andybriggs" <me*********@dbforums.com> schreef in bericht
news:35****************@dbforums.com...

Looking at your code, there are a couple of errors: it won't run as it
is. It should be:

dim rsartikelskl as recordset

set huidigedB= currentdB

set rsartikelskl=huidigedB.openrecordset("qrybegan", dbopendynaset)

rsartikelskl.Movefirst

Do until rsartikelskl.EOF()

rsartikelskl.edit

rsartikelskl!totalpoint=rsartikelskl!sumpointgame

rsartikelskl.update

rsartikelskl.MoveNext

loop

rsartikelskl.Close

You should also add these lines to release the object variables and
reclaim memory space:

Set rsartikelskl = Nothing

Set huidigedB = Nothing

This may not be the cause of your problem, but it's a start. Also, are
you sure that your query is an updateable recordset? Try entering data
manually into your query, because, if you can't, your code won't be able
to either.

The SQL to do the same thing would be "UPDATE QryBegan SET TotalPoint =
SumPointGame".

Hope this helps.
--
Andy Briggs
Elmhurst Solutions Limited
http://www.elmhurstsolutions.com
Posted via http://dbforums.com

thx for reply
as a matter of fact results of the the query are not updatable.....again
something i've leard

but now....other solutions
the question is now......
a have a tabel and i have to make a sum of a field
the result of that field must be put in a field of another table

who can give me some good advice????

thx for reply
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 1/09/2003
Nov 12 '05 #3
Sounds like good advice would be a session with a good book on relational
database design. Storing something that can be calculated when it is needed,
if that is what you are doing, is redundant, and often leads to problems
later.

But, to perform a sum on a particular field in a table in code, with or
without applying selection criteria, you can use the DSum domain aggregate
function. To do it in a query, create a SELECT query in the Query Builder
design view, then click View | Totals and choose the appropriate items for
each field, such as "Sum", "Average", "WHERE", and "GROUP BY".

Larry Linson
Microsoft Access MVP

"Foxster" <er***********@skynet.be> wrote in message
news:3f*********************@reader1.news.skynet.b e...

"andybriggs" <me*********@dbforums.com> schreef in bericht
news:35****************@dbforums.com...

Looking at your code, there are a couple of errors: it won't run as it
is. It should be:

dim rsartikelskl as recordset

set huidigedB= currentdB

set rsartikelskl=huidigedB.openrecordset("qrybegan", dbopendynaset)

rsartikelskl.Movefirst

Do until rsartikelskl.EOF()

rsartikelskl.edit

rsartikelskl!totalpoint=rsartikelskl!sumpointgame

rsartikelskl.update

rsartikelskl.MoveNext

loop

rsartikelskl.Close

You should also add these lines to release the object variables and
reclaim memory space:

Set rsartikelskl = Nothing

Set huidigedB = Nothing

This may not be the cause of your problem, but it's a start. Also, are
you sure that your query is an updateable recordset? Try entering data
manually into your query, because, if you can't, your code won't be able
to either.

The SQL to do the same thing would be "UPDATE QryBegan SET TotalPoint =
SumPointGame".

Hope this helps.
--
Andy Briggs
Elmhurst Solutions Limited
http://www.elmhurstsolutions.com
Posted via http://dbforums.com

thx for reply
as a matter of fact results of the the query are not updatable.....again
something i've leard

but now....other solutions
the question is now......
a have a tabel and i have to make a sum of a field
the result of that field must be put in a field of another table

who can give me some good advice????

thx for reply
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 1/09/2003

Nov 12 '05 #4

"Larry Linson" <bo*****@localhost.not> schreef in bericht
news:hz*******************@nwrddc01.gnilink.net...
Sounds like good advice would be a session with a good book on relational
database design. Storing something that can be calculated when it is needed, if that is what you are doing, is redundant, and often leads to problems
later.

But, to perform a sum on a particular field in a table in code, with or
without applying selection criteria, you can use the DSum domain aggregate
function. To do it in a query, create a SELECT query in the Query Builder
design view, then click View | Totals and choose the appropriate items for
each field, such as "Sum", "Average", "WHERE", and "GROUP BY".

Larry Linson
Microsoft Access MVP

"Foxster" <er***********@skynet.be> wrote in message
news:3f*********************@reader1.news.skynet.b e...

"andybriggs" <me*********@dbforums.com> schreef in bericht
news:35****************@dbforums.com...

Looking at your code, there are a couple of errors: it won't run as it
is. It should be:

dim rsartikelskl as recordset

set huidigedB= currentdB

set rsartikelskl=huidigedB.openrecordset("qrybegan", dbopendynaset)

rsartikelskl.Movefirst

Do until rsartikelskl.EOF()

rsartikelskl.edit

rsartikelskl!totalpoint=rsartikelskl!sumpointgame

rsartikelskl.update

rsartikelskl.MoveNext

loop

rsartikelskl.Close

You should also add these lines to release the object variables and
reclaim memory space:

Set rsartikelskl = Nothing

Set huidigedB = Nothing

This may not be the cause of your problem, but it's a start. Also, are
you sure that your query is an updateable recordset? Try entering data
manually into your query, because, if you can't, your code won't be able to either.

The SQL to do the same thing would be "UPDATE QryBegan SET TotalPoint = SumPointGame".

Hope this helps.
--
Andy Briggs
Elmhurst Solutions Limited
http://www.elmhurstsolutions.com
Posted via http://dbforums.com

thx for reply
as a matter of fact results of the the query are not updatable.....again
something i've leard

but now....other solutions
the question is now......
a have a tabel and i have to make a sum of a field
the result of that field must be put in a field of another table

who can give me some good advice????

thx for reply
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 1/09/2003

thx for reply
i agree what you are saying about redundant matter but i have to make a
report with the first table and sort them first according to fields in that
table and then those who are not sorted by the fields in that table must be
sorted by the total of points in that other table.......
so i need the totals of those points for a certain amount of players in that
table....

so i try to add some fields in that first table with the results i need and
then i can make a report and sorting them easily like i want them to be
sorted.....

i hope you understand this.....

and i agree, i am a self study user of ms access but

now my question: how can you create a report with condition of sorting not
with fields of other tables but with results of other tablefields....

thx for reply
greetings
Nov 12 '05 #5
"Foxster" wrote
i agree what you are saying about
redundant matter but i have to make
a report with the first table and sort
them first according to fields in that
table and then those who are not
sorted by the fields in that table must
be sorted by the total of points in that
other table.......
so i need the totals of those points for
a certain amount of players in that
table....
i hope you understand this.....

and i agree, i am a self study user of ms access but

now my question: how can you create a report with condition of sorting not
with fields of other tables but with results of other tablefields....


The order of the Records in the Record Source of a Report is ignored and
Report sorting is controlled by the Report's Sorting and Grouping
properties. That means that every single Record must have some fields which
are common, and used for sorting.

I think perhaps you might consider whether you could create separate
Reports, each with the appropriate ordering, and put each of them in a
Subreport Control on the actual Report. If you are careful, it will not be
obvious that the Report is actually made up of two Reports. Use a Query as
the RecordSource to select just the Records that will be sorted in a
particular way for each Report.

There are, I am convinced, occasions when creating temporary tables is
unavoidable. But, I've been using Access just about every day since January
1993, and I have never been forced to do so in the business database
applications I normally create.

I have created temporary tables, but only to demonstrate how you can do so
in a temporary database and avoid the database bloat and need for frequent
compacting that inevitably results when you create temporary tables within
your own database. Now, MVP Tony Toews has code illustrating this approach
on his website, http://www.granite.ab.ca/accsmstr.htm.

Larry Linson
Microsoft Access MVP
Nov 12 '05 #6

"Larry Linson" <bo*****@localhost.not> schreef in bericht
news:NL*******************@nwrddc01.gnilink.net...
"Foxster" wrote
> i agree what you are saying about
> redundant matter but i have to make
> a report with the first table and sort
> them first according to fields in that
> table and then those who are not
> sorted by the fields in that table must
> be sorted by the total of points in that
> other table.......
> so i need the totals of those points for
> a certain amount of players in that
> table....
i hope you understand this.....

and i agree, i am a self study user of ms access but

now my question: how can you create a report with condition of sorting not with fields of other tables but with results of other tablefields....


The order of the Records in the Record Source of a Report is ignored and
Report sorting is controlled by the Report's Sorting and Grouping
properties. That means that every single Record must have some fields

which are common, and used for sorting.

I think perhaps you might consider whether you could create separate
Reports, each with the appropriate ordering, and put each of them in a
Subreport Control on the actual Report. If you are careful, it will not be
obvious that the Report is actually made up of two Reports. Use a Query as
the RecordSource to select just the Records that will be sorted in a
particular way for each Report.

There are, I am convinced, occasions when creating temporary tables is
unavoidable. But, I've been using Access just about every day since January 1993, and I have never been forced to do so in the business database
applications I normally create.

I have created temporary tables, but only to demonstrate how you can do so
in a temporary database and avoid the database bloat and need for frequent
compacting that inevitably results when you create temporary tables within
your own database. Now, MVP Tony Toews has code illustrating this approach
on his website, http://www.granite.ab.ca/accsmstr.htm.

Larry Linson
Microsoft Access MVP

hi there
you were right
today i resaw the report i have created and i have create a new query within
the fields i need and created calculated fields wtih the results from the
other database

with that query and the report related with that query i have managed to
sort in a way i want

so no putting results in other fields as you told

thx for the splendid help you gave
greetings
Nov 12 '05 #7
I'm glad it worked for you and glad I could be of help.

Larry

"Foxster" <er***********@skynet.be> wrote in message
news:3f**********************@reader2.news.skynet. be...

"Larry Linson" <bo*****@localhost.not> schreef in bericht
news:NL*******************@nwrddc01.gnilink.net...
"Foxster" wrote
> i agree what you are saying about
> redundant matter but i have to make
> a report with the first table and sort
> them first according to fields in that
> table and then those who are not
> sorted by the fields in that table must
> be sorted by the total of points in that
> other table.......
> so i need the totals of those points for
> a certain amount of players in that
> table....
i hope you understand this.....

and i agree, i am a self study user of ms access but

now my question: how can you create a report with condition of sorting not with fields of other tables but with results of other tablefields....


The order of the Records in the Record Source of a Report is ignored and
Report sorting is controlled by the Report's Sorting and Grouping
properties. That means that every single Record must have some fields

which
are common, and used for sorting.

I think perhaps you might consider whether you could create separate
Reports, each with the appropriate ordering, and put each of them in a
Subreport Control on the actual Report. If you are careful, it will not be obvious that the Report is actually made up of two Reports. Use a Query as the RecordSource to select just the Records that will be sorted in a
particular way for each Report.

There are, I am convinced, occasions when creating temporary tables is
unavoidable. But, I've been using Access just about every day since

January
1993, and I have never been forced to do so in the business database
applications I normally create.

I have created temporary tables, but only to demonstrate how you can do so in a temporary database and avoid the database bloat and need for frequent compacting that inevitably results when you create temporary tables within your own database. Now, MVP Tony Toews has code illustrating this approach on his website, http://www.granite.ab.ca/accsmstr.htm.

Larry Linson
Microsoft Access MVP

hi there
you were right
today i resaw the report i have created and i have create a new query

within the fields i need and created calculated fields wtih the results from the
other database

with that query and the report related with that query i have managed to
sort in a way i want

so no putting results in other fields as you told

thx for the splendid help you gave
greetings

Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Frnak McKenney | last post by:
I'm using an in-core DataSet as an image of my application's 'database' (a multi-table Access97 mdb file). Updates are made to the DataTables within the DataSet via forms with bound TextBoxes,...
1
by: Steven Blair | last post by:
Hi, Here is a short decsription of my problem. I have written a dll for Database accessing. I have one method which can return a Dataset and another method which takes a Dataset and upates a...
1
by: delta7 | last post by:
Hi, I'm new to C sharp and currently writing a small program that uses an Access 2003 database. I am currently having a problem when updating a row that includes numeric data. When updating...
5
by: junglist | last post by:
Hi guys, I've been trying to implement an editable datagrid and i have been succesful up to the point where i can update my datagrid row by row. However what used to happen was that once i updated...
14
by: Lars Netzel | last post by:
A little background: I use three Datagrids that are in a child parent relation. I Use Negative Autoincrement on the the DataTables and that's workning nice. My problem is when I Update these...
2
by: Mike Collins | last post by:
I cannot get the correct drop down list value from a drop down I have on my web form. I get the initial value that was loaded in the list. It was asked by someone else what the autopostback was...
31
by: Lag | last post by:
Having a problem updating my database from a web page, through a submission form. Can anyone help? ----THIS IS MY CODE IN update.php----(user, pass, and database are typed in directly, I...
1
by: r2destini | last post by:
Hi Friends, I am new to .Net. So I don't know much. I am facing a problem in updating database through ADO.Net I am creating the dataset and there is no problem in the updation and...
5
by: Brad Baker | last post by:
I'm trying to write a simple asp.net page which updates some data in a SQL database. At the top of the page I have the following code: <%@ Page Language="C#" Debug="true" %> <%@ import...
11
by: SAL | last post by:
Hello, I have a Gridview control (.net 2.0) that I'm having trouble getting the Update button to fire any kind of event or preforming the update. The datatable is based on a join so I don't know...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.