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

  • Thread starter Thread starter Jim Jones
  • Start date Start date
J

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
 
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?
 
I don't like queries, find them quirky, and avoid them when I can.
Click to expand...

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
 
Do you use Excel?
Click to expand...
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.
Click to expand...
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
Click to expand...

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
 
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.
Click to expand...

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).
Click to expand...

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?
 
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?
Click to expand...



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
 
Pop up form's OnCurrent event:

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

End Sub
Click to expand...

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
Click to expand...

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
Click to expand...

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.
 
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.
Click to expand...

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.
Click to expand...

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
Click to expand...

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.
Click to expand...
 
stLinkCriteria = "[ServiceTicket]=" & Me![ServiceTicket]

DoCmd.OpenForm stDocName, , , stLinkCriteria
Click to expand...

Ok. You're explicitly telling Access that you ONLY want to see records
in this form for the currently displayed ServiceTicket. There is
NOTHING in this code that will cause new records on that form to have
any particular value of ServiceTicket; just because you've popped up
the form from another form, Access doesn't automagically know to put
in a particular value!

Two suggestions:

- Use a Subform rather than a popup form and let Access take care of
it. This is the standard technique, it's easy to implement, it's user
friendly, and (if you put the Subform on a tab page) it doesn't take
up any more screen space than the popup.

- Or, pass the ServiceTicket in the OpenArgs operand of the OpenForm
method:

DoCmd.OpenForm stDocName, Filter:=stLinkCriteria, _
OpenArgs:=Me![ServiceTicket]

and then set the DefaultValue property of the service ticket control
in the popup form's Open event:

If Not IsNull(Me.OpenArgs) Then
Me!txtServiceTicket.DefaultValue = """" & Me.OpenArgs & """"
End If
 
stLinkCriteria = "[ServiceTicket]=" & Me![ServiceTicket]

DoCmd.OpenForm stDocName, , , stLinkCriteria
Click to expand...

Ok. You're explicitly telling Access that you ONLY want to see records
in this form for the currently displayed ServiceTicket. There is
NOTHING in this code that will cause new records on that form to have
any particular value of ServiceTicket; just because you've popped up
the form from another form, Access doesn't automagically know to put
in a particular value!
Click to expand...
John,

Yes, I know. Putting the value in the serviceticket field is not the
problem now. It does pop up with the currently displayed
serviceticket number I want to see.

I have "patched" things together for the little problem with creating
blank records whenever I hit the "next record" button. I fixed that by
creating a delete query, which works fine. That's how I will deal
with it. If I could just know how to run that query whenever that pop
up form is opened . . . .

Anyway, I will investigate the openargs, statement you've detailed for
me below.

Much appreciated.

I have a new little problem on a totally different database.
I've have posted it.

Jim
Two suggestions:

- Use a Subform rather than a popup form and let Access take care of
it. This is the standard technique, it's easy to implement, it's user
friendly, and (if you put the Subform on a tab page) it doesn't take
up any more screen space than the popup.

- Or, pass the ServiceTicket in the OpenArgs operand of the OpenForm
method:

DoCmd.OpenForm stDocName, Filter:=stLinkCriteria, _
OpenArgs:=Me![ServiceTicket]

and then set the DefaultValue property of the service ticket control
in the popup form's Open event:

If Not IsNull(Me.OpenArgs) Then
Me!txtServiceTicket.DefaultValue = """" & Me.OpenArgs & """"
End If
Click to expand...
 
Yes, I know. Putting the value in the serviceticket field is not the
problem now. It does pop up with the currently displayed
serviceticket number I want to see.
ok...

I have "patched" things together for the little problem with creating
blank records whenever I hit the "next record" button. I fixed that by
creating a delete query, which works fine. That's how I will deal
with it. If I could just know how to run that query whenever that pop
up form is opened . . . .
Click to expand...

You SHOULD NOT NEED TO DO THIS. Access will not create a blank record
by just hitting the "next record" button, unless you have some
erroneous code that is "dirtying" the record and then saving it. It
seems you do - in the Form's Current event (which fires when you move
to a new record), and also in the Load event, you're populating the ID
field. Don't! Use the Form's BeforeInsert event instead.
Anyway, I will investigate the openargs, statement you've detailed for
me below.
Click to expand...

Good luck.
 
You SHOULD NOT NEED TO DO THIS. Access will not create a blank record
by just hitting the "next record" button, unless you have some
erroneous code that is "dirtying" the record and then saving it. It
seems you do - in the Form's Current event (which fires when you move
to a new record), and also in the Load event, you're populating the ID
field. Don't! Use the Form's BeforeInsert event instead.
Click to expand...

OK John,

Here is the code that you say I don't need.
Do I need to remove both, and use the Befoer Insert Event instead?
Will doing so give me the effect I need, and populate the
serviceticket field on the pop-up form with the value on the subform
record?


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

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


Thanks,
Jim
 
Here is the code that you say I don't need.
Do I need to remove both, and use the Befoer Insert Event instead?
Will doing so give me the effect I need, and populate the
serviceticket field on the pop-up form with the value on the subform
record?


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

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

Remove both these routines. They (especially the Current) are what are
dirtying your record whenever you move to a new record.

Instead, put the ServiceTicket = line into the Form's BeforeInsert
event. It will now fire only when the user types something (anything)
into any other field on the Form.
 
Remove both these routines. They (especially the Current) are what are
dirtying your record whenever you move to a new record.

Instead, put the ServiceTicket = line into the Form's BeforeInsert
event. It will now fire only when the user types something (anything)
into any other field on the Form.
Click to expand...


John,

The BeforeInsert event seems to be the thing I've needed all along.
I truly appreciate knowing that, now.

Thanks,
Jim
 
Back
Top