Putting Calc'ed values into table - further clarification

[biggles]

Yes I have asked this before, but I have looked and
thought about this enough that all I want is an answer and
want to move on to something else. Here is the code that
is giving me an object required error. I have the source
of all the fields in the form as calced values, I want to
send those to the table for other uses later, and done
when the user closes the form.

Private Sub Closeform_Click()
On Error GoTo Err_Closeform_Click

Dim panels As Long
Dim yards_req As Long

Set rst = tblTREAT_PRICING.OPENRECORDSET

rst!intPANELS = panels
rst!intYARDS_REQ = yards_req

rst.update
rst.close

DoCmd.close

Exit_Closeform_Click:
Exit Sub

Err_Closeform_Click:
MsgBox Err.Description
Resume Exit_Closeform_Click

End Sub


Any ideas? And what does the error mean.

 

[Dirk Goldgar]

Yes I have asked this before, but I have looked and
thought about this enough that all I want is an answer and
want to move on to something else. Here is the code that
is giving me an object required error. I have the source
of all the fields in the form as calced values, I want to
send those to the table for other uses later, and done
when the user closes the form.

Private Sub Closeform_Click()
On Error GoTo Err_Closeform_Click

Dim panels As Long
Dim yards_req As Long

Set rst = tblTREAT_PRICING.OPENRECORDSET

rst!intPANELS = panels
rst!intYARDS_REQ = yards_req

rst.update
rst.close

DoCmd.close

Exit_Closeform_Click:
Exit Sub

Err_Closeform_Click:
MsgBox Err.Description
Resume Exit_Closeform_Click

End Sub


Any ideas? And what does the error mean.

Okay, I see you've made up your mind about this. There are major
problems in the code you posted. I suspect the specfic error you're
getting comes either because rst is not declared (and thus is not
defined as an object type) or because tblTREAT_PRICING is not declared
as anything that possessed an OpenRecordset method. It's possible that
rst and tblTREAT_PRICING are declared elsewhere, at the module or global
level, but I'm guessing they aren't. So your code needs to be revised
to open a proper recordset object, or possibly to save your calculated
values by executing an update query instead.

Can I take it that "tblTREAT_PRICING" is the name of a table in your
database? Is this a table that is only and always going to contain one
record? If not, you have no code to identify which record you are going
to update, so you probably won't get the results you want. Will the
record already exist, in which case you want to update it, or will you
have to create the record? Or does the code have to figure out which is
the case?

 

[Biggles]

Oh Man, I suck. When I was writing this out, I did a Dim
as adodb.recordset. I limit the record on the form using
a where clause in the docmd.openform. Do I need to limit
it again to the specific record in my openrecordset?


Thanks

 

[Dirk Goldgar]

Oh Man, I suck. When I was writing this out, I did a Dim
as adodb.recordset. I limit the record on the form using
a where clause in the docmd.openform. Do I need to limit
it again to the specific record in my openrecordset?

Next time please post the actual code, not a summary; then we wouldn't
be working at cross-purposes.

Yes, you do need to limit the recordset or the update query, whichever
you use. But I'm not clear on what you're doing. Are you trying to
update the same record you currently are showing on the form, or is it a
related record? If you're updating the exact same record you're
currently looking at on the form, you don't need all this malarky --
just include the fields to be updated in the form's recordsource query,
and set them directly with code like:

Me.intPANELS = panels
Me.intYARDS_REQ = yards_req

If that's not the case, then we do need to identify the record to be
updated and do something like what you're attempting, but it might be as
simple as this:

CurrentDb.Execute _
"UPDATE tblTREAT_PRICING " & _
"SET intPANELS = " & panels & _
", intYARDS_REQ = " & yards_req & _
" WHERE " & Me.Filter, _
dbFailOnError

Or it might not.

 

[Biggles]

At the risk of continiuning to sound dumb as a post, I am going to pursue.
Your first assumption is correct, I am trying to update the same record as
displayed. The form is based off the table. The fields I need to update are
already in the table and have a matching field in the form. However, I had to
use the Control Source for the formula, instead of the field on the table.
Should I go ahead and change the fields to the table and do the actual
calculations in VB (using an after update, for example)? Then I could say:

[panel] = x + y

With panel being form field name. And that would be automatically stored
in the table then, right?

I am thinking a little, just not a lot.
Sean

 

[Dirk Goldgar]

At the risk of continiuning to sound dumb as a post, I am going to
pursue. Your first assumption is correct, I am trying to update the
same record as displayed. The form is based off the table. The
fields I need to update are already in the table and have a matching
field in the form. However, I had to use the Control Source for the
formula, instead of the field on the table. Should I go ahead and
change the fields to the table and do the actual calculations in VB
(using an after update, for example)? Then I could say:

[panel] = x + y

With panel being form field name. And that would be automatically
stored
in the table then, right?

I am thinking a little, just not a lot.

Keep it up, it'll grow on you!

Knowing what I know now, here's how I would do it. I would leave the
calculated controls on the form set up so that their formulas would do
the calculation for me. I would change the names of those controls, if
necessary, so that they aren't the same as the names of the fields in
the table. Suppose that the controls are named "txtPanels" and
"txtYards_Req", and that you want to store the values they calculate
into fields (in the table) named "Panels" and "Yards_Req", respectively.
Then I would set an event procedure for the form's BeforeUpdate event
like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.Panels = Me!txtPanels
Me.Yards_Req = Me!txtYards_Req

End Sub

And that should be all that's necessary. Of course, you have to
substitute your own names for the controls and fields.

 

[Biggles]

Thanks for your help - but I have a small question, you
mentioned using the BeforeUpdate event - what does this
do? I guess my question is Before what update, is the
record not updated until a new record is created or form
is closed?

 

[Biggles]

I think I did as you suggest, but missed one thing,
because I still get an Object Required error box when I
close the form. What am I still missing? The names on
the left are from the table, the ones on the right are
from the Form. Do I need to declare something?

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.intPANELS = Me!PANELS
Me.intYARDS_REQ = Me!YARDS_REQ
Me.intTOTAL_COST = Me!TOTAL_COST
Me.intTOTAL_SALES = Me!TOTAL_SALES

End Sub

 

[Dirk Goldgar]

Thanks for your help - but I have a small question, you
mentioned using the BeforeUpdate event - what does this
do? I guess my question is Before what update, is the
record not updated until a new record is created or form
is closed?

There are two kinds of BeforeUpdate events: a control's BeforeUpdate
event, and a form's BeforeUpdate event. The one for a control fires
before the value of a control is updated by user action, while the one
for a form fires before any changes to the current record are saved in
the underlying table(s). We're talking here about the form's
BeforeUpdate event. If any of the actual record data (as opposed to
unbound controls or calculated fields) are changed in any way, then
various user actions -- including moving to a new record or closing the
form -- can cause the record to be saved. Before the record is actually
saved, though, the form's BeforeUpdate event fires, allowing you to make
last-minute changes to the record, or even cancel the update entirely.

 

[Dirk Goldgar]

I think I did as you suggest, but missed one thing,
because I still get an Object Required error box when I
close the form. What am I still missing? The names on
the left are from the table, the ones on the right are
from the Form. Do I need to declare something?

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.intPANELS = Me!PANELS
Me.intYARDS_REQ = Me!YARDS_REQ
Me.intTOTAL_COST = Me!TOTAL_COST
Me.intTOTAL_SALES = Me!TOTAL_SALES

End Sub

That looks right to me, unless there's something you haven't told me.
My best guess at this point is that the error is being raised from some
other bit of code. If you have the form's code module open in the VB
Editor and click Debug -> Compile, do you get a compile error, and is
any line highlighted as being the source of the error?

 

[Biggles]

I got it, finally

-----Original Message-----


That looks right to me, unless there's something you haven't told me.
My best guess at this point is that the error is being raised from some
other bit of code. If you have the form's code module open in the VB
Editor and click Debug -> Compile, do you get a compile error, and is
any line highlighted as being the source of the error?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.