Populating a List Box

S

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
 
S

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
Click to expand...
 
S

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
Steve said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

newbie needs examples of creating a recordset and inserting datausing it in Access 2007? 6
Macro to call database table needs to process faster 2
Assistance with vbYesNo 3
How do I get every fifth record to print? 4
MSDE 2000 Release A: Run-Time Error '-2147467259 (8000040050)' 1
Custom Class with oledbcommand problem 5
Array problem 2
Connection Problem (Oracle and Access join) 5

Top