(AutoNumber) referenced in a default expression, no value?

[Guest]

=DatePart("yyyy",Forms!Attendees![Date Received]) & "-" &
DatePart("m",Forms!Attendees![Date Received]) & "-" &
DatePart("d",Forms!Attendees![Date Received]) & "-" & [RegistrationID]

This expression relies on the [RegistrationID] which is an autonumber field.
When I enter a new record, the (AutoNumber) does not populate in the field
until I have selected data. The above expression is contained in the default
value of a field and does not receive the (AutoNumber) information.

I just need the expression to increment by one number for each record.

 

[Rick Brandt]

=DatePart("yyyy",Forms!Attendees![Date Received]) & "-" &
DatePart("m",Forms!Attendees![Date Received]) & "-" &
DatePart("d",Forms!Attendees![Date Received]) & "-" & [RegistrationID]

This expression relies on the [RegistrationID] which is an autonumber
field. When I enter a new record, the (AutoNumber) does not populate
in the field until I have selected data. The above expression is
contained in the default value of a field and does not receive the
(AutoNumber) information.

I just need the expression to increment by one number for each record.

Would you be satisfied with a placeholder value until the AutoNumber is
assigned?

=Format(Forms!Attendees![Date Received],"yyyy-m-d-") &
Nz([RegistrationID],"xxxx")

 

[Guest]

Yes, this is helpful. =Format(Forms!Attendees![Date Received],"yyyy-m-d-") &

Nz([RegistrationID],"xxxx")

The ideal example number will be: 0607050001, 0607050002 (representing 2006,
July, 5th, 1stRecord of the Day) with the increment starting over each day.
Day two would be: 0607060001, 0607060002 .

=DatePart("yyyy",Forms!Attendees![Date Received]) & "-" &
DatePart("m",Forms!Attendees![Date Received]) & "-" &
DatePart("d",Forms!Attendees![Date Received]) & "-" & [RegistrationID]

This expression relies on the [RegistrationID] which is an autonumber
field. When I enter a new record, the (AutoNumber) does not populate
in the field until I have selected data. The above expression is
contained in the default value of a field and does not receive the
(AutoNumber) information.

I just need the expression to increment by one number for each record.

Would you be satisfied with a placeholder value until the AutoNumber is
assigned?

=Format(Forms!Attendees![Date Received],"yyyy-m-d-") &
Nz([RegistrationID],"xxxx")

 

[Rick Brandt]

Yes, this is helpful. =Format(Forms!Attendees![Date
Received],"yyyy-m-d-") &

Nz([RegistrationID],"xxxx")

The ideal example number will be: 0607050001, 0607050002
(representing 2006, July, 5th, 1stRecord of the Day) with the
increment starting over each day. Day two would be: 0607060001,
0607060002 .

Well yes, if you need the incrementing number to start over each day then you
definitely cannot use an AutoNumber. You need to use an Integer or Long Integer
and use an expression in your form for inserting records to determine the next
number by finding the highest existing number with today's date and adding one
to it.

=Nz(DMax("ID", "TableName", "[Date Received] = Date()"), 0) + 1

The above assumes that [Date Received] is a date value that does not include the
time.

If you use the above as a default value then it will only work for a single view
form with ONE user inserting new records. If you need to support multiple users
then you have to use a code event to assign the number (BeforeUpdate) and then
you will again not see the number until the record is saved.

 

[Guest]

Yes, this is a multi-user environmnet. Thanks for the help

Yes, this is helpful. =Format(Forms!Attendees![Date
Received],"yyyy-m-d-") &

Nz([RegistrationID],"xxxx")

The ideal example number will be: 0607050001, 0607050002
(representing 2006, July, 5th, 1stRecord of the Day) with the
increment starting over each day. Day two would be: 0607060001,
0607060002 .

Well yes, if you need the incrementing number to start over each day then you
definitely cannot use an AutoNumber. You need to use an Integer or Long Integer
and use an expression in your form for inserting records to determine the next
number by finding the highest existing number with today's date and adding one
to it.

=Nz(DMax("ID", "TableName", "[Date Received] = Date()"), 0) + 1

The above assumes that [Date Received] is a date value that does not include the
time.

