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
-
6 1508
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
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.Execute, 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.
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)
-
rst.AddNew 'problem area
-
rst("Name") = Combo0
-
rst("errCodeNum") = Combo2
-
rst("errCodeReason") = text1
-
rst.Update
-
End If
-
End If
-
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
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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
|
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:
...
|
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...
|
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...
|
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 ------ ...
|
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...
|
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
|
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 ...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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....
|
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...
|
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...
| |