Finding the next available number in a table

[debinnj]

Hi Everyone,
I am working on a project where we need to be able to enter the net
number in a series (not an auto number field). So, for instance if I have
DEB-12, DEB-13, DEB-14, how could I find out what number I should use next
without having to look it up each time. We are using so many of these
prefixes that it is difficult to keep track.

Thanks sooooo much!

 

[Stefan Hoffmann]

hi,

I am working on a project where we need to be able to enter the net
number in a series (not an auto number field). So, for instance if I have
DEB-12, DEB-13, DEB-14, how could I find out what number I should use next

I've created this example for another thread:

http://ste5an.org/db3.zip

without having to look it up each time. We are using so many of these
prefixes that it is difficult to keep track.

What does this mean? ABC-1, DEF-2, GHI-3?

Depending on the meaning of these prefixes, you should really consider
storing in its own prefix field and store the number separatly.


mfG
--> stefan <--

 

[debinnj]

Hi Stafan,
They are stored in their own field. I was trying a query from a form. Not
having much luck. What were you thinking I should us. My boss wants a "box"
that she can open and ask for the next number....

Thanks so much!

 

[Yanick]

Try this (I assumed you are trying to do this in VBA, if not let me know)

'First check if the table is empty
If Not IsNull(DMax("[YourAutoFieldNumber]", "YourTable")) Then
sAutoNumber = DMax("[YourAutoFieldNumber]", "YourTable")
sAutoNumber = "DEB-" & Int(Right(sSnagNumber, 4)) + 1
Else
sAutoNumber = "DEB-1"
End If

 

[debinnj]

Hi Yanick,
Thank you so much, I wasnt going to use Vb but I will if you think its the
best way. Where would I put all this?

Try this (I assumed you are trying to do this in VBA, if not let me know)

'First check if the table is empty
If Not IsNull(DMax("[YourAutoFieldNumber]", "YourTable")) Then
sAutoNumber = DMax("[YourAutoFieldNumber]", "YourTable")
sAutoNumber = "DEB-" & Int(Right(sSnagNumber, 4)) + 1
Else
sAutoNumber = "DEB-1"
End If


--
Yanick

Hi Everyone,
I am working on a project where we need to be able to enter the net
number in a series (not an auto number field). So, for instance if I have
DEB-12, DEB-13, DEB-14, how could I find out what number I should use next
without having to look it up each time. We are using so many of these
prefixes that it is difficult to keep track.

Thanks sooooo much!

 

[Yanick]

I can't say if it is the best solution but this is how I did it in the past.

Were to place this code will depends of your db logics. When do you need it?
I will need more details about your DB.
--
Yanick

Hi Yanick,
Thank you so much, I wasnt going to use Vb but I will if you think its the
best way. Where would I put all this?

Try this (I assumed you are trying to do this in VBA, if not let me know)

'First check if the table is empty
If Not IsNull(DMax("[YourAutoFieldNumber]", "YourTable")) Then
sAutoNumber = DMax("[YourAutoFieldNumber]", "YourTable")
sAutoNumber = "DEB-" & Int(Right(sSnagNumber, 4)) + 1
Else
sAutoNumber = "DEB-1"
End If


--
Yanick

Hi Everyone,
I am working on a project where we need to be able to enter the net
number in a series (not an auto number field). So, for instance if I have
DEB-12, DEB-13, DEB-14, how could I find out what number I should use next
without having to look it up each time. We are using so many of these
prefixes that it is difficult to keep track.

Thanks sooooo much!

 

[debinnj]

OK I just tried entering into a form using code builder. Im not seeing
results but I think I am missing something.
I placed the code in the ON CLICK event.
So I would need this coed to work with 5 different possiblilites. For
instance DEB-123, JEN-345, YAN-500,SUE-321, TOM-765. SO each of these (5)
would need the next new number each time the form opened and the first three
letter were enter (thats the prefix I was refering to)

Thanks Yanick!!!

I can't say if it is the best solution but this is how I did it in the past.

Were to place this code will depends of your db logics. When do you need it?
I will need more details about your DB.
--
Yanick

Hi Yanick,
Thank you so much, I wasnt going to use Vb but I will if you think its the
best way. Where would I put all this?

Try this (I assumed you are trying to do this in VBA, if not let me know)

'First check if the table is empty
If Not IsNull(DMax("[YourAutoFieldNumber]", "YourTable")) Then
sAutoNumber = DMax("[YourAutoFieldNumber]", "YourTable")
sAutoNumber = "DEB-" & Int(Right(sSnagNumber, 4)) + 1
Else
sAutoNumber = "DEB-1"
End If


--
Yanick


:

Hi Everyone,
I am working on a project where we need to be able to enter the net
number in a series (not an auto number field). So, for instance if I have
DEB-12, DEB-13, DEB-14, how could I find out what number I should use next
without having to look it up each time. We are using so many of these
prefixes that it is difficult to keep track.

Thanks sooooo much!

 

[Yanick]

Are you familiar with VBA?

I will need more detail like :
- Is your form bound to a table?
- What is your form used for?
- Said you placed the code in the "On click" event. The "On click" of what?
The form? A button?

I need a bit more about the context to give you accurate explanation.

--
Yanick

OK I just tried entering into a form using code builder. Im not seeing
results but I think I am missing something.
I placed the code in the ON CLICK event.
So I would need this coed to work with 5 different possiblilites. For
instance DEB-123, JEN-345, YAN-500,SUE-321, TOM-765. SO each of these (5)
would need the next new number each time the form opened and the first three
letter were enter (thats the prefix I was refering to)

Thanks Yanick!!!

I can't say if it is the best solution but this is how I did it in the past.

Were to place this code will depends of your db logics. When do you need it?
I will need more details about your DB.
--
Yanick

Hi Yanick,
Thank you so much, I wasnt going to use Vb but I will if you think its the
best way. Where would I put all this?

:

Try this (I assumed you are trying to do this in VBA, if not let me know)

'First check if the table is empty
If Not IsNull(DMax("[YourAutoFieldNumber]", "YourTable")) Then
sAutoNumber = DMax("[YourAutoFieldNumber]", "YourTable")
sAutoNumber = "DEB-" & Int(Right(sSnagNumber, 4)) + 1
Else
sAutoNumber = "DEB-1"
End If


--
Yanick


:

Hi Everyone,
I am working on a project where we need to be able to enter the net
number in a series (not an auto number field). So, for instance if I have
DEB-12, DEB-13, DEB-14, how could I find out what number I should use next
without having to look it up each time. We are using so many of these
prefixes that it is difficult to keep track.

Thanks sooooo much!

 

[debinnj]

Hi Yanick,
I placed the code you gave me in the text box of the field where I want
the next new number to appear. The text box is located on a form.
Thanks!
Deb

Are you familiar with VBA?

I will need more detail like :
- Is your form bound to a table?
- What is your form used for?
- Said you placed the code in the "On click" event. The "On click" of what?
The form? A button?

I need a bit more about the context to give you accurate explanation.

--
Yanick

OK I just tried entering into a form using code builder. Im not seeing
results but I think I am missing something.
I placed the code in the ON CLICK event.
So I would need this coed to work with 5 different possiblilites. For
instance DEB-123, JEN-345, YAN-500,SUE-321, TOM-765. SO each of these (5)
would need the next new number each time the form opened and the first three
letter were enter (thats the prefix I was refering to)

Thanks Yanick!!!

I can't say if it is the best solution but this is how I did it in the past.

Were to place this code will depends of your db logics. When do you need it?
I will need more details about your DB.
--
Yanick


:

Hi Yanick,
Thank you so much, I wasnt going to use Vb but I will if you think its the
best way. Where would I put all this?

:

Try this (I assumed you are trying to do this in VBA, if not let me know)

'First check if the table is empty
If Not IsNull(DMax("[YourAutoFieldNumber]", "YourTable")) Then
sAutoNumber = DMax("[YourAutoFieldNumber]", "YourTable")
sAutoNumber = "DEB-" & Int(Right(sSnagNumber, 4)) + 1
Else
sAutoNumber = "DEB-1"
End If


--
Yanick


:

Hi Everyone,
I am working on a project where we need to be able to enter the net
number in a series (not an auto number field). So, for instance if I have
DEB-12, DEB-13, DEB-14, how could I find out what number I should use next
without having to look it up each time. We are using so many of these
prefixes that it is difficult to keep track.

Thanks sooooo much!

 

[Yanick]

I still need more info. When do you want the next number to appears? On form
load? When you click a button? I really need to understand the context. What
your form does. How you would like it to work. It will tell you where to
place the code. We may need to place it at more then one place.

It may be logic to place the code in the form load event and in the form
after update event if your form is used for data entry.

You will then need to change the code a bit to assign the value directly in
the text box.

me.mytextbox.value = DMax("[YourAutoFieldNumber]", "YourTable")

I guess you understand that you need to change "mytextbox",
"YourAutoFieldNumber" & "YourTable" with the name of your textbox, table
field & table. Right?

What is your VBA level of understanding?
--
Yanick

Hi Yanick,
I placed the code you gave me in the text box of the field where I want
the next new number to appear. The text box is located on a form.
Thanks!
Deb

Are you familiar with VBA?

I will need more detail like :
- Is your form bound to a table?
- What is your form used for?
- Said you placed the code in the "On click" event. The "On click" of what?
The form? A button?

I need a bit more about the context to give you accurate explanation.

--
Yanick

OK I just tried entering into a form using code builder. Im not seeing
results but I think I am missing something.
I placed the code in the ON CLICK event.
So I would need this coed to work with 5 different possiblilites. For
instance DEB-123, JEN-345, YAN-500,SUE-321, TOM-765. SO each of these (5)
would need the next new number each time the form opened and the first three
letter were enter (thats the prefix I was refering to)

Thanks Yanick!!!

:

I can't say if it is the best solution but this is how I did it in the past.

Were to place this code will depends of your db logics. When do you need it?
I will need more details about your DB.
--
Yanick


:

Hi Yanick,
Thank you so much, I wasnt going to use Vb but I will if you think its the
best way. Where would I put all this?

:

Try this (I assumed you are trying to do this in VBA, if not let me know)

'First check if the table is empty
If Not IsNull(DMax("[YourAutoFieldNumber]", "YourTable")) Then
sAutoNumber = DMax("[YourAutoFieldNumber]", "YourTable")
sAutoNumber = "DEB-" & Int(Right(sSnagNumber, 4)) + 1
Else
sAutoNumber = "DEB-1"
End If


--
Yanick


:

Hi Everyone,
I am working on a project where we need to be able to enter the net
number in a series (not an auto number field). So, for instance if I have
DEB-12, DEB-13, DEB-14, how could I find out what number I should use next
without having to look it up each time. We are using so many of these
prefixes that it is difficult to keep track.

Thanks sooooo much!