Populating a List Box

[s_wadhwa]

Hi,

I'm populating the listbox with query having a where condition in VBA
code. the first time list box fills correctly but when I change the
WHERE condition which is coming from the combo box in the MS Access
Form it doesn not clear the list box values from the query done first
time and appends the resultset query in the listbox.
Please anyone can suggest me how to clear the previous recordset values
and fill with new recordset values.
I hope the question is clear. I am also appending the code I have
written for it.
Any suggestions are welcome.
Thanks,
Shalini

------------------------------------------------------------------------------------------------
' Variable Declaration

Dim cnn1 As ADODB.Connection
Dim rstDept, rstFloor, rstRoomUseCode As ADODB.Recordset
Dim i, j, k As Integer

' Set Connection to Current Project

Set cnn1 = CurrentProject.Connection

' Set values for Department, Floor and RoomUseCode data based on
BuildingNumber

Set rstDept = New ADODB.Recordset
Set rstFloor = New ADODB.Recordset
Set rstRoomUseCode = New ADODB.Recordset

rstDept.Open "SELECT departmentcode,abbrev FROM
vFDXQryDepartmentFilterCboValue WHERE ((BuildingNumber='" +
[Forms]![frmrooms]![BuildingNumber] + "')) ORDER BY Abbrev;", cnn1

rstFloor.Open "SELECT DISTINCT floor FROM vFDXQryCtlFloorCboValue WHERE
((BuildingNumber='" + [Form].[BuildingNumber] + "')) ORDER BY Floor; ",
cnn1

rstRoomUseCode.Open "SELECT DISTINCT RoomUseCode FROM
vFDXQryRoomsCboValue WHERE ((BuildingNumber='" +
[Form].[BuildingNumber] + "')) ORDER BY RoomUseCode; ", cnn1

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'Fill the DepartmentFilter Listbox with values from recordset
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If rstDept.RecordCount <> 0 Then

rstDept.MoveFirst
i = 1
Me.DepartmentFilter.AddItem "*", 0
Do While Not rstDept.EOF
Me.DepartmentFilter.AddItem rstDept!DepartmentCode + " - " +
rstDept!abbrev, i
rstDept.MoveNext
i = i + 1
Loop
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'Fill the Floor Listbox with values from recordset
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If rstFloor.RecordCount <> 0 Then
rstFloor.MoveFirst
j = 1
Me.ctlFloor.AddItem "*", 0
Do While Not rstFloor.EOF
Me.ctlFloor.AddItem rstFloor!Floor, j
rstFloor.MoveNext
j = j + 1
Loop
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'Fill the RoomUseCode Listbox with values from recordset
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If rstRoomUseCode.RecordCount <> 0 Then
rstRoomUseCode.MoveFirst
k = 1
Me.RmCdFilter.AddItem "*", 0
Do While Not rstRoomUseCode.EOF
Me.RmCdFilter.AddItem rstRoomUseCode!RoomUseCode, k
rstRoomUseCode.MoveNext
k = k + 1
Loop
End If

' Close the Recordset and Connection

rstDept.Close
rstFloor.Close
rstRoomUseCode.Close
cnn1.Close

' Release the memory

Set rstDept = Nothing
Set rstFloor = Nothing
Set rstRoomUseCode = Nothing
Set cnn1 = Nothing

 

[Steve Schapel]

Shalini,

The focus of this newsgroup is n macros in Access. Your quesion is
related to VBA procedure.

Anyway, since you're here...

A brief comment if I may... the syntax:
Dim rstDept, rstFloor, rstRoomUseCode As ADODB.Recordset
.... is not good. rstRoomUseCode is declared as a recordset object, but
rstDept and rstFloor are declared as Variant. You need to type each
one. Similarly, with:
Dim i, j, k As Integer
.... only k is declared as an integer.

Another comment... you are using a + instead of a & as a concatenation
character. This is not equivalent, and I suggest you change this.

I am not familiar with the approach you have taken. I would do it like
this...

With Me.DepartmentFilter
.RowSource = "SELECT departmentcode & " - " & abbrev" & _
" FROM vFDXQryDepartmentFilterCboValue" & _
" WHERE ((BuildingNumber='" & Me.BuildingNumber &
"'))" & _
" ORDER BY Abbrev"
.Requery
End With

Same idea for the other 2 listboxes. That's it.

--
Steve Schapel, Microsoft Access MVP

Hi,

I'm populating the listbox with query having a where condition in VBA
code. the first time list box fills correctly but when I change the
WHERE condition which is coming from the combo box in the MS Access
Form it doesn not clear the list box values from the query done first
time and appends the resultset query in the listbox.
Please anyone can suggest me how to clear the previous recordset values
and fill with new recordset values.
I hope the question is clear. I am also appending the code I have
written for it.
Any suggestions are welcome.
Thanks,
Shalini

------------------------------------------------------------------------------------------------
' Variable Declaration

Dim cnn1 As ADODB.Connection
Dim rstDept, rstFloor, rstRoomUseCode As ADODB.Recordset
Dim i, j, k As Integer

' Set Connection to Current Project

Set cnn1 = CurrentProject.Connection

' Set values for Department, Floor and RoomUseCode data based on
BuildingNumber

Set rstDept = New ADODB.Recordset
Set rstFloor = New ADODB.Recordset
Set rstRoomUseCode = New ADODB.Recordset

rstDept.Open "SELECT departmentcode,abbrev FROM
vFDXQryDepartmentFilterCboValue WHERE ((BuildingNumber='" +
[Forms]![frmrooms]![BuildingNumber] + "')) ORDER BY Abbrev;", cnn1

rstFloor.Open "SELECT DISTINCT floor FROM vFDXQryCtlFloorCboValue WHERE
((BuildingNumber='" + [Form].[BuildingNumber] + "')) ORDER BY Floor; ",
cnn1

rstRoomUseCode.Open "SELECT DISTINCT RoomUseCode FROM
vFDXQryRoomsCboValue WHERE ((BuildingNumber='" +
[Form].[BuildingNumber] + "')) ORDER BY RoomUseCode; ", cnn1

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'Fill the DepartmentFilter Listbox with values from recordset
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If rstDept.RecordCount <> 0 Then

rstDept.MoveFirst
i = 1
Me.DepartmentFilter.AddItem "*", 0
Do While Not rstDept.EOF
Me.DepartmentFilter.AddItem rstDept!DepartmentCode + " - " +
rstDept!abbrev, i
rstDept.MoveNext
i = i + 1
Loop
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'Fill the Floor Listbox with values from recordset
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If rstFloor.RecordCount <> 0 Then
rstFloor.MoveFirst
j = 1
Me.ctlFloor.AddItem "*", 0
Do While Not rstFloor.EOF
Me.ctlFloor.AddItem rstFloor!Floor, j
rstFloor.MoveNext
j = j + 1
Loop
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'Fill the RoomUseCode Listbox with values from recordset
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If rstRoomUseCode.RecordCount <> 0 Then
rstRoomUseCode.MoveFirst
k = 1
Me.RmCdFilter.AddItem "*", 0
Do While Not rstRoomUseCode.EOF
Me.RmCdFilter.AddItem rstRoomUseCode!RoomUseCode, k
rstRoomUseCode.MoveNext
k = k + 1
Loop
End If

' Close the Recordset and Connection

rstDept.Close
rstFloor.Close
rstRoomUseCode.Close
cnn1.Close

' Release the memory

Set rstDept = Nothing
Set rstFloor = Nothing
Set rstRoomUseCode = Nothing
Set cnn1 = Nothing

 

[s_wadhwa]

Thanks for your suggestions Steve,
I'm new to VBA programming. I think I have mixed SQL, VB and stuff...
I'll modify the code as per suggestions. Sorry for posting this mail in
macros group. I thought its a general category.

thanks for help,
Shalini

Shalini,

The focus of this newsgroup is n macros in Access. Your quesion is
related to VBA procedure.

Anyway, since you're here...

A brief comment if I may... the syntax:
Dim rstDept, rstFloor, rstRoomUseCode As ADODB.Recordset
... is not good. rstRoomUseCode is declared as a recordset object, but
rstDept and rstFloor are declared as Variant. You need to type each
one. Similarly, with:
Dim i, j, k As Integer
... only k is declared as an integer.

Another comment... you are using a + instead of a & as a concatenation
character. This is not equivalent, and I suggest you change this.

I am not familiar with the approach you have taken. I would do it like
this...

With Me.DepartmentFilter
.RowSource = "SELECT departmentcode & " - " & abbrev" & _
" FROM vFDXQryDepartmentFilterCboValue" & _
" WHERE ((BuildingNumber='" & Me.BuildingNumber &
"'))" & _
" ORDER BY Abbrev"
.Requery
End With

Same idea for the other 2 listboxes. That's it.

--
Steve Schapel, Microsoft Access MVP

Hi,

I'm populating the listbox with query having a where condition in VBA
code. the first time list box fills correctly but when I change the
WHERE condition which is coming from the combo box in the MS Access
Form it doesn not clear the list box values from the query done first
time and appends the resultset query in the listbox.
Please anyone can suggest me how to clear the previous recordset values
and fill with new recordset values.
I hope the question is clear. I am also appending the code I have
written for it.
Any suggestions are welcome.
Thanks,
Shalini

------------------------------------------------------------------------------------------------
' Variable Declaration

Dim cnn1 As ADODB.Connection
Dim rstDept, rstFloor, rstRoomUseCode As ADODB.Recordset
Dim i, j, k As Integer

' Set Connection to Current Project

Set cnn1 = CurrentProject.Connection

' Set values for Department, Floor and RoomUseCode data based on
BuildingNumber

Set rstDept = New ADODB.Recordset
Set rstFloor = New ADODB.Recordset
Set rstRoomUseCode = New ADODB.Recordset

rstDept.Open "SELECT departmentcode,abbrev FROM
vFDXQryDepartmentFilterCboValue WHERE ((BuildingNumber='" +
[Forms]![frmrooms]![BuildingNumber] + "')) ORDER BY Abbrev;", cnn1

rstFloor.Open "SELECT DISTINCT floor FROM vFDXQryCtlFloorCboValue WHERE
((BuildingNumber='" + [Form].[BuildingNumber] + "')) ORDER BY Floor; ",
cnn1

rstRoomUseCode.Open "SELECT DISTINCT RoomUseCode FROM
vFDXQryRoomsCboValue WHERE ((BuildingNumber='" +
[Form].[BuildingNumber] + "')) ORDER BY RoomUseCode; ", cnn1

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'Fill the DepartmentFilter Listbox with values from recordset
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If rstDept.RecordCount <> 0 Then

rstDept.MoveFirst
i = 1
Me.DepartmentFilter.AddItem "*", 0
Do While Not rstDept.EOF
Me.DepartmentFilter.AddItem rstDept!DepartmentCode + " - " +
rstDept!abbrev, i
rstDept.MoveNext
i = i + 1
Loop
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'Fill the Floor Listbox with values from recordset
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If rstFloor.RecordCount <> 0 Then
rstFloor.MoveFirst
j = 1
Me.ctlFloor.AddItem "*", 0
Do While Not rstFloor.EOF
Me.ctlFloor.AddItem rstFloor!Floor, j
rstFloor.MoveNext
j = j + 1
Loop
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'Fill the RoomUseCode Listbox with values from recordset
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If rstRoomUseCode.RecordCount <> 0 Then
rstRoomUseCode.MoveFirst
k = 1
Me.RmCdFilter.AddItem "*", 0
Do While Not rstRoomUseCode.EOF
Me.RmCdFilter.AddItem rstRoomUseCode!RoomUseCode, k
rstRoomUseCode.MoveNext
k = k + 1
Loop
End If

' Close the Recordset and Connection

rstDept.Close
rstFloor.Close
rstRoomUseCode.Close
cnn1.Close

' Release the memory

Set rstDept = Nothing
Set rstFloor = Nothing
Set rstRoomUseCode = Nothing
Set cnn1 = Nothing