Update unique number

[Guest]

I have created a unique number on my Form. The name of the field on the table
is ‘BNSBatchno’ and is an auto number.

On the Form I have put the following expression into a text box:
='06' & 'A' & Format([BNSBatchno],"0")

This gives an output of:
06A(auto number)

The first two digits represent the month (06 = June) and the letter
represents the year:

A = 2007
B = 2008
C = 2009
Etc.

I was wondering if it is possible to reset the autonumber back to ‘0’
automatically every month?

Also if possible can the month number change to the appropriate month (if
not this can be done by myself manually).

Help would be greatly appreciated.

 

[John W. Vinson]

I have created a unique number on my Form. The name of the field on the table
is ‘BNSBatchno’ and is an auto number.

On the Form I have put the following expression into a text box:
='06' & 'A' & Format([BNSBatchno],"0")

This gives an output of:
06A(auto number)

The first two digits represent the month (06 = June) and the letter
represents the year:

A = 2007
B = 2008
C = 2009
Etc.

I was wondering if it is possible to reset the autonumber back to ‘0’
automatically every month?

Also if possible can the month number change to the appropriate month (if
not this can be done by myself manually).

Help would be greatly appreciated.

An Autonumber won't work for what you want. It is not designed for human
consumption - it cannot be edited, it cannot be started over as you require,
it will develop gaps.

Instead, you can use a Text field for the year and month, and an integer
number field in place of the autonumber. You could make these two fields a
joint Primary Key - in table design, just ctrl-click both fields and click the
key icon.

In your Form's BeforeInsert event you can put code like

Me!BNSBatchDate = Format(Date(), "mmyyyy"

to fill in 062007; and

Me!BNSBatchNo = NZ(DMax("[BNSBatchNo]", "[tablename]", _
"[BNSBatchDate] = '" & Me!BNSBatchDate & "'")) + 1

to look up the largest BatchNo for the current month (0 if there is none), and
add one to that value.

John W. Vinson [MVP]

 

[Guest]

I have attempted this but it is not working as the 'BNSBatchDate' text box on
the Form shows 0 and the 'BNSBatchNo' is blank. Also does anyone else know a
solution to this problem?
--
Thank you for your help

I have created a unique number on my Form. The name of the field on the table
is ‘BNSBatchno’ and is an auto number.

On the Form I have put the following expression into a text box:
='06' & 'A' & Format([BNSBatchno],"0")

This gives an output of:
06A(auto number)

The first two digits represent the month (06 = June) and the letter
represents the year:

A = 2007
B = 2008
C = 2009
Etc.

I was wondering if it is possible to reset the autonumber back to ‘0’
automatically every month?

Also if possible can the month number change to the appropriate month (if
not this can be done by myself manually).

Help would be greatly appreciated.

An Autonumber won't work for what you want. It is not designed for human
consumption - it cannot be edited, it cannot be started over as you require,
it will develop gaps.

Instead, you can use a Text field for the year and month, and an integer
number field in place of the autonumber. You could make these two fields a
joint Primary Key - in table design, just ctrl-click both fields and click the
key icon.

In your Form's BeforeInsert event you can put code like

Me!BNSBatchDate = Format(Date(), "mmyyyy"

to fill in 062007; and

Me!BNSBatchNo = NZ(DMax("[BNSBatchNo]", "[tablename]", _
"[BNSBatchDate] = '" & Me!BNSBatchDate & "'")) + 1

to look up the largest BatchNo for the current month (0 if there is none), and
add one to that value.

John W. Vinson [MVP]

 

[John W. Vinson]

I have attempted this but it is not working as the 'BNSBatchDate' text box on
the Form shows 0 and the 'BNSBatchNo' is blank. Also does anyone else know a
solution to this problem?

Please post your code, the names of your tables and the relevant fields, and
the name of the control and the form. If it's a subform that affects the code
too.

John W. Vinson [MVP]

 

[Guest]

Tables:
Form Details
ProductDeatils

Fields:
BNSBatchDate
BNSBatchNo

The Form has the control source of the ProductDetails table and within this
Form there is a sub form that uses the Form Details table. The sub form
contains the two fields mentioned above. The code is something you suggested
from before, used in the BeforeInsert event

Me!BNSBatchDate = Format(Date(), "mmyyyy"

‘to fill in 062007; and

Me!BNSBatchNo = NZ(DMax("[BNSBatchNo]", "[Form Details]", _
"[BNSBatchDate] = '" & Me!BNSBatchDate & "'")) + 1

 

[John W. Vinson]

ables:
Form Details
ProductDeatils

Fields:
BNSBatchDate
BNSBatchNo

The Form has the control source of the ProductDetails table and within this
Form there is a sub form that uses the Form Details table. The sub form
contains the two fields mentioned above. The code is something you suggested
from before, used in the BeforeInsert event

Me!BNSBatchDate = Format(Date(), "mmyyyy"

‘to fill in 062007; and

Me!BNSBatchNo = NZ(DMax("[BNSBatchNo]", "[Form Details]", _
"[BNSBatchDate] = '" & Me!BNSBatchDate & "'")) + 1

So is it working??

John W. Vinson [MVP]

 

[Guest]

Hi

This is not working. Maybe due to the fact it has a subform?
--
Thank you for your help

ables:
Form Details
ProductDeatils

Fields:
BNSBatchDate
BNSBatchNo

The Form has the control source of the ProductDetails table and within this
Form there is a sub form that uses the Form Details table. The sub form
contains the two fields mentioned above. The code is something you suggested
from before, used in the BeforeInsert event

Me!BNSBatchDate = Format(Date(), "mmyyyy"

‘to fill in 062007; and

Me!BNSBatchNo = NZ(DMax("[BNSBatchNo]", "[Form Details]", _
"[BNSBatchDate] = '" & Me!BNSBatchDate & "'")) + 1

So is it working??

John W. Vinson [MVP]

 

[Guest]

Hi

This doesn't seem to be working. Maybe due to the fact it has a sub form?
--
Thank you for your help

ables:
Form Details
ProductDeatils

Fields:
BNSBatchDate
BNSBatchNo

The Form has the control source of the ProductDetails table and within this
Form there is a sub form that uses the Form Details table. The sub form
contains the two fields mentioned above. The code is something you suggested
from before, used in the BeforeInsert event

Me!BNSBatchDate = Format(Date(), "mmyyyy"

‘to fill in 062007; and

Me!BNSBatchNo = NZ(DMax("[BNSBatchNo]", "[Form Details]", _
"[BNSBatchDate] = '" & Me!BNSBatchDate & "'")) + 1

So is it working??

John W. Vinson [MVP]

 

[John W. Vinson]

Hi

This is not working. Maybe due to the fact it has a subform?
--

Perhaps. Please post the ENTIRE code, and indicate which controls are on the
mainform and which are on the subform. It might also help to know in what
manner it "isn't working" - do you get an error message? if so what? Incorrect
data? nothing at all?

John W. Vinson [MVP]