Help with code to generate primary key

[kd]

My boss has told me to generate a primary key based on the
organisation name and a number, so like XYZ99 I know this
is a bit silly, but I'm just the monkey here.

I have most of the code working fine, but have wasted some
hours with generating a valid number. When I run the
procedure to generate the number, it accesses the database
and should obtain a list of numbers already being used for
that three leter abbreviation. However it is always
returning with an empty recordset. I can't for the life of
me see what's wrong with my code, and I've tried
everything. The sql works in the query builder, so why not
here? Here is the relevant code. Thanks for any help.
I'm using Access 2000 with a mysql odbc backend if that is
relevant, but this didn't work with a access database either.

--- code below --
Private Function getAutoNum(tla As String) As Integer
' Find out what number we can add to the tla to give a
complete org code
' first get all the orgcodes with that starting tla into a
recordset

Dim query As String
' tla is the three leter abbreviation I've already obtained
query = "SELECT Orgcode FROM tbl_org WHERE Orgcode ='" tla
& "*' ORDER BY Orgcode"
Dim count As Integer
Dim autonum As Integer

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Set conn = CurrentProject.Connection

Dim rst As New ADODB.Recordset
rst.Open query, conn, adOpenForwardOnly, adLockReadOnly
Dim result() As Variant
Dim fld As ADODB.Field
' the problem occurs here. The record set is always empty
If Not rst.EOF Then
rst.MoveFirst
End If
Do Until rst.EOF
result(count) = rst.Fields("Orgcode").Value
count = count + 1
rst.MoveNext
Loop

' now find a nice unique number
If Not IsBounded(result) Then
autonum = 1
Else:
Dim tmp As Integer
tmp = Mid(result(0), 4, 5)
If tmp > 1 Then
autonum = tmp - 1
End If
If tmp = 1 Then
tmp = Mid(result(UBound(result)), 4, 5)
autonum = tmp + 1
End If
End If
getAutoNum = autonum
End Function

 

[Graham Mandeno]

Instead of:
... WHERE OrgCode = ...
try:
... WHERE OrgCode like ...

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[Tim Ferguson]

My boss has told me to generate a primary key based on the
organisation name and a number, so like XYZ99 I know this
is a bit silly, but I'm just the monkey here.

If this field is really

XYZ01
XYZ02
XYZ03
NGT01
NGT02

etc, then you have what many developers call a Design Error. There are two
separate bits of information packed into a single field and this is not a
sensible thing to do (see 1NF). If you separate them into separate fields
you will find that your query and update problems miraculously disappear:

MyNumber = NZ(DMax("MyNumber", _
"MyTable", _
"MyTLA = """ & tla & """" _
), 0) _
+1


Hope that helps


Tim F