Simple question...

[andy_dyer]

Hi,

I don't know an awful lot about VBA but here's what I am trying to do...

I have two combo boxes NonStarter & Non_completer, they are both defaulted to "N/A".

I want it so that somone cannot have both boxes displaying anything other than "N/A".

So I have a statement that lists if one box says anything apart from N/A and so does the other box then display a message box telling them off and setting the focus back to the first box I want to them to check for an error...

My code is:

If Me.NonStarter = "No Response" Or Me.NonStarter = "Work Commitments" Or Me.NonStarter = "Illness/Further Tests" Or Me.NonStarter = "Now Wish To" Or Me.NonStarter = "Other" Or Me.NonStarter = "Cost" Or Me.NonStarter = "Transport" And Me.Non_completer = "No Response" Or Me.Non_completer = "Work Commitments" Or Me.Non_completer = "Illness/Further Tests" Or Me.Non_completer = "Now Wish To" Or Me.Non_completer = "Other" Or Me.Non_completer = "Cost" Or Me.Non_completer = "Transport" Then
MsgBox "The Client Cannot Be Both a Non-Starter & a Non-Completer. Please now set the Non-Completer Reason first and then set the Non-Starter Reason."
Cancel = True
Me.Non_completer.SetFocus
Exit Sub
End If

Can anyone tell me what I am doing wrong?

Thanks

Andy

 

[IMO]

You could try this, I haven't tried it though.

If Me.NonStarter <> "N/A" and Me.Non_completer <> "N/A" Then 
MsgBox "The Client Cannot Be Both a Non-Starter & a Non-Completer. Please now set the Non-Completer Reason first and then set the Non-Starter Reason." 
Cancel = True 
Me.Non_completer.SetFocus 
Exit Sub 
End If

IMO

 

[andy_dyer]

Thanks IMO!

Knew I was missing something simple!

Andy

 

[Rogue Programme]

IMO

I'd like to use this code also however. when i press ok on the message box i can't get it to setfocus on the same control..it goes to the next control in the tab order.

Cheers

 

[Rogue Programme]

IMO

Sorry my last post was a bit vague, i'll explain.

I have a control on a form that the only numbers that can be input in is 20 or 0.
Therefore i need code which i presume will be an if statement like the one you have stated above that will create a msgbox "Number must be 20 or 0" and when i click ok it the same control is setfocus.

Mike

 

[IMO]

Try this in the after update event of your text box

If Me.YourTextBox = "0" Or Me.YourTextBox = "20" Then
Else
MsgBox "YourMessageHere"
End If

IMO

 

[Rogue Programme]

IMO

Works perfect thanx!

What about emptying the data from the control on exit of the msgbox??

Is it possible?

 

[IMO]

If Me.YourTextBox = "0" Or Me.YourTextBox = "20" Then
Else
MsgBox "YourMessageHere"
Me.YourTextBox = ""
End If

IMO

 

[Rogue Programme]

IMO

Almost!

It's emptying the control no matter what is put in.

Mike

 

[IMO]

Have a look at the post again, I edited it

IMO

 

[Rogue Programme]

IMO

This seams to work perfect...

If Me.MyControlName = "0" Or Me.MyControlName = "20" Then
Else
MsgBox "Must be 0 or 20"
Me.AnotherControlName.SetFocus
Me.MyControlName.SetFocus
Me.MyControlName = ""
End If

 

[Mile-O]

Me.AnotherControlName.SetFocus
Me.MyControlName.SetFocus

Why?

 

[Rogue Programme]

Dunno mate, i had to ask IMO coz i couldn't get the focus to reset on the same control without it moving to the next control on the tab order when i pressed 'ok' on error MsgBox?!?!?!?!?!?

It works anyway.

Mike

 

[IMO]

You shouldn't need...

Me.AnotherControlName.SetFocus 
Me.MyControlName.SetFocus

as this...

Me.MyControlName = ""

should set the focus onto the control as it clears it

IMO

 

[Rogue Programme]

IMO

For some reason it won't work.

If i take out those two lines of code and leave in:

me.mycontrolname = ""

It still moves the focus to the next control in the tab order.

 

[IMO]

Sorry, my mistake

IMO

 

[Rogue Programme]

No worries mate..

Thanks for your help

Mike