Required field on form

Is it possible to require an entry in a field based on whether another Yes/No field is checked as "Yes".  

For example:  User adds a new record (using a Form).  On the form the User checks Yes for new revision.  Therefore several other fields require entry.  I can't set the field as required at the table level because entry is only required  if it is a new revision.

Something like
If NewRevision = Yes Then Field2 Is Not Null  .... display MsgBox "Field2 Is Required" User clicks OK - Set Focus on Field2......

Thanks,

Tammy
tammyborden32Asked:
Who is Participating?
 
mbizupCommented:
If I'm picturing your code right, you'd just need an Exit Sub statement in each if-then block so that only one field would be validated at a time allowing the user to make that correction before continuing to the next:

private sub Form_BeforeUpdate(Cancel as integer)
If NewRevision = true then
      If Field2 & "" = "" then 
             msgbox "You need a value for field 2"
             cancel = true
             me.field2.setfocus
             Exit Sub  '<-- Exit the validation checks
      end if
      If Field3 & "" = "" then 
             msgbox "You need a value for field 3"
             cancel = true
             me.field3.setfocus
             Exit Sub  '<-- Exit the validation checks
      end if
' etc

end if
end sub 

Open in new window

0
 
mbizupCommented:
Use the before update event of the form.

The code would be something like this:

private sub Form_BeforeUpdate(Cancel as integer)
If NewRevision = true then
      If Field2 & "" = "" then
             msgbox "You need a value for field 2"
             cancel = true
             me.field2.setfocus
      end if
end if
end sub
0
 
Rey Obrero (Capricorn1)Commented:
use the beforeupdate event of the form to check if "NewRevision" is set to yes

private sub form_beforeupdate(cancel as integer)

if NewRevision=-1 then

' your validation codes here
end if


end sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tammyborden32Author Commented:
Genius - That works for validating one field.  How do I validate additional fields?  I tried adding additional If statements, which works but it gives all the Msgboxes one after another and sets focus on the last field.  I want to have the first field checked and when the user clicks ok it goes to the form for thatentry, then if they try to close the form, it checks the next and so on.  Thought about using Else instead of End If but I get an error that I have a If without and End If.

0
 
Rey Obrero (Capricorn1)Commented:
the best way to do this is to use Tag Property of the control
select all the controls you want to be validated and place "REQUIRED" on their Tag property ( it is on the Other Tab of the control property sheet)

see the codes from this thread

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26716098.html?#a34478055
0
 
tammyborden32Author Commented:
mbizup, the Genius!:  That works.  Lastly, I get the additional message from Access saying "can't save the record at this time....Do you want to close the database object anyway".  Is there a way to keep that from appearing?
0
 
mbizupCommented:
Hi -

Can you post a sample illustrating this - with any sensitive data masked or removed?

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
".  Lastly, I get the additional message from Access saying "can't save the record at this time....Do you want to close the database object anyway""

This is probably occurring because ... you are trying to close the Form  (or App) and that field is still in the validation mode.  So, you either need to enter a correct  value OR hit ESCAPE once/twice to Undo control/Form.

mx
0
 
tammyborden32Author Commented:
This solved my original question!  Thanks for the great support!  

I'll open the last part as a new question next week.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.