Problem Saving Data

[Ray Stantz]

I have a button setup on a helpdesk ticket form that should save the record in the original helpdesk table and then open an Append query to add the record to an audit trail table to keep track of when we've updated info. The issue is it sometimes saves the updated info in the append query/table and at other times it appends the old info to the audit table. I'm using the following code in the OnClick event:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 (to save the record first)
stDocName = "qryTicketsSentAppend"
DoCmd.OpenQuery stDocName, acNormal, acEdit (and then open the Append query to add the updated info to the audit table)

Does anyone know a way to fix this so that it consistently updates with the new info?

Thanks in advance!

Ray...

 

[boblarson]

I have a button setup on a helpdesk ticket form that should save the record in the original helpdesk table and then open an Append query to add the record to an audit trail table to keep track of when we've updated info. The issue is it sometimes saves the updated info in the append query/table and at other times it appends the old info to the audit table. I'm using the following code in the OnClick event:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 (to save the record first)
stDocName = "qryTicketsSentAppend"
DoCmd.OpenQuery stDocName, acNormal, acEdit (and then open the Append query to add the updated info to the audit table)

Does anyone know a way to fix this so that it consistently updates with the new info?

Thanks in advance!

Ray...

first of all, don't use the DoMenuItem code. It is out-of-date and should be replaced by DoCmd.RunCommand acCmdSaveRecord

second, what is the SQL of your append query and how does it know what the current record is?

 

[Ray Stantz]

first of all, don't use the DoMenuItem code. It is out-of-date and should be replaced by DoCmd.RunCommand acCmdSaveRecord

second, what is the SQL of your append query and how does it know what the current record is?

Thanks for the reply. The SQL for the query is

[Forms]![Helpdesk]![ID]

The ID field is indexed to not allow duplicates so it always grabs the right record but it does not always grab the updated data. I have found that if i close the form or navigate away from that specific record, it applies the updated data 100% of the time but if there is a situation where the user just changed data but realized they need to correct something and they remain on that same record and press the button again, it will not appeand with the updated info.

Also, i tried the DoCmd.RunCommand acCmdSaveRecord initially and it did not save the updated info at all so that's why i used the old code.

Ray...

 

[boblarson]

Thanks for the reply. The SQL for the query is

[Forms]![Helpdesk]![ID]

Umm, that isn't SQL -

The SQL for the query is available while in design mode of the query and going up to VIEW > SQL VIEW and then copying and pasting the code that looks like:

INSERT INTO...

 

[Ray Stantz]

Sorry about that...

INSERT INTO tblTicketSent ( lngTicketID, ID, lngHelpdeskEmployeeID, dteReceived, strAssignee, TicketRequestID, strSubjectLine, strComments, ExpectedCompletionDate, dteDateCompleted, strRequestedBy, BizDays )
SELECT tblHelpDeskTickets.lngTicketID, tblHelpDeskTickets.ID, tblHelpDeskTickets.lngHelpdeskEmployeeID, tblHelpDeskTickets.dteReceived, tblHelpDeskTickets.strAssignee, tblHelpDeskTickets.TicketRequestID, tblHelpDeskTickets.strSubjectLine, tblHelpDeskTickets.strComments, tblHelpDeskTickets.ExpectedCompletionDate, tblHelpDeskTickets.dteDateCompleted, tblHelpDeskTickets.strRequestedBy, tblHelpDeskTickets.BizDays
FROM tblHelpDeskTickets
WHERE (((tblHelpDeskTickets.lngTicketID)=[Forms]![frmHelpDeskTickets]![lngTicketID]) AND ((tblHelpDeskTickets.ID)=[Forms]![Contract profile]![ID]));