Well,
If you need a field that increments like this, then I would recommend that
you store this data in three fields (ClientID, ClientStatus, and
ClientNum).
I would make ClientID as an Autonumber, and would not expose it to your
users
at any time. This would be the value that you place in other tables as a
foreign key.
Then, to fill in ClientNum, I would use something like the following, in
the
BeforeUpdate event of a form.
Public Sub Form_BeforeUpdate
Dim strCriteria as string
if me.newrecord then
strCriteria = "[ClientStatus] = '" & me.cboClientStatus & "'"
me.txtClientNum = NZ(DMAX("ClientNum", "yourTableName", strCriteria),
0)
+ 1
endif
End Sub
This way, right before you write the record, this code would determine the
ClientNum value. If you do it any earlier than this, then the chance
exists,
if you have a multi-user application, that someone else will be quicker
than
you, and will inadvertently generate the same number.
Then, in your later forms, you can just display the clientStatus and
ClientNum concatenated together, but use the ClientID field as your PK.
--
HTH
Dale
email address is invalid
Please reply to newsgroup only.
"punitha" wrote:
> hi,
>
> i am new to access. currently, i'm working with multiple tables and
forms.
>
> in one form,
> i have text field: txtClientID; Data type: Text, which i use as primary
key
> to number my entries; input mask "L000".
> i also have a combo box, cboClientStatus, which identifies the
> status/category of my clients, e.g. Hospital, Clinic and Other.
>
> now, i need ur help to set the primary key to increase by 1 depending on
the
> status/category of my clients, like:
> H001, H002, H003, ... (for Hospitals)
> C001, 002, C003, ... (for Clinics)
> O001, O002, O003, ... (for all other than Hospitals and Clinics)
>
> i want the number to increase automatically when i choose the status of
my
> clients. i know i need a vb for this, probably at cboClientStatus in
After
> Update event. can u guys pls help me with this.
>
> thanx a bunch.
|
