Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Trying to work out an IF statement to check for matching values

    I need to create an IF statement to check whether the values of two fields match. I have a value in a listbox, list21 column(2) and a value in a text field (groupID).

    If the values match then allow code to run, if values are different (<>?), then cancel the event and display a message box. I have tried the following code, but Im having trouble working out the exact syntax -

    Code:
    If Me.groupID <> Forms![visits_frm]!List21.Column(2) Then
    MsgBox "The group you have selected from the listbox does not match the group in the current visit"
    DoCmd.CancelEvent
    Last edited by moss2076; 09-23-10 at 17:43. Reason: Re-worded my question

  2. #2
    Join Date
    Aug 2004
    Posts
    364
    It works with this code -
    Code:
    Me.groupID.SetFocus
      If Me.List21.Column(2) = Me.groupID.Text Then...

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    That's because you're using the Text Property in Me.groupID.Text, which requires the control in question to have Focus. For this reason Text is seldom used in Access VBA, unlike in straight VB.

    Instead you should be using the Value Property

    Me.groupID.Value

    which doesn't require that the control have focus. And since Value is the Default Property for a Textbox or Combobox, you can drop the .Value and simply use

    Me.groupID

    In your initial code I think the problem was the use of the square brackets around the form name in

    Forms![visits_frm]!List21.Column(2)

    which makes Access assume that visits_frm is a field, not a form. Using Me.List21.Column(2) is really the easiest/shortest way to reference the current form in code.

    Linq ;0)>
    Last edited by Missinglinq; 09-24-10 at 09:30.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Aug 2004
    Posts
    364
    I've tried yout suggestions, and included them in the code shown below, but the message box appears even if the value of the textbox "groupID" and list21.column(2) display the same number or not..below is the entire code from the command button which triggers the code..
    Code:
    ' save the record if needed
    If Me.Dirty Then Me.Dirty = False
    
     Dim strSQL        As String
      Dim db            As DAO.Database
      Dim rs            As DAO.Recordset
      Dim ctl           As Control
      Dim varItem       As Variant
      Set db = CurrentDb()
      Set rs = db.OpenRecordset("tblLinkUserVisits", dbOpenDynaset, dbAppendOnly)
      
      'make sure a selection has been made
      If Forms![visits_frm]!List21.ItemsSelected.Count = 0 Then
        MsgBox "Must select at least 1 user!"
        Exit Sub
        End If
    
    If Me.groupID <> Me.List21.Column(2) Then
    MsgBox "Groups dont match"
    DoCmd.CancelEvent
    Else
    If Me.groupID = Me.List21.Column(2) Then
      'add selected value(s) to table
      Set ctl = Forms![visits_frm]!List21
      For Each varItem In ctl.ItemsSelected
        rs.AddNew
       
         rs!userID = ctl.ItemData(varItem)
         rs!visitID = Forms![visits_frm]![visitID]
         rs.Update
      Next varItem
      
      Me.List71.Requery
      Me.List2361.Requery
      
    End If
    End If
    
      Exit Sub
    
    Err_Command99_Click:
        If Err.Number = 3022 Then
        MsgBox "Sorry The UserID which you have entered is already in use with the Visit, please enter a different UserID before saving"
        
        Else
        MsgBox Err.Number & " " & Err.Description
        End If
    Last edited by moss2076; 09-25-10 at 06:15.

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    The only way I have managed to get this to work is to use the code from my second post -
    Code:
    me.groupID.setfocus
    
    If Me.groupID.Text = Me.List21.Column(2) Then
    MsgBox "Groups Do Match!"
    
    If Me.groupID.Text <> Me.List21.Column(2) Then
    MsgBox "Groups dont match"
    DoCmd.CancelEvent
    If I try the other suggested code, the "groups dont match message" appears regardless of wether there is a match or not.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •