?
Solved

How can I fix my Combined Update/Append Query

Posted on 2009-04-16
12
Medium Priority
?
292 Views
Last Modified: 2013-11-28
I've posted the code I'm using behind a cmd_save_click() event where based on whether I'm adding a record or just updating one my function either appends or updates a record accordingly.  I am getting some weird (funky) errors when I test however.  I'll list them below here, what I would like to know is if these are b/c of my code or somthing else I've done wrong.

"write conflict
this record has been changed by another user since you started editing it.  If you save the record you will overwrite the other changes made. ..
with save record, add to clipboard and drop changes as the buttons at the bottom" - this is what happens when I am attempting to update a record after I have done an update once in my form already

"Runtime Error 3022
The changes you have requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the field or fields that contain duplicate data, remove the index or redefine the index to allow duplicates"  this one occurs when I first try and append a new record to my table.

The weirdest part is that even WITH the records, nine times out of ten the record does get written to the table anyway....  

Let me know what you think!

Cheers
Private Sub cmd_save_Click()
 
 If Me.AllowAdditions = True Then
    
 CurrentDb.Execute "INSERT INTO Players_t ( JerseyNumber, PlayerName, Weight, PositionF ) VALUES (" & _
     Forms!frm_playerinfo!txt_JerseyNumber & ", '" & Forms!frm_playerinfo!txt_PlayerName & "', " & _
     Forms!frm_playerinfo!txt_Weight & ", '" & Forms!frm_playerinfo!cbo_PositionF & "')"
 
 Else
 
CurrentDb.Execute "UPDATE Players_t SET JerseyNumber = " & Forms!frm_playerinfo!txt_JerseyNumber & " , PlayerName = '" & _
    Forms!frm_playerinfo!txt_PlayerName & "', Weight = " & Forms!frm_playerinfo!txt_Weight & ", PositionF = '" & _
    Forms!frm_playerinfo!cbo_PositionF & "' WHERE JerseyNumber = " & Forms!frm_playerinfo!txt_JerseyNumber & " "
    
 End If
 
 Me.Dirty = False
 
End Sub

Open in new window

0
Comment
Question by:Sully143
  • 7
  • 5
12 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24165399
Is this a bounded or unbounded form?

if bounded then at the very least before you update the underlying recordsource, you would save changes first (me.dirty line). If unboundd then no reason to do me.dirty

do u keep an autonumber in this table? if so is it jersey number? if thats the cas then it shouldnt even be updated or inserted
0
 

Author Comment

by:Sully143
ID: 24167024
I use the me.dirty = false just because I have code behind my cancel and and undo buttons that enable them 'on dirty', b/c I was having problems with running my 'save' and then clicking undo or cancel and my form going back to how it looked before I 'saved'...  so I just disabled them that way.

and no, no autonumbers.

How would I go about 'saving' my updates and appends like you said?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24167123
so is this bounded or unbounded
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:Sully143
ID: 24167784
Oh I'm sorry, they are bound
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24167910
if the form is based on Players_t then poerofrm dirty = false first
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24167915
oh wow, cryptic!

perform not poerofrm
0
 

Author Comment

by:Sully143
ID: 24167923
it is based on players_t.  Ok I can do that.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24167944
How did you generate the jersey number?

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24167970
to force existing changes, performing a me.dirty = false will save them

if form based on players_t then there is no need to issue a update

if you are doing an insert then again there is no reason to perform this insert

only required if form is unbounded
0
 

Author Comment

by:Sully143
ID: 24167987
so I don't need my append and update queries if I just throw the me.dirty = false at the top of the sub b/c my form is bounded?
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 24168090
yes thats right

best practice is to do this

if me.dirty = true then me.dirty = fa;se

so if changes have been made then save those changes


For any forms which are unbounded or where you want to insert to a different table to the one that the form is using then you create sql.

your code explains the errors you are getting
regarding update, you have changed that record then you issue some sql to update it, so its like two updates happening
same with insert, you have inserted a record then you try to save those changes again so end up with number error


0
 

Author Closing Comment

by:Sully143
ID: 31571313
That was wicked helpful.  Thanks a lot!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question