number increment depending on a combo box entry

[punitha]

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.

 

[Dale Fye]

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]

thanx for ur reply Dale,

i tried it but it didn't work for me. Client ID (autonumber) works fine of
course, but ClientNum(Num) is not updated. even if it does it takes up the
ClientID number regardless of ClientStatus.

I want to update the CientNum with regards to the last ClientNum + 1 for the
ClientStatus chosen.

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.

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.

 

[Dale Fye]

My guess is that the ClientNum datatype in your table is set to autonumber,
rather than Number (long)

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

thanx for ur reply Dale,

i tried it but it didn't work for me. Client ID (autonumber) works fine of
course, but ClientNum(Num) is not updated. even if it does it takes up the
ClientID number regardless of ClientStatus.

I want to update the CientNum with regards to the last ClientNum + 1 for the
ClientStatus chosen.

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.

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.

 

[punitha]

hi dave,

ClientNum datatype is Number. the first data i entered is ok. when i add new
data (the same status or different), i get this msg:
Run-time error '3464':
Data type mismatch in criteria expression

this the vb i used:
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String
If Me.NewRecord Then
strCriteria = "[ClientStatus] = '" & Me.cboClientStatus & "'"
Me.txtClientNum = Nz(DMax("ClientNum", "Clients", strCriteria), 0) + 1
End If

End Sub

all the field names are refered from the table, rite?

i noticed everytime i use a DMax expression, it doesn't work. usually i use
the following for number increment:
Dim intLabNo as long
DoCmd.GoToRecord,,acLast
intLabNo = LabNo + 1
DoCmd.GoToRecodr,,asNewRec
Me.txtLabNo.DefaultValue = intLabNo

i'm not sure if this cud help, fyi, mine is access 2003

i may sound silly... pls bear with me...

thank you

My guess is that the ClientNum datatype in your table is set to autonumber,
rather than Number (long)

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

thanx for ur reply Dale,

i tried it but it didn't work for me. Client ID (autonumber) works fine of
course, but ClientNum(Num) is not updated. even if it does it takes up the
ClientID number regardless of ClientStatus.

I want to update the CientNum with regards to the last ClientNum + 1 for the
ClientStatus chosen.

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.



:

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.

 

[punitha]

hei Dave Fye,

i tried ur code again and it worked wonderfully. i had to do some major
adjustments to my Status table (my bad).

thank you so much for ur help. i just can't express it enough.
thanks again.

punitha

hi dave,

ClientNum datatype is Number. the first data i entered is ok. when i add new
data (the same status or different), i get this msg:
Run-time error '3464':
Data type mismatch in criteria expression

this the vb i used:
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String
If Me.NewRecord Then
strCriteria = "[ClientStatus] = '" & Me.cboClientStatus & "'"
Me.txtClientNum = Nz(DMax("ClientNum", "Clients", strCriteria), 0) + 1
End If

End Sub

all the field names are refered from the table, rite?

i noticed everytime i use a DMax expression, it doesn't work. usually i use
the following for number increment:
Dim intLabNo as long
DoCmd.GoToRecord,,acLast
intLabNo = LabNo + 1
DoCmd.GoToRecodr,,asNewRec
Me.txtLabNo.DefaultValue = intLabNo

i'm not sure if this cud help, fyi, mine is access 2003

i may sound silly... pls bear with me...

thank you

My guess is that the ClientNum datatype in your table is set to autonumber,
rather than Number (long)

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

thanx for ur reply Dale,

i tried it but it didn't work for me. Client ID (autonumber) works fine of
course, but ClientNum(Num) is not updated. even if it does it takes up the
ClientID number regardless of ClientStatus.

I want to update the CientNum with regards to the last ClientNum + 1 for the
ClientStatus chosen.


:

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.



:

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.

 

[punitha]

hi Dave Fye,

I've used the same code for another form. it works great for the 1st part of
it. Now i need to add another criteria to it. how do i do that?

this time the criterias are category and date (year). i've put in this code
in the Before Update event of the form:

Dim strCriteria As String
If Me.NewRecord Then
strCriteria = "[Category] = '" & Me.cboCategory & "'"
Me.txtLabNo = Nz(DMax("[LabNo]", "SampleDetail", strCriteria), 0) + 1
End If

thanx a bunch for ur help.

hei Dave Fye,

i tried ur code again and it worked wonderfully. i had to do some major
adjustments to my Status table (my bad).

thank you so much for ur help. i just can't express it enough.
thanks again.

punitha

hi dave,

ClientNum datatype is Number. the first data i entered is ok. when i add new
data (the same status or different), i get this msg:
Run-time error '3464':
Data type mismatch in criteria expression

this the vb i used:
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String
If Me.NewRecord Then
strCriteria = "[ClientStatus] = '" & Me.cboClientStatus & "'"
Me.txtClientNum = Nz(DMax("ClientNum", "Clients", strCriteria), 0) + 1
End If

End Sub

all the field names are refered from the table, rite?

i noticed everytime i use a DMax expression, it doesn't work. usually i use
the following for number increment:
Dim intLabNo as long
DoCmd.GoToRecord,,acLast
intLabNo = LabNo + 1
DoCmd.GoToRecodr,,asNewRec
Me.txtLabNo.DefaultValue = intLabNo

i'm not sure if this cud help, fyi, mine is access 2003

i may sound silly... pls bear with me...

thank you

My guess is that the ClientNum datatype in your table is set to autonumber,
rather than Number (long)

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

thanx for ur reply Dale,

i tried it but it didn't work for me. Client ID (autonumber) works fine of
course, but ClientNum(Num) is not updated. even if it does it takes up the
ClientID number regardless of ClientStatus.

I want to update the CientNum with regards to the last ClientNum + 1 for the
ClientStatus chosen.


:

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.



:

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.