Q. I want to store a calculated field in a table

[Jim Jones]

Hi.

People have warned against this, but I don't see the danger.

I have one field which when displayed on the form works fine, but I
want to save the result of the calculated field in the table.

I don't wish to use a query, as my pop up form is based on a table.
I don't like queries, find them quirky, and avoid them when I can.

How do I get the underlying table to store the result of the form's
query ?

Thanks,
Jim

 

[Andrew Smith]

Use a query - it will be much simpler than any other way. You may find them
quirky, but you won't get far in Access without them!

Once you've got the query you can use it as the data source for your form or
for anything else where you would use a table. Don't even think of doing it
by storing the value in the table.

Create a new query in design view, add your table, double click the "*" at
the top of the fields list to add all fields, then enter your expression in
a blank field cell. Then save the query. That's it, job done. The calcuated
result will be there whenever you need it - always correct, no code, nothing
else. What could be simpler?

 

[John Vinson]

I don't like queries, find them quirky, and avoid them when I can.

Do you use Excel?

You might as well say "I don't like cell calculations, find them
quirky, and avoid them when I can". Queries are ABSOLUTELY ESSENTIAL
to any Access application; an Access database without queries is
completely crippled.

That said... if you want to store this calculated field, realizing
that the data in the field will be WRONG as soon as any of the
underlying fields is changed, you can use the BeforeUpdate event of
your form to store it. Have two controls on your form, one with the
calculation, the other (which may be invisible) bound to the
redundant, soon to be incorrect field. In the BeforeUpdate event put
code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me!txtBoundControl = Me!txtCalcControl
End Sub

 

[Jim]

Do you use Excel?

Yes, in fact, I was pondering, just for kicks how I could simulate
this project entirely onto Excel, which could be done ( but I'm
light-years away from learning the code that well).

You might as well say "I don't like cell calculations, find them
quirky, and avoid them when I can". Queries are ABSOLUTELY ESSENTIAL
to any Access application; an Access database without queries is
completely crippled.

I agree, and I use them for Billing. But , this pop-up form you all
helped me with just needs that one calculated field. It will basically
take qty times price, to place the result in "ext.".

So "ext" is the only calculated field.

The reason I don't think any other data would likely change to throw
data off, is that the pop up form would be used only to:

Enter new data or
scroll through the records, if the user wants to.

That said... if you want to store this calculated field, realizing
that the data in the field will be WRONG as soon as any of the
underlying fields is changed, you can use the BeforeUpdate event of
your form to store it. Have two controls on your form, one with the
calculation, the other (which may be invisible) bound to the
redundant, soon to be incorrect field. In the BeforeUpdate event put
code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me!txtBoundControl = Me!txtCalcControl
End Sub

OK, I'll try that, and I'm sure it will work fine.

I do still have a "small" problem with the pop up form.
It works fine as far as connecting to the service form, but the pop up
form says (filtered) next to the navigation buttons. AND the next
record button acts also as a NEW record button.

