Calculate new fields prospectively

  • Thread starter Thread starter Anna
  • Start date Start date
A

Anna

Hi,

I would be grateful for any help:
I have a form bound to a table and have recently added two
fields to the table - one that calculates a number based
on OrderDate, and another that creates an "Order ID" no by
adding this to a customer ID field.

I have got a query to add these fields retrospectively,
but how can I get the form to calculate them(and put the
data in the table) as I enter the data in the form?

Many thanks,
Anna
 
I assume you want this for only a new record. Bind 2 textboxes on the form to these
fields. In the Form's BeforeUpdate event, if you are at a new record then calculate the
values and assign them to these controls.

If Me.NewRecord Then
'calculate here
Me.txtOrderID = some calculation
Me.txtOrderDate = Date
End If

There is no need to store the mix of the OrderID and the CustomerID, just store the
difference and calculate the other whenever you need it.
 
Hi Wayne,

Thanks for that - seems like what I need to know, but when
I tried it I got an error message: "Compile Error: Method
or data member not found" and it highlighted the "Private
Sub Form_BeforeUpdate(Cancel As Integer)" heading.

Here is the code as is:

If Me.NewRecord Then
Me.txtOrderDateNo = DateDiff("d", #1/1/00#, [Date
Order])
Me.txtOrderID = ([Customer No] + [OrderDateNo])
End If

Where OrderDateNo and OrderID are the two fields I want
calculating.

Any ideas?
Thanks for your help!

Anna
 
It should highlight the line that has the problem. The sub heading is autogenerated by
Access and shouldn't be a problem, it looks normal as long as there is a space between
"Private" and "Sub" (it wrapped in the message so I can't tell). If it insists on this
being the error, them I'm guessing the code module is corrupted.

Make a copy of the database and try to decompile the copy to see if that helps. To
decompile use a command line similar to this:

"<path>\msaccess.exe" "<path>\MyDatabase.mdb" /excl /decompile

The switches won't work if you only use the database name, you have to specify Access
also. Also, include any other switches you may normally need, such as user name or
password.

When it is open, do a Compact and Repair. After it reopens from that, try to compile.
 
Back
Top