Autonumber in a field

  • Thread starter Thread starter Jacques
  • Start date Start date
J

Jacques

Hello all...
How can I make a form do autonumbering without the table doing it?
I have an accounts receivables DB that has log numbers that need to be
auto incremented, but they also need to be able to add like letters to the
log number:

....for example

1532
1533
1534
1534A
1534B
1535
1536

Let me say thanks beforehand.
 
Hello all...
How can I make a form do autonumbering without the table doing it?
I have an accounts receivables DB that has log numbers that need to be
auto incremented, but they also need to be able to add like letters to the
log number:

...for example

1532
1533
1534
1534A
1534B
1535
1536

Let me say thanks beforehand.
Click to expand...

I'd separate the numbers and letters into two fields (which can be a
joint two-field Primary Key, and can be concatenated for display
purposes on a Form or in a Query).

How do you want the automatic incrementing to happen? If the last
record in the table is 1537C, what's the next record - 1537D or 1538?
Or what's the next record after 1549?

John W. Vinson[MVP]
 
In your example the next record would be 1538.


John Vinson said:
I'd separate the numbers and letters into two fields (which can be a
joint two-field Primary Key, and can be concatenated for display
purposes on a Form or in a Query).

How do you want the automatic incrementing to happen? If the last
record in the table is 1537C, what's the next record - 1537D or 1538?
Or what's the next record after 1549?

John W. Vinson[MVP]
Click to expand...
 
In your example the next record would be 1538.
Click to expand...

Then use a Form for your data entry; in the Form's BeforeInsert event
put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[your table]")) + 1
End Sub

where ID is the numeric field. You'll need to maintain the second
alpha field manually.

John W. Vinson[MVP]
 
My knowledge of VB is a little limited. I put the below in the BeforeInsert
property of the form itself. I assume you did mean that, as opposed to the
BeforeUpdate property of the field in the form.

Trans is the table name and LOG_NO is the field name of the table.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtLOG_NO = Nz(DMax("[ID]", "[Trans]")) + 1
End Sub

I get:

Run-time error '2001':
You canceled the previous operation.

Is my syntax correct?

John Vinson said:
In your example the next record would be 1538.
Click to expand...

Then use a Form for your data entry; in the Form's BeforeInsert event
put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[your table]")) + 1
End Sub

where ID is the numeric field. You'll need to maintain the second
alpha field manually.

John W. Vinson[MVP]
Click to expand...
 
The misleading error 2001 can be reported when your Domain functions (DAvg,
DLookup, DMax, etc.) have field or table names that don't exist in them.

If you're trying to get the maximum value for LOG_NO, you need to put that
field name in your DMax statement:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtLOG_NO = Nz(DMax("[LOG_NO]", "[Trans]")) + 1
End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jacques said:
My knowledge of VB is a little limited. I put the below in the BeforeInsert
property of the form itself. I assume you did mean that, as opposed to the
BeforeUpdate property of the field in the form.

Trans is the table name and LOG_NO is the field name of the table.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtLOG_NO = Nz(DMax("[ID]", "[Trans]")) + 1
End Sub

I get:

Run-time error '2001':
You canceled the previous operation.

Is my syntax correct?

John Vinson said:
In your example the next record would be 1538.
Click to expand...

Then use a Form for your data entry; in the Form's BeforeInsert event
put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[your table]")) + 1
End Sub

where ID is the numeric field. You'll need to maintain the second
alpha field manually.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
That's working well. I appreciate the help.



Douglas J Steele said:
The misleading error 2001 can be reported when your Domain functions
(DAvg,
DLookup, DMax, etc.) have field or table names that don't exist in them.

If you're trying to get the maximum value for LOG_NO, you need to put that
field name in your DMax statement:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtLOG_NO = Nz(DMax("[LOG_NO]", "[Trans]")) + 1
End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jacques said:
My knowledge of VB is a little limited. I put the below in the BeforeInsert
property of the form itself. I assume you did mean that, as opposed to the
BeforeUpdate property of the field in the form.

Trans is the table name and LOG_NO is the field name of the table.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtLOG_NO = Nz(DMax("[ID]", "[Trans]")) + 1
End Sub

I get:

Run-time error '2001':
You canceled the previous operation.

Is my syntax correct?

John Vinson said:
In your example the next record would be 1538.


Then use a Form for your data entry; in the Form's BeforeInsert event
put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[your table]")) + 1
End Sub

where ID is the numeric field. You'll need to maintain the second
alpha field manually.

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top