If you use the above as a default value then it will only work for a single view
form with ONE user inserting new records. If you need to support multiple users
then you have to use a code event to assign the number (BeforeUpdate) and then
you will again not see the number until the record is saved.

 

[Guest]

It looks like I will have to use a code event for the environment
=Nz(DMax("ID", "TableName", "[Date Received] = Date()"), 0) + 1

Is that an Event Procedure in the BeforeUpdate field?

Yes, this is a multi-user environmnet. Thanks for the help

Yes, this is helpful. =Format(Forms!Attendees![Date
Received],"yyyy-m-d-") &
Nz([RegistrationID],"xxxx")

The ideal example number will be: 0607050001, 0607050002
(representing 2006, July, 5th, 1stRecord of the Day) with the
increment starting over each day. Day two would be: 0607060001,
0607060002 .

Well yes, if you need the incrementing number to start over each day then you
definitely cannot use an AutoNumber. You need to use an Integer or Long Integer
and use an expression in your form for inserting records to determine the next
number by finding the highest existing number with today's date and adding one
to it.

=Nz(DMax("ID", "TableName", "[Date Received] = Date()"), 0) + 1

The above assumes that [Date Received] is a date value that does not include the
time.

If you use the above as a default value then it will only work for a single view
form with ONE user inserting new records. If you need to support multiple users
then you have to use a code event to assign the number (BeforeUpdate) and then
you will again not see the number until the record is saved.

 

[Tim Ferguson]

Yes, this is a multi-user environmnet. Thanks for the help

I reckon the only way to allocate a new number in a multiuser setup is to
do it at the dbengine level:

' pick a number to start at
myNumber = DMax(etc) + 1

do while true
jetSQL = "INSERT INTO etc VALUES (" & myNumber & ", etc);"
db.Execute jetSQL, dbFailOnError

if err.number <> 0 Then
' insert failed, someone else must have grabbed the
' same number just before us
myNumber = myNumber + 1

else
' okay it worked, this number now belongs to us
exit do
end if
loop

' return myNumber to the calling procedure

You can then feed this number back into your form to pick up the empty
record.

Hope that helps


Tim F

 

[Guest]

The ideal example number will be: 0607050001, 0607050002

(representing 2006, July, 5th, 1stRecord of the Day) with the
increment starting over each day. Day two would be: 0607060001,
0607060002 .

What do you mean by the dbengine level? Is it a Event Procedure?

 

[Rick Brandt]

It looks like I will have to use a code event for the environment
=Nz(DMax("ID", "TableName", "[Date Received] = Date()"), 0) + 1

Is that an Event Procedure in the BeforeUpdate field?

On the Events tab of the form property sheet find the BeforeUpdate event
property. In that choose "[Event Procedure]" from the list of choices. Then
press the build button [...] to the right of the property box. That wil take
you to the VBA editor window.

The VBA editor window will be pre-populated with the two lines that mark the
beginning and end of the code procedure. Your DMax() code goes in between those
two lines.

 

[Guest]

My last post may not have been very clear and I really appreciate the help.
I'm relatively new to access so the locations in access to place code and
where to call functions vs. expressions vs. macros is new.

I want to try Tim's code but I'm lost as to how to approach the problem.

Restating the goal of creating a record code for a form entry:

 

[Tim Ferguson]

What do you mean by the dbengine level? Is it a Event Procedure?

It's a clumsy phrase, I'm afraid. It means interacting directly with the
table rather than hoping that the Access interface can help. The problem
is that if you read how many records there are, and then use that
information to guess what the next one will be, there is a chance that
another user will be doing exactly the same thing during the time gap, so
you get left with an illegal record number that has already been taken by
the other user. Or vice versa. The only way (that I know of) to avoid
this is to attempt to write a record with a suitable number and pick up
the error if it's already gone. If it has, you just repeat the attempt
with the next number.

My last post may not have been very clear and I really appreciate the
help. I'm relatively new to access so the locations in access to place
code and where to call functions vs. expressions vs. macros is new.

I want to try Tim's code but I'm lost as to how to approach the
problem.

