Number Generator

  • Thread starter Thread starter brett
  • Start date Start date
B

brett

Hi all,
I'm pretty new to vb so I was hoping someone could help me out:

I have a table "Tempnum" that contains a number for each computer in our
inventory. Our code structure is as follows: ex DL102001 (DLxxyyzz)

DL= Company initials (its always constant)
10= Location (10 is LA, 20 is AZ, 30 is WA etc)
20= Department (20 is "service", 30 "testing" etc)
01= Number of computers (01,02,03)



The problem is i always have to look at the table and sort to the next
number in the sequence. Can someone help me out with a code/querey where i
enter the Company (Default "DL), location (string), department (string) and
it autogenterates the next number in the sequence?

I imagine its just a process of using "if,then" and the "mid" function but i
dont know the sytax. Please help me.

Thank You!
 
Personally, I would break this into 4 separate fields, and only merge them
for reporting purposes. Then you could do something like:

Dim strCriteria as string
Dim lngNextNumber as long

strCriteria = "[Company] = 'DL' AND " _
& "[Location] = 10 AND [Department] = 20"
lngNextNumber = NZ(DMAX("ComputerNumber", "Tempnum", strCriteria), 0) + 1

You can still do this with your structure, but it is a little more
difficult, because you have to parse your string and then convert it to a
number, then increment it. You might want to use a function, something like:

Public Function fnNextNum(CoLocDep as string) as string

Dim strCompNum as string
Dim intCompNum as integer

'you forgot to tell us the name of your field, so I'll use FieldName
strCompNum= NZ(DMAX("FieldName", _
"TempNum", _
"[FieldName] Like '" & CoLocDep &
"*'"), _
CoLocDep & "00")
intCompNum = Val(Mid(strCompNum, 7)) + 1
fnNextNum = CoLocDep & Format(intCompNum, "00")

End Sub

To use this, you would simply call the function and pass it the text
parameter that denotes the company, location, and department.

debug.print fnNextNum("DL1020")


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Thanks for the help, Dale
But its a little late to break it into 4 seperate fields (I know its easier
that way). Right now I have one table called IV_Computer and the field is
called "Temp" (not "tempnum" sorry for not being specific). I would have to
use the "Public Function" to do this and its pretty advanced if i hit a snag.
Also, I dont see any prompts for location or department on the "Public
Function." If I have 4 fields on a form: [company], [location], [department]
and "nextnumber" (the combination of all three fields + the next number in
the sequence) what code do i use for each field? It a little condensed for
me and since its pretty advanced I wouldn't have any way of finishing this
exercise with my own knowledge. You presented a great start for someone who
has good knowledge of VB but I can't continue unless you have a complete
code.

If its easier i dont need to enter a string into these departments/location:
your form could look like this "Company" : a text default 'DL'
"Location": a number
"Department": a number
"Temp": generated by code

Remember, these fields dont have to be part of the database. The only part
that needs to be compared to the database is "temp". Its pretty much adding
text and numeric values and then comparing them to "Temp" in "IV_Computer" to
caculate the next number. Similar to a calculator. Please let me know your
thoughts, thanks again for your help.


Dale Fye said:
Personally, I would break this into 4 separate fields, and only merge them
for reporting purposes. Then you could do something like:

Dim strCriteria as string
Dim lngNextNumber as long

strCriteria = "[Company] = 'DL' AND " _
& "[Location] = 10 AND [Department] = 20"
lngNextNumber = NZ(DMAX("ComputerNumber", "Tempnum", strCriteria), 0) + 1

You can still do this with your structure, but it is a little more
difficult, because you have to parse your string and then convert it to a
number, then increment it. You might want to use a function, something like:

Public Function fnNextNum(CoLocDep as string) as string

Dim strCompNum as string
Dim intCompNum as integer

'you forgot to tell us the name of your field, so I'll use FieldName
strCompNum= NZ(DMAX("FieldName", _
"TempNum", _
"[FieldName] Like '" & CoLocDep &
"*'"), _
CoLocDep & "00")
intCompNum = Val(Mid(strCompNum, 7)) + 1
fnNextNum = CoLocDep & Format(intCompNum, "00")

End Sub

To use this, you would simply call the function and pass it the text
parameter that denotes the company, location, and department.

debug.print fnNextNum("DL1020")


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



brett said:
Hi all,
I'm pretty new to vb so I was hoping someone could help me out:

I have a table "Tempnum" that contains a number for each computer in our
inventory. Our code structure is as follows: ex DL102001 (DLxxyyzz)

DL= Company initials (its always constant)
10= Location (10 is LA, 20 is AZ, 30 is WA etc)
20= Department (20 is "service", 30 "testing" etc)
01= Number of computers (01,02,03)



The problem is i always have to look at the table and sort to the next
number in the sequence. Can someone help me out with a code/querey where i
enter the Company (Default "DL), location (string), department (string) and
it autogenterates the next number in the sequence?

I imagine its just a process of using "if,then" and the "mid" function but i
dont know the sytax. Please help me.

Thank You!
Click to expand...
Click to expand...
 
Brett,

Had you originally provided this level of detail (which I strongly
recommend), I would have given you a different description.

So, you have 3 controls on a form (are these combo boxes, text boxes, ???)
where you enter or select Company, Location, and Department, and a 4th
control that you want to fill in with the value of the next # in the Temp
field for a given combination of the other three.

If that is correct, how do you know whether to generate a new number? I
would recommend a command button (cmd_GetNewNumber). In the code behind that
button you could have:

Private Sub cmd_GetNewNumber_Click

if LEN(me.txtCompany & "") <> 2 then
msgbox "Enter a company"
elseif len(me.txtLocation & "") <> 2 then
msgbox "Enter a location!"
elseif len(me.txtDepartment & "") <> 2
msgbox "Enter a department"
else
me.txt_ComputerNumber = fnNextNum(me.txtCompany & _

me.txtLocation & _

me.txtDepartment)
endif

Exit Sub

Then, change the fnNextNum field to use the "Temp" field

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



brett said:
Thanks for the help, Dale
But its a little late to break it into 4 seperate fields (I know its easier
that way). Right now I have one table called IV_Computer and the field is
called "Temp" (not "tempnum" sorry for not being specific). I would have to
use the "Public Function" to do this and its pretty advanced if i hit a snag.
Also, I dont see any prompts for location or department on the "Public
Function." If I have 4 fields on a form: [company], [location], [department]
and "nextnumber" (the combination of all three fields + the next number in
the sequence) what code do i use for each field? It a little condensed for
me and since its pretty advanced I wouldn't have any way of finishing this
exercise with my own knowledge. You presented a great start for someone who
has good knowledge of VB but I can't continue unless you have a complete
code.

If its easier i dont need to enter a string into these departments/location:
your form could look like this "Company" : a text default 'DL'
"Location": a number
"Department": a number
"Temp": generated by code

Remember, these fields dont have to be part of the database. The only part
that needs to be compared to the database is "temp". Its pretty much adding
text and numeric values and then comparing them to "Temp" in "IV_Computer" to
caculate the next number. Similar to a calculator. Please let me know your
thoughts, thanks again for your help.


Dale Fye said:
Personally, I would break this into 4 separate fields, and only merge them
for reporting purposes. Then you could do something like:

Dim strCriteria as string
Dim lngNextNumber as long

strCriteria = "[Company] = 'DL' AND " _
& "[Location] = 10 AND [Department] = 20"
lngNextNumber = NZ(DMAX("ComputerNumber", "Tempnum", strCriteria), 0) + 1

You can still do this with your structure, but it is a little more
difficult, because you have to parse your string and then convert it to a
number, then increment it. You might want to use a function, something like:

Public Function fnNextNum(CoLocDep as string) as string

Dim strCompNum as string
Dim intCompNum as integer

'you forgot to tell us the name of your field, so I'll use FieldName
strCompNum= NZ(DMAX("FieldName", _
"TempNum", _
"[FieldName] Like '" & CoLocDep &
"*'"), _
CoLocDep & "00")
intCompNum = Val(Mid(strCompNum, 7)) + 1
fnNextNum = CoLocDep & Format(intCompNum, "00")

End Sub

To use this, you would simply call the function and pass it the text
parameter that denotes the company, location, and department.

debug.print fnNextNum("DL1020")


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



brett said:
Hi all,
I'm pretty new to vb so I was hoping someone could help me out:

I have a table "Tempnum" that contains a number for each computer in our
inventory. Our code structure is as follows: ex DL102001 (DLxxyyzz)

DL= Company initials (its always constant)
10= Location (10 is LA, 20 is AZ, 30 is WA etc)
20= Department (20 is "service", 30 "testing" etc)
01= Number of computers (01,02,03)



The problem is i always have to look at the table and sort to the next
number in the sequence. Can someone help me out with a code/querey where i
enter the Company (Default "DL), location (string), department (string) and
it autogenterates the next number in the sequence?

I imagine its just a process of using "if,then" and the "mid" function but i
dont know the sytax. Please help me.

Thank You!
Click to expand...
Click to expand...
Click to expand...
 
Dale,

At this point i have 4 unbound text boxes (txtCompany, txtLocation,
txtDepartment, txtComputerNumber) and one cmd button with the following code:

Private Sub cmdGetNewNumber_Click()
If Len(Me.txtCompany & "") <> 2 Then
MsgBox "Enter a company"
ElseIf Len(Me.txtLocation & "") <> 2 Then
MsgBox "Enter a location!"
ElseIf Len(Me.txtDepartment & "") <> 2 Then
MsgBox "Enter a department"
Else: Me.txtComputerNumber = fnnextnumTEMP(Me.txtCompany &
Me.txtLocation & Me.txtDepartment)
End If

End Sub

'Once i plug in numbers to three boxes, nothing executes in the fourth
(txtComputerNumber. Im not clear when you say "fnnextnum" field to the temp
field.

Any more advice. Thanks for your time.

Brett



Dale Fye said:
Brett,

Had you originally provided this level of detail (which I strongly
recommend), I would have given you a different description.

So, you have 3 controls on a form (are these combo boxes, text boxes, ???)
where you enter or select Company, Location, and Department, and a 4th
control that you want to fill in with the value of the next # in the Temp
field for a given combination of the other three.

If that is correct, how do you know whether to generate a new number? I
would recommend a command button (cmd_GetNewNumber). In the code behind that
button you could have:

Private Sub cmd_GetNewNumber_Click

if LEN(me.txtCompany & "") <> 2 then
msgbox "Enter a company"
elseif len(me.txtLocation & "") <> 2 then
msgbox "Enter a location!"
elseif len(me.txtDepartment & "") <> 2
msgbox "Enter a department"
else
me.txt_ComputerNumber = fnNextNum(me.txtCompany & _

me.txtLocation & _

me.txtDepartment)
endif

Exit Sub

Then, change the fnNextNum field to use the "Temp" field

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



brett said:
Thanks for the help, Dale
But its a little late to break it into 4 seperate fields (I know its easier
that way). Right now I have one table called IV_Computer and the field is
called "Temp" (not "tempnum" sorry for not being specific). I would have to
use the "Public Function" to do this and its pretty advanced if i hit a snag.
Also, I dont see any prompts for location or department on the "Public
Function." If I have 4 fields on a form: [company], [location], [department]
and "nextnumber" (the combination of all three fields + the next number in
the sequence) what code do i use for each field? It a little condensed for
me and since its pretty advanced I wouldn't have any way of finishing this
exercise with my own knowledge. You presented a great start for someone who
has good knowledge of VB but I can't continue unless you have a complete
code.

If its easier i dont need to enter a string into these departments/location:
your form could look like this "Company" : a text default 'DL'
"Location": a number
"Department": a number
"Temp": generated by code

Remember, these fields dont have to be part of the database. The only part
that needs to be compared to the database is "temp". Its pretty much adding
text and numeric values and then comparing them to "Temp" in "IV_Computer" to
caculate the next number. Similar to a calculator. Please let me know your
thoughts, thanks again for your help.


Dale Fye said:
Personally, I would break this into 4 separate fields, and only merge them
for reporting purposes. Then you could do something like:

Dim strCriteria as string
Dim lngNextNumber as long

strCriteria = "[Company] = 'DL' AND " _
& "[Location] = 10 AND [Department] = 20"
lngNextNumber = NZ(DMAX("ComputerNumber", "Tempnum", strCriteria), 0) + 1

You can still do this with your structure, but it is a little more
difficult, because you have to parse your string and then convert it to a
number, then increment it. You might want to use a function, something like:

Public Function fnNextNum(CoLocDep as string) as string

Dim strCompNum as string
Dim intCompNum as integer

'you forgot to tell us the name of your field, so I'll use FieldName
strCompNum= NZ(DMAX("FieldName", _
"TempNum", _
"[FieldName] Like '" & CoLocDep &
"*'"), _
CoLocDep & "00")
intCompNum = Val(Mid(strCompNum, 7)) + 1
fnNextNum = CoLocDep & Format(intCompNum, "00")

End Sub

To use this, you would simply call the function and pass it the text
parameter that denotes the company, location, and department.

debug.print fnNextNum("DL1020")


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Hi all,
I'm pretty new to vb so I was hoping someone could help me out:

I have a table "Tempnum" that contains a number for each computer in our
inventory. Our code structure is as follows: ex DL102001 (DLxxyyzz)

DL= Company initials (its always constant)
10= Location (10 is LA, 20 is AZ, 30 is WA etc)
20= Department (20 is "service", 30 "testing" etc)
01= Number of computers (01,02,03)



The problem is i always have to look at the table and sort to the next
number in the sequence. Can someone help me out with a code/querey where i
enter the Company (Default "DL), location (string), department (string) and
it autogenterates the next number in the sequence?

I imagine its just a process of using "if,then" and the "mid" function but i
dont know the sytax. Please help me.

Thank You!
Click to expand...
Click to expand...
Click to expand...
 
brett said:
Dale,

At this point i have 4 unbound text boxes (txtCompany, txtLocation,
txtDepartment, txtComputerNumber) and one cmd button with the following
code:

Private Sub cmdGetNewNumber_Click()
If Len(Me.txtCompany & "") <> 2 Then
MsgBox "Enter a company"
ElseIf Len(Me.txtLocation & "") <> 2 Then
MsgBox "Enter a location!"
ElseIf Len(Me.txtDepartment & "") <> 2 Then
MsgBox "Enter a department"
Else: Me.txtComputerNumber = fnnextnumTEMP(Me.txtCompany &
Me.txtLocation & Me.txtDepartment)
End If

End Sub
Click to expand...
<snip>

Remove the colon following Else.
 
The Me.txtComputer = ... should be on the line following the Else
statement.

Did you add a command button and name it cmdGetNewNumber? If not, you need
to do that, then put the code below in the click event of that button.

Once you have entered the values in your three text boxes, click the button
and it should fill in the 4th text box.

Dale
 
Dale

Almost there. I've followed you exact instructions and even played around
with it for the last week. My code currently reads:

Option Compare Database

Private Sub cmdGetNewNumber_Click()
If Len(Me.txtCompany & "") <> 2 Then
MsgBox "Enter a company"
ElseIf Len(Me.txtLocation & "") <> 2 Then
MsgBox "Enter a location!"
ElseIf Len(Me.txtDepartment & "") <> 2 Then
MsgBox "Enter a department"
Else
Me.txtComputerNumber = fnNextNum(Me.txtCompany & Me.txtLocation &
Me.txtDepartment)
End If

End Sub

Public Function fnNextNum(CoLocDep As String) As String

Dim strComputerNumber As String
Dim intComputerNumber As Integer

'you forgot to tell us the name of your field, so I'll use FieldName
strComputerNumber = Nz(DMax("Temp", "Temp", "[Temp] Like '" & CoLocDep &
"*'"), CoLocDep & "00")
intComputerNumber = Val(Mid(strComputerNumber, 7)) + 1
fnNextNum = CoLocDep & Format(intComputerNumber, "00")

End Sub


I, for some reason, still get a compiler error at the end. By the way, my
actual field name is called "Temp" if that changes anything i dont know.
Even when i omit the public function and just try the private sub i get a
compiler error: the "me.txDepartment" is highlighted (everything is spelled
correctly).

When i cut and paste the this section of the public function: strCompNum=
NZ(DMAX("Temp", _
"TempNum", _
"[Temp] Like '" & CoLocDep &
"*'"), _
Click to expand...

most of it is red until i take out the "_". I dont know if i should keep
those or not, but if you could take a look at my current setup, maybe i am
missing something. Thanks for your time.

Brett

Dale Fye said:
Brett,

It should read:

Private Sub cmdGetNewNumber_Click()

If Len(Me.txtCompany & "") <> 2 Then
MsgBox "Enter a company"
ElseIf Len(Me.txtLocation & "") <> 2 Then
MsgBox "Enter a location!"
ElseIf Len(Me.txtDepartment & "") <> 2 Then
MsgBox "Enter a department"
Else
Me.txtComputerNumber = fnNextNum(Me.txtCompany & Me.txtLocation &
Me.txtDepartment)
End If

End Sub

Go into the forms design view, click on the command button, the go to the
Properties window. In the Properties window, go to the Event tab, and make
sure that there is something in the row for the click Event. It should read
[Event Procedure]. Click on the elipse (...) to the right of that entry and
it should take you to the code shown above.

Did you copy my function (fnNextNum) into the code window for this form? If
not, copy this, and paste it in the VB editor code window for the form you
are working with.

Public Function fnNextNum(CoLocDep as string) as string

Dim strCompNum as string
Dim intCompNum as integer

'you forgot to tell us the name of your field, so I'll use FieldName
strCompNum= NZ(DMAX("Temp", _
"TempNum", _
"[Temp] Like '" & CoLocDep &
"*'"), _
CoLocDep & "00")
intCompNum = Val(Mid(strCompNum, 7)) + 1
fnNextNum = CoLocDep & Format(intCompNum, "00")

End Sub

Now, when you click the command button on your form, it should check to make
sure you have something in each of the other three fields. If not, it will
give you a warning message, but won't do anything else. If you have two
character codes in each of the other text fields, it will call the function
fnNextNum( ) and pass it the concatenated string from your company,
location, and department fields. The function will check the Temp field in
the table "TempNum" for a string that starts with the same six characters.
If it finds one, it will strip the last two characters and increment that
value by 1. If it doesn't find a matching string, it will start with 00 and
increment that by 1.

HTH
Dale

brett said:
Dale, thank you for being so patient.

I took the "TEMP" out and still nothing happens. If i take the "Temp" out
how does it know what the next number is in the sequence? The "Temp"
comulm
in my table is already populated with numbers so if there is a number
DL102005 it needs to know there is an "05" so it can register an "06,"
right?
Right now i have no control source on any of these text boxes so how does
the cmd button know what information to look at (for the last 2 digits)?
It
currently reads:

Private Sub cmdGetNewNumber_Click()
If Len(Me.txtCompany & "") <> 2 Then
MsgBox "Enter a company"
ElseIf Len(Me.txtLocation & "") <> 2 Then
MsgBox "Enter a location!"
ElseIf Len(Me.txtDepartment & "") <> 2 Then
MsgBox "Enter a department"
Else: Me.txtComputerNumber = fnNextNum(Me.txtCompany & Me.txtLocation &
Me.txtDepartment)
End If

End Sub
Click to expand...
Click to expand...
 
Back
Top