p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Forum and Wrox.com Feedback (http://p2p.wrox.com/forumdisplay.php?f=56)
-   -   SQL for saving record value when knowing index # of selected Recordset (http://p2p.wrox.com/showthread.php?t=89876)

innovationsindm April 18th, 2013 10:31 AM

SQL for saving record value when knowing index # of selected Recordset
 
I have a form that contains a ComboBox to select a CustomerName from the table CustomerHQTbl. The query includes Index, CustomerName, CustomerNumber and EstimateNumber as fields. In the AfterUpdate of the ComboBox I increase EstimateNumber by 1.

I'm brand new to this site access. I wanted to find out how I could save the updated EstimateNumber to the record set (for Index) in the CustomerHQTbl table.

Below is the code I currently have in the Company Name Combo box AfterUpdate:

Code:

    Private Sub CompanyName_AfterUpdate()

    Dim EstimateNumber As String
    Dim RecdSet As Integer

    ' Set form object values
        CSOrderCompanyName = Me!CompanyName.Column(0) 'CompanyName
        CSOrderCompanyNumber = Me!CompanyName.Column(1) ' CompanyNumber
        EstimateNumber = Me!CompanyName.Column(2) 'current EstimateNumber
        RecdSet = Me!CompanyName.Column(3) ' Index
        CSOrderEntryEstimateDate = Date
        CSOrderEntryVersions = "1"
        CSOrderEntryTypeOfPricing = "COMMERCIAL PRINTING"
        CSOrderEstimateNumber = "13-" & Right(CSOrderCompanyNumber, 5) & Right(Str(Val(EstimateNumber) + 1), 4) & "-" & CSOrderEntryVersions
   
    ' Set new estimate values in CompanyHQTbl
        Dim db As Database
        Dim rst As Recordset
        Dim ssql As String
       
        Set db = CurrentDb
        ssql = "SELECT * FROM CompanyHQTbl WHERE (Index = RecdSet)"
        Set rst = db.OpenRecordset(ssql)
        rst.MoveFirst '**** THIS IS WHERE I ERROR OUT ****
                     
        rst.Edit
        rst.Fields("EstimateNumber").Value = (Right(Str(Val(EstimateNumber) + 1), 4))
        rst.Update


winwalk October 22nd, 2013 02:25 AM

Company Name Combo box AfterUpdate:

minifa48 November 27th, 2013 02:09 AM

Thank you[:)][:)][:)][:)]

yedped January 16th, 2014 03:09 AM

Thank you ^^'


All times are GMT -4. The time now is 08:55 AM.

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