HELP - Random numbering & Random ASCII

[Crystal]

Hi Everyone!

I have a table that I've created and I'm trying to do an append query to an
existing table...all my fields match the existing table. One of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the middle 4
characters for this "ItemNo" field so I don't have to manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO DUPLICATES.

Any help would be appreciated. Also could you keep in mind that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Klatuu]

This one was fun to do!
Put a calculated field in your query that calls this function. It generates
the numbers based on your rules. It checks to see if the number already
exists in your tabe and does not use it if it is already in the table.

In the DLookup, I used then name ItemNo for your field name. Since I don't
know your table name, you will have to change "MyTableNameHere" to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex, "A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2", "3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere", "[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

 

[Dirk Goldgar]

Hi Everyone!

I have a table that I've created and I'm trying to do an append query
to an existing table...all my fields match the existing table. One
of my fields "ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the middle 4
characters for this "ItemNo" field so I don't have to manually enter
this for 13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly which can
include NUMERIC and ALPHA but NO symbols, punctuations, etc and NO
DUPLICATES.

Any help would be appreciated. Also could you keep in mind that I'm a
NEWBIE when writing code.

Thanks - Crystal

I have a function that returns a random alphanumeric string of any given
length, but requirement that there be no duplicates means that you don't
really want a random string at all. I can think of three ways to go
about this:

1. Use a function that first generates the pseudo-random key, then
checks to see if it already exists in the table, and generates another
one if it does. With only 4 characters in the "random" string. the odds
are pretty good that you're going to get some duplicates.

-- OR --

2. Don't bother checking for duplicates as the keys are generated, just
generate them and try to add the records in an append query. Some
records will probably be rejected for duplicate key errors. Write a
second query that selects just the records that didn't get added,
generates new keys for them, and tries again to add them. Repeat until
all records have been successfully added.

-- OR --

3. Run the query to generate all the keys in a work table. Examine that
table for duplicates, and regenerate keys for those records that are
dups. Then append from the work table to the live table.

 

[Klatuu]

You, know, I wasn't doing the math. with 130,000 records and requiring a
unique string of only 4 characters, it should not really be a problem. Given
36 possible characters to enter into a 4 character string, that would be 36^
4 which is not quite 1.7 million possibillities.

 

[Crystal]

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

This one was fun to do!
Put a calculated field in your query that calls this function. It generates
the numbers based on your rules. It checks to see if the number already
exists in your tabe and does not use it if it is already in the table.

In the DLookup, I used then name ItemNo for your field name. Since I don't
know your table name, you will have to change "MyTableNameHere" to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex, "A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2", "3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere", "[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

Hi Everyone!

I have a table that I've created and I'm trying to do an append query to an
existing table...all my fields match the existing table. One of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the middle 4
characters for this "ItemNo" field so I don't have to manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO DUPLICATES.

Any help would be appreciated. Also could you keep in mind that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Klatuu]

You can get more detail in VBA Help, but basically, it looks for a value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the value from.
The second is the name of the domain (a table or query) the field is in.
The last is the criteria to select a record. In this case, we will look for
the ItemNo. We don't care what it is, but the function requires a field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a record
based on the criteria. If it finds a record, it returns the value in the
field identified in the first argument.

Since we are checking to make sure the number we have created is unique, we
are looking in the table to see if it already exists. If a Null value is
returned, we know it is unique, so we return the number to the query which
uses the new number to append to the table. If we don't get a Null returned,
it means the number is already used, so we discard it and try another one.

Hope that clears it up for you.

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

This one was fun to do!
Put a calculated field in your query that calls this function. It generates
the numbers based on your rules. It checks to see if the number already
exists in your tabe and does not use it if it is already in the table.

In the DLookup, I used then name ItemNo for your field name. Since I don't
know your table name, you will have to change "MyTableNameHere" to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex, "A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2", "3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere", "[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

Hi Everyone!

I have a table that I've created and I'm trying to do an append query to an
existing table...all my fields match the existing table. One of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the middle 4
characters for this "ItemNo" field so I don't have to manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO DUPLICATES.

Any help would be appreciated. Also could you keep in mind that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Crystal]

Hi Klatuu -

Okay I used your code and put a calculated field in my query to call the
function...it works...however it's creating the same number for every record
I have in that field. I need it to create a random (unique) ItemNo for each
record (13,000). Is this possible? Sorry for not being more specific.

Thanks - Crystal

You can get more detail in VBA Help, but basically, it looks for a value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the value from.
The second is the name of the domain (a table or query) the field is in.
The last is the criteria to select a record. In this case, we will look for
the ItemNo. We don't care what it is, but the function requires a field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a record
based on the criteria. If it finds a record, it returns the value in the
field identified in the first argument.

Since we are checking to make sure the number we have created is unique, we
are looking in the table to see if it already exists. If a Null value is
returned, we know it is unique, so we return the number to the query which
uses the new number to append to the table. If we don't get a Null returned,
it means the number is already used, so we discard it and try another one.

Hope that clears it up for you.

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

This one was fun to do!
Put a calculated field in your query that calls this function. It generates
the numbers based on your rules. It checks to see if the number already
exists in your tabe and does not use it if it is already in the table.

In the DLookup, I used then name ItemNo for your field name. Since I don't
know your table name, you will have to change "MyTableNameHere" to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex, "A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2", "3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere", "[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

:

Hi Everyone!

I have a table that I've created and I'm trying to do an append query to an
existing table...all my fields match the existing table. One of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the middle 4
characters for this "ItemNo" field so I don't have to manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO DUPLICATES.

Any help would be appreciated. Also could you keep in mind that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Klatuu]

I tested the function and it returns a different number each time it is run.
Where are you calling it from?

