Generating unique ID from Combobox

S

Splinters

Registered User.
Local time
Yesterday, 22:45
Joined
Sep 6, 2007
Messages
67
I would like to create a unique ID number (currently the Primary Key) for a table in Access 2000.

I am using 4 elements for the current manual entry, and want to automate it after I make one selection on the Combo box.

The elements are:
1: The species of the wood I am entering into the inventory.
2: The last two digits of the current year.
3: A ":" as a separator.
4: A sequential number unique for each species of wood as purchased in the current year. This would reset when the year changes.

For example, I can use the Combo Box to select either Ash, Maple, Myrtle or Elm. When I make the selection, I want the system to automatically generate the rest of the ID code.

If I had the sixth piece of Maple for 2007 I would select Maple in the box, then the generated code would be Maple07:006. The next piece of wood might be the fiftieth piece of Myrtle, so it would be Myrtle07:050. On Jan 1, 2008 the sequence numbers would all reset to 001 - so the first piece of Maple that year would be Maple08:001.

Can this be done? It would help me to avoid the problem of skipping numbers when I loose track of the last piece of the same species I entered. Can I do this with a simply formula macro, or would it need VBA?

And can this code ten be used in the Primary Key for that table - and will I need to re-enter all the 300+ pieces I already have in the table?

I had thought this could be done with a 3 field key - but I'd like to keep it as simple as possible.
 
You can use anything for primary key as long as it is unique.
Now concerning the second part of your question, you can have a VBA code generating
the key providing that VBA "knows" how to do this.
From your description however I can't figure out how VBA can decide whether the next piece is the 15th or the 64th.
From where will VBA retrieve this info?

The 2nd part of your key is easy to generate.
Just use the Year() funciton of VBA and trim the first two digits,
(you may have to convert the result from number to string first).
Having all parts you make a concatenation easily by using the "&" operator like:
part1 & part2 & part3 & part 4
where part3 will be just a ":", in other words part3=":"
 
Here's what I've come up with so far - the first 3 parts are working, it's the 4th part (the seq number) that is currently alluding me. I'm new to Access & queries, so it's probably right in front of me & I just don't see it yet...

I have tblWood, with a field txtWoodIDcode with entry's that each identify a unique piece of wood. It has the species + the 2 digit year + the sequence in that year for that species.

For example:
txtWoodIDcode
Ash07:001
Ash07:002
Ash07:003
Ash07:004
Elm06:001
Elm06:002
Maple05:001
Maple05:002
Maple05:003

& so forth...

I have a form with a combo box cboSpeciesSelect that I select the first part of the string (the species of wood), the second part (the 2 digit year) is then concatenated onto this from a date function, and then a ":" is added.

So, I now have the substring "Maple07:" At this point it works fine. The problem is when I do a count for all of one species - in this case Maple. I need this to get the next number in the sequence for that species to append to the string for the full ID code. I am trying to do this using a DCount.

For some reason, this does not seem to find matches for the substring - but when I do the same search for the number "07", I get a match for all the records in the field.

The code is:
Private Sub cboSpeciesSelect_Click()
Dim species As String
Dim seq As Integer
species = Me.cboSpeciesSelect.Value & Right(DatePart("yyyy", Date), 2) & ":"
MsgBox (species)
seq = DCount("[txtWoodIDcode]", "tblWood", species) + 1
species = species & seq
Me.SpeciesSelected.Value = species
End Sub

* Note - the Me.SpeciesSelected.Value field is just a temperary field in the form, just for testing of the result.

Am I missing something here? Everything I see indicates that DCount will work with strings - but apparently it may not work with substrings...

I have discovered how to do this in a manual query using the "Like Maple07:" criteria - what I'm not seeing yet is how to write this in the DCount statement, or whatever is the proper method.

Thanks, Stephen
 
I'm a bit confused.
The combobox reads from table

txtWoodIDcode
Ash07:001
Ash07:002
Ash07:003
Ash07:004
Elm06:001
Elm06:002
Maple05:001
Maple05:002
Maple05:003

or it writes to?

And if I understand well, you want each time to search which species is the last one in order to construct the next one by adding +1?
 
fugifox said:
I'm a bit confused.
The combobox reads from table

txtWoodIDcode
Ash07:001
Ash07:002
Ash07:003
Ash07:004
Elm06:001
Elm06:002
Maple05:001
Maple05:002
Maple05:003

or it writes to?

And if I understand well, you want each time to search which species is the last one in order to construct the next one by adding +1?
Click to expand...

It reads the data in that field to get the number of the blanks of that specific species for that year. Actually, all I need is a sequence number so I know what is the next number in that sequence. And yes, the form that holds the combo box will eventually append that compiled ID code to the table as a new record - obviously there are other fields on the record, I just wanted to reduce the confusion by including them. The other fields will be handled by other entry boxes on the main form. I do this manually by hand now - no great strain - but in order to know the next sequence number I need to go to the table to check, or keep it written down on paper. And that's a step I would like to eliminate.

And I am counting only those in a specific species - in the table there are at least 7 species, each with it's own sequence numbers. Look at the sample I gave in the table - there is Ash, Elm and Maple - each with it's own sequence. Note that some have "06:" or "05:" instead of "07:" - because they were purchased in 2006 or 2005. Each year, the sequence restarts at 001.

Hope that helps.

Stephen
 
Well, let's say that your table is exactly the above (in previous post).
Next Ash is Ash07:005?
And similarly next Elm is Elm06:003, next Maple is Maple05:004?
 
fugifox said:
Well, let's say that your table is exactly the above (in previous post).
Next Ash is Ash07:005?
And similarly next Elm is Elm06:003, next Maple is Maple05:004?
Click to expand...

Yes that's it exactly - except that the 05 & 06 sequences are over and I am now using 07.

I should have changed them all to 07 to keep it more apparent what I was doing, but in this example, the next piece of Elm - purchased in 2007 - would be Ash07:001, and the first piece of Maple for this year is coded Maple07:001.

The thing is, I am currently searching for species07: substrings - the 05 & 06 are no longer being used. In 2 months, it will all start over with species08:.

Stephen
 
Splinters said:
Yes that's it exactly - except that the 05 & 06 sequences are over and I am now using 07.

Stephen
Click to expand...
Tha'ts not a problem.
I'll describe you the process for the case your table is as in previous post
and then you can easily adjust it to your table as it is now.

First you will need a Select query to filter every species.
E.g. the query for Maple will be as follows:

SELECT tblWood.ID FROM tblWood WHERE (((tblWood.ID) Like "Maple*"))
ORDER BY tblWood.ID;

This will give you all the Maples sorted, so the last one in the query is the also last in the sequence.
Now that you have collected all your Maples you need to get only the last one using the LAST function of SQL. This is easily done by creating another query upon previous one (qrMaple) like:

SELECT LAST ([qrMaple.ID]) AS seq
FROM qrMaple

where seq is an alias for the resulting record field. You may use any other name.
You can not combine these 2 queries to one because the LAST function will always give you the very last record of your table no matter the WHERE criteria.
Of course you can further improve your DB by using some VBA to eliminate the need for many queries and do the job with only a couple of them if you have any experience about.

Now that you have the last Maple species in your hand, I suppose you know how to continue to find the next in sequence
 
fugifox said:
Tha'ts not a problem.
I'll describe you the process for the case your table is as in previous post
and then you can easily adjust it to your table as it is now.

First you will need a Select query to filter every species.
E.g. the query for Maple will be as follows:

SELECT tblWood.ID FROM tblWood WHERE (((tblWood.ID) Like "Maple*"))
ORDER BY tblWood.ID;
Click to expand...

OK, thanks for this - I entered this in the following code:

