Simple one

[Sam Summers]

On a form, i have a combobox (JobNumber) that the focus is set to when the form is opened. I also have a textbox (JobNo) that displays the current location. Now i want to ensure that the user selects an item from the list that is different from the current location. before they enter an item in a textbox (NumberInput) on the form.

At the moment I have this code on the Exit event:-

Me.JobNumber.SetFocus
If Me.JobNumber = Me.JobNo Then
MsgBox "Please select a new location"
Me.JobNo.Setfocus


However I am getting the cannot reference a contol etc. etc.

Thanks in advance

 

[KenHigg]

If Me!JobNumber = Me!JobNo Then

??

 

[Sam Summers]

Yeah thats right.

Checking to see if a combobox's text = a textbox's text

 

[KenHigg]

Did it work (Using the exlaimation marks instead of the periods)?

 

[Sam Summers]

Hi Ken,

Didn't get any 'Can't reference etc. etc.' message but then again it didn't do anything either......?

This is what I now have

If Me!JobNumber = Me!JobNo Then
MsgBox "Please Select etc. etc.
Me.JobNo.Setfocus
Else
Me.NumberInput.SetFocus
End If

I also moved this to the LostFocus Event?
Maybe I should re-insert the .Text after the control name?

 

[KenHigg]

I think the .text option will cause problems because I think the control being referenced has to have focus - in this case, that would be impossible.

You may try putting some code that would force something to happen either way.

Do it this way first (the way you currently have it):

If Me!JobNumber = Me!JobNo Then

Then try:

If Me!JobNumber <> Me!JobNo Then

If this doesn't work then you know that part of the code ain't broke, this would cut your problem in half...

Also, seems I usually use Afterupdate...

 

[Mike375]

Could you put code on the Combo AfterUpdate that opens a form for the matching record if the the Combo = Text box. The form has a note like "you have tried to enter etc".

The form that opens would also have its own combo etc so the entry could be made from the form that opens and that action would also close the form.

By doing things this way it serves as a visual reminder that they are doing the wrong thing.

Mike

 

[Sam Summers]

Kens bit did the Job:-

If Me!JobNumber <> Me!JobNo Then

It was just to make sure that the user selected a different location from the current one.

But that looks like its got it now.

Thanks

 

[Sam Summers]

Ken, that was wrong what i said before.

It actually comes up with the msgbox all the time now whether the two controls match each other or not?

 

[Pat Hartman]

I find that controlling the form at this level is usually unnecessary, not to mention annoying. Since most of the time the user does the right thing you can put all your error trapping in the final form event prior to the actual record save. That event is the form's BeforeUpdate event. Make it your friend.

If Me.JobNumber = Me.JobNo Then
    MsgBox "Please Select etc. etc.
    Me.JobNo.Setfocus
    Cancel = True
End If
If IsNull(Me.JobNumber) or Me.JobNumber = "" Then
    error....
    Me.JobNumbe.Setfocus
    Cancel = True
End IF
If IsNull(Me.JobNo) or Me.JobNo = "" Then
    error ....
    Me.JobNo.Setfocus
    Cancel = True
End If

Notice the addition of the "Cancel = True" - this cancels the update so that the record cannot be saved. Also, the original code does not take nulls or zero-length strings into account. If one of the job number fields is null or contains a zero-length string, this condition will return false and so it will NOT throw an error. I would expand it.

And finally, the Dot (.) syntax is preferred over the Bang (!) WHENEVER it is available. The Dot syntax gives you intellisense (drop down help) and points out errors immediately rather than at compile time or worse, at run time.

 

[Sam Summers]

Tried all that Pat and it didn'y really work. The problem is that the user selects a new location from the combobox (JobNo) before entering numbers into a text box (NumberInput) on the same form. When they press 'Enter' a query references the 'JobNo' and uses the text in this box as criteria. This procedure may be repeated for 100's of items before the form is closed.
I just wanted to ensure that the user selects a different location from the one being displayed in the text box (JobNumber).

I tried the simple - If Me.JobNumber = Me.JobNo Then


but this throws up Focus issues and I've tried all ways and still can't seem to do this simple task.......?

Thanks anyway

 

[Sam Summers]

Forget the previous, I managed to solve it - I tried one last thing.
I simply reversed the code to this:-

If Me!JobNo.Text = Me!JobNumber Then



and at last a simple problem was solved with a simple answer!!!

Thanks everyone

 

[CBragg]

I know this may seem odd but have you tried it on the Before_Update, or On_Enter of the next tabbed text box after your jobNo??

 

[Sam Summers]

Tired the Before Update but not the On enter. May try that one. The main thing is at least it is working effectively now.

Thanks