Hi Klatuu -

Okay I used your code and put a calculated field in my query to call the
function...it works...however it's creating the same number for every record
I have in that field. I need it to create a random (unique) ItemNo for each
record (13,000). Is this possible? Sorry for not being more specific.

Thanks - Crystal

You can get more detail in VBA Help, but basically, it looks for a value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the value from.
The second is the name of the domain (a table or query) the field is in.
The last is the criteria to select a record. In this case, we will look for
the ItemNo. We don't care what it is, but the function requires a field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a record
based on the criteria. If it finds a record, it returns the value in the
field identified in the first argument.

Since we are checking to make sure the number we have created is unique, we
are looking in the table to see if it already exists. If a Null value is
returned, we know it is unique, so we return the number to the query which
uses the new number to append to the table. If we don't get a Null returned,
it means the number is already used, so we discard it and try another one.

Hope that clears it up for you.

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

:

This one was fun to do!
Put a calculated field in your query that calls this function. It generates
the numbers based on your rules. It checks to see if the number already
exists in your tabe and does not use it if it is already in the table.

In the DLookup, I used then name ItemNo for your field name. Since I don't
know your table name, you will have to change "MyTableNameHere" to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex, "A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2", "3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere", "[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

:

Hi Everyone!

I have a table that I've created and I'm trying to do an append query to an
existing table...all my fields match the existing table. One of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the middle 4
characters for this "ItemNo" field so I don't have to manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO DUPLICATES.

Any help would be appreciated. Also could you keep in mind that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Crystal]

Here's what it looks like...

ItemNo Description NewItemNo
SRT-1- CRIOUXH000
TB-1- CRIOUXH000
-19- CRIOUXH000
-30- CRIOUXH000
-101- CRIOUXH000
-103- CRIOUXH000
-107- CRIOUXH000

I have 13,000+ different Descriptions and I need a different ItemNo for each
one.

Crystal
P.S. Thanks for responding so quickly

I tested the function and it returns a different number each time it is run.
Where are you calling it from?

Hi Klatuu -

Okay I used your code and put a calculated field in my query to call the
function...it works...however it's creating the same number for every record
I have in that field. I need it to create a random (unique) ItemNo for each
record (13,000). Is this possible? Sorry for not being more specific.

Thanks - Crystal

You can get more detail in VBA Help, but basically, it looks for a value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the value from.
The second is the name of the domain (a table or query) the field is in.
The last is the criteria to select a record. In this case, we will look for
the ItemNo. We don't care what it is, but the function requires a field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a record
based on the criteria. If it finds a record, it returns the value in the
field identified in the first argument.

Since we are checking to make sure the number we have created is unique, we
are looking in the table to see if it already exists. If a Null value is
returned, we know it is unique, so we return the number to the query which
uses the new number to append to the table. If we don't get a Null returned,
it means the number is already used, so we discard it and try another one.

Hope that clears it up for you.

:

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

:

This one was fun to do!
Put a calculated field in your query that calls this function. It generates
the numbers based on your rules. It checks to see if the number already
exists in your tabe and does not use it if it is already in the table.

In the DLookup, I used then name ItemNo for your field name. Since I don't
know your table name, you will have to change "MyTableNameHere" to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex, "A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2", "3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere", "[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

:

Hi Everyone!

I have a table that I've created and I'm trying to do an append query to an
existing table...all my fields match the existing table. One of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the middle 4
characters for this "ItemNo" field so I don't have to manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO DUPLICATES.

Any help would be appreciated. Also could you keep in mind that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Klatuu]

I understand what you want. As I said before, I tested the function and it
returns a different number each time I call it.
To be able to help, I need to know where you are calling it from.
If you are calling it using a calculated field in your query, it is possible
it is only running one time. I can't remember why that happens, but I have
seen it before.

Open the module where the funtion is in design view. Put your cursor on the
first line of executable code in the function. Press F9 to set a break point.
run your query. It should go into debug and highlight the line you set the
break point on. Click on Debug, Run to Cursor. See if it stops on the next
record. If it does not, then we need to find out what the problem is.

One other question. Are you building a new table or updating records in an
existing table?

Here's what it looks like...

ItemNo Description NewItemNo
SRT-1- CRIOUXH000
TB-1- CRIOUXH000
-19- CRIOUXH000
-30- CRIOUXH000
-101- CRIOUXH000
-103- CRIOUXH000
-107- CRIOUXH000

I have 13,000+ different Descriptions and I need a different ItemNo for each
one.

Crystal
P.S. Thanks for responding so quickly

I tested the function and it returns a different number each time it is run.
Where are you calling it from?

Hi Klatuu -

Okay I used your code and put a calculated field in my query to call the
function...it works...however it's creating the same number for every record
I have in that field. I need it to create a random (unique) ItemNo for each
record (13,000). Is this possible? Sorry for not being more specific.

Thanks - Crystal


:

You can get more detail in VBA Help, but basically, it looks for a value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the value from.
The second is the name of the domain (a table or query) the field is in.
The last is the criteria to select a record. In this case, we will look for
the ItemNo. We don't care what it is, but the function requires a field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a record
based on the criteria. If it finds a record, it returns the value in the
field identified in the first argument.

Since we are checking to make sure the number we have created is unique, we
are looking in the table to see if it already exists. If a Null value is
returned, we know it is unique, so we return the number to the query which
uses the new number to append to the table. If we don't get a Null returned,
it means the number is already used, so we discard it and try another one.

Hope that clears it up for you.

:

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

:

This one was fun to do!
Put a calculated field in your query that calls this function. It generates
the numbers based on your rules. It checks to see if the number already
exists in your tabe and does not use it if it is already in the table.

