asked on

How can I fix my Combined Update/Append Query

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

rockiroads

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

Sully143

ASKER

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?

rockiroads

so is this bounded or unbounded

Sully143

ASKER

Oh I'm sorry, they are bound

rockiroads

if the form is based on Players_t then poerofrm dirty = false first

rockiroads

oh wow, cryptic!

perform not poerofrm

Sully143

ASKER

it is based on players_t. Ok I can do that.

rockiroads

How did you generate the jersey number?

rockiroads

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

Sully143

ASKER

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?

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sully143

ASKER

That was wicked helpful. Thanks a lot!