I've got a database that stores data on customers and sales for my computer repair business and was wondering if anyone could help me re-write the following code to be more in line with what I need.
On my Repairs form, I have a tab containing around 5 subforms. These subforms are based on queries that total up the total cost of labour and parts for a particular repair job, plus the profit on each job.
When I click the "Export Profit" button, it runs the following code:
So you don't have to work it out, I have a seperate table entitled "Profits" and with the above code, I insert into it the RepairID, the profit from that repair, and the date that the repair was completed.
I then have another form that I can use to calculate profits in any given month.
The code above first runs an Append Query and sends the contents of the RepairID text box, the Profit textbox and the Date Completed textbox to the Profits table for storing. As the "RepairID" is indexed, you can only run the Append Query once else it completely mucks up the contents of the table.
Therefore, I've got an "On Error" statement which runs an Update query instead to update the Profits table where RepairID on my form is the same as RepairID in the table.
The question is, how do I make the code more failsafe and actually CHECK whether the RepairID exists in the Profits table, and then decide for itself whether to run an append query or an update query.
Thanks for any help you can give me.
On my Repairs form, I have a tab containing around 5 subforms. These subforms are based on queries that total up the total cost of labour and parts for a particular repair job, plus the profit on each job.
When I click the "Export Profit" button, it runs the following code:
Code:
Private Sub Command159_Click()
On Error GoTo Command159_ERR
DoCmd.SetWarnings True
DoCmd.RunSQL ("INSERT INTO Profit VALUES ([RepairID], [Forms]![Repair]![TotalProfit].[Form]![SumOfTotal], [DateCompleted])")
Exit_Command159:
MsgBox ("Data inserted into table correctly")
Exit Sub
Command159_ERR:
DoCmd.RunSQL ("UPDATE Profit SET Profit.Profit = [Forms]![Repair]![TotalProfit].[Form]![SumOfTotal] WHERE (((Profit.RepairID)=[Forms]![Repair]![RepairID]))")
MsgBox ("Record updated!")
End Sub
So you don't have to work it out, I have a seperate table entitled "Profits" and with the above code, I insert into it the RepairID, the profit from that repair, and the date that the repair was completed.
I then have another form that I can use to calculate profits in any given month.
The code above first runs an Append Query and sends the contents of the RepairID text box, the Profit textbox and the Date Completed textbox to the Profits table for storing. As the "RepairID" is indexed, you can only run the Append Query once else it completely mucks up the contents of the table.
Therefore, I've got an "On Error" statement which runs an Update query instead to update the Profits table where RepairID on my form is the same as RepairID in the table.
The question is, how do I make the code more failsafe and actually CHECK whether the RepairID exists in the Profits table, and then decide for itself whether to run an append query or an update query.
Thanks for any help you can give me.