after update, make another field required

[mjvm]

Hi,

I have had a similar question answered but don't understand code sufficiently to adapt it myself. Can someone please help and write the code I need?

I have a date field called Received, and when a date is entered in this field, I want another field called Action to be compulsory or required. The Action field is a drop down box (Combo?).

I am assuming it is an AferUpdate event, but I may be wrong.

Thanks so much for the help I get from this forum - the answers are supportive and easy to use!

Marcella

[ADezii]

There are a couple of ways to handle this situation, but in my opinion, the most efficient method would be in the BeforeUpdate() Event of the Form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
 
strMsg = "If you have a Date in the Received Field, then you must " & _
         "also enter a corresponding value in the Action Field"
 
If Not IsNull(Me![Received]) Then
  If IsNull(Me![Action]) Then
    MsgBox strMsg, vbExclamation, "Missing Value in Action Field"
      Cancel = True
  End If
End If
End Sub

[mjvm]

There are a couple of ways to handle this situation, but in my opinion, the most efficient method would be in the BeforeUpdate() Event of the Form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
 
strMsg = "If you have a Date in the Received Field, then you must " & _
         "also enter a corresponding value in the Action Field"
 
If Not IsNull(Me![Received]) Then
  If IsNull(Me![Action]) Then
    MsgBox strMsg, vbExclamation, "Missing Value in Action Field"
      Cancel = True
  End If
End If
End Sub

Hi ADezii,

Brilliant! Thanks for the code. I was a bit nervous because I already had a form_BeforeUpdate event happening, but I put it in and it worked. Then I started to reply with questions in my efforts to understand what you did but then worked those out.

However, what does the [& _] do in the string message? I thought it might force a second line in the message box - but it doesn't. Do you know how I can break a message into two lines - both in Code and when I set up a Message Box in the Properties sheet?

I'm in Access 2003.

Thanks again!

marcella

[RuralGuy]

Try:
strMsg = "If you have a Date in the Received Field, then you must " & vbCrLf & _
"also enter a corresponding value in the Action Field"

[ADezii]

Hi ADezii,

Brilliant! Thanks for the code. I was a bit nervous because I already had a form_BeforeUpdate event happening, but I put it in and it worked. Then I started to reply with questions in my efforts to understand what you did but then worked those out.

However, what does the [& _] do in the string message? I thought it might force a second line in the message box - but it doesn't. Do you know how I can break a message into two lines - both in Code and when I set up a Message Box in the Properties sheet?

I'm in Access 2003.

Thanks again!

marcella

1. The combination of <Ampersand><Space><Underscore>, namely & _, is a line continuation sequence. I'll post some code to illustrate my point. All three statements will produce exactly the same results as indicated below:
   

   Dim strString As String
    
   strString = "She sells seashells at the "
   strString = strString & "seashore!"
    
   Debug.Print "She sells seashells at the seashore!"
   Debug.Print "She sells seashells at the " & _
               "seashore!"
   Debug.Print strString

   OUTPUT:
   

   She sells seashells at the seashore!
   She sells seashells at the seashore!
   She sells seashells at the seashore!

2. As far as forcing Lines (Carriage Return/Line Feed), namely vbCrLf, in Text, again I'll post some code to illustrate my point
   

   Debug.Print "She" & vbCrLf & "sells" & vbCrLf & "seasheels" & _
               vbCrLf & "at" & vbCrLf & "the" & _
               vbCrLf & "seashore!"

   OUTPUT:
   

   She
   sells
   seasheels
   at
   the
   seashore!

3. Here is sort of a Hybrid with no Line Breaks (CR/LFs), but with the Concatenation Operator (&) and Line Continuation Character (_):
   

   Debug.Print "She " & "sells " & "seasheels " & _
               "at " & "the " & _
               "seashore!"

   OUTPUT:
   

   She sells seashells at the seashore!

4. Has this helped or have I totally confused you? (LOL)!
   

[mjvm]

1. The combination of <Ampersand><Space><Underscore>, namely & _, is a line continuation sequence. I'll post some code to illustrate my point. All three statements will produce exactly the same results as indicated below:
   

   Dim strString As String
    
   strString = "She sells seashells at the "
   strString = strString & "seashore!"
    
   Debug.Print "She sells seashells at the seashore!"
   Debug.Print "She sells seashells at the " & _
               "seashore!"
   Debug.Print strString

   OUTPUT:
   

   She sells seashells at the seashore!
   She sells seashells at the seashore!
   She sells seashells at the seashore!

2. As far as forcing Lines (Carriage Return/Line Feed), namely vbCrLf, in Text, again I'll post some code to illustrate my point
   

   Debug.Print "She" & vbCrLf & "sells" & vbCrLf & "seasheels" & _
               vbCrLf & "at" & vbCrLf & "the" & _
               vbCrLf & "seashore!"

   OUTPUT:
   

   She
   sells
   seasheels
   at
   the
   seashore!

3. Here is sort of a Hybrid with no Line Breaks (CR/LFs), but with the Concatenation Operator (&) and Line Continuation Character (_):
   

   Debug.Print "She " & "sells " & "seasheels " & _
               "at " & "the " & _
               "seashore!"

   OUTPUT:
   

   She sells seashells at the seashore!

4. Has this helped or have I totally confused you? (LOL)!
   

And I did LOL! Yep - I get it.

I have made it work - but Rural Guy, I needed to put a space between the & and the underscore before it worked perfectly.

Can I force a carriage return when I set the message box in the Properties Sheet?

marcella

[ADezii]

And I did LOL! Yep - I get it.

I have made it work - but Rural Guy, I needed to put a space between the & and the underscore before it worked perfectly.

Can I force a carriage return when I set the message box in the Properties Sheet?

marcella

Can I force a carriage return when I set the message box in the Properties Sheet?

Yes, you can force a Carriage Return/Line Feed in the Properties Box by entering the CTRL+ENTER Key Combination. Typing Hello, then CTRL/ENTER, then World!, will produce:

Hello
World!

P.S. - Hold the CTRL Key down, then press the ENTER Key.

[mjvm]

Yes, you can force a Carriage Return/Line Feed in the Properties Box by entering the CTRL+ENTER Key Combination. Typing Hello, then CTRL/ENTER, then World!, will produce:

Hello
World!

P.S. - Hold the CTRL Key down, then press the ENTER Key.

You are a star! Thank you.

Marcella

[ADezii]

You are quite welcome.