Creating key field name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My macros create the entry for the key field, DiaryID. I do not want Access
to AutoNumber.
Is there a way to search the existing records prior to placing the entry in
the table so there is not a duplicate created ? Prior to getting an error
message.

The entry is based on the date, a page no. and who is writing the diary
ex.: 050429-1RWJ. The 1 is the page no. If the same person writes more
than one diary for the same day it would be - 2RWJ, - 3RWJ, etc.
I didn't want to split the page no. into a separate field and would have the
same problem there making sure there was no duplicate before entering it into
the table.
Thanks and hopefully I'm being clear
 
I didn't get any responses here but I figured out how to do this
satisfactorily from reading other postings. So thanks to all those who gave
responses to those.
This is the code I came up with. It starts with page no. = 1 and increases
it until there is not a duplicate key. Maybe this will help someone else or
someone can propose a better way to do this. This routine to create the ID
can be called from several different places depending whether a date or name
is being entered.

PgNO = 1
10 ADIDX = "YY & MMM & DDD & "-" & PgNO & Name
With Me.RecordsetClone
.FindFirst "[DiaryID] = '" & ADIDX & "'"
If .NoMatch = True Then
Exit Function
Else
PgNO = PgNO + 1
GoTo 10
End If
End With
 
Back
Top