By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,497 Members | 2,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,497 IT Pros & Developers. It's quick & easy.

after update, make another field required

P: 52
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
Nov 15 '08 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,597
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Dim strMsg As String
  3.  
  4. strMsg = "If you have a Date in the Received Field, then you must " & _
  5.          "also enter a corresponding value in the Action Field"
  6.  
  7. If Not IsNull(Me![Received]) Then
  8.   If IsNull(Me![Action]) Then
  9.     MsgBox strMsg, vbExclamation, "Missing Value in Action Field"
  10.       Cancel = True
  11.   End If
  12. End If
  13. End Sub
Nov 15 '08 #2

P: 52
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Dim strMsg As String
  3.  
  4. strMsg = "If you have a Date in the Received Field, then you must " & _
  5.          "also enter a corresponding value in the Action Field"
  6.  
  7. If Not IsNull(Me![Received]) Then
  8.   If IsNull(Me![Action]) Then
  9.     MsgBox strMsg, vbExclamation, "Missing Value in Action Field"
  10.       Cancel = True
  11.   End If
  12. End If
  13. 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
Nov 16 '08 #3

RuralGuy
Expert 100+
P: 375
Try:
strMsg = "If you have a Date in the Received Field, then you must " & vbCrLf & _
"also enter a corresponding value in the Action Field"
Nov 16 '08 #4

ADezii
Expert 5K+
P: 8,597
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:
    Expand|Select|Wrap|Line Numbers
    1. Dim strString As String
    2.  
    3. strString = "She sells seashells at the "
    4. strString = strString & "seashore!"
    5.  
    6. Debug.Print "She sells seashells at the seashore!"
    7. Debug.Print "She sells seashells at the " & _
    8.             "seashore!"
    9. Debug.Print strString
    OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. She sells seashells at the seashore!
    2. She sells seashells at the seashore!
    3. 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
    Expand|Select|Wrap|Line Numbers
    1. Debug.Print "She" & vbCrLf & "sells" & vbCrLf & "seasheels" & _
    2.             vbCrLf & "at" & vbCrLf & "the" & _
    3.             vbCrLf & "seashore!"
    OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. She
    2. sells
    3. seasheels
    4. at
    5. the
    6. seashore!
  3. Here is sort of a Hybrid with no Line Breaks (CR/LFs), but with the Concatenation Operator (&) and Line Continuation Character (_):
    Expand|Select|Wrap|Line Numbers
    1. Debug.Print "She " & "sells " & "seasheels " & _
    2.             "at " & "the " & _
    3.             "seashore!"
    OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. She sells seashells at the seashore!
  4. Has this helped or have I totally confused you? (LOL)!
Nov 16 '08 #5

P: 52
  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:
    Expand|Select|Wrap|Line Numbers
    1. Dim strString As String
    2.  
    3. strString = "She sells seashells at the "
    4. strString = strString & "seashore!"
    5.  
    6. Debug.Print "She sells seashells at the seashore!"
    7. Debug.Print "She sells seashells at the " & _
    8.             "seashore!"
    9. Debug.Print strString
    OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. She sells seashells at the seashore!
    2. She sells seashells at the seashore!
    3. 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
    Expand|Select|Wrap|Line Numbers
    1. Debug.Print "She" & vbCrLf & "sells" & vbCrLf & "seasheels" & _
    2.             vbCrLf & "at" & vbCrLf & "the" & _
    3.             vbCrLf & "seashore!"
    OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. She
    2. sells
    3. seasheels
    4. at
    5. the
    6. seashore!
  3. Here is sort of a Hybrid with no Line Breaks (CR/LFs), but with the Concatenation Operator (&) and Line Continuation Character (_):
    Expand|Select|Wrap|Line Numbers
    1. Debug.Print "She " & "sells " & "seasheels " & _
    2.             "at " & "the " & _
    3.             "seashore!"
    OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. 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
Nov 16 '08 #6

ADezii
Expert 5K+
P: 8,597
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:
Expand|Select|Wrap|Line Numbers
  1. Hello
  2. World!
P.S. - Hold the CTRL Key down, then press the ENTER Key.
Nov 16 '08 #7

P: 52
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:
Expand|Select|Wrap|Line Numbers
  1. Hello
  2. World!
P.S. - Hold the CTRL Key down, then press the ENTER Key.



You are a star! Thank you.

Marcella
Nov 17 '08 #8

ADezii
Expert 5K+
P: 8,597
You are quite welcome.
Nov 17 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.