Fill in next available "#" in text field

  • Thread starter Thread starter toby131
  • Start date Start date
T

toby131

I am working in Access 2003 with a form that will need a order number in
certain cases. I need to have the next available number pulled when it is
needed. I am trying to put code in the double click property of the text box
on a form that will store the number. The field is is text, because the
order numbers are in the format RM#####. I had seen code about using the
DMax function to find the highest number then adding 1, but this seems to
only work for numbers.

Could some please help me out with the code to find the highest value in a
text field and then fill in the next number?

Thank you!
 
toby,A text field doesn't have a highest value...

If all your numbers are preceded by "RM", the just store the number
portion
in a numeric field (ex. [RMNo]).
Increment that number...
=NZ(DMax("[RMNo]","tblYourTable") + 1, 0)
and concatenate when ever you need to display the RM "string."
= "RM" & RMNo

In other words, just store the number, and concatenate the "RM" when
needed
for display purposes... in any form, query, or report.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Get extract the number from the text field buy using the Clng() function
(convert to Long) and Right() and Len() functions...

Dim lngOrderNum As Long
lngOrderNum = Clng(Right(Me.controlname, Len(me.controlname) - 2))

then use lngOrderNum in your DMax function.

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
Thanks for the response. I am still having some trouble.

I was getting an error message on the IngOrderNum = CLng(Right(Me.[RMA No],
5)) that says "Invalid use of Null". I tried putting in If Len(Me.[RMA No])
0, but then nothing happens.
Click to expand...

It seems like I am only looking at the record I have open in the form, do I
need to change something so that it converts all the records so I can pull
the highest value, then store a single value?

Here is my current code:

Private Sub Text48_DblClick(Cancel As Integer)


If Len(Me![RMA No]) > 0 Then
Dim IngOrderNum As Long
IngOrderNum = CLng(Right(Me.[RMA No], 5))

Dim NewOrderNum As Long
NewOrderNum = DMax(IngOrderNum, "Warranty Data") + 1
Me![RMA No] = ("RM") + NewOrderNum

End If

End Sub
 
Al,
I was considering storing just the number, but I had one reservation. I
need the form to display RM#####, but this is also where the data is being
entered.

Is there anyway to display the "RM" & the value on the form after the number
has been automatically generated by the form originator double clicking on
the box?

Thanks!

Al Campagna said:
toby,A text field doesn't have a highest value...

If all your numbers are preceded by "RM", the just store the number
portion
in a numeric field (ex. [RMNo]).
Increment that number...
=NZ(DMax("[RMNo]","tblYourTable") + 1, 0)
and concatenate when ever you need to display the RM "string."
= "RM" & RMNo

In other words, just store the number, and concatenate the "RM" when
needed
for display purposes... in any form, query, or report.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

toby131 said:
I am working in Access 2003 with a form that will need a order number in
certain cases. I need to have the next available number pulled when it is
needed. I am trying to put code in the double click property of the text
box
on a form that will store the number. The field is is text, because the
order numbers are in the format RM#####. I had seen code about using the
DMax function to find the highest number then adding 1, but this seems to
only work for numbers.

Could some please help me out with the code to find the highest value in a
text field and then fill in the next number?

Thank you!
Click to expand...


.
Click to expand...
 
toby131 said:
Al,
I was considering storing just the number, but I had one reservation.
I need the form to display RM#####, but this is also where the data
is being entered.

Is there anyway to display the "RM" & the value on the form after the
number has been automatically generated by the form originator double
clicking on the box?
Click to expand...
If it is always RM then you could left justify teh number and use
Order "number RM" as the label.
You could also use an unbound field and set it ="RM & OrderNumber.

I'd also consider gettig rid of the "RM" completely.
 
toby131,
need the form to display RM#####, but this is also where the data is being
entered.
Click to expand...
No... you don't "enter" the data there. That field will be a calculated
field.
Name a text control [RMNoString]
In the ControlSource of that [RMNoString] control...
="RM" & [RMNo]

You'll increment and save the RMNo numeric value to the RMNo field
in your table, (it doesn't even need to be visible)...and just
"Display" the RMNoString on the form... for the visual convenience of the
user.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

toby131 said:
Al,
I was considering storing just the number, but I had one reservation. I
need the form to display RM#####, but this is also where the data is being
entered.

Is there anyway to display the "RM" & the value on the form after the
number
has been automatically generated by the form originator double clicking on
the box?

Thanks!

Al Campagna said:
toby,
code to find the highest value in a text field and then fill in the
next
number?
Click to expand...
A text field doesn't have a highest value...

If all your numbers are preceded by "RM", the just store the number
portion
in a numeric field (ex. [RMNo]).
Increment that number...
=NZ(DMax("[RMNo]","tblYourTable") + 1, 0)
and concatenate when ever you need to display the RM "string."
= "RM" & RMNo

In other words, just store the number, and concatenate the "RM" when
needed
for display purposes... in any form, query, or report.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

toby131 said:
I am working in Access 2003 with a form that will need a order number in
certain cases. I need to have the next available number pulled when it
is
needed. I am trying to put code in the double click property of the
text
box
on a form that will store the number. The field is is text, because
the
order numbers are in the format RM#####. I had seen code about using
the
DMax function to find the highest number then adding 1, but this seems
to
only work for numbers.

Could some please help me out with the code to find the highest value
in a
text field and then fill in the next number?

Thank you!
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top