Access 2003 - Required Fields, Case Select, Return focus

[Dave Brydon]

I'm checking for required fields, and using the code below, which I normally
have no difficulties with; however, as a twist I decided to return the focus
back to the specific field(s) in question for input.

Under the "Case vbYes" I indicated the two required fields to return the
focus, based on which one is empty, however, I cannot seem to return the
focus. If I only have one field indicated in the code, example;

If Me.txtUDateStart = " " Then
Me.txtUDateStart.SetFocus

Then all is fine, but with both, it will not return the focus to either
field.

Any help appreciated!

Cheers,

Dave

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMissing As String
Dim CRLF As String

DoCmd.SetWarnings False

' line feed
CRLF = Chr(10) & Chr(13)

' 1st field value is null?
strMissing = IIf(IsNull(Me.txtUDateStart), " - Start Date" + CRLF, "")

' 2nd field value is null?
strMissing = strMissing & IIf(IsNull(Me.txtUName), " - Unit name" + CRLF,
"")

' Any required field was null?

If strMissing <> "" Then

' No update
Cancel = True

' ask user is (s)he wants to enter data or cancel

Select Case MsgBox("Required information is missing: " & vbCrLf & ""
_
& CRLF & strMissing & vbCrLf & "Do you want to enter the missing "
_
& "data?" & vbCrLf & "" & vbCrLf & "NOTE: Selecting ""No"" will
clear " _
& "any incomplete data entries", vbYesNo Or vbQuestion Or _
vbDefaultButton1, "Data missing")

Case vbYes
If Me.txtUDateStart = " " Then
Me.txtUDateStart.SetFocus
ElseIf Me.txtUName = " " Then
Me.txtUName.SetFocus
End If

Case vbNo
Me.Undo

End Select

End If

DoCmd.SetWarnings True

End Sub

[Salad]

Dave Brydon wrote:

I'm checking for required fields, and using the code below, which I normally
have no difficulties with; however, as a twist I decided to return the focus
back to the specific field(s) in question for input.

Under the "Case vbYes" I indicated the two required fields to return the
focus, based on which one is empty, however, I cannot seem to return the
focus. If I only have one field indicated in the code, example;

If Me.txtUDateStart = " " Then
Me.txtUDateStart.SetFocus

Then all is fine, but with both, it will not return the focus to either
field.

Any help appreciated!

Cheers,

Dave

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMissing As String
Dim CRLF As String

DoCmd.SetWarnings False

' line feed
CRLF = Chr(10) & Chr(13)

' 1st field value is null?
strMissing = IIf(IsNull(Me.txtUDateStart), " - Start Date" + CRLF, "")

' 2nd field value is null?
strMissing = strMissing & IIf(IsNull(Me.txtUName), " - Unit name" + CRLF,
"")

' Any required field was null?

If strMissing <> "" Then

' No update
Cancel = True

' ask user is (s)he wants to enter data or cancel

Select Case MsgBox("Required information is missing: " & vbCrLf & ""
_
& CRLF & strMissing & vbCrLf & "Do you want to enter the missing "
_
& "data?" & vbCrLf & "" & vbCrLf & "NOTE: Selecting ""No"" will
clear " _
& "any incomplete data entries", vbYesNo Or vbQuestion Or _
vbDefaultButton1, "Data missing")

Case vbYes
If Me.txtUDateStart = " " Then
Me.txtUDateStart.SetFocus
ElseIf Me.txtUName = " " Then
Me.txtUName.SetFocus
End If

Case vbNo
Me.Undo

End Select

End If

DoCmd.SetWarnings True

End Sub

Brain freeze. You checked for null and then compared to a space.
Correct one or the other.

[Dave Brydon]

Salad wrote:

Brain freeze. You checked for null and then compared to a space.
Correct one or the other.

I replaced my original code with the following, however, I now get a compile
error "Case without Select Case" which highlights the "Case" in "Case vbNo"

Any additional guidance would be welcome!

Select Case MsgBox("Required information is missing: " & vbCrLf & ""
_
& CRLF & strMissing & vbCrLf & "Do you want to enter the missing " _
& "data?" & vbCrLf & "" & vbCrLf & "NOTE: Selecting ""No"" will
clear " _
& "any incomplete data entries", vbYesNo Or vbQuestion Or _
vbDefaultButton1, "Data missing")

Case vbYes
If IsNull(Me.txtUDateStart) Then
Me.txtUDateStart.SetFocus
Else
If IsNull(Me.txtUName) Then
Me.txtUName.SetFocus

Case vbNo
Me.Undo

End Select

End If

[Salad]

Dave Brydon wrote:

Salad wrote:

Brain freeze. You checked for null and then compared to a space.
Correct one or the other.

I replaced my original code with the following, however, I now get a compile
error "Case without Select Case" which highlights the "Case" in "Case vbNo"

Any additional guidance would be welcome!

I don't know. I will assume it is your messagebox string that is the
real culprit.

I did notice that you have CRLF, not vbCRLF on one of your lines

If you are having problems with case, use If/Endif.

