Data validation: input Thursdays only

[dgtampa]

I'm trying to get this code to work in the BeforeUpdate event; the idea is that when the user enters a date which falls on any other day of the week except for a Thursday, a message box prompts them to pick a date which falls on a Thursday. Once they do pick a "Thurs-date", the focus is set on a command button on the form.

I can kinda-sorta get this to work using different combinations of code, but 4 different types of things invariably happen:

1) The message box appears as desired, but the focus cannot be re-set;


2) I get an error which states 'Invalid use of Null' (over and over each time I choose a subsequent date, even Thursday);
3) I get an error for a data mis-match;
4) the message box appears over-and-over, no matter if I pick a "Thurs-date" or not...

Here is the code I am using:

Private Sub cboDatePick_Change()
If Weekday(Me.cboDatePick) = 5 Then
        Me.btnOpenSchedule.SetFocus
    Else
        Beep
        MsgBox "You need to pick a date which falls on a Thursday...", vbInformation, "Oops!"
        DoCmd.CancelEvent
        DoCmd.ClearMacroError
    End If
End Sub

I've tried also changing the first section to

If Weekday(Forms!frmStart.cboDatePick) = 5 Then

To no avail.

I'm guessing my "IF" argument is not being written correctly, or something like that...Thanks in advance!

[Missinglinq]

When doing something that is based on the Value entered in a Control, you need to use the Control's BeforeUpdate event:

Private Sub cboDatePick_BeforeUpdate(Cancel As Integer)
If Weekday(Me.cboDatePick) <> 5 Then
  Beep
  MsgBox "You need to pick a date which falls on a Thursday...", vbInformation, "Oops!"
  Cancel = True
  DoCmd.ClearMacroError
End If
End Sub

But you cannot Move to another Control from this event until the Control's Value is saved, so you need to move that piece of code into another event:

Private Sub cboDatePick_Exit(Cancel As Integer)
 Me.btnOpenSchedule.SetFocus
End Sub

I'm curious about something, though! The name cboDatePick would seem to indicate that the Control is a Combobox, and if this is true, why are non-Thursday Dates available?

Also note that I left the line

DoCmd.ClearMacroError

in place, although I have no idea why it is there, since it has nothing to do with this task!

Linq ;0)>

[dgtampa]

Thank you Missinglinq! (feels kinda wierd saying that...)

Your solution works very well! I wound up putting the SetFocus code in the OnDirty event for the control.

As for the control name cboDatePick; I was just sloppy with that particular prefix. I am usually more careful with my naming conventions. The control is, in fact, a TextBox, not a ComboBox, but by the time I realized the error myself, I already had several other objects referring to that form and didn't want to change it. The reason for TextBox over ComboBox is that I wanted to have a date picker available for the user to select a Thursday date in the future.

I have no idea why the ClearMacroError command is there either; I guess I was just trying to get rid of the error boxes somehow?

Again, I thank you for your help!