Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

On click event with SQL Query

Status
Not open for further replies.

cac5

Technical User
Oct 17, 2006
9
US
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. :eek:)
 
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
 
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?
 
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
 
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?
 
... & "# 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
 
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

 
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
 
It works!!! Thanks so much!!

CAC
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top