Cbo Box, Selected, setfocus, then validation, please help (1 Viewer)

[campanellisj]

I hope that someone is able to help me with this bizarre situation. I have a combo box with status codes selectable within.

The situation that I have been asked to perform is that when a specific selection is made from the combo box (say TDY) it displays a message (i have this part covered lol) and then sets focus to the start date field (TDYstart). The date field must not allow an entry of a future date.

the other part of this task is to have the start date field unavalible until TDY is selected from the combobox. once selected it becomes active and the above is needed. can anyone help?

 

[dkinley]

In the AfterUpdate event of the combo box you can use: Me.txtTDYstart.SetFocus.

In the AfterUpdate event of this control you can use something like: If Me.txtTDYstart.Value < Date() Then .......

HTH,

-dK

 

[campanellisj]

I have tried the afterupdate suggestion but after I make a selection the focus remains on the combo box.

 

[dkinley]

So you click the combo box ... then a message pops up, right?

-dK

 

[campanellisj]

I click the dropdown select 'TDY' the message box appears stating that a start date must be entered to continue, but the focus is not set to the date box, it stays at the dropdown.
Also If possible I would like the date field to be in inactive until 'TDY' is selected, then the message, then the focus.

 

[dkinley]

Try something like ....

In the OnCurrent event of the form use ...

Me.txtControlName.Locked = True

In the AfterUpdate event of the combo box use ...

If Me.cboComboName.Column(x) = "TDY" Then
   MsgBox ....
   Me.txtControlName.Locked = False
   Me.txtControlName.SetFocus
End If

In the AfterUpdate event of the control use ...

If Me.txtControlName.Value < Date() Then
   MsgBox .... if you want one
   Cancel = True
   Me.txtControlName.SetFocus = True
End IF

This is all aircode but should get you going. If you any issues, post back.

-dK

 

[campanellisj]

everything worked great up to the afterupdate part.
I typed:

If me.arrival_data.value > date Then
cancel = true
me.arrival_data.setfocus = true
end if

i used greater than cause I didn't want a future date to be allowed, but it still allows the input of any date..

 

[dkinley]

Well .. there were a couple of mistakes .. mine and yours. Make it look like ...

If Me.arrival_data.Value > Date Then
    MsgBox "No"
    Me.arrival_data = Me.arrival_data.OldValue
    Me.arrival_data.SetFocus
End If

-dK

 

[campanellisj]

That did it, thank you so much for your time and effort

 

[dkinley]

Glad I could assist and we got it working for you right.

Good luck!

-dK

 

[campanellisj]

DK,
I hate to ask you this but my wonderful boss has also asked to not allow this field to accept a null value once it has focus based on the code we worked yesterday.
In other words once "TDY" is selected and the focus is changed to the box, the message displays the individual can then just leave the box without adding data. How can we make this box a required field only when "TDY" is selected.
I know how to make it a required filed with the properties on the form but that makes it required regardless of what was selected in teh dropdown so it won't help in this situation.

 

[dkinley]

In the BeforeUpdate of the form you can use ....

If Me.cboComboName.Column(x) = "TDY" Then
     If Len(Nz(Me.arrival_data, "")) = 0 Then
         Msgbox 'something about what the deal is
         Cancel = True
     End If
End If

I was erroneous earlier, the control event does not have, but the form event for this has the cancel so it is not saved.

-dK

 

[campanellisj]

DK,
Sorry to keep bothering you, you have been a great help. I tried your last suggestion but the results were not good lol. It still allowed me to tab out of the date field without entering a date at all. Everything works great except that.....
Is there a way we can add something to the afterupdate part of our code to not allow null values?

If Me.arrival_data.Value > Date Then
MsgBox "No"
Me.arrival_data = Me.arrival_data.OldValue
Me.arrival_data.SetFocus
End If

 

[vbaInet]

...It still allowed me to tab out of the date field without entering a date at all.

It should allow you tab out. You should be validating the date field on the Before Update and the Before Insert events of the FORM.

1. Use the IsDate() function to test for a valid date. It should trap Nulls too.
2. THEN, perform the check for whether Arrival_date is greater than Date.
3. If it doesn't meet your criteria then CANCEL.

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

 

[campanellisj]

I'm Sorry I truly don't understand. I am not a VB coder (obviously lol). I understand what you mean by placing something in the before update area of the FORM, but what is the code? and the code has to allow empty (null) values for the date when "TDY" is not selected from the dropdown....I only need to force a data entry when "TDY" is selected and the box receives focus. does that make sense?

 

[dkinley]

Reread my posts and look at where I was referencing the form verses control as vbaInet pointed out ...

This link might help you when it comes to VBA.

-dK

 

[vbaInet]

....I only need to force a data entry when "TDY" is selected and the box receives focus. does that make sense?

What would the scenario be if I want to cancel the record entry completely? Does that mean I must enter a date that gets saved, then delete the record?

If you still want to follow with your type of validation, then dk had already given you want you needed. All you do is set focus to back to that text box. Here's the amended version:

If Me.cboComboName.Column(x) = "TDY" Then
     If Not IsDate(Me.arrival_data) Then
         Msgbox 'something about what the deal is
         Cancel = True
         txtbox1.SetFocus
     End If
End If

You already know what two events to test this on