Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 9th, 2005, 10:50 AM
Authorized User
 
Join Date: Apr 2005
Location: , , USA.
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default Edit record help

Hi all,

I need help with the following code. It performs as it should as far as editing the Invoice Balance when a payment is posted. However, the code applies all payments posted to the first record in tblInvoices. How do I target the correct record?


Private Sub PostPayment_Click()

Dim db As Database
Dim rst As Recordset
Dim SearchID As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblInvoices")

SearchID = Forms![subfrmPaymentsNew]![InvoiceID]

    With rst
        .Edit
        ![InvoiceBalance] = Nz(rst![InvoiceBalance]) - (Me.DiscountAmount + Me.PaymentAmount)
        .Update
        .Close
    End With

  Forms!frmInvoices.Refresh

End Sub

Thanks in advance for your help.

Don


D. Bartelt
__________________
D. Bartelt
 
Old November 9th, 2005, 10:59 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Ya Don!

You are opening the whole table with no restrictions to get the correct record.

Change your code to this:


SearchID = Forms![subfrmPaymentsNew]![InvoiceID]
SearchSql = "Select * from tblInvoices Where InvoiceID = " & SearchID
Set rst = db.OpenRecordset(SearchSql)

Hope This Helps,

Kevin


dartcoach
 
Old November 9th, 2005, 11:19 AM
Authorized User
 
Join Date: Apr 2005
Location: , , USA.
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Kevin,

Thank you for your quick response. With your advice, the following code works great!!!

Private Sub PostPayment_Click()

Dim db As Database
Dim rst As Recordset
Dim SearchID As String
Dim SearchSql As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblInvoices")

SearchID = Forms![frmInvoices]![InvoiceID]
SearchSql = "Select * from tblInvoices Where InvoiceID = " & SearchID
Set rst = db.OpenRecordset(SearchSql)

    With rst
        .Edit
        ![InvoiceBalance] = Nz(rst![InvoiceBalance]) - (Me.DiscountAmount + Me.PaymentAmount)
        .Update
        .Close
    End With

        Forms!frmInvoices.Refresh

Exit_PostPayment_Click:
    Exit Sub

Err_PostPayment_Click:
    MsgBox Err.Description
    Resume Exit_PostPayment_Click

End Sub

Thanks so much! Take care!

Don

D. Bartelt
 
Old November 9th, 2005, 11:23 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Don,

Glad I could help!

Take care.

Kevin

dartcoach
 
Old November 9th, 2005, 06:02 PM
Authorized User
 
Join Date: Sep 2004
Location: Nanaimo, BC, Canada.
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have a few suggestions.
Can you put future programming topics like this in VBA section?

Use DAO.Database, DAO.Recordset to avoid later confusion with ADO

You have error correcting, but you have no
On Error Goto statement.

As soon as you open the rst, make you sure you have a record to play with.
If you are a new record, you would have a Null value for Search ID, and an Error there too.




Database Agreements




Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Edit record right after it was Inserted? aladov ASP.NET 2.0 Professional 4 December 11th, 2007 03:18 PM
how to add new record as first record in dataset hunzian78@yahoo.com ASP.NET 1.0 and 1.1 Professional 4 April 21st, 2006 05:23 AM
Adding a blank record to a grid (in edit-mode) ColSandrs ASP.NET 1.0 and 1.1 Basics 1 December 21st, 2005 08:42 AM
Record locking - user needs the next queued record cbtoolkit SQL Server 2000 0 December 6th, 2004 08:29 AM
edit-update record- help TnTandyO Classic ASP Databases 37 September 11th, 2003 06:31 PM





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