Creating key field name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rich J - 29 Apr 2005 20:32 GMT

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

Reply to this Message

Rich J - 30 Apr 2005 01:00 GMT

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

> My macros create the entry for the key field, DiaryID. I do not want Access
> to AutoNumber.
[quoted text clipped - 9 lines]
> the table.
> Thanks and hopefully I'm being clear

Reply to this Message