How do I save over an old record??

[slenish]

Hello,

what i am trying to do is I have a form set up where you enter in 3 types of information a name, errcode and a number. What I want to do is is when you enter in the record and then hit the save button it looks at the table that its saving to and finds out if there is already a matching record, if there is a matching record prompt the user if they would like to save over the record. Now i have this sort of working but the problem im having is either when I go to save it saves over the old record but for some as it saves over the old one it creates a new one. So I end up with two records or the other if I change some things I just get it to save new records and not save over the old one any help would be great.

here is the code im working with

also i have this in the Forms BeforeUpdate event


appreciate the help

If DCount("*", _
          "[Table]", _
          "(([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") AND (" & _
          "[Codetext]=" & 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, Code, Num " & _
            '"FROM [table] " & _
            '"WHERE (([table]=" & Chr(34) & Me.Combo0 & Chr(34) & ") " & _
            '"AND ([Codetxt]=" & Chr(34) & Me.Combo2 & Chr(34) & ") " & _
            '"AND ([Num]=" & Chr(34) & Me.Text4 & Chr(34) & "))"
 
       Set rst = CurrentDb.OpenRecordset(sql)
 
    rst.AddNew
 
    rst("Name") = Combo0
    rst("Codetxt") = Combo2
    rst("Number") = Text4
 
    rst.Update
 
    rst.Close
    End If
    End If

[yarbrough40]

it doesn't appear as though this code overwrites anything. using 'addnew' simply adds a new record.

What you are looking for is an UPDATE statement I think. OR you may be able to set a dynamic cursor on your recordset and update the values that way.

Another suggestion is instead of using DCount, fire an ado select statement which has the primary key in it. count the # of records in that result (or handle the error because that is what ado does when it has nothing if I remember correctly) then you'll have to use that primary key to delete the record. THEN do your addnew or INSERT INTO may be a better route

[slenish]

Hi Yarbough40,

you are right about this code does not overwrite anything. Im trying to figure out how to make it overwrite. Everytime I try to change the addnew part to update or overwrite i just get errors back.

Could you show me an example or a link to an example of how to do what you are talking about??

appreciate the help! :D

[parodux]

..if everything else work then your only problem is the rst.addnew it should be rst.edit!~)

[slenish]

Hi Parodux,

Yeah i have tried that, but changing it to .edit does not work. It kicks back errors saying you can not use edit with out add new...

:)

[parodux]

are you sure that the sql has a record to edit?~)

[parodux]

can you do rst.movefirst

[ADezii]

This can easily be done, but to me it makes no sense. Why Overwrite the Duplicate Record?

• Create a Unique, Composite Index on all three Fields so Duplicates will never be allowed.
• Notify the User that a Duplicate Record exists, and do not allow them to Save the Current Record.

[Steven Kogan]

Once the code is fixed you would use rst.Edit instead of rst.AddNew.

There are syntax errors and perhaps a logic error in the code. I've modified the code so that fieldnames are consistent.

Your DCount looks at the values for Name and Codetext. The Where portion of the Select statement should look at the same fields, instead of also looking for Num (that way you should find a match, since the DCount = 1 (or more)). For those key values you may not want to allow duplicate records: if you do it is unclear which record you want to overwrite.

This code will find a record with a matching Name and Codetext, and will overwrite the existing Num field with what is in your Text4 field.

If DCount("*", _
          "[Table]", _
          "(([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") AND (" & _
          "[Codetext]=" & 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, Codetext, Num " & _
        "FROM [table] " & _
        "WHERE (([name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") " & _
        "AND ([Codetext]=" & Chr(34) & Me.Combo2 & Chr(34) & "))"
 
        Set rst = CurrentDb.OpenRecordset(SQL)
 
        rst.Edit
        rst("Name") = Combo0
        rst("Codetext") = Combo2
        rst("Num") = Text4
 
        rst.Update
 
        rst.Close
    End If
End If
 

[NeoPa]

The really basic question here is not about how to manage manipulating data with unbound forms. That is a question we get over and over again. The question that is most fundamental to this is why for goodness' sake are you trying to do things such an unnatural and complicated way in the first place? This is a question that should always be asked of members who come here with similar questions.

Members mostly ask this type of question because they don't have the experience to know that Access has built-in wizards to handle this functionality for them quite easily using bound forms.

So slenish, why the desire to use the complexity of unbound forms?

[parodux]

I agree with NeoPa... and the only reason I can see as to why you are doing what you are doing, is that you want the end user to press save before any changes is done.
If you can't get this unbound thing to work... You could turn the scenario around and do NeoPa's solution with an added 'please-let-me-take-it-back' button.. that's straight forward!~)

[parodux]

by the way slenish you never answered the question about rst.movefirst!~)

[slenish]

Hello everyone,

Appreciate all the help on this issue!! Means a lot. Been driving me crazy for a few days now.

To answer a few of the questions.

parodux - i tried the movefirst and was getting the same result as before.

ADezii - well i orignally set it up so you could not overwrite the record but the team that will be using the program wants that function since no one will be allowed backend access. Also the reason behind it is because there only needs to be one record for each person of each type.

NeoPa - well the form is a bound form its not unbound. I tried using an update query but was having no luck so i thought using code might be a little easier, plus i feel the coding give me the programmer more control over how things work. Also if you say there is a built in wizard for this i havent been able to find it...

Last Big thanks to Steven so far your code is working, only having one problem instead of writing over the whole record it deletes part of the record and then creates a new one, just wondering how i can get it to overwrite the whole record so there is not two??

Example of what its doing. It keeps the Name, and CodeText but errases the Num then makes a whole new record with the new information.

thanks again everyone :D

[Steven Kogan]

For the code I provided it turns out lines 19 and 20 aren't needed, since the old values equal the new values.

As written Line 21 overwrites the old value of Num with the value in Text4.

In that way the old record is overwritten with new information.

The code does not add a new record to a table though... It only edits an existing record.

Hopefully you can make sense of the code and adapt it to what you are trying to do.

From what you are writing it sounds like Num should not be overwritten, and that perhaps it is a key value? If that's the case you could add Num to the DCount statement and to the Where clause of the Select statement. Plus you would remove line 21.

In the place of lines 19-21 you would put in your code to overwrite existing fields with new values.

Perhaps this is closer to what you want:

If DCount("*", _ 
          "[Table]", _ 
          "(([Name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") AND (" & _ 
          "[Codetext]=" & Chr(34) & Me.Combo2 & Chr(34) & ") AND ([Num]=" & Chr(34) & Me.Text4 & 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, Codetext, Num, YourField, AnotherField " & _ 
        "FROM [table] " & _ 
        "WHERE (([name]=" & Chr(34) & Me.Combo0 & Chr(34) & ") " & _ 
        "AND ([Codetext]=" & Chr(34) & Me.Combo2 & Chr(34) & ") AND ([Num]=" & Chr(34) & Me.Text4 & Chr(34)& "))" 
 
        Set rst = CurrentDb.OpenRecordset(SQL) 
 
        rst.Edit 
        rst("YourField") = me.txtYourField 
        rst("AnotherField") = me.txtAnotherField 
        ' i.e. populate the other fields with values from your form
 
        rst.Update 
 
        rst.Close 
    End If 
End If 
 
 

[NeoPa]

Slenish: NeoPa - well the form is a bound form its not unbound. I tried using an update query but was having no luck so i thought using code might be a little easier, plus i feel the coding give me the programmer more control over how things work. Also if you say there is a built in wizard for this i havent been able to find it...

In that case I really am confused.

Looking at your original code posted I see that it probably is a bound form, for all the code that doesn't make sense in that environment and all the posting that seems to have overlooked this entirely. Had you included the line saying :

Private Sub Form_BeforeUpdate(Cancel As Integer)

It would have been a lot clearer what you were attempting.

In a bound form, the saving of the record is done automatically. Not by extra code. I don't understand why you would be asking about UPDATE queries if the data is already managed by the bound form. That would only make any sense if you were dealing with an unbound form. You seem to be using a bound form, but trying to treat it as unbound. Very strange.

How strange depends very much on your logic, which isn't clearly explained I'm afraid, as there are many combinations you haven't covered or explained.

When modifying a table, you can edit or add records.

When editing, do you want the operator to be prompted to create a new record instead of updating the existing one?

When adding records, do you want the operator to be prompted to overwrite a similar record if one exists?

The latter would certainly be complicated and not advised. If they want to update an existing record then that's what they should do, rather than enter it again and have it overwritten. Anyway, please let us know exactly what's in your mind on this.

[Steven Kogan]

Since the form is bound then it makes sense a new record would be added - that would be done automatically.

Instead of editing the existing record you may want to delete it. After the before update is done the after update event would fire, saving the record on screen.

The Else portion, Lines 11-25, would be replaced by a delete query for the specific record to be overwritten.

[NeoPa]

While you very well may be right Steven, many of our problems arise from members who don't have the basic understandings of how things work and therefore ask for things that get misunderstood by experts who assume a basic understaning of the principles. That's why I'm looking for definitive answers from the OP (specifically).

I think there's enough in here to show that understanding of the issues is limited, I'm not sure by how much though.

[slenish]

Hello again,

Well I got it to work finally i just adjusted added in one line. Around 19 - 20. I added in rst.delete so it deletes the old record and then it creates a new one with the new information. Works great!! Thanks again Steve!!

NeoPa,
My bad on the update query. I was just saying that I was experiementing with an update query to see if it would work to update ie..overwrite the current record which it was not doing. Also the before update....part that you asked about is in there just didnt copy that part in to the code example since I said way back in the beginning that is where i put it didnt think I need to show that part. Just to clear up any confusion you might have I'll run through the whole thing again.

Im looking to edit or overwrite or delete and make a new record (not sure the best way to say that for coding purposes)a record on a table. The record contains 3 fields that I want the user to be able to save over if need be. So the code that I started with did part of the job, checked the form information against the table record and found a match, then it prompts the user to say there is currently a matching record do you want to overwrite the record yes or no? If yes then it would save over the current record with the new information is no then cancel. All in all the whole thing works now.

Really appreciate all the help with this!! Unforunatly this is the downside to talking over internet with out being able to either show you or physically talk to you to explain things a little clearer that is how information can be confusing. I am sorry if I confused anyone along the way. But over all everything worked out successfully :D

[NeoPa]

You're right Slenish. Talking (communicating) across a forum web page does take extra care. So many of the normal clues are absent.

As to your issue. I can sort of understand what you are trying to do, but I think you're unwise to approach it that way. That is all I need to say on the subject, as it's ultimately your decision. My only concern is that I warn you from my experience. What you do with that is not really my business.

I'm pleased you have something you're happy with anyway :)

[eneyardi]

Maybe this code can help based on slenish tried to accomplish:
To do this make a table AuditCustomers & Customers
then a form CustomerA, CustomerB & Customers
In table must have fields CustomerID, EmpName, From, Date Received, To & Date Released
Also make a query to Customers table: CustomerA & CustomerB, in design view of query CustomerA, put "A" in the criteria of field TO in order to see all your entry containing all A in To Textbox. Then in design view query of CustomerB, put "B" in the criteria of field to, in order to see all entry containing B in To textbox.

In these sample program, when you add entry or record to Customers form, the customers table and AuditCustomers will be updated, and any changes you will try to do in any record you have for example, it will save to the table AuditCustomers

In Form Customers try to put button by help of wizard button: Add, Save & Delete in order to prove that it works!

This is slenish i think tried to do.

Put this code to Form Customers-Event-After Update

Private Sub Form_AfterUpdate()
Dim db As Database
 
Set db = CurrentDb
db.Execute "INSERT INTO [AuditCustomers] " _
& " SELECT * FROM [Customers] WHERE " _
& " [Customers].[CustomerID]='" & Me![CustomerID] & "';"
Set db = Nothing
End Sub