Private Sub cboSpeciesSelect_Click()
Dim db As DAO.Database
Dim seq As DAO.QueryDef
Dim strSpecies As String
Dim Str As String
Dim strSQL As String
Set db = CurrentDb
strSpecies = Me.cboSpeciesSelect.Value & Right(DatePart("yyyy", Date), 2) & ":"
MsgBox (strSpecies)
Str = strSpecies & "*"
MsgBox (Str)

seq = SELECT tblWood.txtWoodIDcode FROM tblWood WHERE (((tblWood.txtWoodIDcode) Like "Maple07:*"))ORDER BY tblWood.txtWoodIDcode;

strSpecies = strSpecies & seq
Me.SpeciesSelected.Value = strSpecies
End Sub

It works right up to the Select statement - then I get a syntax error.

This will give you all the Maples sorted, so the last one in the query is the also last in the sequence.
Now that you have collected all your Maples you need to get only the last one using the LAST function of SQL. This is easily done by creating another query upon previous one (qrMaple) like:

SELECT LAST ([qrMaple.ID]) AS seq
FROM qrMaple

where seq is an alias for the resulting record field. You may use any other name.
You can not combine these 2 queries to one because the LAST function will always give you the very last record of your table no matter the WHERE criteria.
Of course you can further improve your DB by using some VBA to eliminate the need for many queries and do the job with only a couple of them if you have any experience about.

Now that you have the last Maple species in your hand, I suppose you know how to continue to find the next in sequence
Click to expand...

Yeah, that I can do. I just have problems with the cryptic form of things like SQL :D
 
Splinters,

You can do this with just one line of code.

Ideally, you'd maintain three fields; TheSpecies, TheYear and TheSequence.

You'd combine them for display purposes.

TheSpecies & TheYear & ":" & Format(TheSequence, "000")

To generate the number, you'd use the BeforeInsert event of your table:

Code: 
TheSequence = Nz(DMax("[TheSequence]", _
                      "tblWood", _
                      "[TheSpecies] = '" & Me.cboSpecies & " And " & _
                      "[TheYear] = " & FOrmat(Date, "yyyy")), 0) + 1
                                                              ^
                                                              |
        This is for the Nz (for the "start" of each year) ----+

In your example (I think) you've combined the species and the year:

Code: 
TheSequence = Nz(DMax("[TheSequence]", _
                      "tblWood", _
                      "[TheSpecies] = '" & Me.cboSpecies & Format(Date, "yyyy")), 0) + 1
                                                                                  ^
                                                                                  |
                           This is for the Nz (for the "start" of each year)  ----+

Wayne
 
WayneRyan said:
Splinters,

You can do this with just one line of code.

Ideally, you'd maintain three fields; TheSpecies, TheYear and TheSequence.

You'd combine them for display purposes.

TheSpecies & TheYear & ":" & Format(TheSequence, "000")

To generate the number, you'd use the BeforeInsert event of your table:

Code: 
TheSequence = Nz(DMax("[TheSequence]", _
                      "tblWood", _
                      "[TheSpecies] = '" & Me.cboSpecies & " And " & _
                      "[TheYear] = " & FOrmat(Date, "yyyy")), 0) + 1
                                                              ^
                                                              |
        This is for the Nz (for the "start" of each year) ----+

In your example (I think) you've combined the species and the year:

Code: 
TheSequence = Nz(DMax("[TheSequence]", _
                      "tblWood", _
                      "[TheSpecies] = '" & Me.cboSpecies & Format(Date, "yyyy")), 0) + 1
                                                                                  ^
                                                                                  |
                           This is for the Nz (for the "start" of each year)  ----+

Wayne
Click to expand...

Wayne - yes, I do have them in a combined field, which is my primary key for the table. I had originally wanted to have a 3 field key - just as you suggest - but was strongly advised not to do so as it could be difficult to maintain properly and could be more trouble than it was worth.

I now have almost 300 pieces of wood inventoried - I don't relish the idea that I need to split all those ID codes up into 3 fields.

And at this point, I don't think I need to go to that extreme - I can already create the first 3 parts of the code from the combo box VB, all I need now is a way to find the highest number already used in the existing codes for the current year. Who should it be that hard to simply compare the partial strung I have to find those records that contain it as part of their ID code? I could do this in my sleep in C++, simply use the strcspn() function to see if the substring existed in the string being checked.

