Setting # Field To Max # +1 on Form

D

DallasLDY

I know the answer is going to be simple, but I just can't make it happen.

Our Quote #'s are just sequential #'s, and we were previously using a
spreadsheet to track these. We are currently at record 10640.

I built a db, and when the user opens the Add Quote form, I want the Quote #
to be populated by the max of the Quote# +1 from tblQuote table. I don't
want to use autonumber, because I want the user to be able to change this
number if they wish (long story, but it has to be this way).

How do I do this? I've tried several things and I just can't get it to
work. Please help.
 
L

Larry Daugherty

Look in Access Help for DMax(). You'll want to do something like
DMax(Quote#) + 1 to get the next highest number.

Allowing people to choose their own numbers can break that system.
All things being well, DMax()+1 will generate an unbroken sequence.
If someone chooses a lower number then it will likely conflict with a
number already used. if they choose a higher number then DMax will
find and use that higher number, leaving a gap.

HTH
 
D

DallasLDY

I completely understand the ramifications of allowing users to moify this
field. It is a necessary evil for now. Fortunately, there will only be
approx. 2 people who will have access to adding a record.

I tried the DMax function, thinking "Duh, of course", but I still can't get
it to work. I put in:

Private Sub Form_Open(Cancel As Integer)
Me.Quote_ = DMax("tblQuote![Quote#]", "tblQuote") + 1
End Sub

It told me I can't assign a value to that field.

I've tried putting it in the expression, creating a query and assigning it
that value, running a query from the VB side, and that's about all I can
remember I've done so far. Like I said, i know I'm missing something
simple...
 
A

Armen Stein

Look in Access Help for DMax(). You'll want to do something like
DMax(Quote#) + 1 to get the next highest number.

Allowing people to choose their own numbers can break that system.
All things being well, DMax()+1 will generate an unbroken sequence.
If someone chooses a lower number then it will likely conflict with a
number already used. if they choose a higher number then DMax will
find and use that higher number, leaving a gap.

HTH
Click to expand...

And, in a multi-user situation you may get duplicate Quote Numbers,
because two people can ask for the Max at the same time. This problem
will be reduced (but not eliminated) if you make sure to save your
quote record immediately after getting the new number.

If you want a more bulletproof sequential number, you need to store
the LastQuoteNumber in a separate table, and retrieve it, add 1 and
update it all in the same chunk of code. That way it will remain
locked to another user until you're done. There are lots of places
online that show this approach. That still won't solve the problem of
your users modifying it later though.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
A

Armen Stein

Private Sub Form_Open(Cancel As Integer)
Me.Quote_ = DMax("tblQuote![Quote#]", "tblQuote") + 1
End Sub

It told me I can't assign a value to that field.
Click to expand...

The Open event is too soon - none of the field are available yet.

Use the Load event instead.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
L

Larry Daugherty

If Quote# is the name of the control on your form and Quote_ is the
name of the field in tblQuote then something like the below might
work.

me!Quote# = DMax("[Quote_]","tblQuote") + 1

HTH
--
-Larry-
--

DallasLDY said:
I completely understand the ramifications of allowing users to moify this
field. It is a necessary evil for now. Fortunately, there will only be
approx. 2 people who will have access to adding a record.

I tried the DMax function, thinking "Duh, of course", but I still can't get
it to work. I put in:

Private Sub Form_Open(Cancel As Integer)
Me.Quote_ = DMax("tblQuote![Quote#]", "tblQuote") + 1
End Sub

It told me I can't assign a value to that field.

I've tried putting it in the expression, creating a query and assigning it
that value, running a query from the VB side, and that's about all I can
remember I've done so far. Like I said, i know I'm missing something
simple...

Larry Daugherty said:
Look in Access Help for DMax(). You'll want to do something like
DMax(Quote#) + 1 to get the next highest number.

Allowing people to choose their own numbers can break that system.
All things being well, DMax()+1 will generate an unbroken sequence.
If someone chooses a lower number then it will likely conflict with a
number already used. if they choose a higher number then DMax will
find and use that higher number, leaving a gap.

HTH
--
-Larry-
--

it
happen. using
a the
Quote # I
don't change
this get it
to
Click to expand...
Click to expand...
 
D

DallasLDY

Ahhh, thank you. I knew it would be simple. Thanks to both of you. I'm
feeling quite embarassed for making this so difficult!

Armen Stein said:
Private Sub Form_Open(Cancel As Integer)
Me.Quote_ = DMax("tblQuote![Quote#]", "tblQuote") + 1
End Sub

It told me I can't assign a value to that field.
Click to expand...

The Open event is too soon - none of the field are available yet.

Use the Load event instead.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
Click to expand...
 
A

Armen Stein

Ahhh, thank you. I knew it would be simple. Thanks to both of you. I'm
feeling quite embarassed for making this so difficult!
Click to expand...

The only embarrassing question is the one not asked. I'm happy to be
able to help!

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Top