• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

Data validation in access

Hi

I have 2 combo box cbo1 and cbo2, here is the visual basic code
on the cbo2 on lost focus event:

if me.cbo1.text = "value1" and me.cbo2.text = "" then
msgbox("You must enter a value for cbo2")
me.cbo2.setfocus
After running this I receive an error (I know this will work on visual basic, any idea on how it will work on access)
0
serigne
Asked:
serigne
  • 5
  • 2
  • 2
  • +5
1 Solution
 
BrianWrenCommented:
Private Sub cbo2_LostFocus()
   
    If Me.cbo1 = "value1" And IsNull(Me.cbo2) Then
        MsgBox ("You must enter a value for cbo2")
        Me.cbo2.SetFocus
    End If
   
End Sub

The default property of a combo, (and the property that you would want to reference if you were going to be explicit is .Value.  The empty cbo would be Null.

Brian
0
 
chewhoungCommented:
what error do you receive? and which line is that?

Move move the above code to Before Update event of your form and
add another line below the setfocus line like this:

if me.cbo1.text = "value1" and me.cbo2.text = "" then
    msgbox("You must enter a value for cbo2")
    me.cbo2.setfocus
    cancel = true
end if
0
 
CEBAKACommented:
BrainWren is right
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
serigneAuthor Commented:
Hi

When I try and test Brian solution nothing happens after the control lost the focus, what I may doing something wrong, any idea. I am using office 97.
0
 
simonbennettCommented:
Try your code on the AfterUpdate event e.g.

Private Sub cbo2_AfterUpdate()
   
    If Me.cbo1 = "value1" And IsNull(Me.cbo2) Then
        MsgBox ("You must enter a value for cbo2")
        Me.cbo2.SetFocus
    End If
     
End Sub  

HTH

Simon
0
 
brewdogCommented:
Question: wouldn't you actually want this check done if the value in cbo1 or cbo2 changes? If that's the case, then how about:

Private Sub CheckCombos()
   if me.cbo1 = "value" then
      if len(nz(cbo2,"")) = 0 then
         msgbox "You must enter a value for cbo2"
      end if
   end if
End Sub

and then place this line in the AfterUpdate event of both cbos:

Call CheckCombos

brewdog
0
 
BrianWrenCommented:
The test for cbo2 is tricky.  Controls without values are sometimes 'Empty' sometimes 'Null' and sometimes '""'...

Try an individual test for the two conditions in the Event.

If cbo1 ...

and

If cbo2 ...

to see if at least one of them is working.

The code I posted last night was cut and paste out of an Access data base where I tested it for the conditions you had described...

Brian
0
 
Green_BoyCommented:
Private Sub cbo2_LostFocus()
If Me![cbo1] = "Value1" And IsNull(Me![cbo2]) = True Then
MsgBox ("You must enter a value for cbo2")
Me![cbo2].SetFocus
End If
End Sub
0
 
wesleystewartCommented:
I usually test empty controls with:

If Len(NZ(MyControl)) = 0 then
'control is empty or "" or null
End If

It might be a little clumsy but it works when there's a chance that a control might be null or empty or set to a zero-length string or  . . .

Wes
0
 
brewdogCommented:
That's why I posted mine that way, Wes. :o)
0
 
wesleystewartCommented:
I'm sorry I didn't catch that.

Actually I thought your comment was (as usual) a great solution to serigne's issue.  I don't know how I missed that . . .Didn't mean to step on any toes.

It is definitely the way to get around controls that might be "empty" but evaluate to any number of nll, zero-length strings, etc.

I suppose it would simplify things to encapsulate the Len(NZ(MyControl)) construct into a function like

Public Function IsCtrlEmpty(varData as Variant) as Boolean
If Len(NZ(varData)) = 0 Then
IsCtrlEmpty = true
End If
End Function

Would make it simpler than retyping the whole thing everytime.

Wes
0
 
brewdogCommented:
I didn't take any offense, Wes. I know you're solid.

Actually, one of my coworkers just did a similar thing to what you were proposing, except he also wanted to take into account the entry of a space(s) in a field. So he did:

len(trim(nz([Field],""))=0

which works well for us.
0
 
brewdogCommented:
Any news here, serigne? I think you have a few viable solutions . . .
0
 
brewdogCommented:
Glad I could help out. Did you use my suggestion about ComboCheck, or just decide to give me the points because I was the most persistent? :o)
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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