If statement

[Sniper-BoOyA-]

Good Morning,

I ve got the following question.

Ive got a table, with one field using a lookup function, linked to a table with settings for the press-machine. Untill now the user had to select the setting he/she wanted.

It turns out, the setting depends on the diameter of the sample.

So, i would like to help them a bit, and do it automatically.

The layout of the setting table is as follows.

nr
test
material
micrometer
ring
spring
setting

What i would like to do is:

If the diameter of the sample is 100, then the list should select nr 1 automatically, and if the diameter is 150, then the list should select nr 2 automatically.

Ive put this in last data-entry field (afterupdate event).

If Me.diameter.Value = 100 Then Me.persinstelnr.Value = 1 Else
If Me.diameter.Value = 150 Then Me.persinstelnr.Value = 2
End If

Persinstelnr is the name of the field which the user uses to select the right settings.

But it doesnt seem to work. What am i doing wrong?

 

[Sniper-BoOyA-]

Well, i just added me.requery.

And it seemed to work, but it did not have the result i want.

Its a continious form, and it only worked on the first set of data.

I need to find an other way to do the selection process automatically.

 

[vbaInet]

Still writing slightly long messages

Is the control a Textbox or a Combo box? You should be using the name of the control not the name of the field.

Also, use a SELECT CASE statement instead:

http://www.techonthenet.com/access/functions/advanced/case.php

 

[vbaInet]

Well, i just added me.requery.

And it seemed to work, but it did not have the result i want.

Its a continious form, and it only worked on the first set of data.

I need to find an other way to do the selection process automatically.

For continuous forms, you can use CHOOSE() or IIF() in the Control Source property of the control only.

 

[Sniper-BoOyA-]

Still writing slightly long messages

Is the control a Textbox or a Combo box? You should be using the name of the control not the name of the field.

Also, use a SELECT CASE statement instead:

http://www.techonthenet.com/access/functions/advanced/case.php

ya sorry about the long message

It is a field in the table, with a lookup function. So its a textbox but acts like a combobox.

The controlname is persinnstelnr, and the field name is perinstel.

But ya, i have never used a Select Case in VBA, checking ur link as we speak.

Cheers!

 

[vbaInet]

ya sorry about the long message

Just kidding!

But ya, i have never used a Select Case in VBA, checking ur link as we speak.

Since it is a continuous form you need to follow what I explained in my last post. Select Case in code won't work. That site has an explanation of the two functions I mentioned and you can look in the Access help files too.

 

[Sniper-BoOyA-]

Just kidding!
Since it is a continuous form you need to follow what I explained in my last post. Select Case in code won't work. That site has an explanation of the two functions I mentioned and you can look in the Access help files too.

Cheers m8.

Ill have a look.

 

[Sniper-BoOyA-]

Hi there, its me again.

I used the following code in VBA

Select Case [diabrkrn]
Case 100
Me.persinstelnr = 1
Case 150
Me.persinstelnr = 2
End Select

And it seems to work, BUT, it only works on the first set of data. And not on the rest (continious form).

Any ideas?

 

[vbaInet]

You've not been following what I mentioned twice. Please re-read my last two posts.

 

[Sniper-BoOyA-]

You've not been following what I mentioned twice. Please re-read my last two posts.

Its working now though.. (for some reason)

 

[vbaInet]

Happy to hear!