Default value "the previous value +1"

[toaster045]

Hey

Right i've got a number field that i've set up as the primary key. These are the properties

Basically as the title says - does anyone know anyway of having the default value that's the previous value +1?

 

[Lady Gragor]

Could this not be data type autonumber? What is it based on?

 

[toaster045]

Could this not be data type autonumber? What is it based on?

To be honest i thought about an autonumber but the trouble is if that record was deleted that number wouldn't be avalible and I was advised to move away from autonumber. And this field is just the ID for each customer in this bookings database.

 

[Mike375]

You will find a lot of posts/threads on this general topic asit is very common.

General advice is to use the Access autonumber field for the ID/Primary Key fields.

But if you want a separate number field that increments for each new record then DMax is one way.

If you only want the incrementing number to indicate the order in which records were entered you could use a Date field and have Now() as the default.

I think there is a recent thread on this topic that covered about everything.

 

[toaster045]

You will find a lot of posts/threads on this general topic asit is very common.
General advice is to use the Access autonumber field for the ID/Primary Key fields.
But if you want a separate number field that increments for each new record then DMax is one way.
If you only want the incrementing number to indicate the order in which records were entered you could use a Date field and have Now() as the default.
I think there is a recent thread on this topic that covered about everything.

I must admit I've seen alot of threads/posts on the auto-number issue but to be honest I've already made the relationships and most of the data based on the number field. So i might have a go at using the "DMax" option. The date isn't that critical so hopefully that might make it a little easier.
Browsing the net I found that DMax is:


DMax(expression, domain [criteria])

I'm guess the criteria would be the "Customer_No + 1" but I'm not sure about the expressions and domain.

 

[Mike375]

Try this

=DMax("[YourNumberField]","Table or Query Name")+1

 

[RuralGuy]

In the BeforeUpdate event of the form put:

If Me.NewRecord Then
   [Customer_No] = DMax("[Customer_No]","YourTable/QueryName") + 1
End If

Using your names of course. FYI: There is *no* order to a RecordSet in Access unless you give it one. Think of each table as a bucket of records. The db engine (Jet) hands them to you in what it considers the most efficient manner for speed.

 

[RuralGuy]

PMFJI you guys. Sorry.

 

[Mike375]

The attached DB has two macros, form and table. I use it for making a field full of numbers. You will see from macro2 you just put in whatever you want for the increment or a - to go backwards.

This might be of use if you add bulk records at any time. Also handy for numbering records after a sort. The label macro1 on the form runs it

 

[Mike375]

PMFJI you guys. Sorry.

You are Excused....

 

[toaster045]

Try this

=DMax("[YourNumberField]","Table or Query Name")+1

ok I tried this:

But when i went to save it and switch the view i got this :

i've checked the Table name and Field Name and they're identical

btw thx you for the example database and your contineuing help

 

[Mike375]

It does not work as a Default.

Put the expression in an unbound text box or use what RuralGuy posted earlier

If Me.NewRecord Then
[Customer_No] = DMax("[Customer_No]","YourTable/QueryName") + 1
End If

I personally prefer it to be in an unbound text box and the value of the textbox sets the value of the field. I just like seeing the number in the box

FieldName = TextboxName

 

[RuralGuy]

If it is a Multi-User system then creating the value in the small BeforeUpdate event window is important. You may still need to code for duplicate errors.

 

[toaster045]

If it is a Multi-User system then creating the value in the small BeforeUpdate event window is important. You may still need to code for duplicate errors.

Ok i did exactly as you said and copied the code with the modifications so it looks like this:

and then in the properties of the text box i made sure the event was there

However, whenever i switch to a new record i just get a blank box where as i kinda hoped it would show the next number (in my case 16 as i have 15 records). any ideas? or have i gone wrong somewhere in the code - i've never done that before

 

[boblarson]

However, whenever i switch to a new record i just get a blank box where as i kinda hoped it would show the next number (in my case 16 as i have 15 records). any ideas? or have i gone wrong somewhere in the code - i've never done that before

Since you should be adding that field in the form's BEFORE UPDATE event, it will not show up until the record is saved (which is what you want with multiple users so you run less risk of conflicts).

 

[RuralGuy]

I would put the code in the FORM's BeforeUpdate event as Bob talked about.

 

[RuralGuy]

BTW, creating a PrimaryKey and showing how many records are in the table are two different issues. Since you seem to want a continuous sequence of numbers then you are going to want the code in the FORM's BeforeUpdate event. If you want to display the current record number then look at this link.

 

[toaster045]

BTW, creating a PrimaryKey and showing how many records are in the table are two different issues. Since you seem to want a continuous sequence of numbers then you are going to want the code in the FORM's BeforeUpdate event. If you want to display the current record number then look at this link.

ok thxs for the help

the trouble i've got is i'm working in the primary key field and as a result everytime i try and save the record (which i understand will then generate the customer number (+1 on the previous)) i get:

is there anyway around this??

 

[RuralGuy]

Do you have the code I posted in the FORM's BeforeUpdate event? Can you post the code starting with Private and ending with End Sub?

 

[toaster045]

Do you have the code I posted in the FORM's BeforeUpdate event? Can you post the code starting with Private and ending with End Sub?

this is what i put in Microsoft Visual Basic after selecteing - properties - event - before update - code builder

Private Sub Customer_No_BeforeUpdate(Cancel As Integer)
= If Me.NewRecord Then
   [Customer_No] = DMax("[Customer_No]", "Customers") + 1
End If
End Sub