I wondered about being more specific; but since I think that your plan to
have what is called an "intelligent key" is basically flawed, I figured
it was better not to. An "intelligent key", by the way, is not a good
thing: for a start it's not in First Normal Form, and for a second it
causes many kinds of insert and update difficulties. I would always vote
for having two (or more) fields in a compound primary key. If your users
like seeing a long 12-digit number, it's easy enough to display it like
that on reports and queries and forms.

In your place, I would have two fields: one for the date and one for the
serial number. In any case, the method is similar: you query the table to
see what the next number is likely to be and attempt to create a new
record with it. If that fails it's because another user has pipped you to
the post, so you simply try again with the next number up.

Hope that helps


Tim F

 

[Guest]

Hi Rick,
I'm still new to this so some clarity would really help. In trying the
=Nz(DMax("ID", "TableName", "[Date Received] = Date()"), 0) + 1

I'm not sure what the "ID" represents. Is that the ID of the field in the
"TableName"?

It looks like I will have to use a code event for the environment
=Nz(DMax("ID", "TableName", "[Date Received] = Date()"), 0) + 1

Is that an Event Procedure in the BeforeUpdate field?

Yes, this is a multi-user environmnet. Thanks for the help

Zanstemic wrote:
Yes, this is helpful. =Format(Forms!Attendees![Date
Received],"yyyy-m-d-") &
Nz([RegistrationID],"xxxx")

The ideal example number will be: 0607050001, 0607050002
(representing 2006, July, 5th, 1stRecord of the Day) with the
increment starting over each day. Day two would be: 0607060001,
0607060002 .

Well yes, if you need the incrementing number to start over each day then you
definitely cannot use an AutoNumber. You need to use an Integer or Long Integer
and use an expression in your form for inserting records to determine the next
number by finding the highest existing number with today's date and adding one
to it.

=Nz(DMax("ID", "TableName", "[Date Received] = Date()"), 0) + 1

The above assumes that [Date Received] is a date value that does not include the
time.

If you use the above as a default value then it will only work for a single view
form with ONE user inserting new records. If you need to support multiple users
then you have to use a code event to assign the number (BeforeUpdate) and then
you will again not see the number until the record is saved.

 

[Rick Brandt]

Hi Rick,
I'm still new to this so some clarity would really help. In trying the
=Nz(DMax("ID", "TableName", "[Date Received] = Date()"), 0) + 1

I'm not sure what the "ID" represents. Is that the ID of the field in
the "TableName"?

It was a generic example. Replace that with the name of your field that you are
incrementing.

 

[Tim Ferguson]

It's been a while and making progress.

I see that I am in the references list of this post, but I don't have any
clue what it's about. After a long delay, it's usually a good idea to
start a new thread and explain the context anew.

when I updated some tables

That could mean almost anything; you'll need to explain what you've done
to break this particular application.

The problem is that the number needs to start over from 1 each day and
currently is continuing to increment even when the day changes.
This code is the default for a "CPSAutoNumber" field in the active
"Participant" form

=Nz(DMax(" [CPSAutoNumber]","Attendees","[RegistrationDate]
=Date()"),0)+1

There's a space at the front of the " [CPSAutonumber]" which may or may
not be significant... probably not.

"Attedees" is a table

[CPSAutoNumber] is a field in the "Participant" form and it is also a
field in the Attendees Table which I'm anticipating is poor practice.

A thing on a form is a control, not a field. Fields only exist in tables
and recordsets.

By the way, please reassure me that CPSAutonumber is not an autonumber...

[RegistrationDate] is also both a field in a form an table

I'm wondering how come the criterion

"[RegistrationDate] =Date()"

is set to true for all values... if you've changed or done something to
the RegistrationDate field then it could be looking at the
RegistrationDate control instead. That's one of the reasons why it's good
practice to name controls differently from the fields they are bound to.
Try changing the control to something like "txtRegistrationDate" and see
if you get a more helpful error message.

Another approach would be to make the field reference completely
qualified:

"Attendees.RegistrationDate = DATE()"

or even

"Attendees.RegistrationDate = " & format(Date(),"\#yyyy\-mm\-dd\#")

Apart from that it's a bit of a puzzle...


Best of luck


Tim F