Pop Up Form To Show Next Number

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200907/1

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" u27332@uwe wrote in message
news:9976dc895359c@uwe...
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200907/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

Jeff Boyce wrote:
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

--
Message posted via http://www.accessmonster.com

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

"hobbit2612 via AccessMonster.com" u27332@uwe wrote in message
news:997793a6718bf@uwe...
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

Jeff Boyce wrote:
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

--
Message posted via http://www.accessmonster.com

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 he
http://www.rogersaccesslibrary.com/f...9f 8157ee139e

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

"hobbit2612 via AccessMonster.com" u27332@uwe wrote in message
news:997793a6718bf@uwe...
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

Jeff Boyce wrote:
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

--
Message posted via http://www.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


BruceM wrote:
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 he
http://www.rogersaccesslibrary.com/f...9f 8157ee139e

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

Hi Jeff, many thanka for replying.

[quoted text clipped - 37 lines]

Chris

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200907/1

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.

"hobbit2612 via AccessMonster.com" u27332@uwe wrote in message
news:99824e77b8e44@uwe...
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


BruceM wrote:
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 he
http://www.rogersaccesslibrary.com/f...9f 8157ee139e

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

Hi Jeff, many thanka for replying.

[quoted text clipped - 37 lines]

Chris

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200907/1

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

BruceM wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200907/1

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.

"hobbit2612 via AccessMonster.com" u27332@uwe wrote in message
news:9982ce651b9c5@uwe...
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

BruceM wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200907/1

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

BruceM wrote:
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

--
Message posted via http://www.accessmonster.com