Please advise on how I can return the standard next record button to
NOT create a new record in ServiceNotes (which is the the one side of
it's own primary key, autonum field).


Thanks,
Jim

 

[John Vinson]

I do still have a "small" problem with the pop up form.
It works fine as far as connecting to the service form, but the pop up
form says (filtered) next to the navigation buttons. AND the next
record button acts also as a NEW record button.

That's undoubtedly because you ARE filtering it (to the record linked
to the form that you're popping it up from). Could you post the VBA
code in the Click event of the button that opens this form?

Please advise on how I can return the standard next record button to
NOT create a new record in ServiceNotes (which is the the one side of
it's own primary key, autonum field).

Don't base the Form on the ServiceNotes table. I'm not sure just what
you're doing to get this behavior. What *IS* the RecordSource property
of this form?

 

[Jim Jones]

That's undoubtedly because you ARE filtering it (to the record linked
to the form that you're popping it up from). Could you post the VBA
code in the Click event of the button that opens this form?


Don't base the Form on the ServiceNotes table. I'm not sure just what
you're doing to get this behavior. What *IS* the RecordSource property
of this form?

Pop up form's OnCurrent event:

Private Sub Form_Current()
ServiceTicket =
Forms!CustomerData!CustomerCarData.Form!Service!ServiceTicket
Me.Dirty = False

End Sub



Pop up form's OnLoad event

Private Sub Form_Load()
ServiceTicket =
Forms!CustomerData!CustomerCarData.Form!Service!ServiceTicket
End Sub


The Record Source is "ServiceNotes" (table)


The Formula for the "ext" field is: =[Qty]*[Price]
I placed that expression right in the "ext" field, which is also the
Control Source

Thanks,
Jim

 

[John Vinson]

Pop up form's OnCurrent event:

Private Sub Form_Current()
ServiceTicket =
Forms!CustomerData!CustomerCarData.Form!Service!ServiceTicket
Me.Dirty = False

End Sub

The Current event fires whenever you move to a new record. Do you in
fact want to overwrite whatever is currently in ServiceTicket with
that from the other form? Suppose you want this form to have all
records visible - this code will overwrite the ServiceTicket in every
record that the user visits.

Pop up form's OnLoad event

Private Sub Form_Load()
ServiceTicket =
Forms!CustomerData!CustomerCarData.Form!Service!ServiceTicket
End Sub

Could you post the code which OPENS this form? Presumably it's on the
other form. THAT code is what's setting the Filter, I'm sure.

The Record Source is "ServiceNotes" (table)


The Formula for the "ext" field is: =[Qty]*[Price]
I placed that expression right in the "ext" field, which is also the
Control Source

This will calculate the Ext but will not store it in the table. That's
what I'd recommend, unless the price may change and you want to store
the Ext. If that is the case, use the Form's BeforeUpdate event to
transfer the calculated value to a (perhaps invisible) control bound
to the Table's Ext field.

 

[Jim Jones]

The Current event fires whenever you move to a new record. Do you in
fact want to overwrite whatever is currently in ServiceTicket with
that from the other form? Suppose you want this form to have all
records visible - this code will overwrite the ServiceTicket in every
record that the user visits.

John, the records in the pop-up form ServiceNotes displays the
current displayed serviceticket number on the underlying form.

Ok, as far as that goes, I can see the proper record for the proper
car in service. If it came in for belts, then decided it allso needed
a tune-up, for instance, it can be added in this pop-up form, for
however many service details need to be added.
That part is ok, it's just that the "next record" button wants to add
a new record.

I have now removed the primary key, and IT STILL does it !

Could you post the code which OPENS this form? Presumably it's on the
other form. THAT code is what's setting the Filter, I'm sure.

What opens the pop-up form is the botton's on click event; here's the
code for that:

Private Sub cmdServiceDetails_Click()
On Error GoTo Err_cmdServiceDetails_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Service Details"

'stLinkCriteria = "[ServiceTicket]=" &
Forms![CustomerData].[Service].Forms[ServiceTicket]
'stLinkCriteria = "ServiceTicket =
Forms!CustomerData!CustomerCarData!Service!ServiceTicket"
stLinkCriteria = "[ServiceTicket]=" & Me![ServiceTicket]

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdServiceDetails_Click:
Exit Sub

Err_cmdServiceDetails_Click:
MsgBox Err.Description
Resume Exit_cmdServiceDetails_Click

End Sub

The Record Source is "ServiceNotes" (table)


The Formula for the "ext" field is: =[Qty]*[Price]
I placed that expression right in the "ext" field, which is also the
Control Source

This will calculate the Ext but will not store it in the table. That's
what I'd recommend, unless the price may change and you want to store
the Ext. If that is the case, use the Form's BeforeUpdate event to
transfer the calculated value to a (perhaps invisible) control bound
to the Table's Ext field.