?
Solved

If then question

Posted on 2011-10-27
12
Medium Priority
?
331 Views
Last Modified: 2013-11-05
On a form I have two fields.  When the user enters a value that does not match the value in another field I want a message box to popup.  Here is my code which is not working.  It pops up even if the two field values do match.  Note that both fields are number fields.

    If Me.cbofield2 <> Me.cbofield1 Then
        MsgBox "Caution:  The value does not match the value in field 1."
    End If
0
Comment
Question by:SteveL13
  • 4
  • 3
  • 2
  • +3
12 Comments
 

Expert Comment

by:jamie77777
ID: 37041322
If not Me.cbofield2 = Me.cbofield1 Then
        MsgBox "Caution:  The value does not match the value in field 1."
    End If
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 37041333
Are both fields the same type of numeric field (long vs integer vs real vs double, etc) ?
0
 
LVL 75
ID: 37041415
Use the Form BeforeUpdate event, otherwise, you will have to test each combo and cover more cases (null, not null, etc)

Private Sub Form_BeforeUpdate (Cancel As Integer)

    If Nz(Me.cbofield2,"")  <> Nz(Me.cbofield1,"") Then
        MsgBox "Caution:  The value does not match the value in field 1."
    End If

End Sub
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37041425
...Watch for misspellings, and upper/lower case issues as well;

Mike Jone/Mike Jones
Issac, 1ssac

William Smith/william smith


With numbers, you may have a value like:
4.12345
But "Format" it to display:
4.12

So when you compare this formatted value, to an actual value of 4.12, then will not be equal, even though they "appear" to be the same...


JeffCoachman
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 37042850
I assume that the fields you are referring are combo boxes. You may check as well the row source of your combo boxes as well as the column bound.

Sincerely,
Ed
0
 

Author Comment

by:SteveL13
ID: 37044317
This is not working and I suspect that it is because the bound column on the combo box is displaying as 00.00 instead of as a full number with no decimals.  How can I display with no decimals?
0
 

Author Comment

by:SteveL13
ID: 37044469
I now have the number displaying as a full number, no decimals but the form is not working correctly.  I get the message box even if the values do match.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37044739
We have covered almost all the bases here...

Obviously we are at the point where you will have to post a sample db that exhibits this issue.

Then I am sure any expert will see the problem immediately...
0
 
LVL 75
ID: 37045817
Steve ... what are some examples of the numbers in each combo - w/o any formatting ?

mx
0
 
LVL 75
ID: 37045867

Private Sub Form_BeforeUpdate (Cancel As Integer)

   dim x,y
   x= Replace(Replace(Nz(Me.cbofield1,0) ,".","") ,",","")
   y= Replace(Replace(Nz(Me.cbofield2,0) ,".","") ,",","")
    If x <> y Then
        MsgBox "Caution:  The values do NOT match - take evasive, preemptive action now."
    End If

End Sub

So, if x = 125.210  and y =125.121  ... they will not match

mx
0
 

Author Comment

by:SteveL13
ID: 37047269
This seems to work.  But now one more issue.  The message box does appear if the two fields don't match.  But I want the form to stay open and set the focus on the field, cbofield2 so the user can correct the mistake.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 37047310
sure ...

Private Sub Form_BeforeUpdate (Cancel As Integer)

   dim x,y
   x= Replace(Replace(Nz(Me.cbofield1,0) ,".","") ,",","")
   y= Replace(Replace(Nz(Me.cbofield2,0) ,".","") ,",","")
    If x <> y Then
        MsgBox "Caution:  The values do NOT match - take evasive, preemptive action now."
       Cancel = True
        Me.CboField2.SetFocus

 End If

End Sub
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question