Check Listbox value

[tt1611]

Hi again
I am hoping there is a quick easy solution to this.

I have a listbox and a textbox among several controls on my form. I have setup code to automatically populate the textbox but i want the value in the textbox to be checked or validated against values in a listbox and if the entry exists, display a message box and delete the textbox value and setfocus on it for the user to make another selection.

Due to the limited nature of VBA compared to VB i do not have all the options..

An example code would be

if me.textbox.value = me.listbox.item?? then
msgbox("This item already exists")

me.textbox.value = ""
me.textbox.setfocus

else

end if
...

naturally the above code wont work in VBA and thats where I'm stuck. Can anyone help with this?

 

[boblarson]

Is the list box's Multi-Select property set to NONE or One of the multi-select options?

 

[tt1611]

Hi Bob
It's set to none

 

[boblarson]

Then a single select listbox has a value (the multi-select does too but it is always NULL) which you can compare against the text box:

If Me.textbox = Me.listbox ...etc.

 

[tt1611]

Hey Bob
i already tried comparing the Me.listbox.value to me.textbox.value and this hasnt worked both with the listbox multi select property set to simple or extended.

 

[boblarson]

Hey Bob
i already tried comparing the Me.listbox.value to me.textbox.value and this hasnt worked both with the listbox multi select property set to simple or extended.

I had said -

It works if the multi-select is set to NONE. If it is set to simple or extended, you can't use the value of the listbox because it is ALWAYS NULL, even if something is selected. To use with a multi-select listbox you would need to iterate through the selections to see if any selections matched and it would be something like this -

Dim i As Integer

For i = 0 to Me.Listbox.ItemsSelected.Count
   If Me.TextBox = Me.Listbox.ItemData(Me.ItemsSelected(i)) Then
      Do Something Here
   End If
Next i

 

[tt1611]

Thanks for your patience on this Bob. I actually did mean to say i had tried it both with None and Simple.

As of right now, the multi select property is set to simple and i have tried your code like below (forgive the namings - my textbox was previously a combo box)

Private Sub cmbstag_AfterUpdate()
Me.cmbasset.Value = Me.cmbstag.Column(1)
Dim i As Integer
For i = 0 To Me.lstloan.ItemsSelected.Count
If Me.cmbasset.Value = Me.lstloan.ItemData(Me.ItemsSelected(i)) Then
MsgBox ("This laptop is already on loan")
Me.cmbasset.Value = ""
Me.cmbstag.SetFocus
Else
End If
End Sub

The error I am getting when this is now called is 'Compile Error method or datamemeber not found" Error highlights If Me.cmbasset.Value = Me.lstloan.ItemData(Me.ItemsSelected(i)) Then...

 

[boblarson]

You're missing part of it:

If Me.cmbasset.Value = Me.lstloan.ItemData(Me.lstloan.ItemsSelected(i)) Then

 

[Scooterbug]

Also:

When using a For...Next loop, you need to have both the For and the Next. You neglected to add the Next part.

 

[boblarson]

When using a For...Next loop, you need to have both the For and the Next. You neglected to add the Next part.

Good catch on that - I got part but missed that other part. Good tag team combo

 

[Scooterbug]

I missed the part you got

 

[tt1611]

love is in the air...sorry i should have picked up on

(Me.lstloan.ItemsSelected(i))

my code now looks like the below

Private Sub cmbstag_AfterUpdate()
Dim i As Integer
Me.cmbasset.Value = Me.cmbstag.Column(1)
For i = 0 To Me.lstloan.ItemsSelected.Count
If Me.cmbasset.Value = Me.lstloan.ItemData(Me.lstloan.ItemsSelected(i)) Then
MsgBox ("This laptop is already on loan")

Me.cmbasset.Value = ""
Me.cmbstag.SetFocus
Else
End If
Next
End Sub

The message i am now getting is "You referred to a property by a numeric argument thats isnt one of the property numbers in this collection." I am sorry to keep bugging. I will do my best to figure this out on my own but really appreciate the help you have given.

 

[boblarson]

Sorry, you might need to add a -1 (I think I forgot to add that):

For i = 0 To Me.lstloan.ItemsSelected.Count - 1

 

[Scooterbug]

to continue on the tag team path...

it should be Next i, not just next

 

[boblarson]

to continue on the tag team path...

it should be Next i, not just next

Although good practice to include the i, NEXT without the i will work.

 

[tt1611]

Well I give up...The text is updated in the cmbasset text box but does validate or generate any message boxes even with the value in the list box entered.

I have tried this also in the afterupdate event of the "cmbasset" textbox and still no luck. Thank you for the input Scooter. I might just look at another design to ease the validation process. Thank you for your help anyway

 

[Scooterbug]

Doh! Guess i'm just use to good practice My bad!

 

[boblarson]

Well I give up...The text is updated in the cmbasset text box but does validate or generate any message boxes even with the value in the list box entered.

Well, are you sure the combo is returning the values you think it should. Try putting a message box in to display what the combo's value and the listbox value is just before your comparison to see what they actually are.

 

[tt1611]

Try putting a message box in to display what the combo's value and the listbox value is just before your comparison to see what they actually are.

As per your suggestion i ran code like so
Private Sub cmbstag_AfterUpdate()
Me.cmbasset.Value = Me.cmbstag.Column(1)
MsgBox ("This laptop is a test" & " " & Me.cmbasset.Value)
End Sub

Its pulling the correct name everytime...the list box runs off a sql query that is also pulling the correct assetname...so i am completely baffled

 

[boblarson]

As per your suggestion i ran code like so
Private Sub cmbstag_AfterUpdate()
Me.cmbasset.Value = Me.cmbstag.Column(1)
MsgBox ("This laptop is a test" & " " & Me.cmbasset.Value)
End Sub

Its pulling the correct name everytime...the list box runs off a sql query that is also pulling the correct assetname...so i am completely baffled

If you want to compact and repair the database and then zip it and upload it here, we can take a look and see if we can spot something. If it is 2007, if you save it to 2003 format it would make it so I could look while still here at work.