p2p.wrox.com Forums - View Single Post - SQL for saving record value when knowing index # of selected Recordset

innovationsindm · #1 (**permalink**) April 18th, 2013, 10:31 AM

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