Pop Up Form To Show Next Number

[hobbit2612 via AccessMonster.com]

Hi,

I wonder whether someone can help me please.

I have created a database which uses data from some Excel spreadsheets that
I've inherited from another work unit. One of the fields contained in the
data is a reference number which I know should, but doesn't follow a
chronological order.

What I would like, if possible is to have a pop up form that upon clicking a
button will appear showing the next number in the sequence, but to be honest
I'm really not sure where to start.

Can anyone help please?

Many thanks

Chris

 

[Jeff Boyce]

It sounds like you've decided on "how" you want to solve your issue ... are
you open to other ideas?

For example, what will you/your application do with the "next number"? If
you want to use it to add a sequence number to a record, why not let Access
do that for you?

In other words, will you ALWAYS be using the "next number" generated? If
so, let Access do it... If not, what else could happen?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[hobbit2612 via AccessMonster.com]

Hi Jeff, many thanka for replying.

I'm certainly open to new ideas, and I must admit I had thought about
tackling the problem in this way. I had intially dismissed this, but having
read your reply, I've had a rethink and I think it's the way to go.

Would it be possible please for you to give me a helping hand with this? I've
continued to research the problem and I think from what I've read the code
below is perhaps the correct method to use, but I'm not sure where to link
the code to, is it to the field or is it part of an 'Event procedure'?

DMax("ReferenceNumber", "tblJobs") + 1

Many thanks and regards

Chris

It sounds like you've decided on "how" you want to solve your issue ... are
you open to other ideas?

For example, what will you/your application do with the "next number"? If
you want to use it to add a sequence number to a record, why not let Access
do that for you?

In other words, will you ALWAYS be using the "next number" generated? If
so, let Access do it... If not, what else could happen?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

[quoted text clipped - 17 lines]

Chris

 

[Jeff Boyce]

Chris

Yes, that's a common code snippet to calculate the "next" sequence number.

Now, if you are working in a form (you ARE working in forms, not tables,
right?!), you could use that expression (or adapted as you need to for your
situation) to create a "sequence number" when the form starts a new record,
or before it saves an new record or ...

I've even used a variation on that to create/store a sequence number after
an entry is made in an <Insert a date in this> field. I used the
AfterUpdate event.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff, many thanka for replying.

I'm certainly open to new ideas, and I must admit I had thought about
tackling the problem in this way. I had intially dismissed this, but
having
read your reply, I've had a rethink and I think it's the way to go.

Would it be possible please for you to give me a helping hand with this?
I've
continued to research the problem and I think from what I've read the code
below is perhaps the correct method to use, but I'm not sure where to link
the code to, is it to the field or is it part of an 'Event procedure'?

DMax("ReferenceNumber", "tblJobs") + 1

Many thanks and regards

Chris

It sounds like you've decided on "how" you want to solve your issue ...
are
you open to other ideas?

For example, what will you/your application do with the "next number"? If
you want to use it to add a sequence number to a record, why not let
Access
do that for you?

In other words, will you ALWAYS be using the "next number" generated? If
so, let Access do it... If not, what else could happen?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

[quoted text clipped - 17 lines]

Chris

 

[BruceM]

To add a little to what Jeff said, using a control's Default Value is
another way to go. One advantage is that no record is created if you back
out of the new record without entering anything. Default value applies only
to new records. In any case you may want to guard against changing the
number if the record is updated after initially being saved.

Another consideration is a multi-user environment. Both of these points are
addressed in the sample found here:
http://www.rogersaccesslibrary.com/...?TID=395&SID=8c7e2e33bfdb53dzbz8e9f8157ee139e

Note that the link is all on one line. Alternatively, go to this link and
look for AutonumberProblem.mdb:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1

Hi Jeff, many thanka for replying.

I'm certainly open to new ideas, and I must admit I had thought about
tackling the problem in this way. I had intially dismissed this, but
having
read your reply, I've had a rethink and I think it's the way to go.

Would it be possible please for you to give me a helping hand with this?
I've
continued to research the problem and I think from what I've read the code
below is perhaps the correct method to use, but I'm not sure where to link
the code to, is it to the field or is it part of an 'Event procedure'?

DMax("ReferenceNumber", "tblJobs") + 1

Many thanks and regards

Chris

It sounds like you've decided on "how" you want to solve your issue ...
are
you open to other ideas?

For example, what will you/your application do with the "next number"? If
you want to use it to add a sequence number to a record, why not let
Access
do that for you?

In other words, will you ALWAYS be using the "next number" generated? If
so, let Access do it... If not, what else could happen?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

