If statement problem

[Matt Brown]

Ok, what am i doing wrong?
Thought this would work:

Private Sub Form_AfterUpdate()
If Me.ProductClass = 1 Then
Me.ProductClass = P
End If
End Sub

It just remains at 1 when a 1 value is in the recordset.
I thought if a 1 value is present it would just change it to P when the form is updated.

Matt

 

[Mile-O]

Me.ProductClass = "P"

Private Sub Form_AfterUpdate()
    If Me.ProductClass = 1 Then Me.ProductClass = "P"
End Sub

 

[Matt Brown]

Thanks Milo but you would have thought that your suggestion would have worked.
Putting in the comas around the "P" as its text, nope, still stays as 1.

Matt

 

[Mile-O]

WHat data type is the field bound to ProductClass?

 

[Matt Brown]

Text, field size is 1

 

[Mile-O]

Why are you doing this in the Form's AfterUpdate event - put it in the BeforeUpdate event. It won't save in the AfterUpdate because it saves and then changes the value; you need it the other way around.

 

[Matt Brown]

Sorry dude,

Still no good, still stays as 1

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.ProductClass = 1 Then Me.ProductClass = "P"
End If
End Sub

Matt

 

[Rich]

put it on the afterupdate of the textbox

 

[Matt Brown]

Nope, still stays at 1

This is getting silly.

All i have done now is changed the name of the textbox for naming conventions sake, everything else is as before.

Private Sub txtProductClass_AfterUpdate()
If Me.txtProductClass = 1 Then Me.txtProductClass = "P"
End If
End Sub

i have also tried

Private Sub txtProductClass_AfterUpdate()
If Me.txtProductClass.Value = 1 Then Me.txtProductClass.Value = "P"
End If
End Sub

and

Private Sub txtProductClass_AfterUpdate()
If Me.txtProductClass.Value = "1" Then Me.txtProductClass.Value = "P"
End If
End Sub

and

Private Sub txtProductClass_AfterUpdate()
If Me.txtProductClass.Value = 1 Then Me.txtProductClass.Value = P
End If
End Sub

and

Private Sub txtProductClass_AfterUpdate()
If Me.txtProductClass= 1 Then Me.txtProductClass= P
End If
End Sub

and

Private Sub txtProductClass_AfterUpdate()
If Me.txtProductClass= "1" Then Me.txtProductClass = "P"
End If
End Sub

I have placed it in the controls before and after and on the forms onload, oncurrent and anywhere else which i thought may trigger this to work!!!

It must be something straight forward as the code isn't exactly a lot but its stumped me.

Matt

 

[Mile-O]

I'm stumped. Can you upload the form and its recordset?

Also, when using IF...THEN... one one line you don't need to close it with an END IF.

 

[Rich]

Try
Private Sub txtProductClass_AfterUpdate()
If Val(Me.txtProductClass) = 1 Then
Me.txtProductClass = "P"
End If
End Sub

 

[Matt Brown]

Ah!

The form is an actual subform called Final1

Will this make a difference?

Do you have to reference the control on the form using the full identifier rather than Me



Matt

 

[Mile-O]

Is one on the subform and the other isn't?

If your code is on the subform then you can use Me. for the subform but must reference Me.Parent. for the other control.

If your code is on the main form then you can use Me. for the main form but must use the full 'path' when referencing a control on the child form.

 

[Matt Brown]

I may be getting a little confused here then.

The control which is called txtProductClass is on the subform.

The end user wants to see any class code which this control displays equal to 1 to display a P instead.

There are no other objects involved just the one txtProductClass control.

Basically the data is being pulled from a database where there are 1's in the ProductClass, these need to be displayed as P's.

The data in the db cannot be changed so i thought the easyest thing to do was to change it whenever a 1 is displayed on the form in this control.
The underlying table does not change just the display of the control.

Does that sort of make sense?

When i just loaded the subform it worked, 1's instead of P's when i loaded the parent form it didn't work.

Hence the question of referencing the control on the subform using its full identifier.

 

[Rich]

unless you update every record the code won't work anyway, why not just use an update query?

 

[Matt Brown]

Hi Rich,

I did think about using an update query, i actually put one in place but then thought the following:

The data , once gone through this application needs to be exported back out and back into the main business system DB.
If i used the Update query to change all 1's to P's i will need to put in another update query to convert them all back to 1's again on export so that the main business system can understand the ProductClass.

Think i have inherited a problem during the design of the business system by swapping 1's for P's why they couldn't just have designed it to have P's only is beyond me!!!

 

[Matt Brown]

Just to let you all know placed update queries into app and works fine.

1 gets changed to P on incoming data and P gets changed to 1 on outgoing data.

Proberly a bit crude but it works.

Thanks for all of your time and inputs on this one.

Matt