In the DLookup, I used then name ItemNo for your field name. Since I don't
know your table name, you will have to change "MyTableNameHere" to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex, "A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2", "3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere", "[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

:

Hi Everyone!

I have a table that I've created and I'm trying to do an append query to an
existing table...all my fields match the existing table. One of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the middle 4
characters for this "ItemNo" field so I don't have to manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO DUPLICATES.

Any help would be appreciated. Also could you keep in mind that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Crystal]

Hi Klatuu -

Your code works great and your right it does generate a different number
each time you run the application. Please see my earlier response to
you...my apologies for not being more specific earlier.

Thanks - C

I tested the function and it returns a different number each time it is run.
Where are you calling it from?

Hi Klatuu -

Okay I used your code and put a calculated field in my query to call the
function...it works...however it's creating the same number for every record
I have in that field. I need it to create a random (unique) ItemNo for each
record (13,000). Is this possible? Sorry for not being more specific.

Thanks - Crystal

You can get more detail in VBA Help, but basically, it looks for a value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the value from.
The second is the name of the domain (a table or query) the field is in.
The last is the criteria to select a record. In this case, we will look for
the ItemNo. We don't care what it is, but the function requires a field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a record
based on the criteria. If it finds a record, it returns the value in the
field identified in the first argument.

Since we are checking to make sure the number we have created is unique, we
are looking in the table to see if it already exists. If a Null value is
returned, we know it is unique, so we return the number to the query which
uses the new number to append to the table. If we don't get a Null returned,
it means the number is already used, so we discard it and try another one.

Hope that clears it up for you.

:

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

:

This one was fun to do!
Put a calculated field in your query that calls this function. It generates
the numbers based on your rules. It checks to see if the number already
exists in your tabe and does not use it if it is already in the table.

In the DLookup, I used then name ItemNo for your field name. Since I don't
know your table name, you will have to change "MyTableNameHere" to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex, "A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2", "3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere", "[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

:

Hi Everyone!

I have a table that I've created and I'm trying to do an append query to an
existing table...all my fields match the existing table. One of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the middle 4
characters for this "ItemNo" field so I don't have to manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO DUPLICATES.

Any help would be appreciated. Also could you keep in mind that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Crystal]

I'm trying to update an existing table.
Here's what the code looks like in it's entirety

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex, "A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R",
"S", "T", _
"U", "V", "W", "X", "Y", "Z", _
"1", "2", "3", "4", "5", "6", "7", "8", "9", "0")
Loop
If IsNull(DLookup("[ItemNo]", "tbl_CRI", "[ItemNo] = '" _
& strProdNumber & "'")) Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

I put my cursor on the Do While True, F9, Debug, Run to Cursor...it
highlites in yellow and doesn't move to next line. Your probably pulling
your hair out with me...LOL

C

I understand what you want. As I said before, I tested the function and it
returns a different number each time I call it.
To be able to help, I need to know where you are calling it from.
If you are calling it using a calculated field in your query, it is possible
it is only running one time. I can't remember why that happens, but I have
seen it before.

Open the module where the funtion is in design view. Put your cursor on the
first line of executable code in the function. Press F9 to set a break point.
run your query. It should go into debug and highlight the line you set the
break point on. Click on Debug, Run to Cursor. See if it stops on the next
record. If it does not, then we need to find out what the problem is.

One other question. Are you building a new table or updating records in an
existing table?

Here's what it looks like...

ItemNo Description NewItemNo
SRT-1- CRIOUXH000
TB-1- CRIOUXH000
-19- CRIOUXH000
-30- CRIOUXH000
-101- CRIOUXH000
-103- CRIOUXH000
-107- CRIOUXH000

I have 13,000+ different Descriptions and I need a different ItemNo for each
one.

Crystal
P.S. Thanks for responding so quickly

I tested the function and it returns a different number each time it is run.
Where are you calling it from?

:

Hi Klatuu -

Okay I used your code and put a calculated field in my query to call the
function...it works...however it's creating the same number for every record
I have in that field. I need it to create a random (unique) ItemNo for each
record (13,000). Is this possible? Sorry for not being more specific.

Thanks - Crystal


:

You can get more detail in VBA Help, but basically, it looks for a value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the value from.
The second is the name of the domain (a table or query) the field is in.
The last is the criteria to select a record. In this case, we will look for
the ItemNo. We don't care what it is, but the function requires a field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a record
based on the criteria. If it finds a record, it returns the value in the
field identified in the first argument.

Since we are checking to make sure the number we have created is unique, we
are looking in the table to see if it already exists. If a Null value is
returned, we know it is unique, so we return the number to the query which
uses the new number to append to the table. If we don't get a Null returned,
it means the number is already used, so we discard it and try another one.

Hope that clears it up for you.

:

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

:

This one was fun to do!
Put a calculated field in your query that calls this function. It generates
the numbers based on your rules. It checks to see if the number already
exists in your tabe and does not use it if it is already in the table.

In the DLookup, I used then name ItemNo for your field name. Since I don't
know your table name, you will have to change "MyTableNameHere" to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex, "A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2", "3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere", "[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

:

Hi Everyone!

I have a table that I've created and I'm trying to do an append query to an
existing table...all my fields match the existing table. One of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the middle 4
characters for this "ItemNo" field so I don't have to manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO DUPLICATES.

Any help would be appreciated. Also could you keep in mind that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Klatuu]

Sorry, you did describe what you are doing. I had forgotten.

In my foggy memory I think this has to do with not passing an argument to
the query. I don't know if this will work or not, but if you are willing to
experiment a bit, we can make it work.

Let's try adding an argument first. I don't think we need to do anything
with it.

