Set Textbox to Record Number

  • Thread starter Thread starter John Fanton
  • Start date Start date
J

John Fanton

I must be really getting slow in my old age. I've been trying to locate a
method either in Access, or if necessary in VBasic, to simply set the
default value of a textbox to the record number of the current record. In
other words, when presenting a user with an entry form for a new record, I'd
like to default a textbox to the record number of the record that will be
created.

Does anyone have a simple way of doing this? It can't be that hard!

Thanks
(e-mail address removed)
 
I must be really getting slow in my old age. I've been trying to locate a
method either in Access, or if necessary in VBasic, to simply set the
default value of a textbox to the record number of the current record. In
other words, when presenting a user with an entry form for a new record, I'd
like to default a textbox to the record number of the record that will be
created.
Click to expand...

Is the field defined in the table as an "autonumber" field? If it is, you may
want to consider not even displaying that field as it really shouldn't be
utilized as anything other than a unique record identifier mainly for linking
purposes (creating relationships between data tables). If this field is not an
autonumber field and needs to be filled in by the user, then set its "Control
Source" property to the pertinent field name by selecting it from the dropdown
list. Setting the default value of a control does not do anything to display the
value of a field in an *existing* record - it merely provides a value that,
unless modified, will be inserted into that field when a new record is first
created.
 
Does anyone have a simple way of doing this? It can't be that hard!
Click to expand...

It is, since Access tables don't HAVE "record numbers". Could you
explain what you mean in this context? an Autonumber value, or what?
 
John Fanton said:
I must be really getting slow in my old age. I've been trying to locate a
method either in Access, or if necessary in VBasic, to simply set the
default value of a textbox to the record number of the current record. In
other words, when presenting a user with an entry form for a new record, I'd
like to default a textbox to the record number of the record that will be
created.
Click to expand...

And what determines the record number? Do you mean the record count (the
25th record get number 25)?

If so, will only one person be using the database to add records? If more
than one then using the DefaultValue property to do this will cause lots of
problems because multiple users can move to the new record position at the
same time. In fact one user could move to the new record position, the
default value would be displayed and that user could let the form sit like
that indefinitely before they actually fill out the form and save the
record. Any other user that adds a record during that time will get the
same value as the first user and whoever saves last will get an error
(assuming you are not allowing duplicates).

In addition, the DefaultValue property doesn't work for this in a
continuous or datasheet form even if there is only one user. If you open
the form and it has 24 records currently moving to where the new record
position is will show that it will attempt to use number 25 for the next
new record. As soon as you begin typing in the new record position another
new record position will be displayed to take its place. Since you haven't
saved the record yet the new record position will again use the number 25
since there are only 24 records actually saved in the table.

What you can do is use the BeforeUpdate event of the form to calculate the
next value and apply it to the record. Since BeforeUpdate fires a split
second before the record is saved there is very little chance for two
people to grab the same value. The only caveat is that this event can fire
multiple times for a given record over its lifetime so you need an If-Then
block to insure the number is only assigned to records that don't already
have a value.

If IsNull(Me![RecNum]) = True Then
Me![RecNum] = Nz(DMax("RecNum", "YourTableName"), 0)+1
End If

DCount() could be used if records were NEVER deleted, but DMax() is safer
since it doesn't matter if the number sequence currently has gaps in it.
 
Bruce, correct me if I'm wrong, but as far as I know, the value for an
autonum field is not set until the row is saved. So there's no way to
predict it in a multi-user environment. In a single-user environment, you
could retrieve the highest current autonum value and add 1 I suppose but
that's not recommended.

The only way to predict a "next record number" is to write your own "next
record number" routine.

David Straker
 
David Straker said:
Bruce, correct me if I'm wrong, but as far as I know, the value for an
autonum field is not set until the row is saved.
Click to expand...

No, the AutoNumber is assigned as soon as you dirty the record. An
indefinite amount of time could pass after that before the record is saved.
That is at least if we are talking about a JET autonumber. If you link to
an ODBC table with an IDENTITY column (or similar) that that assignment
would happen when the record was saved.
 
John,

Based on Rick's insight, ... Personally I've never shown the user an autonum
field. However, the following code works (mess with the event procedure if
required - I've used the Exit event for a second field on the form/in the
table).

Private Sub Form_Dirty(Cancel As Integer)
If Me.NewRecord Then
If IsNumeric(Me.ParentID) Then
Else
Me.ParentID.Requery ' the autonum field/control
End If
End If
End Sub

Private Sub ParentKey_Exit()
' The second field in the table/control on the form
' This would have to be a mandatory field
MakeMeDirty
End Sub

Private Sub MakeMeDirty()
If Me.NewRecord Then
Me.Dirty = True
End If
End Sub

Thanks again Rick - I never knew you could do that.

David Straker
 
Back
Top