Hello, all you out there, :)
I am building a new database for my business, and I have a question and hope someone can help me. I am working with Access 2002 and SQL Server 2003.
My Question is just this , I have a field called empappearance , and it is a combo box, in the box we have Poor = 5 , Fair =10, Good = 15, Excellent = 20. And the field that gets the answer is called emppoints1 = either 5, 10, 15, 20. And the formula look like this ,

Private Sub number_BeforeUpdate(Cancel As Integer)
If empappearance = poor Then Let emppoint1 = 5
If empappearance = Good Then Let emppoint1 = 10
If empappearance = Fair Then Let emppoint1 = 15
If empappearance = Excellent Then Let emppoint1 = 20

End Sub

And I can not get this to work.

Help Me Please……


Johnny C

I don't understand. But...two things:

1) after THEN, put new lines of code in, not on the same line.
2) Remove all of the "Let" words...I believe you have the wrong code language on the brain.

Is this what you mean.

Private Sub number_BeforeUpdate(Cancel As Integer)
If empappearance = poor Then emppoint1 = 5
If empappearance = Good Then emppoint1 = 10
If empappearance = Fair Then emppoint1 = 15
If empappearance = Excellent Then emppoint1 = 20

End Sub


Johnny C :)

CBOs usually contain strings (words) or numbers. Strings are to be in quotes. Maybe do this:

If empappearance = "poor" Then...

Johnny,

They're the same.

I don't see why you're storing the same value twice.

What is the "bound" value of the combo box?

If empappearance = Good <-- what is Good?

If the ComboBox really has a value (5, 10, 15,20)
then, you really need Me.empoint1 = Me.empappearance,
but why store it twice?

p.s. The Let is OK, it is a BASIC part of the VB BASIC language.
ppss The single-line If is OK, it just means there is only ONE line.

hth,
Wayne

Hi
Keep in mind that I am a novice.

Private Sub number_BeforeUpdate(Cancel As Integer)
If empappearance = "poor" Then GoTo 5
If empappearance = "Good" Then GoTo 10
If empappearance = "Fair" Then GoTo 15
If empappearance = "Excellent" Then GoTo 20
End Sub

This is not working either.

I am going back to try somthing you said...


Johnny C :)

Johnny,

Don't leave me!!

You really can lose the GoTos though ...

You have to say what you're trying to do.
What does the ComboBox have to do with the BeforeUpdate of "number".

Don't call it number though ... Bad name.

Need more info here,
Wayne

Hi Wayneryan, Thank You for helping me.
I have a field called empappearance and it is a combo box with a multiple choice
Poor , Fair, Good, Excellent.

Now I have a field called emppoint1 and this is a number field only

I go to emppoint1 , I right click on to properties,
then I right click on Build Event.
The Code Builder , OK,

And this is what I am trying to do.

Keep in mind that I am a novice. With Micrsoft Access – SQL Server.
What I had was a UNIX Server for years and I work with UNIX SQL.

This is not working….

Private Sub number_BeforeUpdate(Cancel As Integer)
If empappearance = "poor" Then GoTo 5
If empappearance = "Good" Then GoTo 10
If empappearance = "Fair" Then GoTo 15
If empappearance = "Excellent" Then GoTo 20
End Sub


Johnny C

Johnny,

This will solve your initial problem.


Private Sub number_BeforeUpdate(Cancel As Integer)
If empappearance = "poor" Then emppoint1 = 5
If empappearance = "Good" Then emppoint1 = 10
If empappearance = "Fair" Then emppoint1 = 15
If empappearance = "Excellent" Then emppoint1 = 20
End Sub


But, it you have a table called Rating:

tblRating
=========
RatingID - AutoNumber PK
RatingName - String (Excellent, Fair, Good, Poor)
RatingValue - Number (20, 15, 10, 5)

Then just store the AutoNumber --> RatingID
Very easy to add a new Rating like --> Terrible (and associated value = 1).

Anytime you want the Numeric rating just incude the RatingValue in your query
using the RatingID that your combo stores.

btw, I still don't see why this code is on the BeforeUpdate event of some
field called number.

Wayne

Hi, Wayne and thank you for your Time....

I know that in access we can use an AutoNumber
But in SQL no AutoNumber.

And I do not know what an RatingID is.

How can I add a new RatingID

What else can I do if i change BeforeUpdate

This code not working .
There must be somthing that I am doing wrong. ????


Private Sub number_BeforeUpdate(Cancel As Integer)If empappearance = "poor" Then emppoint1 = 5If empappearance = "Good" Then emppoint1 = 10If empappearance = "Fair" Then emppoint1 = 15If empappearance = "Excellent" Then emppoint1 = 20End Sub


Thank You Wayne.

Johnn C :)

Hello Wayne, and thank you for your time.

I have found the problem it was in the properties
What I had was in properties Name emppoint1
And the control source emppoint1 and that was the problem
The control source should have been empappearance, now I get a return,
But I do not get the returns that I went, I get 1, 2, 3, 4, and not 5, 10, 15, 20.
This is another problem???

this code returns a number
I am getting a return of, 1, 2, 3, 4.


Private Sub number_BeforeUpdate(Cancel As Integer)
If empappearance = "poor" Then GoTo 5
If empappearance = "Good" Then GoTo 10
If empappearance = "Fair" Then GoTo 15
If empappearance = "Excellent" Then GoTo 20
End Sub


Can I say Thank you again, again
My the way do you like playing pool,

Johnny C :)