Using VB code to run append and update queries (Simple problem) (1 Viewer)

[PTRACER]

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:

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.

 

[Uncle Gizmo]

Study this example by Allen Browne, I believe it provides the functionality you are after.

 

[PTRACER]

I'm afraid someone will have to explain it to me literally because I have about...8 days experience with VB and SQL.

 

[Call_Me_Sam]

Putting it simply (and this is the way i work) is to first run a query where you look for the Repair ID

'declare variables
Dim myDAO as DAO.recordset
Dim mySQL as string

mySQL = "SELECT Profit FROM Profit WHERE (RepairID=" & [Forms]![Repair]![RepairID] & ")"

'Load a recordset with the results from a query

Set myDAO = CurrentDb.openrecordset(mysql)

'Check if there are any records in the temporary recordset

If myDAO.recordcount >0 then

'update query here

else

'append query here

end if

'don't forget to close recordset

myDAO.close
set myDAO = nothing

Others may argue my use of DAO over ADO, but we're moving to SQL Server or Oracle soon so i make do.

Hope this helps.

 

[stopher]

Personally I think you've got some fundamental issues in your table design. The idea that you've got to calculate profit and then store it in another table is wrong:

• In general you should not store calculated values. You should calculate them when needed using a query.
• That aside, if data is associated with, in your case, a repair, then the relational model should prevent duplication (assuming your have the right keys)

It might seem like the right thing to to to generate a nice neat separate table but this will continue to haunt you as you develop your model. What you need to do is design your tables in such a way that you can create queries to provide the profit and other reporting requirements.

Regards
Chris

 

[Call_Me_Sam]

I agree with Chris' thoughts, and i guess if profit is a calulated field then on the fly in a query is better than storing it... my bad i guess.

 

[PTRACER]

Personally I think you've got some fundamental issues in your table design. The idea that you've got to calculate profit and then store it in another table is wrong:

• In general you should not store calculated values. You should calculate them when needed using a query.
• That aside, if data is associated with, in your case, a repair, then the relational model should prevent duplication (assuming your have the right keys)

Hmmm, well, with regards to that, I have a "Repairs" table, a "Parts" table and a "Labour" table. RepairID is present as a foreign key in both "Parts" and "Labour".

If a customer needs a part for their computer, I go to the "Parts" subform and select the parts I want from a drop down menu (which is in turn linked to a lookup table.)

In the parts table, I store the price I paid for the part, the quantity and the sale price and I use an AfterUpdate property of the Sale text box to calculate the Net Profit.

I do a similar thing with the Labour table, except that's done by entering the hourly rate and the number of hours to calculate the total labour charge, although that's also stored in a seperate table.

I then have several queries which help me calculate the final total. The PartsTotal Query is setup as follows:

Labour total is calculated by the same method. I also have a TotalFuelCost which is calculated from values I input into a subform linked to the Visits table.

My Total Job Charge is calculated by this query:

And for my total profits, I have this query:

Trying to link the profit query up to the [DateCompleted] field of the Repair table simply does not work, because the Total Profits query is based on the results of other queries and the Criteria are all set up to look at the [Forms]![Repair]![RepairID] field.

If you've got any suggestions on how to do this better, I would be very grateful.

 

[stopher]

Your basic table setup seems sound (apologies for suggesting otherwise).

And your approach to getting the total profit is ok and I think you are almost there. The only thing I don't personally like is having serveral refences to [Forms]![Repair]![RepairID]. While this should stop your query working it just seems like overkill and means you don't get the best control over the query. You only need to use it in your final join query.

Trying to link the profit query up to the [DateCompleted] field of the Repair table simply does not work, because the Total Profits query is based on the results of other queries and the Criteria are all set up to look at the [Forms]![Repair]![RepairID] field.

I don't get this bit. Could you explain more. If I understand correctly then you enter a completed date so you can use this to list all your profit from completed repairs?

I've attached how I would do it. Pretty close to what you have done but the TotRepairCosts query gives you all the information you might need I think. You can add your criteria here to this query.

Note you can always resort to Dlookup as a blunt but precise tool for pulling data e.g. to a form or report.

hth
Chris

 

[PTRACER]

That is fantastic stopher, thank you so so much! I'm incredibly grateful. It took a tiny bit of tweaking here and there, but it works perfectly.

That obviously means I can get rid of my Export Profits button and the silly table that stores the data, and create a proper form to display the information. I can also get rid of a lot of "AfterUpdate" codes, because I used a lot of those to calculate totals (instead of using queries). I seemingly took the difficult path with that rather than the easy one.

With regards to this waffle...:

Trying to link the profit query up to the [DateCompleted] field of the Repair table simply does not work, because the Total Profits query is based on the results of other queries and the Criteria are all set up to look at the [Forms]![Repair]![RepairID] field.

...my problems were all caused by putting [Forms]![Repair]![RepairID] in the Criteria of the queries I used. I thought that was the only way of linking up the subforms, with the main form - I'd completely forgot about the Master and Child links properties.

 

[Rich]

Why is labour in a separate table from repairs?

 

[PTRACER]

To make it more complicated for myself, naturally

No, it's because there may be multiple labour charges assigned to a job, for example - a standard Windows re-installation is 2 hours at £25 an hour. A Windows installation with Data recovery is £65, which is 2 hours at £25 an hour, plus another £15 extra labour charge.

 

[Rich]

Very reasonable rates too

 

[PTRACER]

Thanks Now...need any computers repaired?

 

[Rich]

Well it's funny you should mention that................

 

[PTRACER]

Unfortunately, I'm having a major problem with these queries...

If a job has no parts assigned to it, then the queries return a blank result...Hence I get no totals. I have to insert a dummy record into each one of £0.00 just to get it to display.

Is there a way around this?

 

[ajetrumpet]

Unfortunately, I'm having a major problem with these queries...

Well, the title says it's a simple problem sir. Kinda misleading if you ask me...

 

[stopher]

Unfortunately, I'm having a major problem with these queries...

If a job has no parts assigned to it, then the queries return a blank result...Hence I get no totals. I have to insert a dummy record into each one of £0.00 just to get it to display.

Is there a way around this?

I'm not entirely sure I understand what you want. If there is no labour the TotRepairCosts returns a line with parts costs but no labour costs. If there are no parts then TotRepairCosts returns a line with labour costs but no parts costs. I don't see how else you would want to see it?

Chris

 

[Rich]

The parts should have a default value of 0, if you carry out work where no parts are used then that should be recorded as £0

 

[PTRACER]

I'm not entirely sure I understand what you want. If there is no labour the TotRepairCosts returns a line with parts costs but no labour costs. If there are no parts then TotRepairCosts returns a line with labour costs but no parts costs. I don't see how else you would want to see it?

Chris

Have a look at these screenshots...

The customer in the first screenshot purchased parts and was charged labour:


The second customer was charged labour, but purchased no parts...:


See the difference? And when I print off a Repair report for the second customer, the "Parts" subreport disappears entirely and the "Total Charge" subreport at the bottom of the page disappears entirely.

It's because the TotalJobCharge query is calculated off of the PartsTotal and LabourTotal queries. As there are no records in the "Parts" table for that particular RepairID, the query returns a blank result.

So, the question is, how can I get the query to see a Null value as £0.00 or do I have to enter a new, empty record into the Parts subform every time someone has a repair, but doesn't buy any parts?

 

[stopher]

So, the question is, how can I get the query to see a Null value as £0.00 or do I have to enter a new, empty record into the Parts subform every time someone has a repair, but doesn't buy any parts?

Just use the function NZ(myfield,0) in the final query. Let me know if this isn't clear and I'll repost the example.
Chris