So where you call the function, pick any field and put it in as the argument
like this:

MakeProductNumber([AnyField])

Then in the code for the function,
Public Function MakeProdNumber(IgnoreMe as ???) As String

??? should be the same data type as the field you pick to be the argument.
Let me know what happens.
(don't forget to wear protective googles and hearing protection)

Hi Klatuu -

Your code works great and your right it does generate a different number
each time you run the application. Please see my earlier response to
you...my apologies for not being more specific earlier.

Thanks - C

I tested the function and it returns a different number each time it is run.
Where are you calling it from?

Hi Klatuu -

Okay I used your code and put a calculated field in my query to call the
function...it works...however it's creating the same number for every record
I have in that field. I need it to create a random (unique) ItemNo for each
record (13,000). Is this possible? Sorry for not being more specific.

Thanks - Crystal


:

You can get more detail in VBA Help, but basically, it looks for a value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the value from.
The second is the name of the domain (a table or query) the field is in.
The last is the criteria to select a record. In this case, we will look for
the ItemNo. We don't care what it is, but the function requires a field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a record
based on the criteria. If it finds a record, it returns the value in the
field identified in the first argument.

Since we are checking to make sure the number we have created is unique, we
are looking in the table to see if it already exists. If a Null value is
returned, we know it is unique, so we return the number to the query which
uses the new number to append to the table. If we don't get a Null returned,
it means the number is already used, so we discard it and try another one.

Hope that clears it up for you.

:

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

:

This one was fun to do!
Put a calculated field in your query that calls this function. It generates
the numbers based on your rules. It checks to see if the number already
exists in your tabe and does not use it if it is already in the table.

In the DLookup, I used then name ItemNo for your field name. Since I don't
know your table name, you will have to change "MyTableNameHere" to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex, "A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2", "3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere", "[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

:

Hi Everyone!

I have a table that I've created and I'm trying to do an append query to an
existing table...all my fields match the existing table. One of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the middle 4
characters for this "ItemNo" field so I don't have to manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO DUPLICATES.

Any help would be appreciated. Also could you keep in mind that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[John Spencer]

You just need to make a slight change to the function. Require an
argument - you don't need to use it.
In the query, call the function with any field as an argument.

I'm not sure that the function will work as advertised, as I didn't bench
test it.

Public Function MakeProdNumber(vAnything) As String
....

Update query call
Field: FieldToUpdate
Update To: MakeProdNumber([AnyField-Even-fieldtoUpdate])

I understand what you want. As I said before, I tested the function and it
returns a different number each time I call it.
To be able to help, I need to know where you are calling it from.
If you are calling it using a calculated field in your query, it is
possible
it is only running one time. I can't remember why that happens, but I
have
seen it before.

Open the module where the funtion is in design view. Put your cursor on
the
first line of executable code in the function. Press F9 to set a break
point.
run your query. It should go into debug and highlight the line you set
the
break point on. Click on Debug, Run to Cursor. See if it stops on the
next
record. If it does not, then we need to find out what the problem is.

One other question. Are you building a new table or updating records in
an
existing table?

Here's what it looks like...

ItemNo Description NewItemNo
SRT-1- CRIOUXH000
TB-1- CRIOUXH000
-19- CRIOUXH000
-30- CRIOUXH000
-101- CRIOUXH000
-103- CRIOUXH000
-107- CRIOUXH000

I have 13,000+ different Descriptions and I need a different ItemNo for
each
one.

Crystal
P.S. Thanks for responding so quickly

I tested the function and it returns a different number each time it is
run.
Where are you calling it from?

:

Hi Klatuu -

Okay I used your code and put a calculated field in my query to call
the
function...it works...however it's creating the same number for every
record
I have in that field. I need it to create a random (unique) ItemNo
for each
record (13,000). Is this possible? Sorry for not being more
specific.

Thanks - Crystal


:

You can get more detail in VBA Help, but basically, it looks for a
value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the
value from.
The second is the name of the domain (a table or query) the field
is in.
The last is the criteria to select a record. In this case, we will
look for
the ItemNo. We don't care what it is, but the function requires a
field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a
record
based on the criteria. If it finds a record, it returns the value
in the
field identified in the first argument.

Since we are checking to make sure the number we have created is
unique, we
are looking in the table to see if it already exists. If a Null
value is
returned, we know it is unique, so we return the number to the
query which
uses the new number to append to the table. If we don't get a Null
returned,
it means the number is already used, so we discard it and try
another one.

Hope that clears it up for you.

:

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

:

This one was fun to do!
Put a calculated field in your query that calls this function.
It generates
the numbers based on your rules. It checks to see if the
number already
exists in your tabe and does not use it if it is already in the
table.

In the DLookup, I used then name ItemNo for your field name.
Since I don't
know your table name, you will have to change "MyTableNameHere"
to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex,
"A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N",
"O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2",
"3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere",
"[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

:

Hi Everyone!

I have a table that I've created and I'm trying to do an
append query to an
existing table...all my fields match the existing table. One
of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the
middle 4
characters for this "ItemNo" field so I don't have to
manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly
which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO
DUPLICATES.

Any help would be appreciated. Also could you keep in mind
that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Klatuu]

Thanks, John. That validates what I thought the problem was. I missed that
point in my original design.
The function does work as advertised.

You just need to make a slight change to the function. Require an
argument - you don't need to use it.
In the query, call the function with any field as an argument.

I'm not sure that the function will work as advertised, as I didn't bench
test it.

Public Function MakeProdNumber(vAnything) As String
....

Update query call
Field: FieldToUpdate
Update To: MakeProdNumber([AnyField-Even-fieldtoUpdate])