I fully expected the code from fugifox to solve the problem - from what I've learned so far it should do the job. Even if I just used the first line it should show me the ordered list & I could just input the next number in the sequence. Would be nice, though, to have the computer do that for me...:)

And maybe I'm having trouble making what I am needing clear - so I'm attaching the DB file so you can see what I've got so far. Most likely it's just a small error in wording or misspelling on my part

Take a look & see if this clears it up. Also note that in the query I have, the "Maple07:" is hard coded - and gets me all the Maple07 records in the table. So it should be easy to code in VBA to find those same files - shouldn't it?

BTW, what's the NZ? The IDcode doesn't know that the "07" is a year, so how would the "NZ" help?

Stephen
 

Attachments

Splinters said:
.


And maybe I'm having trouble making what I am needing clear - so I'm attaching the DB file so you can see what I've got so far. Most likely it's just a small error in wording or misspelling on my part

Stephen
Click to expand...

The error is here:
seq = SELECT tblWood.txtWoodIDcode FROM tblWood WHERE (((tblWood.txtWoodIDcode) Like "Maple07:*"))ORDER BY tblWood.txtWoodIDcode;

You can't get the result of a query just that.
You need a recordset.
Here's the VB code that would do the job assuming that you have already
a query named qrMarple as I described you in my previous post.

private sub getLastSeq()

Dim stSQL As String
Dim dbs As DAO.Database
Dim rst As Recordset
dim theLastSpecies as String


Set dbs = CurrentDb

stSQL="SELECT LAST ([qrMaple.ID]) AS seq FROM qrMaple;"
Set rst = dbs.OpenRecordset(stSQL)


theLastSpecies=rst!seq

'Now we have a String containg the last Species
'E.g. for Marple species the String should be "Marple05:003"
'Trim undesired part and you have the sequence number as String
'Convert it to integer or whatever and add +1


rst.Close
Set rst = Nothing

Exit Sub
 
Splinters,

It really is much easier with them split into seperate fields, but it is
still only one line of code required.

Code: 
Me.SpeciesSelected = Nz(DMax("Right([txtWoodIDcode], 3)", _
                             "tblWood", _
                             "[txtWoodIDcode] Like '" & Me.cboSpeciesSelect & Format(Date, "yy") & ":" & "*'"), 0) + 1
MsgBox Me.cboSpeciesSelect & Format(Date, "yy") & ":" & Me.SpeciesSelected

btw,
The Nz was for when the DMax doesn't find your 2008 entry like "Elm08:"
The DMax will return Null.
The Nz will convert it into 0, then add 1 to start a new years sequences.


Wayne
 
WayneRyan said:
Splinters,

It really is much easier with them split into seperate fields, but it is
still only one line of code required.

Code: 
Me.SpeciesSelected = Nz(DMax("Right([txtWoodIDcode], 3)", _
                             "tblWood", _
                             "[txtWoodIDcode] Like '" & Me.cboSpeciesSelect & Format(Date, "yy") & ":" & "*'"), 0) + 1
MsgBox Me.cboSpeciesSelect & Format(Date, "yy") & ":" & Me.SpeciesSelected

btw,
The Nz was for when the DMax doesn't find your 2008 entry like "Elm08:"
The DMax will return Null.
The Nz will convert it into 0, then add 1 to start a new years sequences.


Wayne
Click to expand...

Wayne - that worked just great, thanks for the solution.

That's just what I need - and simple to plant back into the table with the rest of the data for the new record.

I don't know why it didn't occur to me that the last in the current year would also be the last in the entire species - too busy looking in the brush to see the bear, I guess :o ...

One small additional question - is there an easy way to make the sequence number 3 digits instead of 2? So the full code would read Maple07:027. I do expect to have more than 99 blanks of any one species in the not too distant future. I'm assuming that Maple07:93 would fall after Maple07:123 in the sort...?

