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

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 Removed)
Please post new questions or followups to newsgroup.

"kd" <(E-Mail Removed)> wrote in message
news:074c01c34768$79d2b9d0$(E-Mail Removed)...
> 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

"kd" <(E-Mail Removed)> wrote in
news:074c01c34768$79d2b9d0$(E-Mail Removed):

>
> 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