Using a calculated Field instead of AutoNumber

[Isis]

I am having all sorts of problems with AutoNumbered fields - they seem to
have their own method of deciding their value and I am not in control
enough so.....

I want to calculate my own - I have change the UID field in my Table from
AutoNumber to number - I have some code on my Form in the 'On Open' method
like this;
Me.UID = "SELECT * FROM Whiteboard"
Me.UID = Me.UID + 1
but the compiler says I can't assign a value to this object !

Any ideas of how to do this would be really useful.

Thanks

 

[Rick Brandt]

I am having all sorts of problems with AutoNumbered fields - they
seem to have their own method of deciding their value and I am not in
control enough so.....

I want to calculate my own - I have change the UID field in my Table
from AutoNumber to number - I have some code on my Form in the 'On
Open' method like this;
Me.UID = "SELECT * FROM Whiteboard"
Me.UID = Me.UID + 1
but the compiler says I can't assign a value to this object !

Any ideas of how to do this would be really useful.

Thanks

You cannot assign the result of a SQL statement to a control like that. In
fact what you are doing is setting a number field to a string since your
code has no way of knowing that your string is a SQL statement. It is
merely a string of characters.

What you want is...

Me.UID = DMax("UID", "WhiteBoard") + 1

For the very first record (no max to find) you modify that to...

Me.UID = Nz(DMax("UID", "WhiteBoard"),0) + 1

The Open event would only work for one record each time you open the form.
If you need to add more than one then you should use an event that fires as
each record is created. This would be BeforeInsert or BeforeUpdate.
BeforeInsert would behave more like AutoNumber in that you would see the
value as soon as you dirty the record with the keyboard. However that event
is not 100% guaranteed to fire when creating new records and you would have
collissions more than one user is adding records at the same time. So that
leaves BeforeUpdate.

BeforeUpdate's main issue is that a record can be updated numerous times and
you need the number to be assigned only when the record is first created.
So you have to test for that...

If Me.NewRecord Then
Me.UID = Nz(DMax("UID", "WhiteBoard"),0) + 1
End If

 

[John Spencer]

Me.UID = "SELECT * FROM Whiteboard"
Me.UID = Me.UID + 1

Line 1 attempts to set UID to a string "SELECT * FROM WhiteBoard"
Line 2 attempts to set UID to a number value.

What are you attempting to do? If you want to get the Maximum value of UID
and increment it by 1 then the following may be the expression you are looking
for.

Me.UID = NZ(DMAX("UID","WhiteBoard")) + 1

I also would not do this in the Open event as that is probably too early. I
would use the Load event or the Current event. Actually, now that I think
about this, I would probably wait until the record was dirtied and use Before
Insert or After Insert to do this.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Isis]

Rick and John,

Thanks very much for the replies - very illuminating - I think I can see
the way forward from here.

Thanks

 

[Isis]

Rick,

How do I tell whether this is the first record or not ?

Thanks

 

[Rick Brandt]

Rick,

How do I tell whether this is the first record or not ?

You might not even need that. If you set up a DMax()+1 strategy for a table
the very first record you insert will have DMax() return a Null. Null + 1 =
Null so that doesn't work. Once you have one record in your table the Nz()
accomplishes exactly nothing from that point on, but is harmless.

In a table that was repeatedly being purged and re-populated the DMax()
would be a requirement for sure or in some cases you might restart your
numbering (per day, per year), and again you would need the Nz() wrapper for
the first record on a given day (or given year, etc.).