Anyway, thanks for the help - really cleared up a lot of confusion...but fear not, there is plenty left for later! :D

Stephen
 
fugifox said:
The error is here:
seq = SELECT tblWood.txtWoodIDcode FROM tblWood WHERE (((tblWood.txtWoodIDcode) Like "Maple07:*"))ORDER BY tblWood.txtWoodIDcode;

You can't get the result of a query just that.
You need a recordset.
Here's the VB code that would do the job assuming that you have already
a query named qrMarple as I described you in my previous post.

private sub getLastSeq()

Dim stSQL As String
Dim dbs As DAO.Database
Dim rst As Recordset
dim theLastSpecies as String


Set dbs = CurrentDb

stSQL="SELECT LAST ([qrMaple.ID]) AS seq FROM qrMaple;"
Set rst = dbs.OpenRecordset(stSQL)


theLastSpecies=rst!seq

'Now we have a String containg the last Species
'E.g. for Marple species the String should be "Marple05:003"
'Trim undesired part and you have the sequence number as String
'Convert it to integer or whatever and add +1


rst.Close
Set rst = Nothing

Exit Sub
Click to expand...


Fugifox, many thanks for your advice - as you can see, Wayne's solution was quite easier than what either of us were heading for, but I appreciate the time and help. It cleared up some questions I've had on queries using VBA - so will come in handy in the future.

Again, thanks for the help.

Stephen
 
Stephen,

After you calculate SpeciesSelected

SpeciesSelected = Format(SpeciesSelected, "000")

Wayne
 
WayneRyan said:
Stephen,

After you calculate SpeciesSelected

SpeciesSelected = Format(SpeciesSelected, "000")

Wayne
Click to expand...

Perfect!

I really appreciate the help I get on this forum - from you, fugifox, Oldsoftboss, The_Doc_Man and others. Helped me move from a total :confused: newbe to a focused :cool: newbe...

'Tis good company I find...

Stephen
 
Wayne, Another question - given the current code:

Me.SpeciesSelected = Nz(DMax("Right([txtWoodIDcode], 3)", _
"tblSplintersWoodInventory", _
"[txtWoodIDcode] Like '" & Me.cboSpeciesSelect & Format(Date, "yy") & ":" & "*'"), 0) + 1


How would I modify that to separate the Me.cboSpeciesSelect and the Format(Date, "yy") with an AND?

I need this to use the same form design on my other table, which lists the finished bowls made from the wood blanks.

The code to be parsed it similar to the Maple07:017 format - except that it may have other information between the Maple and the 07: part, which indicates any special attributes of the bowl, such as burl or spalting, etc., that could add to it's attraction. For the Maple above, if it had spalting, the code might be MapleSp07-017.

I've tried using the AND operator - and have tried every combination of ", ', ( that I can think of - but keep getting shot down with syntax and/or type errors.

BTW, I also tried changing the year on the PC to 2008, and the code you provided worked just fine - but this new code, if I just search for the species, gives me the numbers for this year.

Thanks,

Stephen
 
Stephen,

Welcome to my world!

When you don't break the ID up into its SEVERAL fields this happens!

Fortunately there's a short-term solution:

"[txtWoodIDcode] Like '" & Me.cboSpeciesSelect & "*" & Format(Date, "yy") & ":" & "*'"), 0) + 1

But they really should all be seperate fields.

Wayne
 
You must log in or register to reply here.

Similar threads

R
Unique values
Replies
15
Views
796
Pat Hartman
P
E
Emmanuel Katto Dubai : Help Needed with Generating Unique Customer Codes in Access
Replies
8
Views
656
Pat Hartman
P
S
Incremental Sequential Numbering by Year via Calculated Field
Replies
17
Views
758
CJ_London
C
H
Generate a table with a list of numbers between the given range
Replies
11
Views
962
Uncle Gizmo
Uncle Gizmo
D
Query to return one id for all unique combinations of four columns
Replies
4
Views
405
dobseh
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom