Autonumber field with string chars

[Jose Lopes]

Hi.

I've been searching here for a way to solve my problem but with no success.
I have a table with a string field (TypeProduct) where I just store the type
of product: A, B, C. But I need also a calculated field where the number for
the next product would be "A002" or "C023" acording to my choice on the
TypeProduct field. I will need a form with a drop down box to select the
correct type of product and then this text box must calculate the max
existing value for the default value. How can this be done?

Thx for any help.

 

[Graham R Seach]

Jose,

Assuming the following:
* That your combo is called cboTypeProduct, and its bound column is a
single character denoting the type.
* That the numbers are stored in a table called "tblMyTable"

Private Sub cboTypeProduct_AfterUpdate()
Dim sMaxNo As String

'Get the last number used for the selected product
sMaxNo = Nz(DMax("[TypeProduct]", "tblMyTable", _
"Left([TypeProduct], 1) = """ & Me.cboTypeProduct &
""""), _
Me.cboTypeProduct & "0")

'Populate the textbox with the next number
Me.txtNextNum = Me.cboTypeProduct & CStr(Val(Mid(sMaxNo, 2)) + 1)
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Jose Lopes]

Hi.

I've tried your code just today and unfortunatly it does not run. It detects
some bracket missing in the """ & Me.cboTypeProduct and does not allow me to
leave the VB window. I've counted and recounted the brackets and the
quotation marks several times now and tried to learn more about the functions
but got to a dead end

Can you help me again with this? Thanks.

Jose,

Assuming the following:
* That your combo is called cboTypeProduct, and its bound column is a
single character denoting the type.
* That the numbers are stored in a table called "tblMyTable"

Private Sub cboTypeProduct_AfterUpdate()
Dim sMaxNo As String

'Get the last number used for the selected product
sMaxNo = Nz(DMax("[TypeProduct]", "tblMyTable", _
"Left([TypeProduct], 1) = """ & Me.cboTypeProduct &
""""), _
Me.cboTypeProduct & "0")

'Populate the textbox with the next number
Me.txtNextNum = Me.cboTypeProduct & CStr(Val(Mid(sMaxNo, 2)) + 1)
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi.

I've been searching here for a way to solve my problem but with no
success.
I have a table with a string field (TypeProduct) where I just store the
type
of product: A, B, C. But I need also a calculated field where the number
for
the next product would be "A002" or "C023" acording to my choice on the
TypeProduct field. I will need a form with a drop down box to select the
correct type of product and then this text box must calculate the max
existing value for the default value. How can this be done?

Thx for any help.

 

[Graham R Seach]

Jose,

I have no idea what could be wrong. The code works for me.

Try compiling the code and see what shows up.

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------

Hi.

I've tried your code just today and unfortunatly it does not run. It
detects
some bracket missing in the """ & Me.cboTypeProduct and does not allow me
to
leave the VB window. I've counted and recounted the brackets and the
quotation marks several times now and tried to learn more about the
functions
but got to a dead end

Can you help me again with this? Thanks.

Jose,

Assuming the following:
* That your combo is called cboTypeProduct, and its bound column is a
single character denoting the type.
* That the numbers are stored in a table called "tblMyTable"

Private Sub cboTypeProduct_AfterUpdate()
Dim sMaxNo As String

'Get the last number used for the selected product
sMaxNo = Nz(DMax("[TypeProduct]", "tblMyTable", _
"Left([TypeProduct], 1) = """ & Me.cboTypeProduct
&
""""), _
Me.cboTypeProduct & "0")

'Populate the textbox with the next number
Me.txtNextNum = Me.cboTypeProduct & CStr(Val(Mid(sMaxNo, 2)) + 1)
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi.

I've been searching here for a way to solve my problem but with no
success.
I have a table with a string field (TypeProduct) where I just store the
type
of product: A, B, C. But I need also a calculated field where the
number
for
the next product would be "A002" or "C023" acording to my choice on the
TypeProduct field. I will need a form with a drop down box to select
the
correct type of product and then this text box must calculate the max
existing value for the default value. How can this be done?

Thx for any help.