Update table based on textboxes data (1 Viewer)

NahualXl

New member
Local time
Today, 10:26
Joined
Sep 23, 2021
Messages
19
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:

Code:
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
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 07:26
Joined
Oct 29, 2018
Messages
21,357
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

Enthusiastic Amateur
Local time
Today, 14:26
Joined
Sep 21, 2011
Messages
14,044
That would because of the .AddNew ? :unsure: Clue is in the method name?
You would want to use .Edit ?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:26
Joined
May 21, 2018
Messages
8,463
change Addnew to Edit.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:26
Joined
May 21, 2018
Messages
8,463
Wow. Three simultaneous answers.
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:26
Joined
Sep 21, 2011
Messages
14,044
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? :(
 

NahualXl

New member
Local time
Today, 10:26
Joined
Sep 23, 2021
Messages
19
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
 

Attachments

  • Capture.PNG
    Capture.PNG
    37.5 KB · Views: 237

Gasman

Enthusiastic Amateur
Local time
Today, 14:26
Joined
Sep 21, 2011
Messages
14,044
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 :D )
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:26
Joined
Feb 19, 2002
Messages
42,970
  • 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.
 

Users who are viewing this thread

Top Bottom