Then - Let Not Working. ??/ (1 Viewer)

caljohn527 · Feb 4, 2009

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 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 = 20
If empappearance = Excellent Then Let emppoint1 = 30 ( NOT WOIRKING )

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 = 20
If empappearance = “Excellent” Then Let emppoint1 = 30 ( NOT WOIRKING )

Private Sub number_BeforeUpdate(Cancel As Integer)
If empappearance = “poor” Then Let emppoint1 = 5
Elselet = “Good” Then Let emppoint1 = 10
Elselet = “empappearance = “Fair” Then Let emppoint1 = 20
Elselet = “empappearance = “Excellent” Then Let emppoint1 = 30 ( NOT WOIRKING )

End Sub
What am I doing wron???

And I can not get this to work. Access 2002 and SQL 203 Server .

Johnny C

SQL_Hell · Feb 4, 2009

You need to reference the text box name rather than the field name

Say field empapperence is in a text box called txt_empapperence and emppoint is in a text box called txt_emppoint. then the code would be:

Code:

If me.txt_empappearance = "poor" Then me.txt_emppoint1 = 5

caljohn527 · Feb 5, 2009

Hi SQL Hell

This is what I try to do and is not working ???

Private Sub emppoint1_BeforeUpdate(Cancel As Integer)
If me.txt_empappearance = poor Then me.txt_emppoint1 = 5
If me.txt_empappearance = Good Then me.txt_emppoint1 = 10
If me.txt_empappearance = Fair Then me.txt_emppoint1 = 15
If me.txt_empappearance = Excellent Then me.txt_emppoint1 = 20 not working ??? ( The return is #Nmae? )

End Sub

Private Sub emppoint1_BeforeUpdate(Cancel As Integer)
If txt_empappearance = poor Then txt_emppoint1 = 5
If txt_empappearance = Good Then txt_emppoint1 = 10
If txt_empappearance = Fair Then txt_emppoint1 = 15
If txt_empappearance = Excellent Then txt_emppoint1 = 20 not working ??? ( The return is #Nmae? )

End Sub

empappearance is a combo box = ( Poor – Fair – Good – Excellent )
emppoint1 is the receiving end ( 5 – 10 – 15 – 20 )
emppoint1 / right click / go to properties /
name = emppoint1 / Control Source = emppoint1 / Format = blank / Decimal places = auto /
Input Mask = blank / Default Value = blank .

In The Tables we have empappearance / nvarchar / 10
In The Tables we have emppoint1 / smallint / 2

What am I doing so wrong ?????

Johnny C

And Thank you all for your help…

SQL_Hell · Feb 5, 2009

I am going to knock you up a sample database,

Can you tell me whether the combo box is being changed therefore emppoint should also change to reflect that value?

Or is the data in the combo box already entered?

caljohn527 · Feb 5, 2009

Hi SQL Hell Thank you for your help and time.

The data is in the combo box already, ( multiple choice )
Poor
Fair
Good
Excellent

Johnn C

SQL_Hell · Feb 5, 2009

Hi again

Ok I have created a test table and entered the following values:

Poor
Fair
Good
Excellent

Then I created a form with a combo box with the name "empappearance" and a text box with the name "emppoint", I set the recordsource of the combo box to select from the table I created.

Then on the form I created the following On Current event

Code:

Private Sub Form_Current()
Dim text As String
If IsNull(Me.empappearance.Value) = False Then
    text = Me.empappearance.Value
    Select Case text
        Case "Good"
            Me.emppoint.Value = 1
        Case "Poor"
            Me.emppoint.Value = 2
        Case "Excellent"
            Me.emppoint.Value = 3
        Case "Fair"
            Me.emppoint.Value = 4
        Case Else
            Me.emppoint.Value = "Nothing"
    End Select
End If
End Sub

Have a go and see if you can figure it out from here, if not I will send you my test database, but it's better if you figure some of it out for yourself

dkinley · Feb 5, 2009

Just as an aside ... why not in the test table put in two columns. Column 1 with "empappearance" and column 2 with the correlating number/data.

Then modify the combo box to have 2 columns, set the second table column in the combo box row source but set the format to hide the second column (0").

On the AfterUpdate() event of the combo box say something like ... Me.txtSomeControlName = Me.cboComboBoxName.Column(1)

If the control on the form is unbound then use the OnCurrent() event of the form to say "Call cboComboBoxName_AfterUpdate"

In this manner the only maintenance involved is adjusting the table values, everything else - adding/editing/changing doesn't require any coding. You can set the 'adjustment wizard' on an admin toolbox form so if you publish the database in an mde or accde format, the lists can be updated without the need to update the mde and republish.

Just some food for thought.
-dK

SQL_Hell · Feb 5, 2009

dkinley said:
Just as an aside ... why not in the test table put in two columns. Column 1 with "empappearance" and column 2 with the correlating number/data.

Then modify the combo box to have 2 columns, set the second table column in the combo box row source but set the format to hide the second column (0").

On the AfterUpdate() event of the combo box say something like ... Me.txtSomeControlName = Me.cboComboBoxName.Column(1)

If the control on the form is unbound then use the OnCurrent() event of the form to say "Call cboComboBoxName_AfterUpdate"

In this manner the only maintenance involved is adjusting the table values, everything else - adding/editing/changing doesn't require any coding. You can set the 'adjustment wizard' on an admin toolbox form so if you publish the database in an mde or accde format, the lists can be updated without the need to update the mde and republish.

Just some food for thought.
-dK

Ok, good call. This makes much more sense from a maintenance and general database design point of view.

I am now wondering why I didn't think of this

, oh well

dkinley · Feb 5, 2009

Well .. the caveat is 'what about job security?'

You can still bury this under a bunker of techie stuff and show the 'modification wizardry button thingee' to update the table under the 'developer' group so the secret is safe.

That way you can grimace and say "It's gonna take me three hours to get that updated. I have to do this and then that and republish the whole thing. Gawd! When you are all of you going to give the requirements up front!"

Then you spend 20 seconds updating it and the remaining 2 hrs 59 min and 40 seconds at the movies.

-dK

caljohn527 · Feb 6, 2009

Hi all Read This If You have The Time .

Dim text As String
If IsNull(Me.empappearance.Value) = False Then
text = Me.empappearance.Value
Select Case text
Case "Good"
Me.emppoint.Value = 1
Case "Poor"
Me.emppoint.Value = 2
Case "Excellent"
Me.emppoint.Value = 3
Case "Fair"
Me.emppoint.Value = 4
Case Else
Me.emppoint.Value = "Nothing"
End Select
End If
End Sub

When I try to compile this I get an error,
and it said Microsoft Visual Basic
Compile error
Invalid Outside Procedure

On the first line at the Me. it stops Compile.If IsNull(Me.???

Are you by any chance refering to change the combo box from one column to two columns
Sample 1 - Poor
2 - Fair
3 - Good and so on.

Johnny C

SQL_Hell · Feb 6, 2009

Did you put the code in a procedure?

Just out of interest how much experience do you have designing databases and writing VBA code?

I am wondering if there are some books / online resources that might help you on your way

dkinley · Feb 6, 2009

You can't evaluate: IsNull(Me.empappearance.Value) = False in this manner.

It is either true or it isn't - the true/fals bit comes in because you are asking the question IsNull. You can either say: If IsNull(...) Then which would first execute the true portion or you can say: If Not IsNull(...) Then which would execute the false portion first.

Basically either one allows you to not have to put a Else statment if there is no else in your logic.

-dK

SQL_Hell · Feb 6, 2009

It works for me...

Isnull() returns either true or false, so what do you mean when you say you can evaluate false in this manner?

It was infact intelisense suggesting true or false when I typed it, so I cant believe it's wrong and plus it works.

dkinley · Feb 6, 2009

Perhaps I should back up and say the = False is not required?

I am not sure. Per Access Help files ...

IsNull returns True if expression is Null; otherwise, IsNull returns False.

Which is why I never put = False/True because it is already done for you. I would assume an error - but who knows ....

-dK

Uncle Gizmo · Feb 6, 2009

dkinley said:
You can't evaluate: IsNull(Me.empappearance.Value) = False in this manner.

It is either true or it isn't - the true/fals bit comes in because you are asking the question IsNull. You can either say: If IsNull(...) Then which would first execute the true portion or you can say: If Not IsNull(...) Then which would execute the false portion first.

Basically either one allows you to not have to put a Else statment if there is no else in your logic.

-dK

from MSDN :

IsNull Function
Updated: November 2007
Returns a Boolean value that indicates whether an expression contains no valid data (Null).

I would say you are both correct, both will work because an IF statement allows you to enter directly a boolean value, and also to test the difference between two values.

If...Then...Else Statement

Mind you from access help online, (Correction it was not from Access) it does actually suggest a statement, as opposed to a boolean value. However I would suggest this was an omission.

dkinley · Feb 6, 2009

hehehe ... phreow!

That makes sense then ... I have honestly never seen the = False applied and therefore assumed based on some problems I had long ago in the evaluation process.

Thanks Gizmo!
-dk

SQL_Hell · Feb 10, 2009

Thanks Gizmo,

Yeah I think we are both right, but probably dkinley's version is the more correct / elegant way of writing the statement.

Caljohn527 did you get it working?

boblarson · Feb 21, 2009

Yes, to qualify -

You can test for true like this:

If IsNull(Me.TextBox) Then

or like

If IsNull(Me.TextBox) = True Then

or to test for false

If IsNull(Me.TextBox) = False Then

or

If Not IsNull(Me.TextBox) Then

There are many VALID ways to do the same thing. A lot of it is just personal preference.

Also, I don't know if anyone ever addressed the original poster's syntax about using the word LET which doesn't belong in the If...Then...Else statement that they were originally trying.

wazz · Feb 22, 2009

similar to a boolean field:
If (blnIsCurrentMember) = True Then ...
or
If (blnIsCurrentMember) Then

both are valid, but the first one evaluates the equation twice, adding a tiny bit of overhead. the first one works like this:
1. [(blnIsCurrentMember)] is evaluated to return true or false;
2. [If True/False = True] is evaluated.

the second one:
1. If True/False Then...

Then - Let Not Working. ??/ (1 Viewer)

Registered User.

SQL Server DBA

Registered User.

SQL Server DBA

Registered User.

SQL Server DBA

Access Hack by Choice

SQL Server DBA

Access Hack by Choice

Registered User.

SQL Server DBA

Access Hack by Choice

SQL Server DBA

Access Hack by Choice

Nifty Access Guy

Access Hack by Choice

SQL Server DBA

Smeghead

Super Moderator

Similar threads

Users who are viewing this thread