Assigning a number

[scra]

I have a command button name "New Issue" on a form. When I click on that command button, I get a clean form to enter new data. Within that form, there is a field named Issue Number. I would like to assign that field an automatic fill-in number (i.e. last issue number is 35..would like 36 to be the next number and so on) when the "new issue" button is clicked. The form is populating a table. I can't change the issue number field to an autonumber data type because there is already a primary key and have been attached as a relationship to other queries and tables.

Do you have a code to share to populate and auto number?

 

[MazeWorX]

you could simply look up the last entry and add 1 to it

Something like

Private Sub Form_Open(Cancel As Integer)
Dim rs As DAO.Recordset
Dim db As DAO.Database

On Error GoTo ErrHandle

Set db = CurrentDb()
Set rs = db.OpenRecordset("NameofYourTable")

rs.MoveFirst
rs.MoveLast

Me.FormFieldName.Value = rs!TableFieldName + 1

ErrHandle:
'Handle a table with no records
If Err.Number = 3021 Then
Me.FormFieldName.Value = 1
Else
'Do something else
End If

End Sub

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[scra]

Thanks, Maze. Now, the other question is...where do I place the code you provided?

 

[MaxieMax]

scra,

Place the code in the form load event.... that way when you open a new blank form it will populate with your new issue number.

hope that helps

Max

 

[dhookom]

You might want to be carefull if this is a multiuser application. This solution might create duplicate issue numbers.

There are other methods documented on the web and possibly the FAQs here for generating sequential numbers.

Duane
Hook'D on Access
MS Access MVP

 

[scra]

Max, I'm getting this compile error which hi-lights the IssueAssignedNum.

Private Sub Form_Open(Cancel As Integer)
Dim rs As DAO.Recordset
Dim db As DAO.Database

On Error GoTo ErrHandle

Set db = CurrentDb()
Set rs = db.OpenRecordset("Issues")

rs.MoveFirst
rs.MoveLast

Me.FormFieldName.Value = rs!IssueAssignedNum + 1

ErrHandle:
'Handle a table with no records
If Err.Number = 3021 Then
Me.FormFieldName.Value = 1
Else
'Do something else
End If

End Sub

 

[dhookom]

[tt]FormFieldName[/tt] should be the name of the control on your form.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

You might want to review the small sample database on Rogers Access Library.

This sample illustrates a number of ways to simulate your own Autonumber primary key.
Form1 illustrates how to use the DMax function to simulate the action of the Autonumber field.

Form2 does much the same as the Products form, but allows for multi-user collisions.

Duane
Hook'D on Access
MS Access MVP

 

[scra]

I'm still getting a compile error on this...

Me.FormFieldName.Value = rs!IssueAssignedNum + 1

Please help.

 

[TheAceMan1]

How are ya scra . . .

Typically [blue]DMax[/blue] is used for something like this:

[blue]   Me!TextboxName = DMax("[IssueAssignedNum]", "Issues") + 1[/blue]

Note: if there's any sorting in the table then the last record is not necessarily the most currently added. [blue]DMax[/blue] is independent of sorting.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MaxieMax]

Scra.....

You will always get that message if you use
Me.FormFieldName.Value = rs!IssueAssignedNum + 1

you need to replace FormFieldname with your actual name of the field so for example if your field on your form is named ISSUENUMBERFIELD then

Me.FormFieldName.Value = rs!IssueAssignedNum + 1

will become

Me.ISSUENUMBERFIELD.Value = rs!IssueAssignedNum + 1

hope this helps

M

 

[scra]

Thanks so much who contributed to this post. You Rock!

 

[scra]

It's me again! What is the correct code to have the form go to a blank form? Currently the form is loaded showing previous records into the form.

Private Sub Command44_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database

On Error GoTo ErrHandle

Set db = CurrentDb()
Set rs = db.OpenRecordset("Issues")

rs.MoveFirst
rs.MoveLast

Me.IssueAssignedNum.Value = rs!IssueAssignedNum + 1

ErrHandle:
'Handle a table with no records
If Err.Number = 3021 Then
Me.IssueAssignedNum.Value = 1
Else
'Do something else
End If

End Sub

 

[dhookom]

Use the command button wizard to write the code to go to a new record (not a new form). You can then cut and paste the code into your other code.

Duane
Hook'D on Access
MS Access MVP