View Single Post
  #1 (permalink)  
Old April 18th, 2013, 10:31 AM
innovationsindm innovationsindm is offline
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2013
Location: Canton
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

Last edited by innovationsindm; April 18th, 2013 at 03:42 PM.. Reason: Adding code