Access VBA listbox value
Ngày đăng:
27/12/2021
Trả lời:
0
Lượt xem:
62
T tt1611Registered User.Local timeToday, 06:26JoinedJul 17, 2009Messages116
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? Last edited: Aug 25, 2009 boblarsonSmegheadLocal timeToday, 03:26JoinedJan 12, 2001Messages32,068
Is the list box's Multi-Select property set to NONE or One of the multi-select options? T tt1611Registered User.Local timeToday, 06:26JoinedJul 17, 2009Messages116
Hi Bob It's set to none boblarsonSmegheadLocal timeToday, 03:26JoinedJan 12, 2001Messages32,068
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. T tt1611Registered User.Local timeToday, 06:26JoinedJul 17, 2009Messages116
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. boblarsonSmegheadLocal timeToday, 03:26JoinedJan 12, 2001Messages32,068
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 - Code:Copy to clipboard 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 T tt1611Registered User.Local timeToday, 06:26JoinedJul 17, 2009Messages116
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... boblarsonSmegheadLocal timeToday, 03:26JoinedJan 12, 2001Messages32,068
You're missing part of it: If Me.cmbasset.Value = Me.lstloan.ItemData(Me.lstloan.ItemsSelected(i)) Then S ScooterbugRegistered User.Local timeToday, 06:26JoinedMar 27, 2009Messages853
Also: When using a For...Next loop, you need to have both the For and the Next. You neglected to add the Next part. boblarsonSmegheadLocal timeToday, 03:26JoinedJan 12, 2001Messages32,068
Good catch on that - I got part but missed that other part. Good tag team combo boblarsonSmegheadLocal timeToday, 03:26JoinedJan 12, 2001Messages32,068
Sorry, you might need to add a -1 (I think I forgot to add that): For i = 0 To Me.lstloan.ItemsSelected.Count - 1 S ScooterbugRegistered User.Local timeToday, 06:26JoinedMar 27, 2009Messages853
to continue on the tag team path... it should be Next i, not just next boblarsonSmegheadLocal timeToday, 03:26JoinedJan 12, 2001Messages32,068
Although good practice to include the i, NEXT without the i will work. T tt1611Registered User.Local timeToday, 06:26JoinedJul 17, 2009Messages116
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 S ScooterbugRegistered User.Local timeToday, 06:26JoinedMar 27, 2009Messages853
Doh! Guess i'm just use to good practice boblarsonSmegheadLocal timeToday, 03:26JoinedJan 12, 2001Messages32,068
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. T tt1611Registered User.Local timeToday, 06:26JoinedJul 17, 2009Messages116
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 boblarsonSmegheadLocal timeToday, 03:26JoinedJan 12, 2001Messages32,068
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. |