Record exists in ListBox?

[detrie]

I have a form with a command button btnEditRecord.
Also a listbox lstMyGroups

The listbox (column 1) lists all the groups the user is a member of.


This code will search the listbox(Column 1) for the entered "Group Number. If it exists, a message "You are free to edit this record". this part is fine...

What I need to add is if the "Group Number" is NOT in the listbox I need a message "You Cannot edit this record"

The "Group Number" must match EXACTLY... I'm only able to get this to work with ...If .Column(1, i) LIKE strSearch Then ......

I don't know if this is okay. It seemes to work. If the "Group Number" is 4 it does not recognize 44 or 24 etc.

CODE START

Private Sub btnEditRecord_Click()
Dim strSearch As String
Dim i As Long


strSearch = InputBox("Enter your Group Number")
strSearch = strSearch

With Me.lstMyGroups
If .ListCount > 0 Then
.Selected(0) = True
For i = 0 To .ListCount - 1
If .Column(1, i) Like strSearch Then
.Selected(i) = True

MsgBox "You are free to edit this record"

Exit For
End If

Next i
End If

End With
End Sub

CODE END

I appreciate any help I could get
Detrie

 

[RuralGuy]

Try this code:

Private Sub btnEditRecord_Click()
   Dim strSearch As String
   Dim i As Long

   Dim MyMsg As String
   MyMsg = "You Cannot edit this record"  '-- Default value

   strSearch = InputBox("Enter your Group Number")
   With Me.lstMyGroups
      If .ListCount > 0 Then
         .Selected(0) = True
         For i = 0 To .ListCount - 1
            If .Column(1, i) Like strSearch Then
               .Selected(i) = True
               MyMsg = "You are free to edit this record"
               Exit For
            End If
         Next i
      End If
   End With
   MsgBox MyMsg
End Sub

 

[detrie]

Hi RG
THANKS... that did it!

However, for the sake of the posting, I used "MSG BOXES". I thought I'd be able to look the code and insert the code I actually need. Unfortunately I cant.
I'm sorry, I thought I was being helpful

I need to insert:
If "InputBox" = Me.lstMyGroupsThen
Me.AllowEdits = True

If "InputBox" = Me.lstMyGroups Then
Me.AllowEdits = False

 

[RuralGuy]

You are trying to disallow edits on the current form based on your search?

 

[detrie]

Yes... exaclty...

 

[RuralGuy]

How about:

Private Sub btnEditRecord_Click()
   Dim strSearch As String
   Dim i As Long

   Dim MyMsg As String
   MyMsg = "You Cannot edit this record"  '-- Default value
   [COLOR="Red"]Me.AllowEdits = False    '-- Default to turning off edits[/COLOR]
   strSearch = InputBox("Enter your Group Number")
   With Me.lstMyGroups
      If .ListCount > 0 Then
         .Selected(0) = True
         For i = 0 To .ListCount - 1
            If .Column(1, i) Like strSearch Then
               .Selected(i) = True
               MyMsg = "You are free to edit this record"
               [COLOR="Red"]Me.AllowEdits = True   '-- Turn the Edits back on[/COLOR]
               Exit For
            End If
         Next i
      End If
   End With
   MsgBox MyMsg
End Sub

 

[detrie]

RG.
That worked like a charm.
THANK you for all of your help

Detrie

 

[RuralGuy]

You're very welcome and glad I could help, Detrie.