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.