Update table based on textboxes data

[NahualXl]

Hello,
I'am trying to perform an update on my table based on the textboxes data; however, when the code is executed it generates a new record instead of updating.
The issue is this there are 2 ppl at the area:

• either one of them can either start the log and/or edit the record created by the other person
• If the record exists we only want to edit the record without overwriting the existing data from previous person
• The query version of the attempt has the WHERE clause in it
• this was an old db attempt by someone else unfortunately I inherited it
• the WHERE clause comes from the data on a textbox but I can get it to work

As always I truly appreciate all your assistance and patience, thank you so much
here is the code I am working with:

Dim db As Database
Dim rec As Recordset


Set db = CurrentDb
'TRIED THE FOLLOWING AFTER A FEW TRIES, CAN NOT GET THE WHERE CLAUSE TO WORK?***********
GCriteria = "[LOG.Thermal_WorkOrder]=[Forms]![THERMALFrm]![Thermal_WorkOrder]"

Set rec = db.OpenRecordset("SELECT Thermal_WorkOrder , Thermal_MaterialNumber, Thermal_DateTime2, Thermal_Quantity2, Thermal_Operator2, Thermal_Reject2 FROM Log WHERE " & GCriteria)

rec.AddNew  ' CHANGED THIS TO UPDATE AND DID NOT CHANGE THE OUTCOME
rec("Thermal_DateTime2") = Me.Thermal_DateTime2
rec("Thermal_WorkOrder") = Me.Thermal_WorkOrder
rec("Thermal_MaterialNumber") = Me.Thermal_MaterialNumber
rec("Thermal_Quantity2") = Me.Thermal_Quantity2
rec("Thermal_Operator2") = Me.Thermal_Operator2
rec("Thermal_Reject2") = Me.Thermal_Reject2


rec.Update


Set rec = Nothing
Set db = Nothing


    'move to empty record
    DoCmd.GoToRecord Record:=acNext
    End If

 

[theDBguy]

Well, using rec.AddNew adds a new record to the table. To update an existing record, you need to use rec.Edit. However, make sure you are on the record you want to update, because you could be updating the wrong record with that approach.

 

[Gasman]

That would because of the .AddNew ? Clue is in the method name?
You would want to use .Edit ?

 

[MajP]

change Addnew to Edit.

 

[MajP]

Wow. Three simultaneous answers.

 

[Gasman]

Plus whatever you are using for the source would need a WHERE to locate the correct record.

Why not just have the controls bound?

Seems to be a lot of new members recently who want to do everything the hard way?

 

[Gasman]

Wow. Three simultaneous answers.

And I know I got it right, as it was confirmed by two experts

 

[NahualXl]

Plus whatever you are using for the source would need a WHERE to locate the correct record.

Why not just have the controls bound?

Seems to be a lot of new members recently who want to do everything the hard way?

one would think one is here not because one is a pro but a total newb, else would not be asking for all of your expertise indeed .. much appreciated always...
The issue is this there are 2 ppl at the area:

• either one of them can either start the log and/or edit the record created by the other person
• If the record exists we only want to add the new data without overwriting the existing data from previous person
• The query version of the attempt has the WHERE clause in it
• this was an old db attempt by someone else unfortunately I inherited it

As always I truly appreciate all your assistance and patience, thank you so much

 

[Gasman]

Well if you inherited it, and are unable to change it, which would be a fair rewrite, fair enough.
However people new to Access seem to think that they have to save data.?
I must admit, it was strange to me when I started using Access, as that is how other old DBs would work, and having bound data was strange.
However, once you get used to it, you are doing it the Access way (as @Pat Hartman would say )

 

[Pat Hartman]

• If the record exists we only want to add the new data without overwriting the existing data from previous person

There is nothing in the code that checks to see if a record exists. It always adds a new record.

There is no such thing as "adding data to an existing record". When you are talking about tables and data, adding means adding a NEW record. Updating means updating an EXISTING record. Sometimes the way we say things when we speak is not accurate in terms of what we need to do when programming. So if a record exists, think in terms of updating it. If it does not exist then we add it.

If you are thinking in terms of the update only filling in data fields that are "empty", that requires coding logic (which doesn't exist in the code you posted) and we can help with that if that is what you actually mean by the sentence I highlighted above.

And as someone else mentioned, you should probably be using a bound form anyway and not using DAO at all. When Novices use DAO instead of bound forms it is generally because they do not understand how form events work and do not know how to control whether or nao a record gets saved.