How to update a table with vba?

[slenish]

Hello all,

What im trying to do is create a form that can update and save over a record that already exisits in the table it is saving to.

So far I have it so the form will detect a record that is already there and prompts the user to let them know that there is a record but what i want to do is after that prompt the user again and ask if they wish to overwrite the record that is already there?

So to give a little more detail I have 3 fields on my form Name, errCodeNum, errCodeReason. So i want to make it that if name and errCodeNum are already in there but the reason is different it will find the original record and then save over it.

Here is what i have so far i know the problem is the With statement just not sure how to get it to work right??

Appreciate any help with this!

Dim Db As DAO.Recordset
Dim rst As DAO.Database
Dim LResponse as string

If DCount("*", _
          "[Table]", _
          "(([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") AND (" & _
          "[errCodeNum]=" & Chr(34) & Me.Combo2 & Chr(34) & "))") > 0 Then
    Call MsgBox("This record already exists.", vbExclamation)
    LResponse = MsgBox("Would you like to overwrite this record?", vbYesNo, "Overwrite")
    If LResponse = vbNo Then
    Cancel = True
    Else
     with Current.Db
       [Name]= Combo0
       [errCodeNum] = combo2
       [errCodeReason] = text1
       Db.update
    end with
    
End If
End If

[MikeTheBike]

Hi

Thwere are a number of ways of achieving this. Using DAO, which I only raraly use, I would mod your code to something like this

Dim rst As DAO.Recordset
    Dim LResponse As Integer
    Dim sql As String
    
    If DCount("*", _
              "[Table]", _
              "(([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") AND (" & _
              "[errCodeNum]=" & Chr(34) & Me.Combo2 & Chr(34) & "))") > 0 Then
        
        LResponse = MsgBox("This record already exists." & lvblf & "Would you like to overwrite this record?", vbYesNo + vbQuestion, "Overwrite")
        
        If LResponse = vbNo Then
            Cancel = True
        Else
            sql = "SELECT Name, EttCodeNum, errCodeReason " & _
            "FROM [Table] " & _
            "WHERE (([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") " & _
            "AND ([errCodeNum]=" & Chr(34) & Me.Combo2 & Chr(34) & "))"
              
            Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynamic, dbOptimistic)
            rst.AddNew
            rst("Name") = Combo0
            rst("errCodeNum") = Combo2
            rst("errCodeReason") = text1
            rst.Update
        End If
    End If

If I was doing this I would use an Insert query and the built in DAO connection object like this

Dim sql As String
    
    sql = "INSERT INTO [Table] ( [Name], errCodeNum, errCodeReason ) " & _
          "SELECT '" & Combo1 & "' AS Nm, '" & Combo2 & "' AS ErrNum, '" & text1 & "' AS Resaon;"
            
    CurrentProject.Connection.Execute sql

There is also perhaps a simpler solution using the DoCmd object thus

Dim sql As String
    
    sql = "INSERT INTO [Table] ( [Name], errCodeNum, errCodeReason ) " & _
          "SELECT '" & Combo1 & "' AS Nm, '" & Combo2 & "' AS ErrNum, '" & text1 & "' AS Resaon;"
    
    DoCmd.RunSQL sql

All the above presuppose that [Name] and errCodeNum is the [Table] primary key?

HTH

MTB

[thelonelyghost]

MikeTheBike:
There is also perhaps a simpler solution using the DoCmd object thus

Dim sql As String 
  
    sql = "INSERT INTO [Table] ( [Name], errCodeNum, errCodeReason ) " & _ 
          "SELECT '" & Combo1 & "' AS Nm, '" & Combo2 & "' AS ErrNum, '" & text1 & "' AS Resaon;" 
  
    DoCmd.RunSQL sql

I much prefer to use CurrentDb.Execu te, but that's all personal preference. Here's a copy of a bit of code I use for logging:

CurrentDb.Execute ("INSERT INTO [tblFilterTemp] (strSQLCode, dtLastSearched, tmLastSearched) VALUES (""" & stringSQL & """, #" & Format(Now(), "MM/DD/YYYY") & "#, '" & Format(Now(), "HH:MM:SS") & "');")

You may also find THIS LINK useful.

[slenish]

hello Mikethebike and thelonleyghost,

thanks for the response back on this issue. Well i tried the code suggestion you had mike and it seems to not be working quite the way i need it to instead of saving over a record it just creates a new record and for some reason its creating a new record twice. I think its due to the part of the code that says rst.AddNew. I have been trying to change it up to rst.Edit or Update but so far no luck. any ideas on how to change this? For the most part it all seems to work fine except does not update the record just creates a new one.

code im working with from uptop

 If LResponse = vbNo Then 
            Cancel = True 
        Else 
            sql = "SELECT Name, EttCodeNum, errCodeReason " & _ 
            "FROM [Table] " & _ 
            "WHERE (([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") " & _ 
            "AND ([errCodeNum]=" & Chr(34) & Me.Combo2 & Chr(34) & "))" 
  
            Set rst = CurrentDb.OpenRecordset(sql)
            [B]rst.AddNew [/B]'problem area
            rst("Name") = Combo0 
            rst("errCodeNum") = Combo2 
            rst("errCodeReason") = text1 
            rst.Update 
        End If 
    End If

[MikeTheBike]

Hi again

Yes slight omission. Try this

    sql = "SELECT Name, EttCodeNum, errCodeReason " & _
    "FROM [Table] " & _
    "WHERE (([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") " & _
    "AND ([errCodeNum]=" & Chr(34) & Me.Combo2 & Chr(34) & "))"

    Set rst = CurrentDb.OpenRecordset(sql)
    If rst.RecordCount > 0 Then
        If MsgBox("This record already exists." & vbLf & _
                "Would you like to overwrite this record?", _
                vbYesNo + vbQuestion, "Overwrite") = vbNo Then
            Cancel = True
            Exit Sub
        End If
        rst.edit
    Else
        rst.AddNew
    End If
    
    rst("Name") = Combo0
    rst("errCodeNum") = Combo2
    rst("errCodeReason") = text1
    
    rst.Update
    
    rst.Close

Sorry about that.

MTB

[slenish]

Hi Mike,

Well i tried your new suggestion and im still getting the same result. Instead of saving over the old record it just makes a new one and for some reason doubles the new record. Not sure why that is happending, but thats whats going on so far. I'm still playing with it but if you think of any ideas let me know.

Thanks a lot for the help with this.

[MikeTheBike]

Hi again

Don't know what the problem is, but, on the basis that all fields are Text data types, this code does work OK as you expect/require

    Dim SQL As String
    Dim Db As DAO.Database
    Dim rst As DAO.Recordset


    SQL = "SELECT Name, EttCodeNum, errCodeReason " & _
    "FROM [Table] " & _
    "WHERE (([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") " & _
    "AND ([EttCodeNum]=" & Chr(34) & Me.Combo2 & Chr(34) & "))"

    Set rst = CurrentDb.OpenRecordset(SQL)
    If rst.RecordCount > 0 Then
        If MsgBox("This record already exists." & vbLf & _
                "Would you like to overwrite this record?", _
                vbYesNo + vbQuestion, "Overwrite") = vbNo Then
            Cancel = True
            rst.Close
            Set rst = Nothing
            Exit Sub
        End If
        rst.Edit
    Else
        rst.AddNew
    End If
  
    rst("Name") = Combo0
    rst("EttCodeNum") = Combo2
    rst("errCodeReason") = Text1
  
    rst.Update
  
    rst.Close

As I mentioned in my first post, the [Name] & [EttCodeNum] fields are obviously not defined as the table primary key, otherwise you would not be allowed to make a duplicate entry!

MTB