Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 1st, 2007, 03:16 AM
Authorized User
 
Join Date: Dec 2003
Location: Vashi, Navi Mumbai, Maharashtra, India.
Posts: 63
Thanks: 0
Thanked 0 Times in 0 Posts
Default not update all record

hi all
My table tblDet there are Cid, OB, Dep, withdraw, CB fields like below

Cid OB Dep withdraw CB
01 1000 0 100 900
01 900 500 0 1400
01 1400 500 0 1900
01 1900 0 500 1400

My code is below

Private Sub cmdUpd_Click()
Dim sSQLa, sSQLb As String
Dim mob, mdeo, mwith, mcb, mcid As Double
Dim iPK, i As Double
iPK = Me.txtCid
sSQLa = "SELECT * FROM tblDet WHERE [Cid] = " & iPK
Set rs1 = New ADODB.Recordset
rs1.Open sSQLa, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
mob = rs1.Fields("ob")
mcb = mob + rs1.Fields("Dep") - rs1.Fields("withdraw")
Do Until rs1.EOF
rs1.Fields("ob") = mob
rs1.Fields("cb") = mob + rs1.Fields("Dep") - rs1.Fields("withdraw")
rs1.Update
rs1.Fields("ob") = rs1.Fields("ob") + rs1.Fields("Dep") - rs1.Fields("withdraw")
rs1.Fields("cb") = rs1.Fields("ob") + rs1.Fields("Dep") - rs1.Fields("withdraw")
rs1.MoveNext
Loop
End Sub
Now my problems is when I click update only one record update
remaining records not update I want replace all record like above
what is wrong my code should rectified

Mohamed Mohiddin
Officer
__________________
Mohamed Mohiddin
Officer
Reply With Quote
  #2 (permalink)  
Old May 2nd, 2007, 12:22 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Dim sSQLa, sSQLb As String
Dim mob, mdeo, mwith, mcb, mcid As Double
Dim iPK, i As Double

iPK = Me.txtCid
sSQLa = "SELECT * FROM tblDet WHERE [Cid] = " & iPK

Set rs1 = New ADODB.Recordset
rs1.Open sSQLa, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until rs1.EOF
   mob = rs1.Fields("ob")
   mcb = mob + rs1.Fields("Dep") - rs1.Fields("withdraw")

   rs1.Fields("ob") = mob
   rs1.Fields("cb") = mob + rs1.Fields("Dep") - rs1.Fields("withdraw")
   rs1.Update

   'you want variables here to do calculations, you can't put them back into the recordset since it will not be updated.

rs1.MoveNext
Loop

Whatever you are doing to all the fields, you must do that inside the recordset loop. If this is not working, create a second loop.

Also, any code after the rs1.Update will not be executed on the other fields.

Did any of this help?

mmcdonal
Reply With Quote
  #3 (permalink)  
Old May 3rd, 2007, 12:03 PM
Authorized User
 
Join Date: Dec 2003
Location: Vashi, Navi Mumbai, Maharashtra, India.
Posts: 63
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your reply's , but I still have the problem...
when i add deposit field ob fields should come cb field
like blow
OB Dep with CB
1000 100 100 1000
1000 0 100 900
 900 100 100 900
in above method updating how should write the code then second loop how should create please explain me


Mohamed Mohiddin
Officer
Reply With Quote
  #4 (permalink)  
Old May 3rd, 2007, 12:20 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What is the event that it triggering these updates? Do you click a button?

Is there a source table and a target table, or are you doing this all in one table? It seems to me you would have a transaction table, and then a daily balance table.

Please post the table structures, what the event it, what the data looks like before processing and where it is stored, and what the data should look like after processing and where it is stored.

Normally I think you would do this in a report based on a single transaction table, and then store the data in a temporary table to run the report from.

This is not a difficult problem.


mmcdonal
Reply With Quote
  #5 (permalink)  
Old May 3rd, 2007, 12:30 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I started on this and here is the table structure I am using:

tblAccount (your account information table)
AcctID - PK
AcctNo - text
Name - text

tblTransaction (account transaction information)
TransID - PK
AcctID - FK to tblAccount
TransDate - Date
Trans - currency

This should be enough to generate the information you want when you need it. There shouldn't be a table with the info you have here, except as a temp table.

Based on this structure, I would clean out the temp table, then build the data you want by date, then run a report based on the the temp table. You could also create a form based on the temp table using tblAccount as the main form data source, and the temp table as a subform datasource (not updateable).

