Select Case Problem

[midge]

Hi ,

I am trying to write a Select Case program using vba. I am using Microsoft Access 2000. Below is my program that I wrote so far.

Private Sub ProductID_BeforeUpdate(Cancel As Integer)

Select Case ProductID
Case "am"
SupplierID = "1"
Case "kk"
SupplierID = "2"
Case "sm"
SupplierID = "3"
End Select
End Sub

ProductID field contains 4 characters 2 letters & 2 numbers. Here is some data that I already put into the database.

Any help to my problem would be much appreciated.

am01
am02
am03
kk01
kk02
kk03
sm01
sm02
sm03

ProductID am = SupplierID 1
ProductID kk = SupplierID 2
ProductID sm = SupplierID 3

So everytime you enter a new product in the database it automatically enters its corresponding SupplierID.

 

[shay]

Try

Select Case left(ProductID, 2)
Case "am"
SupplierID = "1"
Case "kk"
SupplierID = "2"
Case "sm"
SupplierID = "3"
End Select

hth

shay

 

[midge]

Hi,

It worked !! Thanks for your help !!