I understand what you want. As I said before, I tested the function and it
returns a different number each time I call it.
To be able to help, I need to know where you are calling it from.
If you are calling it using a calculated field in your query, it is
possible
it is only running one time. I can't remember why that happens, but I
have
seen it before.

Open the module where the funtion is in design view. Put your cursor on
the
first line of executable code in the function. Press F9 to set a break
point.
run your query. It should go into debug and highlight the line you set
the
break point on. Click on Debug, Run to Cursor. See if it stops on the
next
record. If it does not, then we need to find out what the problem is.

One other question. Are you building a new table or updating records in
an
existing table?

Here's what it looks like...

ItemNo Description NewItemNo
SRT-1- CRIOUXH000
TB-1- CRIOUXH000
-19- CRIOUXH000
-30- CRIOUXH000
-101- CRIOUXH000
-103- CRIOUXH000
-107- CRIOUXH000

I have 13,000+ different Descriptions and I need a different ItemNo for
each
one.

Crystal
P.S. Thanks for responding so quickly
:

I tested the function and it returns a different number each time it is
run.
Where are you calling it from?

:

Hi Klatuu -

Okay I used your code and put a calculated field in my query to call
the
function...it works...however it's creating the same number for every
record
I have in that field. I need it to create a random (unique) ItemNo
for each
record (13,000). Is this possible? Sorry for not being more
specific.

Thanks - Crystal


:

You can get more detail in VBA Help, but basically, it looks for a
value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the
value from.
The second is the name of the domain (a table or query) the field
is in.
The last is the criteria to select a record. In this case, we will
look for
the ItemNo. We don't care what it is, but the function requires a
field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a
record
based on the criteria. If it finds a record, it returns the value
in the
field identified in the first argument.

Since we are checking to make sure the number we have created is
unique, we
are looking in the table to see if it already exists. If a Null
value is
returned, we know it is unique, so we return the number to the
query which
uses the new number to append to the table. If we don't get a Null
returned,
it means the number is already used, so we discard it and try
another one.

Hope that clears it up for you.

:

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

:

This one was fun to do!
Put a calculated field in your query that calls this function.
It generates
the numbers based on your rules. It checks to see if the
number already
exists in your tabe and does not use it if it is already in the
table.

In the DLookup, I used then name ItemNo for your field name.
Since I don't
know your table name, you will have to change "MyTableNameHere"
to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex,
"A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N",
"O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2",
"3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere",
"[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

:

Hi Everyone!

I have a table that I've created and I'm trying to do an
append query to an
existing table...all my fields match the existing table. One
of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the
middle 4
characters for this "ItemNo" field so I don't have to
manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly
which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO
DUPLICATES.

Any help would be appreciated. Also could you keep in mind
that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Crystal]

Klatuu -
So I put John's suggestion (below) following the End Function of your code?

Public Function MakeProdNumber(vAnything) As String
End Function

Then I do an UPDATE Query
Field: ItemNo
Table: tbl_CRI
Update to: MakeProdNumber([vAnything-Even-fieldtoUpdate])
You said to pick any field I have in my table...StockNo, Description,
ShortDescr...can you tell me what John means by [vAnything-Even-ItemNo]

TY - C

Thanks, John. That validates what I thought the problem was. I missed that
point in my original design.
The function does work as advertised.

You just need to make a slight change to the function. Require an
argument - you don't need to use it.
In the query, call the function with any field as an argument.

I'm not sure that the function will work as advertised, as I didn't bench
test it.

Public Function MakeProdNumber(vAnything) As String
....

Update query call
Field: FieldToUpdate
Update To: MakeProdNumber([AnyField-Even-fieldtoUpdate])

I understand what you want. As I said before, I tested the function and it
returns a different number each time I call it.
To be able to help, I need to know where you are calling it from.
If you are calling it using a calculated field in your query, it is
possible
it is only running one time. I can't remember why that happens, but I
have
seen it before.

Open the module where the funtion is in design view. Put your cursor on
the
first line of executable code in the function. Press F9 to set a break
point.
run your query. It should go into debug and highlight the line you set
the
break point on. Click on Debug, Run to Cursor. See if it stops on the
next
record. If it does not, then we need to find out what the problem is.

One other question. Are you building a new table or updating records in
an
existing table?

:

Here's what it looks like...

ItemNo Description NewItemNo
SRT-1- CRIOUXH000
TB-1- CRIOUXH000
-19- CRIOUXH000
-30- CRIOUXH000
-101- CRIOUXH000
-103- CRIOUXH000
-107- CRIOUXH000

I have 13,000+ different Descriptions and I need a different ItemNo for
each
one.

Crystal
P.S. Thanks for responding so quickly
:

I tested the function and it returns a different number each time it is
run.
Where are you calling it from?

:

Hi Klatuu -

Okay I used your code and put a calculated field in my query to call
the
function...it works...however it's creating the same number for every
record
I have in that field. I need it to create a random (unique) ItemNo
for each
record (13,000). Is this possible? Sorry for not being more
specific.

Thanks - Crystal


:

You can get more detail in VBA Help, but basically, it looks for a
value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the
value from.
The second is the name of the domain (a table or query) the field
is in.
The last is the criteria to select a record. In this case, we will
look for
the ItemNo. We don't care what it is, but the function requires a
field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a
record
based on the criteria. If it finds a record, it returns the value
in the
field identified in the first argument.

Since we are checking to make sure the number we have created is
unique, we
are looking in the table to see if it already exists. If a Null
value is
returned, we know it is unique, so we return the number to the
query which
uses the new number to append to the table. If we don't get a Null
returned,
it means the number is already used, so we discard it and try
another one.

Hope that clears it up for you.

:

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