[quoted text clipped - 17 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

Bruce and Jeff

Both many thanks for taking the time to read and reply to my query.

I've been playing around with the database this afternoon and by adjusting
the code a little to:

Private Sub Form_Current()
If Me.NewRecord Then
Me.Fieldname=DMax("[Fieldname]","tablename")+1
End If
End Sub

I've got it to work.

But, unfortunately what I can't work out is how to get to it to look at the
table and give me the next number which hasn't been used, rather than the
next number in the sequence because the data I've inherited doesn't
necessarily follow on in sequential numbers.

Could either of you shed a little light on this please?

Many thanks and regards

Chris

To add a little to what Jeff said, using a control's Default Value is
another way to go. One advantage is that no record is created if you back
out of the new record without entering anything. Default value applies only
to new records. In any case you may want to guard against changing the
number if the record is updated after initially being saved.

Another consideration is a multi-user environment. Both of these points are
addressed in the sample found here:
http://www.rogersaccesslibrary.com/...?TID=395&SID=8c7e2e33bfdb53dzbz8e9f8157ee139e

Note that the link is all on one line. Alternatively, go to this link and
look for AutonumberProblem.mdb:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1

Hi Jeff, many thanka for replying.

[quoted text clipped - 37 lines]

 

[BruceM]

If you mean you want to fill in the gaps of the existing numbering, I have
to wonder why. I think it can be done, but I believe it is a task of
daunting complexity. I do not know how, but a Google groups search for
something like "Access sequence missing number" should turn up some
discussion.

Bruce and Jeff

Both many thanks for taking the time to read and reply to my query.

I've been playing around with the database this afternoon and by adjusting
the code a little to:

Private Sub Form_Current()
If Me.NewRecord Then
Me.Fieldname=DMax("[Fieldname]","tablename")+1
End If
End Sub

I've got it to work.

But, unfortunately what I can't work out is how to get to it to look at
the
table and give me the next number which hasn't been used, rather than the
next number in the sequence because the data I've inherited doesn't
necessarily follow on in sequential numbers.

Could either of you shed a little light on this please?

Many thanks and regards

Chris

To add a little to what Jeff said, using a control's Default Value is
another way to go. One advantage is that no record is created if you back
out of the new record without entering anything. Default value applies
only
to new records. In any case you may want to guard against changing the
number if the record is updated after initially being saved.

Another consideration is a multi-user environment. Both of these points
are
addressed in the sample found here:
http://www.rogersaccesslibrary.com/...?TID=395&SID=8c7e2e33bfdb53dzbz8e9f8157ee139e

Note that the link is all on one line. Alternatively, go to this link and
look for AutonumberProblem.mdb:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1

Hi Jeff, many thanka for replying.

[quoted text clipped - 37 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

Hi Bruce thanks for getting back to me.

As I said in my earlier email I inherited this data and shall we say it isn't
in the best of condition. There are reference numbers that don't follow on
for whatever reason, and I was just trying to find away of plugging those
gaps in the numbers. I'll give the Google search a try

Many thanks and regards

Chrsi

If you mean you want to fill in the gaps of the existing numbering, I have
to wonder why. I think it can be done, but I believe it is a task of
daunting complexity. I do not know how, but a Google groups search for
something like "Access sequence missing number" should turn up some
discussion.

Bruce and Jeff

[quoted text clipped - 44 lines]

 

[BruceM]

My wondering was because if the numbers are arbitrary references the actual
values don't matter, and if they are sequential it is not usual to assign a
new record a value lower than an older record.

Hi Bruce thanks for getting back to me.

As I said in my earlier email I inherited this data and shall we say it
isn't
in the best of condition. There are reference numbers that don't follow on
for whatever reason, and I was just trying to find away of plugging those
gaps in the numbers. I'll give the Google search a try

Many thanks and regards

Chrsi

If you mean you want to fill in the gaps of the existing numbering, I have
to wonder why. I think it can be done, but I believe it is a task of
daunting complexity. I do not know how, but a Google groups search for
something like "Access sequence missing number" should turn up some
discussion.

Bruce and Jeff

[quoted text clipped - 44 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

Bruce,

You have fair comment, it isn't usual to assign a new record to a lower value.
But it's something that I really need to try and do.

Many thanks and regards

Chris

My wondering was because if the numbers are arbitrary references the actual
values don't matter, and if they are sequential it is not usual to assign a
new record a value lower than an older record.

Hi Bruce thanks for getting back to me.

[quoted text clipped - 19 lines]

 

[Jeff Boyce]

Depending on how many, it might be (?considerably?) faster to just
"manually" add those sequence numbers directly in that table. After all, it
IS a one-time need, right?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Bruce,

You have fair comment, it isn't usual to assign a new record to a lower
value.
But it's something that I really need to try and do.

Many thanks and regards

Chris

My wondering was because if the numbers are arbitrary references the
actual
values don't matter, and if they are sequential it is not usual to assign
a
new record a value lower than an older record.

Hi Bruce thanks for getting back to me.

[quoted text clipped - 19 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

Jeff,

There are a few records, but you make a valid point that I could make the
changes manually.

Many thanks for your time and trouble

Regards

Chris

Depending on how many, it might be (?considerably?) faster to just
"manually" add those sequence numbers directly in that table. After all, it
IS a one-time need, right?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Bruce,

[quoted text clipped - 17 lines]