Check Listbox value

[Scooterbug]

Well, I hate to see ppl give up....so I did a quick test. I setup a listbox on a form. I used a value list with the following data: Test One, Test Two, Test Three, Test Four. I then put in a text box. I added the following code to the before update of the text box:

Dim i As Integer
'Me.cmbasset.Value = Me.cmbstag.Column(1)
Debug.Print Me.List115.ItemsSelected.Count

For i = 0 To Me.List115.ItemsSelected.Count - 1
  If Me.Text117 = Me.List115.ItemData(Me.List115.ItemsSelected(i)) Then
    MsgBox ("This laptop is already on loan")
    Me.Text117.Value = ""
    Me.Text117.SetFocus
   Else
  End If
Next

Now, if the Value list is just the text, i always equals 0 using the code above. Dunno why. I'm sure Bob knows. If I use some kind of numeric ID number, i equals the correct number of entries. However, I am assuming that the text box that you are comparing to isn't a number, it's text.

If that is the case, you are comparing Apples to Oranges. I am guessing that the list box is bound to a table or query where the bound column is the ID number. Since the Text box you are trying to validate contains text and the listbox's bound column is an ID, they will never match.

Without seeing your database...my assessment may be wrong...so take with a grain of salt.

Your list box's bound

 

[tt1611]

Not sure if that will work as i have some queries in here running of a SQL server.

I will see if i can export the tables into a new database and the form in question..Bob you rock men..Thanks for even trying to look into this for me...i'll get back in a sec

 

[boblarson]

Yeah, sorry - not quite enough. The combo box is missing its row source so it can't work right and then the listbox doesn't have it's row source either. If you want to "import" a small subset of records (and change any sensitive data) into a separate database file and upload, I can then link to it to use.

Need the dbo_Workstation table and the On Loan table.

 

[tt1611]

Yeah, sorry - not quite enough. The combo box is missing its row source so it can't work right and then the listbox doesn't have it's row source either. If you want to "import" a small subset of records (and change any sensitive data) into a separate database file and upload, I can then link to it to use.

Need the dbo_Workstation table and the On Loan table.

thought as much. I'll get that round for you when i get into work tomorrow

Thank you Bob

 

[boblarson]

Okay - have a good evening

 

[tt1611]

Hey Scooter
Thanks for the code. I'll implement the code and test.

Thanks again for the help.

 

[Scooterbug]

Hey Scooter
Thanks for the code. I'll implement the code and test.

Thanks again for the help.

Well, the code I posted is the same as what was posted before...I just changed control names.

After playing with it some more this morning, I did get it to work with just text and no ID number. (I didn't have any of the list items selected). I got it working. So, a few things to check.

The code only counts the items from the list box if they are selected. If they aren't, then they are skipped. Are you looking to compare what is in the text box (Me.cmbasset on your form) against all the items in the list box? If so, the code only works if those items are selected.

If you want to compare whats in the text box to a set list of items without having them selected, you are going to have to modify the code. You can open a recordset that has what you want to compare to, loop through the records comparing the text box to the field you want to compare them to and see if they match. Something like this (air code):

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim stCompare as string
 
Set db = Currentdb
Set rs as db.OpenRecordset("TableA")
 
With rs
    .MoveFirst
  Do
    Do until .EOF
       stCompare = ![FieldNameFromTable]
         if stCompare = Me.cmbasset then
           MsgBox ("This laptop is already on loan")
            'Whatever else you want to do
           Exit Do
         Else
            .MoveNext
            stCompare = ![FieldNameFromTable]
         End If
    Loop
Loop Until .EOF
 
.Close
 
End With
 
Set db = Nothing
Set rs = Nothing

 

[tt1611]

Hey Guys
Sorry i wasnt able to get back yesterday..The nature of work. I have created a very simple database and form that demonstrates what i am trying to do.

The user will enter text in the text box and if the entry exists in the listbox, a message box should popup saying ("This value is already in the list") else a message box should appear saying ("The value in the textbox is " & " " & Me.txttest.Value)

Again using the method that iterates through the list box searching for that value doesnt seemt to be working.

I have included this code in my on click event for the command buttion. The multi select listbox property is set to Extended.

[COLOR=black][FONT=Verdana]Dim i As Integer[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]For i = 0 To Me.lsttest.ItemsSelected.Count - 1[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]If Me.lsttest.ItemData(Me.lsttest.ItemsSelected(i)) = Me.txttest.Value Then[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]MsgBox ("This value is already in the list")[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]Else[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]MsgBox ("The value in the textbox is " & " " & Me.txttest.Value)[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End If[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Next i[/COLOR][/FONT]

 

[Scooterbug]

Got it working. My previous post confirmed what I suspected. What your code was doing was counting up the selected entries in your list box. Since none of them were selected when you clicked your button, there were no items to compare them too.

I added a button with code that loops through the entries in the table that populates the list box. As it does, it compares what is in the text box to each field in the table. If it finds a match, a message box will appear. If not, it goes to the next record. It continues to do so until it gets to the end of the table.

 

[tt1611]

Scooter...You're the man..This is exactly what i needed. I am going to try and implement this into my main database..I appreciate your help in this. I am kinds new to VBA so may need to go over some tutorial on loops and listbox iterations. But nice idea to compare details to the table as supposed to the listbox..Didnt think about that idea.

 

[tt1611]

Scooter...Quick Question...I'm testing now but the issue i am thinking may come up is that

stCompare = ![FieldNameFromTable]

I am comparing against a query..ie Select field from FieldNameFromTable Where condition is true.

Doesnt the above line of code just compare of a whole field as supposed to a query that populates the listbox?

 

[tt1611]

You know...never mind my previous question...I tested it and it works like a charm. Truly appreciated Scooter...and also thanks Bob for looking into for me the other day. Well Done to you.

 

[Scooterbug]

Glad you got everything worked out....I'll answer your other question, just so you have the answer if you need it down the road...

Queries and table, 99% of the time, are interchangeable when it comes to getting data. The line:

stCompare =![FieldName]

sets the variable, stCompare, to the value of the FieldName for the record it's currently in. So in the code, as it goes through the record, it will set the value, compare it to whats in the text box. If it matches, the message box will show up. If not, it will move to the next record and start the process all over again.

As for using a recordset in code to edit, yes you can. You would need to add a few more commands however. First, you have to .Edit. This tells access that you want to change one or more values for that record. Then you use .Fields("FieldName") = NewValue. Then you have to tell Access to .Update. So it would look like this:

With rs
     .Edit
     .Fields("Field1") = me.Text1
     .Fields("Field2") = me.Text2
     .Update
     .Close
End With

 

[tt1611]

Ok Scooter. Again thanks a mil for all your support men..