:

This one was fun to do!
Put a calculated field in your query that calls this function.
It generates
the numbers based on your rules. It checks to see if the
number already
exists in your tabe and does not use it if it is already in the
table.

In the DLookup, I used then name ItemNo for your field name.
Since I don't
know your table name, you will have to change "MyTableNameHere"
to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex,
"A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N",
"O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2",
"3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere",
"[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

:

Hi Everyone!

I have a table that I've created and I'm trying to do an
append query to an
existing table...all my fields match the existing table. One
of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the
middle 4
characters for this "ItemNo" field so I don't have to
manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly
which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO
DUPLICATES.

Any help would be appreciated. Also could you keep in mind
that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Klatuu]

He means what I said, just any field. My apologies, I had forgotten about
having to have an argument in a function for it to execute for each row in
the query.

Going home now, let me know how it works out.

Klatuu -
So I put John's suggestion (below) following the End Function of your code?

Public Function MakeProdNumber(vAnything) As String
End Function

Then I do an UPDATE Query
Field: ItemNo
Table: tbl_CRI
Update to: MakeProdNumber([vAnything-Even-fieldtoUpdate])
You said to pick any field I have in my table...StockNo, Description,
ShortDescr...can you tell me what John means by [vAnything-Even-ItemNo]

TY - C

Thanks, John. That validates what I thought the problem was. I missed that
point in my original design.
The function does work as advertised.

You just need to make a slight change to the function. Require an
argument - you don't need to use it.
In the query, call the function with any field as an argument.

I'm not sure that the function will work as advertised, as I didn't bench
test it.

Public Function MakeProdNumber(vAnything) As String
....

Update query call
Field: FieldToUpdate
Update To: MakeProdNumber([AnyField-Even-fieldtoUpdate])


I understand what you want. As I said before, I tested the function and it
returns a different number each time I call it.
To be able to help, I need to know where you are calling it from.
If you are calling it using a calculated field in your query, it is
possible
it is only running one time. I can't remember why that happens, but I
have
seen it before.

Open the module where the funtion is in design view. Put your cursor on
the
first line of executable code in the function. Press F9 to set a break
point.
run your query. It should go into debug and highlight the line you set
the
break point on. Click on Debug, Run to Cursor. See if it stops on the
next
record. If it does not, then we need to find out what the problem is.

One other question. Are you building a new table or updating records in
an
existing table?

:

Here's what it looks like...

ItemNo Description NewItemNo
SRT-1- CRIOUXH000
TB-1- CRIOUXH000
-19- CRIOUXH000
-30- CRIOUXH000
-101- CRIOUXH000
-103- CRIOUXH000
-107- CRIOUXH000

I have 13,000+ different Descriptions and I need a different ItemNo for
each
one.

Crystal
P.S. Thanks for responding so quickly
:

I tested the function and it returns a different number each time it is
run.
Where are you calling it from?

:

Hi Klatuu -

Okay I used your code and put a calculated field in my query to call
the
function...it works...however it's creating the same number for every
record
I have in that field. I need it to create a random (unique) ItemNo
for each
record (13,000). Is this possible? Sorry for not being more
specific.

Thanks - Crystal


:

You can get more detail in VBA Help, but basically, it looks for a
value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the
value from.
The second is the name of the domain (a table or query) the field
is in.
The last is the criteria to select a record. In this case, we will
look for
the ItemNo. We don't care what it is, but the function requires a
field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a
record
based on the criteria. If it finds a record, it returns the value
in the
field identified in the first argument.

Since we are checking to make sure the number we have created is
unique, we
are looking in the table to see if it already exists. If a Null
value is
returned, we know it is unique, so we return the number to the
query which
uses the new number to append to the table. If we don't get a Null
returned,
it means the number is already used, so we discard it and try
another one.

Hope that clears it up for you.

:

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

:

This one was fun to do!
Put a calculated field in your query that calls this function.
It generates
the numbers based on your rules. It checks to see if the
number already
exists in your tabe and does not use it if it is already in the
table.

In the DLookup, I used then name ItemNo for your field name.
Since I don't
know your table name, you will have to change "MyTableNameHere"
to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex,
"A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N",
"O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2",
"3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere",
"[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

:

Hi Everyone!

I have a table that I've created and I'm trying to do an
append query to an
existing table...all my fields match the existing table. One
of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the
middle 4
characters for this "ItemNo" field so I don't have to
manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly
which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO
DUPLICATES.

Any help would be appreciated. Also could you keep in mind
that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Crystal]

IT WORKS...SWEET!!!!
THANKS SO MUCH

You just need to make a slight change to the function. Require an
argument - you don't need to use it.
In the query, call the function with any field as an argument.

I'm not sure that the function will work as advertised, as I didn't bench
test it.

Public Function MakeProdNumber(vAnything) As String
....

Update query call
Field: FieldToUpdate
Update To: MakeProdNumber([AnyField-Even-fieldtoUpdate])

I understand what you want. As I said before, I tested the function and it
returns a different number each time I call it.
To be able to help, I need to know where you are calling it from.
If you are calling it using a calculated field in your query, it is
possible
it is only running one time. I can't remember why that happens, but I
have
seen it before.

Open the module where the funtion is in design view. Put your cursor on
the
first line of executable code in the function. Press F9 to set a break
point.
run your query. It should go into debug and highlight the line you set
the
break point on. Click on Debug, Run to Cursor. See if it stops on the
next
record. If it does not, then we need to find out what the problem is.

One other question. Are you building a new table or updating records in
an
existing table?

Here's what it looks like...

