Summing Text boxes in Form (apologies) 2

[WillShakespeare]

All,

I have doene a quick search, and I know a few people have had the same trouble I am having, but a solution is still forthcoming. I wonder if you could help?

I have a DB that stores Products and Jobs (and customers), and on my Job form is a subform for Products. It all works well except that the price on the Job form is actually worked out by multiplying the price field for the product by the Area (or Perimeter, using IIF) field in Jobs.

The price for each record is perfect in the box assigned. However, when I try to SUM the totals from this text box on each record, I get an error.

As far as I can tell, this is because my textbox has a calculation in it. This is fine, but I need to figure this out, and quick!

I tried adding a hidden text box that is simply set as "=[txtPrice]" from the form, and then the total box is "=Sum([txtTempPrice])" but again withthe error.

I have tried adding the IIF nto te sum, and that also doesn't seem to work.

The probelm is the price on the form i not from a DB field, else I could just sum the fields. Plus, I cannot see an easy way to add a new colum to any of the tables to hold the price for a specific job, because its for the products, which each is different for each job!

Any help would be appeciated.

If I could do this with a query or VBA code, fine, but I am fairly new to Access so not sure where to put any code. I have some requery code in one ofmy text boxes (not to d with summing), so have written some code, but that was in a specific AfterUpdate() function.

Please help! I am at a loose end!!

Will

 

[MajP]

It is actually probably easier to put the functions in the query of the subform as:

Select
Job_Prod_Link.Job_Prod_Link_ID
...
getAreaOrPerim([Unit_Type],[Products].[Product_ID],[Jobs].[Job_ID]) AS AreaOrPerim,
getPrice([Unit_type],[Products].[product_ID],[Jobs].[job_ID],[price],[discount]) AS DiscountPrice
....

now in the subform you can bind these two values
and then your calculated control is simply

=Sum([Discountprice])

See Demo

http://www.4shared.com/document/q-VXfa68/MajPStaff_DB_V1.html

 

[MajP]

Also if you use those functions in a query you want to make sure to make them bullet proof so that you do not throw an error without handling it. If not you the code breaks for each row of the query.

 

[WillShakespeare]

Wow, thanks MajP. This all looks straight forward.

My plan with using the IIFs was just to get a quick solution. I am guessing you had a quick look at the work in progress, and the reason there are a few tex boxes on the subform was because I was experimenting with different ways until I could find a solution.

I plan to hve a go at your qury/function solution a little later on today, but one thing that still bothers me.

The final =Sum(getPrice([unit_Type],[Job_Prod_Link_Product_ID],[Job_ID],[price],[Discount]))

Where does this go? Do I put it in the Footer as I wish to? Will it basically sum up each time that function is called in that way? I think I am confused because I see this:

Record Area/Per Price (Function run once)
Record Area/Per Price (Function run twice)
Record Area/Per Price (Thrice)

Total "=Sum(function)" Run once

Because I am not naming a field or control I can't see how the Sum Function works. It doesn't seem logical.

For example, if I knew the Price was in a column called NewPrice, and at the bottom of the table I Sum(NewPrice), the Sum function can use the NewPrice handle to access each of the fields in that column.

Maybe I'm just looking at it wrong.

Like I said, I will try again today. And also even if it doesn't work (I am sure it will) I really appreciate your efforts here! You are awesome!

Will

 

[WillShakespeare]

Yes!!!

I till can't "see" how the Sum works here but not with the IIf, but it does and that's all I need.

Thanks so very much.

I jut need to do one for Discount now and I should be good to go.

Thx, Maj!

Will

 

[WillShakespeare]

MajP,

I discovered a little problem, which may be ralted to your warning about Error handling. When I run the Jobs form, if I add "Fitting" (ProdID 6) as a job line to an existing order it throws an Error 94, not able to handle Null correctly.

Here is the code it highlights (the Case "sq m" line) in Debug:

Public Function getAreaOrPerim(areaOrPerimType As Variant, prodID As Variant, jobID As Variant) As Single
    If Not (IsNull(areaOrPerim) Or IsNull(prodID) Or IsNull(jobID)) Then
        Select Case areaOrPerimType
            Case "m"
                getAreaOrPerim = DLookup("Perimeter", "qryAreaOrPerimeter", "Job_ID =" & jobID & " AND product_ID = " & prodID)
            Case "sq m"
                getAreaOrPerim = DLookup("Area", "qryAreaOrPerimeter", "Job_ID =" & jobID & " AND product_ID = " & prodID)
        End Select
    End If
End Function

Any idea how I can avoid this? Annoyingly if I add Fitting to an order that already has Fitting, then there is no error! It just seems to bethat first job record.

I have updated the Staff_DB.zip link above to DL the new version that should be complete apart from this minor error handling.

Can I use On Error Resume? Or could I take out the IsNull parts of the funtion (seeing as I plan to handle data entry validation on these fields as not allowed to be Null)?

Will

 

[PHV]

getAreaOrPerim = [!]Nz([/!]DLookup(.....)[!], 0)[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Issue also discussed here:
thread702-1606364

 

[WillShakespeare]

Awesome, thanks!

By the way, does anyone have any suggestions how I could handle Fitting option on the main form?

You see Fitting is one of the Products in the Job lines. What I would like to do is if the person entering the Job data selects Fitting as a product, then the "Fitting?" checkbox or texbox (I am supposed to display Yes or No) will automatically change, rather than having to select "fitting?" and remeber you must also select Fitting as a product (as you currently must do).

This is sloppy as is because if you forget to change the checkbox and dd the product, then they won't match.

Currently in order to add the total Fitting charge of £20 to the footer of the sub form I am using the following:

Public Function getFitting(prodID As Variant, jobID As Variant)
    If Not (IsNull(prodID) Or IsNull(jobID)) Then
        
        If prodID = 6 Then
            getFitting = 20
        Else
            getFitting = 0
        End If
    End If
End Function

And then Summing 20 plus all the zeros like I did for total, using a hidden text box on each line. Only because I am low on time, else I woulda found a more efficient method. I do the same for Discount (see the Form in the link above).

Anyway, I was thinking maybe I could add some code to the above function where it sets getFitting = 20. Perhaps just before this something like:

Me.FittingControl.Check

I know this code wouldn't work in the public function, but surely ther is some syntax tha would do the same thing?

Any ideas?

Will

 

[WillShakespeare]

Okay,

I am having a go at inserting "updateFitting(JobID)" before Fitting value is set at 20.

The function is here:

Public Function updateFitting(JobID As Variant)

   Dim strSql As String, Db As dao.Database

   strSql = "UPDATE Jobs SET Jobs.Fitting=True WHERE (([Jobs].Job_ID)=JobID);"

   Set Db = CurrentDb()
   
   Db.Execute strSql

   Set Db = Nothing

End Function

Theproblem is when it comes to "Db.Execute strSQL" I am getting a "too few parameters" error.

However, I opened up the query builder and pasted the query as is, and it ran fine...

Am I leaving something out?

Will

 

[WillShakespeare]

Of course I just realised that fitting would be checked, but if Ftting is removed from the order later on, I would need to update again to False... but how would I do that?

sigh...

Anyay, if any ideas, pleaselet me know. It's only a minor problem now.

Will

 

[kjv1611]

Do you need multiple conditions in your WHERE clause perhaps? I've not read the whole thing yet, but what differentiates between when Fitting should be True or False?

 

[MajP]

I am having a hard time following everything. But at a mininum, this can not work:

strSql = "UPDATE Jobs SET Jobs.Fitting=True WHERE (([Jobs].Job_ID)=JobID);

StrSql = "UPDATE Jobs SET Jobs.Fitting=True WHERE [Jobs].Job_ID = " & JobID

before ever running a sql string from code do this:

debug.print strSql

and verify you get the correct string. You would see clearly you do not get what you want.

 

[PHV]

strSql = "UPDATE Jobs SET Jobs.Fitting=True WHERE (([Jobs].Job_ID)=[!]" & Nz([/!]JobID[!], 0) & "[/!]);"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WillShakespeare]

Thanks Guys, that makes sense. I needed to put JobID outside of the string, or the value doesn't work. doh!

Anyway, as mentioned before I don't know how to do the reverse, or test for deleting fitting from the job.

Honetly though I am just trying to make it work nicely. It's not a requirement.

One last requirement before I am finished, however is a quick Validation that's causing me grief.

For the Table Jobs I have both Job_Date and Fitting_Date columns. Both are Date/Time.

Job_date can be any date (for example the job might be taken when the "system is down" or some such and entered at a later date, but adding Job_Date as the date of the phone call.

The Validation I need is on Fittig_Date whic must be AFTER Job_Date.

I have tried:

> Date() (Which doesn't wrk unless you have Job date as today - in most cases this would be fine, but given the scenario above, I need something that works all the time)
> [Job_Date] (Gives a SQL error)
> CDate([Job_Date]) (Gives a SL error)

I'm going to have a search for a solution, but if you happen to read this before I find one and post, brill and thanks!

Will

 

[MajP]

You can use this on the form to ensure a valid fitting date in the before update event. Or you could take out the msgboxes and use it in a query to show if the date is valid.

Public Function isValidFittingDate(jobID As Variant, fittingDate As Variant) As Boolean
Dim JobDate As Date
If Not (IsNull(fittingDate) Or IsNull(jobID)) Then
JobDate = Nz(DLookup("job_Date", "Jobs", "Job_ID = " & jobID), 0)
If JobDate = 0 Then
MsgBox "Can not add a Fitting Date until after adding a job date"
ElseIf JobDate > fittingDate Then
MsgBox "Fitting date must be after job date"
Else
isValidFittingDate = True
End If
End If
End Function

 

[WillShakespeare]

I was justadding this to test, but I am not sure what to put in Before_Update.

Do I simply call the Function? Will JobID and FittingDate be available to the Before_Update Sub?

I had thought last night that I should use the Form, and have tried a couple of things, but this certainly seems to cover the bases.

Thanks in advance for the clarification.

Will

 

[MajP]

validation of a value entered in a control is often done at the before update event. This event happens once the data is entered, but before it is committed to the underlying table. This event can be canceled which then stops the data from being committed. So the normal construct is something like

Private sub yourControl_BeforeUpdate(cancel as integer)
test something here
if test fails then
docmd.cancelevent
'so the data does not get committed
probably a message box saying the user needs to do
something
end if
end sub

so in your case something like this on the form

private Sub fitting_dateControl_beforeUpdate(cancel as integer)
'Check valid date and cancel if not
if not isvalidFittingDate(me.Job_ID,me.fitting_Date) Then
docmd.cancelEvent
end if
end sub

 

[MajP]

Also there is a before update event for a control and for the form.
For a control it happens when you go from one field in a record to another field or another record.
For the form event it happens when you go to another record, and does not happen when leaving the control and going to another control in the same record.

 

[MajP]

This demo shows the form level validation.

Now you can make a similar function for Job_Date and do the same thing to verify the Job_date is before the fitting date.

http://www.4shared.com/document/FvPmwKpb/MajPStaff_DB_V2.html

 

[WillShakespeare]

Cool,thanks. I knew how the events worked, just wasn't sure how to pass Job_ID, etc. and to handle the cancelling part in the Sub. Your reponse showed this perfectly.

Will