Code to store a value in table where ID = X

[iProRyan]

Hi Guys.

Weird problem and not sure if this is posted in the correct section, but I'll do my best to explain it, so here goes...

I have my Jobs form which has a sub-form for all the different parts used on that job which then calculates the total charge and displays this on the main jobs form. Now how would I get this value to be stored in table tblJobs on the record where the ID = JobID on Jobs form. I need to do this to display the total on a report.

Looked around and couldn't find what I was looking for, is there a visual basic command to set it then I can use docmd.saverecord after or what would I need to do?

Thanks,
Ryan



Edit: Or is there an SQL statement to do this I could execute on a button click or something similar? Any ideas are greatly appreciated!

[June7]

Saving calculated data is usually a bad idea, especially aggregate values. Calculate the value when needed.

[iProRyan]

That's going to be quite tricky though I think. Basically the report shows a list of all unpaid invoices so the query powering it says Where tblJobs.Paid = No and tblJobs.Completed = Yes and has the customers details in it as well. All the Job Parts are stores in tblJobParts and if I was to add this table it would create so many duplicate records on this report, so how would I go about calculating it? Hoping I make sense!

[June7]

There are methods to accomplish that don't involve compromising data integrity. Saving aggregate value has risk of data getting 'out-of-sync'.

I would have to know more about data structure and the report design. Perhaps do an aggregate query of the tblJobParts and include that query in the report RecordSource. Perhaps a subreport. Perhaps DSum in textbox.

However, if you really must save the aggregate value and destination field is in the form's RecordSource, simply: Me.fieldname = Me.textboxname

The real trick is figuring out what event to put the code in. Maybe the form BeforeUpdate event.

[iProRyan]

Hi!

That works great, one slight issue I have is sometimes it says #Error (because there are no parts for that Job) which then gives an error message when saving the record using this code attached to a button, so I'm working on a work around and for someone such as myself gets really confused with VB access I'm not sure what's wrong, any ideas?

If Me.txtJobTotalPrice.Text = "#Error" Then
Me.TotalCharge = "0.00"
Else
Me.TotalCharge = Me.txtJobTotalPrice
End If
DoCmd.Save

[June7]

An If Then to prevent the insert is one approach.

If IsError(Me.txtJobTotalPrice) Then

However, I am not understanding why the #Error generates because there are no parts, should just be null.

[iProRyan]

Still can't get it to work, getting a few different errors, this is what I'm trying on the OnClick event of a button:

If Me.JobDate < Date Then
MsgBox ("Error! - Invalid Date!")
Else
If IsError(Me.txtJobTotalPrice) Then
Me.TotalCharge = "£0.00"
Else
Me.TotalCharge = Me.txtJobTotalPrice
DoCmd.Save
End If
End If

Any ideas? If not I have a work around although it's quite bad.

[June7]

Not seeing anything wrong with code. What are the error messages? Step debug. Refer to link at bottom of my post for debugging guidelines.

[iProRyan]

I had to take out the Me.JobDate < Date IF statement because it was causing errors with amending old records. Works perfect when creating new ones, anyway I can distinguish between the two?

Back on topic,
The error is: The value you entered isn't valid for this field

Could this be because it says #Error but then what does If IsError do?

I have a work around so if it's a pain, we'll leave it.

[June7]

I don't know. Not something I ever had to deal with. Can't work with Access until after 3/26. If you still need help after that, add a post to get my attention.