ItemNo Description NewItemNo
SRT-1- CRIOUXH000
TB-1- CRIOUXH000
-19- CRIOUXH000
-30- CRIOUXH000
-101- CRIOUXH000
-103- CRIOUXH000
-107- CRIOUXH000

I have 13,000+ different Descriptions and I need a different ItemNo for
each
one.

Crystal
P.S. Thanks for responding so quickly
:

I tested the function and it returns a different number each time it is
run.
Where are you calling it from?

:

Hi Klatuu -

Okay I used your code and put a calculated field in my query to call
the
function...it works...however it's creating the same number for every
record
I have in that field. I need it to create a random (unique) ItemNo
for each
record (13,000). Is this possible? Sorry for not being more
specific.

Thanks - Crystal


:

You can get more detail in VBA Help, but basically, it looks for a
value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the
value from.
The second is the name of the domain (a table or query) the field
is in.
The last is the criteria to select a record. In this case, we will
look for
the ItemNo. We don't care what it is, but the function requires a
field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a
record
based on the criteria. If it finds a record, it returns the value
in the
field identified in the first argument.

Since we are checking to make sure the number we have created is
unique, we
are looking in the table to see if it already exists. If a Null
value is
returned, we know it is unique, so we return the number to the
query which
uses the new number to append to the table. If we don't get a Null
returned,
it means the number is already used, so we discard it and try
another one.

Hope that clears it up for you.

:

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

:

This one was fun to do!
Put a calculated field in your query that calls this function.
It generates
the numbers based on your rules. It checks to see if the
number already
exists in your tabe and does not use it if it is already in the
table.

In the DLookup, I used then name ItemNo for your field name.
Since I don't
know your table name, you will have to change "MyTableNameHere"
to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex,
"A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N",
"O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2",
"3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere",
"[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

:

Hi Everyone!

I have a table that I've created and I'm trying to do an
append query to an
existing table...all my fields match the existing table. One
of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the
middle 4
characters for this "ItemNo" field so I don't have to
manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly
which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO
DUPLICATES.

Any help would be appreciated. Also could you keep in mind
that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Crystal]

IT WORKS...SWEET!!!!
THANKS SO MUCH

He means what I said, just any field. My apologies, I had forgotten about
having to have an argument in a function for it to execute for each row in
the query.

Going home now, let me know how it works out.

Klatuu -
So I put John's suggestion (below) following the End Function of your code?

Public Function MakeProdNumber(vAnything) As String
End Function

Then I do an UPDATE Query
Field: ItemNo
Table: tbl_CRI
Update to: MakeProdNumber([vAnything-Even-fieldtoUpdate])
You said to pick any field I have in my table...StockNo, Description,
ShortDescr...can you tell me what John means by [vAnything-Even-ItemNo]

TY - C

Thanks, John. That validates what I thought the problem was. I missed that
point in my original design.
The function does work as advertised.

:

You just need to make a slight change to the function. Require an
argument - you don't need to use it.
In the query, call the function with any field as an argument.

I'm not sure that the function will work as advertised, as I didn't bench
test it.

Public Function MakeProdNumber(vAnything) As String
....

Update query call
Field: FieldToUpdate
Update To: MakeProdNumber([AnyField-Even-fieldtoUpdate])


I understand what you want. As I said before, I tested the function and it
returns a different number each time I call it.
To be able to help, I need to know where you are calling it from.
If you are calling it using a calculated field in your query, it is
possible
it is only running one time. I can't remember why that happens, but I
have
seen it before.

Open the module where the funtion is in design view. Put your cursor on
the
first line of executable code in the function. Press F9 to set a break
point.
run your query. It should go into debug and highlight the line you set
the
break point on. Click on Debug, Run to Cursor. See if it stops on the
next
record. If it does not, then we need to find out what the problem is.

One other question. Are you building a new table or updating records in
an
existing table?

:

Here's what it looks like...

ItemNo Description NewItemNo
SRT-1- CRIOUXH000
TB-1- CRIOUXH000
-19- CRIOUXH000
-30- CRIOUXH000
-101- CRIOUXH000
-103- CRIOUXH000
-107- CRIOUXH000

I have 13,000+ different Descriptions and I need a different ItemNo for
each
one.

Crystal
P.S. Thanks for responding so quickly
:

I tested the function and it returns a different number each time it is
run.
Where are you calling it from?

:

Hi Klatuu -

Okay I used your code and put a calculated field in my query to call
the
function...it works...however it's creating the same number for every
record
I have in that field. I need it to create a random (unique) ItemNo
for each
record (13,000). Is this possible? Sorry for not being more
specific.

Thanks - Crystal


:

You can get more detail in VBA Help, but basically, it looks for a
value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the
value from.
The second is the name of the domain (a table or query) the field
is in.
The last is the criteria to select a record. In this case, we will
look for
the ItemNo. We don't care what it is, but the function requires a
field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a
record
based on the criteria. If it finds a record, it returns the value
in the
field identified in the first argument.

Since we are checking to make sure the number we have created is
unique, we
are looking in the table to see if it already exists. If a Null
value is
returned, we know it is unique, so we return the number to the
query which
uses the new number to append to the table. If we don't get a Null
returned,
it means the number is already used, so we discard it and try
another one.

Hope that clears it up for you.

:

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

:

This one was fun to do!
Put a calculated field in your query that calls this function.
It generates
the numbers based on your rules. It checks to see if the
number already
exists in your tabe and does not use it if it is already in the
table.

In the DLookup, I used then name ItemNo for your field name.
Since I don't
know your table name, you will have to change "MyTableNameHere"
to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex,
"A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N",
"O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2",
"3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere",
"[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

:

Hi Everyone!