Is any of this helpful?



mmcdonal
Reply With Quote
  #6 (permalink)  
Old May 5th, 2007, 06:31 AM
Authorized User
 
Join Date: Dec 2003
Location: Vashi, Navi Mumbai, Maharashtra, India.
Posts: 63
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dear mmcdonal,
My source table tblDet structures is like below
cid,Ob,dep_date,dep,with_date, with,cb
I put ob, dep_date,dep(deposit),with_date, with,cb,(withdraw) fields manually
Then I click the cmd(R)refresh button the fields should update like below

Cid Ob dep_date dep with_date with cb
A001 1000 01/04/2007 500 0 1500
A001 1500 0 02/042007 500 1000
A001 1000 03/04/2007 1000 0 2000
A001 2000 0 0 2000
The above method I want updating the fields


Mohamed Mohiddin
Officer
Reply With Quote
  #7 (permalink)  
Old May 7th, 2007, 02:03 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Where are you getting the Deposit and Withdrawl information from? Is that calculated from another table, or does someone figure out the total amounts of deposits and withdrawals for a day and then enter them in? Or is the customer only allowed one deposit and one withdrawal per day?

The answer will determine how this is done. With my structure, you can enter as many trasnactions as the customer cares to make, and them run a routine on the main table (your table) after each transaction. So it updates after each transaction. That would be cool. Otherwise, it seems you just want to figure out the closing balance after deposits and withdrawals are somehow entered into this table for each date. Would you then run a routine at a certain time of day? That we could do.

Either way, we can do this, you just have to be clear on your processes. I will be able to post code tomorrow. Can you clarify in the meantime. It looks like the values are entered manually, and you want to total them up at the end of the day and populate the next day's opening balance at the same time.



mmcdonal
Reply With Quote
  #8 (permalink)  
Old May 7th, 2007, 02:16 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

This is what is confusing to me:

"I put ob, dep_date,dep(deposit),with_date, with,cb,(withdraw) fields manually"

This looks like you are entering ALL the information manually, so I am not sure what is being automated. I would say that normalizing the data and using a transaction table, and then building this table data automatically from the transaction table is the way to go. Can you redesign the database like this, or are you stuck with this current design?

mmcdonal
Reply With Quote
  #9 (permalink)  
Old May 7th, 2007, 02:19 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Based on this structure:

tblAccount (your account information table)
AcctID - PK
AcctNo - text
Name - text

tblTransaction (account transaction information)
TransID - PK
AcctID - FK to tblAccount
TransDate - Date
Trans - currency

tblRunningBalance
RBID - PK
OBDate - Date
OpenBalance - currency
Withdrawals - currency
Deposits - currency
ClosingBalance - currency

You would get all the functionality and lots more automation. Then your tellers would only have to pull up a customer record and enter a transaction at a time, which could be automatically dated for the date of entry - with the option to change. Then on the Before Insert and Before Update events of that form, you would force a new set of data into the record with the date of the transaction entered each time.

mmcdonal
Reply With Quote
  #10 (permalink)  
Old May 8th, 2007, 08:34 AM
Authorized User
 
Join Date: Dec 2003
Location: Vashi, Navi Mumbai, Maharashtra, India.
Posts: 63
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your reply's
Acutely in my table there are only ob,dep,with,cb fields now my problems is if I put ob,dep,with manually like below
OB DEP WITH CB
5000 0 500 4500
4500 500 0 5000
5000 0 1000 4000
4000 0 2000 2000
2000 1000 0 3000
Now I want cb should come like ob+dep-with and ob should come cb like above method when I go to update my table.It is possible the same table or I want create another table? after updating my table I want take daily report



Mohamed Mohiddin
Officer
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
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

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update one record jemacc SQL Server 2000 0 September 12th, 2007 11:47 AM
Update record error Dejitan ASP.NET 1.0 and 1.1 Basics 5 December 20th, 2006 10:15 AM
cannot update record keyvanjan ASP.NET 1.0 and 1.1 Basics 0 September 9th, 2006 05:25 AM
Update Record Issue phungleon Classic ASP Databases 5 June 10th, 2005 04:26 AM
update record problems whyulil Dreamweaver (all versions) 2 July 24th, 2003 04:38 AM



All times are GMT -4. The time now is 05:57 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.