Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

list boxes

  Asked By: Shawn    Date: Dec 21    Category: MS Office    Views: 846
  

I am adding items to a list box. I am getting the items from a query.
Only the last item is available in the list box. The first ones are
gone. When I step through the code, I am accessing all the items from
the query. Any ideas?

Share: 

 

8 Answers Found

 
Answer #1    Answered By: Robin Bailey     Answered On: Dec 21

Dim db As ADODB.Connection
Dim tblRst As New ADODB.Recordset
Set db = CurrentProject.Connection
Set tblRst = New ADODB.Recordset
tblRst.Open "tblMeterEntry", db, adOpenKeyset,
adLockOptimistic, adCmdTableDirect

strBuilding = "[Building]= '" & strBuilding & "'"
strUtility = "[Utility]= '" & strUtility & "'"
strBuilding = strBuilding & strUtility
tblRst.MoveFirst
If tblRst.EOF Or tblRst.BOF Then
Exit Sub
Else

End If
Do Until tblRst.EOF

strMeter = tblRst.Fields("meterNumber")

With lstMeterNo
.RowSource = " "
.RowSourceType = "Value list"
.AddItem strMeter
End With


tblRst.MoveNext
Loop

Set db = Nothing




 
Answer #2    Answered By: Oscar Evans     Answered On: Dec 21

I'm having trouble finding a context in which a listbox would support these
values in RowSource and RowSourceType, so I can't generate a test sheet.
However, I suspect that you don't want to keep resetting these two
properties.

What happens if you set them once, outside the loop, then only do the
AddItem inside the loop?

If that doesn't give any joy, you might like to upload the spreadsheet to
the files section and we'll have a look at it.

 
Answer #3    Answered By: Michael Evans     Answered On: Dec 21

That appears to be the problem.

 
Answer #4    Answered By: Woodrow Jones     Answered On: Dec 21

I got the list  box to load ok now. But when I make a
selection on the list box, I can't trigger the update sub routine
and even with a selection the lstMeterNo.value=Null. Does this have
to do with the RowSource property?

 
Answer #5    Answered By: Iris Sanders     Answered On: Dec 21

I couldn't find a listbox that would take a rowsource
property of the type you had (i.e. a single space). So I don't know your
context. I think you'll need to post a lot more information - certainly all
the relevant code.

 
Answer #6    Answered By: Olga Allen     Answered On: Dec 21

This is the code. The line that it gets stuck on is " strMeter =
lstMeterNo.Value". It shows that lstMeterNo.Value is Null but I
just selected from the MeterNo list  box because it triggered the
subroutine.

Private Sub lstMeterNo_click()

Label14.Visible = True
lblDescription.Visible = True
strMeter = lstMeterNo.Value
strBuilding = lstBuilding.Value
strUtility = lstUtility.Value
Dim db As ADODB.Connection
Dim tblRst As New ADODB.Recordset
Set db = CurrentProject.Connection
Set tblRst = New ADODB.Recordset

tblRst.Open "tblMeterEntry", db, adOpenKeyset,
adLockOptimistic, adCmdTableDirect

tblRst.MoveFirst
If tblRst.EOF Or tblRst.BOF Then
Set db = Nothing
Exit Sub
Else

End If

strMeter = tblRst2.Fields("Description")
lblDescription.Caption = strMeter
Set db2 = Nothing
end sub

 
Answer #7    Answered By: Botan Suzuki     Answered On: Dec 21

Well I can access the value of a list  box with no problem from its click
subroutine.

You absolutely need to post ALL of the relevant code  - not just individual
little subroutines. It is the context that is wrong, not the individual
statements.

My recommendation is that you upload the whole spreadsheet to the files
area.

 
Answer #8    Answered By: Fahmida Ahmed     Answered On: Dec 21


There are two separate problems with you list  box:

- First (and most serious) - you have multi-select set on the list box. You
need to turn multi-select off.

- Second - your box  initially shows as empty. This is because you have a
first value of space, before your other two values. Almost certainly, this
is because you have assigned it an initial value of a single space.

I also notice the statement

Dim strBuilding, strUtility, strString, strMeter, strMeter2 As String

This is incorrect. It defines all but strMeter2 as Variant. You need an
"As String" for each one.

 
Didn't find what you were looking for? Find more on list boxes Or get search suggestion and latest updates.




Tagged: