Access: append query gives me a violation error the first time only.

[celineusa]

Hi all,

I am having problems with an append query. Basically, here is how it is
structured:
StudentTbl: StudentID (Primary Key as a long integer autonumber), and
more fields...
ModifiedTbl: StudentID (as a long integer), DateModified (as a
date/time)

So on my Student form, I have a subform that contains the Modified form
that is automatically updated with the current date/time the student
was changed.

When I create a new student on the student form and I use the Save
button I created, it will save the new student info in the student
table and add the date/time it was created/modified in the ModifiedTbl
with an append query.

However, the first time I create a student and I save it, it will tell
me that it will append 1 row to the ModifiedTbl, but then that there is
1 key violation.
Now, if I modify that student and save it again, the append query will
work fine and append the studentID and DateModified to the ModifiedTbl.

When I create a query to verify why it would not work, before I save
the student, the studentID autonumber is empty in the query, but shows
on the form with a number...

Any suggestion why this would not work the first time, but only the
second time?

Thanks so much,
Celine

 

[Jeff L]

It sounds like your information on the Add student form has not been
saved or committed to the database. Before you run your query to
append to the Modified table, put Me.Refresh. That will save your
data. Just curious, but is there a reason that you have the modified
field as a whole separate table instead of part of the student table?

 

[celineusa]

Thanks for your reply.
I have to use a separate table to keep track of when a student has been
modified (the first date being the date it has been created).

Here are my lines in the before update:
Dim strSQL As String

If MsgBox("Changes have been made to this record." _
& vbCrLf & vbCrLf & "Do you want to save these changes?" _
, vbYesNo, "Changes Made...") = vbYes Then
DoCmd.Save
MsgBox [Forms]![students]![StudentID]
strSQL = "INSERT INTO [Students and Modified] ( StudentID,
Modified ) " & _
"SELECT " & [Forms]![students]![StudentID] & " AS SID,
Now() AS DateModifiedNow;"
DoCmd.RunSQL (strSQL)
Me![Students and Modified Subform].Form.Requery

Else
DoCmd.RunCommand acCmdUndo
End If


If I add a Me.Refresh, I get an error message, probably because of the
If.
Also, if I add a MsbBox strSQL right after the strSQL query, then I can
see the studentID number showing up... but it still won't add it to the
table the first time... only the second time I save it it will add it
to the table.

Any other suggestion?
Thanks,
Celine

 

[celineusa]

OK OK, I got it... instead of doing my append query in the before
update sub, I created an after update sub where I do that... and now it
works.

Thanks for your reply.
I have to use a separate table to keep track of when a student has been
modified (the first date being the date it has been created).

Here are my lines in the before update:
Dim strSQL As String

If MsgBox("Changes have been made to this record." _
& vbCrLf & vbCrLf & "Do you want to save these changes?" _
, vbYesNo, "Changes Made...") = vbYes Then
DoCmd.Save
MsgBox [Forms]![students]![StudentID]
strSQL = "INSERT INTO [Students and Modified] ( StudentID,
Modified ) " & _
"SELECT " & [Forms]![students]![StudentID] & " AS SID,
Now() AS DateModifiedNow;"
DoCmd.RunSQL (strSQL)
Me![Students and Modified Subform].Form.Requery

Else
DoCmd.RunCommand acCmdUndo
End If


If I add a Me.Refresh, I get an error message, probably because of the
If.
Also, if I add a MsbBox strSQL right after the strSQL query, then I can
see the studentID number showing up... but it still won't add it to the
table the first time... only the second time I save it it will add it
to the table.

Any other suggestion?
Thanks,
Celine

It sounds like your information on the Add student form has not been
saved or committed to the database. Before you run your query to
append to the Modified table, put Me.Refresh. That will save your
data. Just curious, but is there a reason that you have the modified
field as a whole separate table instead of part of the student table?