How do i...

[Pwyd]

I'd like to put the record number shown in the record selector ON the form
itself, on top of the form. In fact, i'd like to leave the record selector
alone, and simply show the record number the user is looking at, on the form
itself, so it may print out. How would i do this?

 

[Pwyd]

In what way is it insufficient?

You can set a text box's value to the form recordset's
AbsolutePosition property:

Sub Form_Current()
Me.textbox = Me.Recordset,AbsolutePosition +1


Note: that is insufficient on continuous and datasheet
forms.

 

[J_Goddard via AccessMonster.com]

Hi -

A word of caution to what Marshall suggested - the record number returned by
AbsolutePosition is not consistent - it is totally dependent on whichjrecords
are in the form's underlying recordset, and the order the records are
presented on the screen. So, if you sort the data on the form differently,
the records numbers change.

If you want to have some sort of permanent number associated with your
records, you should make that number a field in underlying table.

John

I'd like to put the record number shown in the record selector ON the form
itself, on top of the form. In fact, i'd like to leave the record selector
alone, and simply show the record number the user is looking at, on the form
itself, so it may print out. How would i do this?

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[Pwyd]

I've been told NOT to use an autonumber field for such purposes, that the
user should never see such a thing. What would you suggest?

 

[Marshall Barton]

I'd like to put the record number shown in the record selector ON the form
itself, on top of the form. In fact, i'd like to leave the record selector
alone, and simply show the record number the user is looking at, on the form
itself, so it may print out.

You can set a text box's value to the form recordset's
AbsolutePosition property:

Sub Form_Current()
Me.textbox = Me.Recordset,AbsolutePosition +1


Note: that is insufficient on continuous and datasheet
forms.

 

[J_Goddard via AccessMonster.com]

The record number does not have to be AutoNumber - it can just as easily be a
(usually) integer field which is defined as the primary key, so the user has
control over the values.

One way to set the number when entering new data might be to assign the
number when the record is created, for example in the On Current event of the
data form:

if me.newrecord then
me![record number] = nz(dmax("TableName","[Record Number]"),0) + 1
endif

which assigns a new record number to each new record, with the record number
being 1 higher than the current maximum.

If you use this technique, you will need a way to cancel (i.e. delete) the
new record if the user does not want the new record to be saved. A "Cancel"
button, with me.Undo in its On Click event would work.

John

I've been told NOT to use an autonumber field for such purposes, that the
user should never see such a thing. What would you suggest?

Hi -

[quoted text clipped - 13 lines]

 

[George Nicholson]

I've been told NOT to use an autonumber field for such purposes, that the

user should never see such a thing. What would you suggest?

Well, "rules" like "should never see" are made to be broken as long as you
have valid reasons to do so and know the consequences. I think "never" is
an overstatement myself. Id be more inclined to say "users rarely have any
need to see record ID numbers, and they can frequently be confusing, so you
are usually better off to hide ID numbers from them".

That said, *if* you feel the user needs to see some underlying unique,
persistent record number then go ahead and allow them to see the autonumber
field, or whatever you are using as the primary field. The Absolute
position property value, or the value shown in the navigation bar would
*not* be persistent. Any given record would show different values once you
changed the sort order or filtering.

This assumes you are not working with replication and random GUID primary
keys.

 

[Pwyd]

they do need a unique id -- its how the batch is referred to by each person
who needs to know where it is, if its processed, etc. What we have done in
the past is overwrite records that we ended up needing to "delete." or simply
filling out the fields that keep it on our status page so it would drop off,
and just using a new record. The problem is, in the past, we have used the
record navigation to find a record, because the numbers matched. Could i
replace the record navigator on the bottom to simply reflect the unique id
for that record? That seems like it would be a lot simpler than trying to
renumber or anything like that.

 

[Marshall Barton]

What I posted before does exactly what you asked, but maybe
you asked the wrong question?

I think you might be confusing two different things. The
record number in the nav buttons is only useful for the
form's current set of records. Different criteria, sort
order and other thing will result in any specific record
having a different number each time you view the records.
If you want each record to retain its number regardless of
all those things, then you need to have a field in your
table and use code in the form's BeforeUpdate event to set
the number:
Me.numberfield = Nz(DMax("numberfield", "table"), 0) + 1

In case you did ask your original question correctly and to
answer your current question, a form that displays multiple
records can not use VBA code to display different values in
an unbound text box. Instead, you need to create a function
to calculate the record number and use the function in a
text box. This can be slow if the form is used to display a
lot of records, but it will do what you originally asked:

Public Function GetRecNum(pk As ???)
With Me.RecordsetClone
.FindFirst "primarykeyfield=" & pk
GetRecNum = .AbsoluteRecord + 1
End With
End Function

With that function, the text box's expression would be:
=GetRecNum(primarykeyfield)

 

[Pwyd]

Chuckle. don't get your panties in a bunch. First of all, we don't do that,
unless its in the same week. Second, there are time stamps all over the
thing that show when it was assigned, completed, last modified, etc.

Thirdly, its not really that big of a deal. just an annoyance. I was simply
asking how it would be done, for my own knowledge, i wasn't asking to be
berated for my failings in not being an access expert.

 

[Pwyd]

Don't worry about it, i know passion when i read it. No harm done

Hi -

Sorry - I wasn't berating you - just giving some suggestions.

Put an unbound text box on the form to contain the number. Then, in the On
Current event of the form, you could put:

me![RecordNumberBox] = Me.Recordset,AbsolutePosition +1
(the +1 is used because AbsolutePosition is zero-based.)


Don't feel badly about not being an expert. Not one of here ever was to
start off, and becoming an "expert" (whatever that means!) just takes time,
given the relatively steep learning curve for MS Access.

John

Chuckle. don't get your panties in a bunch. First of all, we don't do that,
unless its in the same week. Second, there are time stamps all over the
thing that show when it was assigned, completed, last modified, etc.

Thirdly, its not really that big of a deal. just an annoyance. I was simply
asking how it would be done, for my own knowledge, i wasn't asking to be
berated for my failings in not being an access expert.