I have a table that I've created and I'm trying to do an
append query to an
existing table...all my fields match the existing table. One
of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the
middle 4
characters for this "ItemNo" field so I don't have to
manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly
which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO
DUPLICATES.

Any help would be appreciated. Also could you keep in mind
that I'm a
NEWBIE when writing code.

Thanks - Crystal

 

[Klatuu]

Thanks for letting me know.
Glad to help.

IT WORKS...SWEET!!!!
THANKS SO MUCH

He means what I said, just any field. My apologies, I had forgotten about
having to have an argument in a function for it to execute for each row in
the query.

Going home now, let me know how it works out.

Klatuu -
So I put John's suggestion (below) following the End Function of your code?

Public Function MakeProdNumber(vAnything) As String
End Function

Then I do an UPDATE Query
Field: ItemNo
Table: tbl_CRI
Update to: MakeProdNumber([vAnything-Even-fieldtoUpdate])
You said to pick any field I have in my table...StockNo, Description,
ShortDescr...can you tell me what John means by [vAnything-Even-ItemNo]

TY - C

:

Thanks, John. That validates what I thought the problem was. I missed that
point in my original design.
The function does work as advertised.

:

You just need to make a slight change to the function. Require an
argument - you don't need to use it.
In the query, call the function with any field as an argument.

I'm not sure that the function will work as advertised, as I didn't bench
test it.

Public Function MakeProdNumber(vAnything) As String
....

Update query call
Field: FieldToUpdate
Update To: MakeProdNumber([AnyField-Even-fieldtoUpdate])


I understand what you want. As I said before, I tested the function and it
returns a different number each time I call it.
To be able to help, I need to know where you are calling it from.
If you are calling it using a calculated field in your query, it is
possible
it is only running one time. I can't remember why that happens, but I
have
seen it before.

Open the module where the funtion is in design view. Put your cursor on
the
first line of executable code in the function. Press F9 to set a break
point.
run your query. It should go into debug and highlight the line you set
the
break point on. Click on Debug, Run to Cursor. See if it stops on the
next
record. If it does not, then we need to find out what the problem is.

One other question. Are you building a new table or updating records in
an
existing table?

:

Here's what it looks like...

ItemNo Description NewItemNo
SRT-1- CRIOUXH000
TB-1- CRIOUXH000
-19- CRIOUXH000
-30- CRIOUXH000
-101- CRIOUXH000
-103- CRIOUXH000
-107- CRIOUXH000

I have 13,000+ different Descriptions and I need a different ItemNo for
each
one.

Crystal
P.S. Thanks for responding so quickly
:

I tested the function and it returns a different number each time it is
run.
Where are you calling it from?

:

Hi Klatuu -

Okay I used your code and put a calculated field in my query to call
the
function...it works...however it's creating the same number for every
record
I have in that field. I need it to create a random (unique) ItemNo
for each
record (13,000). Is this possible? Sorry for not being more
specific.

Thanks - Crystal


:

You can get more detail in VBA Help, but basically, it looks for a
value in
your database based on the criteria you give it.
The first argument is the name of the field you want to get the
value from.
The second is the name of the domain (a table or query) the field
is in.
The last is the criteria to select a record. In this case, we will
look for
the ItemNo. We don't care what it is, but the function requires a
field
name. We are looking for the new ProductNumber we just generated.
The DLookup function will return a Null value if it can't find a
record
based on the criteria. If it finds a record, it returns the value
in the
field identified in the first argument.

Since we are checking to make sure the number we have created is
unique, we
are looking in the table to see if it already exists. If a Null
value is
returned, we know it is unique, so we return the number to the
query which
uses the new number to append to the table. If we don't get a Null
returned,
it means the number is already used, so we discard it and try
another one.

Hope that clears it up for you.

:

Hi Klatuu -
Could you tell me what the DLookup means.

Thanks - Crystal

:

This one was fun to do!
Put a calculated field in your query that calls this function.
It generates
the numbers based on your rules. It checks to see if the
number already
exists in your tabe and does not use it if it is already in the
table.

In the DLookup, I used then name ItemNo for your field name.
Since I don't
know your table name, you will have to change "MyTableNameHere"
to the name
of the table you are appending to.

Public Function MakeProdNumber() As String
Dim strProdNumber As String
Dim intIndex As Integer

Do While True
Randomize
Do While Len(strProdNumber) < 4
intIndex = ((36 * Rnd) + 1)
strProdNumber = strProdNumber & Choose(intIndex,
"A", "B", "C",
"D", "E", _
"F", "G", "H", "I", "J", "K", "L", "M", "N",
"O", "P", "Q",
"R", "S", _
"T", "U", "V", "W", "X", "Y", "Z", "1", "2",
"3", "4", "5",
"6", _
"7", "8", "9", "0")
Loop
If IsNull(DLookup, "[ItemNo]", "MyTableNameHere",
"[ItemNo] = '" _
& strProdNumber & "'") Then
Exit Do
Else
strProdNumber = ""
End If
Loop
MakeProdNumber = "CRI" & strProdNumber & "000"
End Function

Post back if you have questions

:

Hi Everyone!

I have a table that I've created and I'm trying to do an
append query to an
existing table...all my fields match the existing table. One
of my fields
"ItemNo" looks like this... CRIA3DA000

My question is...
Can someone show me how to write the code to generate the
middle 4
characters for this "ItemNo" field so I don't have to
manually enter this for
13000+ records.
*the CRI is always the first 3 characters
*the 000 is always the last 3 characters...
so I only need the middle 4 characters to output randomly
which can include
NUMERIC and ALPHA but NO symbols, punctuations, etc and NO
DUPLICATES.

Any help would be appreciated. Also could you keep in mind
that I'm a
NEWBIE when writing code.

Thanks - Crystal