[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

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
0
tammyborden32
Asked:
tammyborden32
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 MVP, Access and Data Platform)Commented:
".  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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now