If msgbox(strMsg,vbYesNo + vbQuestion + vbDefaultbutton1) = vbYes Then
If IsNull(...)
ELse
Me.undo
endif

Select Case MsgBox("Required information is missing: " & vbCrLf & ""
_
& CRLF & strMissing & vbCrLf & "Do you want to enter the missing " _
& "data?" & vbCrLf & "" & vbCrLf & "NOTE: Selecting ""No"" will
clear " _
& "any incomplete data entries", vbYesNo Or vbQuestion Or _
vbDefaultButton1, "Data missing")

Case vbYes
If IsNull(Me.txtUDateStart) Then
Me.txtUDateStart.SetFocus
Else
If IsNull(Me.txtUName) Then
Me.txtUName.SetFocus

Case vbNo
Me.Undo

End Select

End If

I am not sure why you are checking for IsNull(Me.txtUName). Either the
start date or name is blank. Since you checked for the date, it must be
the name.

Although valid code, I as a programmer like to know where my if/endifs
begin and end. Here is how I code, I simply find it more readable

Case vbYes
If IsNull(Me.txtUDateStart) Then
Me.txtUDateStart.SetFocus
Else
Me.txtUName.SetFocus
Endif
Case vbNo

I like to keep things as simple as possible. Good indents. Easy
readability.
I also do things like:
Dim strMsg As String
strMsg = "Are you sure you want to continue?"
If msgbox(strMsg,...) Then

when strMsg may be multiple lines. I find it easier to modify the code
and read and the overhead will not slow your program.

I try to do things that make my life easier to modify & debug. My $.02

[Dave Brydon]

Salad wrote:

I don't know. I will assume it is your messagebox string that is the
real culprit.

I did notice that you have CRLF, not vbCRLF on one of your lines

If you are having problems with case, use If/Endif.

If msgbox(strMsg,vbYesNo + vbQuestion + vbDefaultbutton1) = vbYes Then
If IsNull(...)
ELse
Me.undo
endif

Select Case MsgBox("Required information is missing: " & vbCrLf & "" _
& CRLF & strMissing & vbCrLf & "Do you want to enter the missing " _ & "data?" & vbCrLf & "" & vbCrLf & "NOTE: Selecting ""No"" will
clear " _
& "any incomplete data entries", vbYesNo Or vbQuestion Or _
vbDefaultButton1, "Data missing")

Case vbYes
If IsNull(Me.txtUDateStart) Then
Me.txtUDateStart.SetFocus
Else
If IsNull(Me.txtUName) Then
Me.txtUName.SetFocus

Case vbNo
Me.Undo

End Select

End If

I am not sure why you are checking for IsNull(Me.txtUName). Either the
start date or name is blank. Since you checked for the date, it must be
the name.

Although valid code, I as a programmer like to know where my if/endifs
begin and end. Here is how I code, I simply find it more readable

Case vbYes
If IsNull(Me.txtUDateStart) Then
Me.txtUDateStart.SetFocus
Else
Me.txtUName.SetFocus
Endif
Case vbNo

I like to keep things as simple as possible. Good indents. Easy
readability.
I also do things like:
Dim strMsg As String
strMsg = "Are you sure you want to continue?"
If msgbox(strMsg,...) Then

when strMsg may be multiple lines. I find it easier to modify the code
and read and the overhead will not slow your program.

I try to do things that make my life easier to modify & debug. My $.02

Thank you very much for your help, and I certainly appreciate your comments.
Everything is currently working well; final code below.

Cheers,

Dave

' ask user is (s)he wants to enter data or cancel

If MsgBox("Required information is missing: " & _
vbCrLf & "" & vbCrLf & strMissing & vbCrLf & "Do you want to " _
& "enter the missing data?" & vbCrLf & "" & vbCrLf & "NOTE: " _
& "Selecting ""No"" will clear any incomplete data entries", _
vbYesNo + vbQuestion + vbDefaultButton1, "Data missing") = vbYes
Then

If IsNull(txtUDateStart) Then
Me.txtUDateStart.SetFocus

ElseIf IsNull(txtUName) Then
Me.txtUName.SetFocus
End If

Else
Me.Undo
End If

[Salad]

Dave Brydon wrote:

Salad wrote:

Thank you very much for your help, and I certainly appreciate your comments.
Everything is currently working well; final code below.

Cheers,

Dave

' ask user is (s)he wants to enter data or cancel

If MsgBox("Required information is missing: " & _
vbCrLf & "" & vbCrLf & strMissing & vbCrLf & "Do you want to " _
& "enter the missing data?" & vbCrLf & "" & vbCrLf & "NOTE: " _
& "Selecting ""No"" will clear any incomplete data entries", _
vbYesNo + vbQuestion + vbDefaultButton1, "Data missing") = vbYes
Then

If IsNull(txtUDateStart) Then
Me.txtUDateStart.SetFocus

ElseIf IsNull(txtUName) Then
Me.txtUName.SetFocus
End If

Else
Me.Undo
End If

Very Pretty Code! :-)

One last comment and it will be perfecto. You really don't need ElseIf
unless you are doing multiple comparisons. For a comparision/action
between two items you can use Else instead of ElseIf.