473,322 Members | 1,431 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How to update a table with vba?

283 100+
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!
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim Db As DAO.Recordset
  3. Dim rst As DAO.Database
  4. Dim LResponse as string
  5.  
  6. If DCount("*", _
  7.           "[Table]", _
  8.           "(([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") AND (" & _
  9.           "[errCodeNum]=" & Chr(34) & Me.Combo2 & Chr(34) & "))") > 0 Then
  10.     Call MsgBox("This record already exists.", vbExclamation)
  11.     LResponse = MsgBox("Would you like to overwrite this record?", vbYesNo, "Overwrite")
  12.     If LResponse = vbNo Then
  13.     Cancel = True
  14.     Else
  15.      with Current.Db
  16.        [Name]= Combo0
  17.        [errCodeNum] = combo2
  18.        [errCodeReason] = text1
  19.        Db.update
  20.     end with
  21.  
  22. End If
  23. End If 
  24.  
Aug 2 '10 #1
6 1508
MikeTheBike
639 Expert 512MB
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
Expand|Select|Wrap|Line Numbers
  1. Dim rst As DAO.Recordset
  2.     Dim LResponse As Integer
  3.     Dim sql As String
  4.  
  5.     If DCount("*", _
  6.               "[Table]", _
  7.               "(([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") AND (" & _
  8.               "[errCodeNum]=" & Chr(34) & Me.Combo2 & Chr(34) & "))") > 0 Then
  9.  
  10.         LResponse = MsgBox("This record already exists." & lvblf & "Would you like to overwrite this record?", vbYesNo + vbQuestion, "Overwrite")
  11.  
  12.         If LResponse = vbNo Then
  13.             Cancel = True
  14.         Else
  15.             sql = "SELECT Name, EttCodeNum, errCodeReason " & _
  16.             "FROM [Table] " & _
  17.             "WHERE (([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") " & _
  18.             "AND ([errCodeNum]=" & Chr(34) & Me.Combo2 & Chr(34) & "))"
  19.  
  20.             Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynamic, dbOptimistic)
  21.             rst.AddNew
  22.             rst("Name") = Combo0
  23.             rst("errCodeNum") = Combo2
  24.             rst("errCodeReason") = text1
  25.             rst.Update
  26.         End If
  27.     End If
If I was doing this I would use an Insert query and the built in DAO connection object like this
Expand|Select|Wrap|Line Numbers
  1. Dim sql As String
  2.  
  3.     sql = "INSERT INTO [Table] ( [Name], errCodeNum, errCodeReason ) " & _
  4.           "SELECT '" & Combo1 & "' AS Nm, '" & Combo2 & "' AS ErrNum, '" & text1 & "' AS Resaon;"
  5.  
  6.     CurrentProject.Connection.Execute sql
There is also perhaps a simpler solution using the DoCmd object thus
Expand|Select|Wrap|Line Numbers
  1. Dim sql As String
  2.  
  3.     sql = "INSERT INTO [Table] ( [Name], errCodeNum, errCodeReason ) " & _
  4.           "SELECT '" & Combo1 & "' AS Nm, '" & Combo2 & "' AS ErrNum, '" & text1 & "' AS Resaon;"
  5.  
  6.     DoCmd.RunSQL sql
All the above presuppose that [Name] and errCodeNum is the [Table] primary key?

HTH

MTB
Aug 3 '10 #2
thelonelyghost
109 100+
MikeTheBike:
There is also perhaps a simpler solution using the DoCmd object thus
Expand|Select|Wrap|Line Numbers
  1. Dim sql As String 
  2.  
  3.     sql = "INSERT INTO [Table] ( [Name], errCodeNum, errCodeReason ) " & _ 
  4.           "SELECT '" & Combo1 & "' AS Nm, '" & Combo2 & "' AS ErrNum, '" & text1 & "' AS Resaon;" 
  5.  
  6.     DoCmd.RunSQL sql
I much prefer to use CurrentDb.Execute, but that's all personal preference. Here's a copy of a bit of code I use for logging:
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 3 '10 #3
slenish
283 100+
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
Expand|Select|Wrap|Line Numbers
  1.  If LResponse = vbNo Then 
  2.             Cancel = True 
  3.         Else 
  4.             sql = "SELECT Name, EttCodeNum, errCodeReason " & _ 
  5.             "FROM [Table] " & _ 
  6.             "WHERE (([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") " & _ 
  7.             "AND ([errCodeNum]=" & Chr(34) & Me.Combo2 & Chr(34) & "))" 
  8.  
  9.             Set rst = CurrentDb.OpenRecordset(sql)
  10.             rst.AddNew 'problem area
  11.             rst("Name") = Combo0 
  12.             rst("errCodeNum") = Combo2 
  13.             rst("errCodeReason") = text1 
  14.             rst.Update 
  15.         End If 
  16.     End If 
  17.  
