On click event with SQL Query

[cac5]

I have a form which is bound to a table. When there is a change on the record (there are about 10 fields including combo boxes) I want to move that record to a history table. I have figured out how to do it when a record is added but cannot figure out how to just add the changed record-it's adding every record in the table to the history table.

Here is the code I have so far.. I know I need to specify that changed record but I don't know how to do it.

DoCmd.RunSQL ("INSERT INTO tblIncidentHistory (IncidentNumber,IncidentDate,IncidentType,IncidentSeverity,IncidentRiskAssessment,IncidentResponsibility,IncidentDescription,IncidentStatus,DateResolved,DateReportedtoFDIC,DateCustomerCommunication,DateUpdated)

SELECT IncidentNumber,IncidentDate,IncidentType,IncidentSeverity,IncidentRiskAssessment,IncidentResponsibility,IncidentDescription,IncidentStatus,DateResolved,DateReportedtoFDIC,DateCustomerCommunication,#" & Now() & "# FROM tblIncident")

I'm a newbie at this so would appreciate any help. )

 

[PHV]

Add a WHERE clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cac5]

I figured that would be the way to do it. Do you have any suggestion on how to write it. Basically, any of the fields can change so how do I drill down to that in the code?

 

[PHV]

No PrimaryKey in tblIncident ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cac5]

Yes there is. And, when the record is added to the history table it gets assigned an auto number. I did this because the history table could have more than one record with the same incident number.

Is there a way to pull the current record primary key and tell it to add that record to the history table if changed?

 

[PHV]

... & "# FROM tblIncident WHERE [Name of PK field][tt]='"[/tt] & Me![Name of PK control] & [tt]"'"[/tt])
If the PK is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cac5]

Thanks for your help! The only issue I am having now is that I cannot reference a property or method unless the field has focus. How do I give the Me.IncidentID.Text field focus?

FROM tblIncident WHERE IncidentID=" & Me.IncidentID.Text

 

[PHV]

Use the Value property instead of Text:
FROM tblIncident WHERE IncidentID=" & Me.IncidentID.Value

Or, simpler, just like I suggested you:
FROM tblIncident WHERE IncidentID=" & Me!IncidentID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cac5]

It works!!! Thanks so much!!

CAC