Aug 4 '10 #4
MikeTheBike
639 Expert 512MB
Hi again

Yes slight omission. Try this
Expand|Select|Wrap|Line Numbers
  1.     sql = "SELECT Name, EttCodeNum, errCodeReason " & _
  2.     "FROM [Table] " & _
  3.     "WHERE (([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") " & _
  4.     "AND ([errCodeNum]=" & Chr(34) & Me.Combo2 & Chr(34) & "))"
  5.  
  6.     Set rst = CurrentDb.OpenRecordset(sql)
  7.     If rst.RecordCount > 0 Then
  8.         If MsgBox("This record already exists." & vbLf & _
  9.                 "Would you like to overwrite this record?", _
  10.                 vbYesNo + vbQuestion, "Overwrite") = vbNo Then
  11.             Cancel = True
  12.             Exit Sub
  13.         End If
  14.         rst.edit
  15.     Else
  16.         rst.AddNew
  17.     End If
  18.  
  19.     rst("Name") = Combo0
  20.     rst("errCodeNum") = Combo2
  21.     rst("errCodeReason") = text1
  22.  
  23.     rst.Update
  24.  
  25.     rst.Close 
Sorry about that.

MTB
Aug 5 '10 #5
slenish
283 100+
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.
Aug 6 '10 #6
MikeTheBike
639 Expert 512MB
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
Expand|Select|Wrap|Line Numbers
  1.     Dim SQL As String
  2.     Dim Db As DAO.Database
  3.     Dim rst As DAO.Recordset
  4.  
  5.  
  6.     SQL = "SELECT Name, EttCodeNum, errCodeReason " & _
  7.     "FROM [Table] " & _
  8.     "WHERE (([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") " & _
  9.     "AND ([EttCodeNum]=" & Chr(34) & Me.Combo2 & Chr(34) & "))"
  10.  
  11.     Set rst = CurrentDb.OpenRecordset(SQL)
  12.     If rst.RecordCount > 0 Then
  13.         If MsgBox("This record already exists." & vbLf & _
  14.                 "Would you like to overwrite this record?", _
  15.                 vbYesNo + vbQuestion, "Overwrite") = vbNo Then
  16.             Cancel = True
  17.             rst.Close
  18.             Set rst = Nothing
  19.             Exit Sub
  20.         End If
  21.         rst.Edit
  22.     Else
  23.         rst.AddNew
  24.     End If
  25.  
  26.     rst("Name") = Combo0
  27.     rst("EttCodeNum") = Combo2
  28.     rst("errCodeReason") = Text1
  29.  
  30.     rst.Update
  31.  
  32.     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
Aug 9 '10 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: amwi | last post by:
I have tried to solve this on my own for a long time now, so i really need some help here... I use Oracle 10.1 and SQL *plus 10.1. How do i update table a.fkid from table b.pkid with the...
2
by: Mike Leahy | last post by:
Hello all, This question is related to updating tables - is there any way to calculate or update the values in a column in a table to the values in a field produced by a query result? An...
9
by: baonks | last post by:
hello all here is my problem: I have 2 table 1: K_POS SALDO_A_D SALDO_A_K 11100 105 5 11200 5 105
2
by: Ron | last post by:
Hello, I am trying to do a simple update on employee information. I am a novice at both aspx and SQLServer, so I hope you are not too offended by my code. The following is the update code: ...
2
by: Ennio-Sr | last post by:
Hi! I'm thinking about creating a table listing my shares (say 'Shares') and then update its price column from a .txt file (say 'Prices') downloaded from an internet site periodically. Recalling...
2
by: Paul712 | last post by:
Recently, I have a table that I use to update a master table. When I run the same Update query that's been successful in the past, most all of the data in the fields in the update fields has been...
0
by: jainapurva108 | last post by:
Hi, I have one table with some values as shown in below format... Column-A Column-B Column-C Column-D Column-E 11AA ------ 1234 ------ ASDF------ FIRST ------ONE 22BB ------ ...
5
by: SQL Learner | last post by:
Hi Alex (Kuznetsov) and All, This is to follow up with my last post, "Link two tables using partial word match". How can I UPDATE table using partial word match? How can I write a SQL statement...
1
by: mrobinsc | last post by:
** This SQL statement returns 4 rows SELECT COUNT(*) G.ACTIVITY_ID G.RESOURCE_TYPE G.RESOURCE_CATEGORY G.RESOURCE_SUB_CAT G.ANALYSIS_TYPE G.PROJECT_ID
1
by: adithi | last post by:
My Table Structure is: Table A Table B Table C colA -PK Col B-PK Col C-PK Col B-FK ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.