Populating List Box from dynamic query

  • Thread starter Thread starter George Papadopoulos
  • Start date Start date
G

George Papadopoulos

Hello everybody

I have a form with a push-button on it. I need another form to open when I
press that button. The second form contains a list box which is to be
populated by running a programmatic query. Firstly, I show the code I am
using for the on_click handler of the button.

Private Sub List_spares_Click()
On Error GoTo Err_cmdGoHere_Click

Dim stDocName As String

stDocName = "LISTA_ANTALLAKTIKWN"
DoCmd.OpenForm stDocName, OpenArgs:=Me.[Kwdikos_episkeyhs]

Exit_cmdGoHere_Click:
Exit Sub

Err_cmdGoHere_Click:
MsgBox Err.Description
Resume Exit_cmdGoHere_Click

End Sub

The code opens the second form and passes a parameter to the second form.
The after_update handler for the second form is shown below :

Private Sub Form_Load()

Dim dbEPEMBATHS As Database
Dim rsSpares As Recordset
Dim Kwdikos As Long
Dim objvar As Object

If Not IsNull(Me.OpenArgs) Then
Kwdikos = Me.OpenArgs
End If

' Set dbLib to the current database
Set dbEPEMBATHS = CurrentDb

' Create a query
strselect = "Select * FROM ANTALLAKTIKA WHERE 'Kwdikos_episkeyhs =
Kwdikos'"

Set rsSpares = dbEPEMBATHS.OpenRecordSet(strselect)

For Each objvar In rsSpares
[Forms].[LISTA_ANTALLAKTIKWN].[Spares_List].AddItem objvar
Next

End Sub

When I run the code I get the error 'The function is not supported by this
type of object'. I believe the problematic line is the one where I add new
items.

The general idea is that I want to add each row of the selected recordset
to the listbox. What am i missing?

Thanks in advance

George Papadopoulos
 
Hi George

You don't need to add the items from a recordset individually to the
listbox. Just set the RowSourceType to "Table/Query" and set the RowSource
to a SQL statement.

Also, you need to get the syntax correct for the WHERE clause in your SQL
statement:
Instead of:
"...WHERE 'Kwdikos_episkeyhs = Kwdikos'"
you need to substitute the actual value of Kwdikos:
"...WHERE Kwdikos_episkeyhs = " & Kwdikos
or, if Kwdikos_episkeyhs is a text field, include quotes:
"...WHERE Kwdikos_episkeyhs = '" & Kwdikos & "'"

So then, after the "strselect = " line, all you need is:
[Spares_list].RowSource = strselect
 
ok. thx. It worked. Now I only need the first row of the list box to display
a title for each column. How can I do that?

Ï "Graham Mandeno said:
Hi George

You don't need to add the items from a recordset individually to the
listbox. Just set the RowSourceType to "Table/Query" and set the RowSource
to a SQL statement.

Also, you need to get the syntax correct for the WHERE clause in your SQL
statement:
Instead of:
"...WHERE 'Kwdikos_episkeyhs = Kwdikos'"
you need to substitute the actual value of Kwdikos:
"...WHERE Kwdikos_episkeyhs = " & Kwdikos
or, if Kwdikos_episkeyhs is a text field, include quotes:
"...WHERE Kwdikos_episkeyhs = '" & Kwdikos & "'"

So then, after the "strselect = " line, all you need is:
[Spares_list].RowSource = strselect

--
Good Luck!

Graham Mandeno [Access MVP]
New Zealand - Home of Lord of the Rings :-)

George Papadopoulos said:
Hello everybody

I have a form with a push-button on it. I need another form to open when I
press that button. The second form contains a list box which is to be
populated by running a programmatic query. Firstly, I show the code I am
using for the on_click handler of the button.

Private Sub List_spares_Click()
On Error GoTo Err_cmdGoHere_Click

Dim stDocName As String

stDocName = "LISTA_ANTALLAKTIKWN"
DoCmd.OpenForm stDocName, OpenArgs:=Me.[Kwdikos_episkeyhs]

Exit_cmdGoHere_Click:
Exit Sub

Err_cmdGoHere_Click:
MsgBox Err.Description
Resume Exit_cmdGoHere_Click

End Sub

The code opens the second form and passes a parameter to the second form.
The after_update handler for the second form is shown below :

Private Sub Form_Load()

Dim dbEPEMBATHS As Database
Dim rsSpares As Recordset
Dim Kwdikos As Long
Dim objvar As Object

If Not IsNull(Me.OpenArgs) Then
Kwdikos = Me.OpenArgs
End If

' Set dbLib to the current database
Set dbEPEMBATHS = CurrentDb

' Create a query
strselect = "Select * FROM ANTALLAKTIKA WHERE 'Kwdikos_episkeyhs =
Kwdikos'"

Set rsSpares = dbEPEMBATHS.OpenRecordSet(strselect)

For Each objvar In rsSpares
[Forms].[LISTA_ANTALLAKTIKWN].[Spares_List].AddItem objvar
Next

End Sub

When I run the code I get the error 'The function is not supported by this
type of object'. I believe the problematic line is the one where I add new
items.

The general idea is that I want to add each row of the selected recordset
to the listbox. What am i missing?

Thanks in advance

George Papadopoulos
Click to expand...
Click to expand...
 
Another thing, I did not notice due to the joy of moment. The code works,
but returns only the first field of my record. How can I get to have all my
fields displayed.

George Papadopoulos

Ï "Graham Mandeno said:
Hi George

You don't need to add the items from a recordset individually to the
listbox. Just set the RowSourceType to "Table/Query" and set the RowSource
to a SQL statement.

Also, you need to get the syntax correct for the WHERE clause in your SQL
statement:
Instead of:
"...WHERE 'Kwdikos_episkeyhs = Kwdikos'"
you need to substitute the actual value of Kwdikos:
"...WHERE Kwdikos_episkeyhs = " & Kwdikos
or, if Kwdikos_episkeyhs is a text field, include quotes:
"...WHERE Kwdikos_episkeyhs = '" & Kwdikos & "'"

So then, after the "strselect = " line, all you need is:
[Spares_list].RowSource = strselect

--
Good Luck!

Graham Mandeno [Access MVP]
New Zealand - Home of Lord of the Rings :-)

George Papadopoulos said:
Hello everybody

I have a form with a push-button on it. I need another form to open when I
press that button. The second form contains a list box which is to be
populated by running a programmatic query. Firstly, I show the code I am
using for the on_click handler of the button.

Private Sub List_spares_Click()
On Error GoTo Err_cmdGoHere_Click

Dim stDocName As String

stDocName = "LISTA_ANTALLAKTIKWN"
DoCmd.OpenForm stDocName, OpenArgs:=Me.[Kwdikos_episkeyhs]

Exit_cmdGoHere_Click:
Exit Sub

Err_cmdGoHere_Click:
MsgBox Err.Description
Resume Exit_cmdGoHere_Click

End Sub

The code opens the second form and passes a parameter to the second form.
The after_update handler for the second form is shown below :

Private Sub Form_Load()

Dim dbEPEMBATHS As Database
Dim rsSpares As Recordset
Dim Kwdikos As Long
Dim objvar As Object

If Not IsNull(Me.OpenArgs) Then
Kwdikos = Me.OpenArgs
End If

' Set dbLib to the current database
Set dbEPEMBATHS = CurrentDb

' Create a query
strselect = "Select * FROM ANTALLAKTIKA WHERE 'Kwdikos_episkeyhs =
Kwdikos'"

Set rsSpares = dbEPEMBATHS.OpenRecordSet(strselect)

For Each objvar In rsSpares
[Forms].[LISTA_ANTALLAKTIKWN].[Spares_List].AddItem objvar
Next

End Sub

When I run the code I get the error 'The function is not supported by this
type of object'. I believe the problematic line is the one where I add new
items.

The general idea is that I want to add each row of the selected recordset
to the listbox. What am i missing?

Thanks in advance

George Papadopoulos
Click to expand...
Click to expand...
 
Hi George

George Papadopoulos said:
ok. thx. It worked. Now I only need the first row of the list box to display
a title for each column. How can I do that?
Click to expand...

Set the ColumnHeads property of the listbox to True (Yes).
 
Hello again

George Papadopoulos said:
Another thing, I did not notice due to the joy of moment. The code works,
but returns only the first field of my record. How can I get to have all my
fields displayed.
Click to expand...

Set the ColumnCount property to the number of fields from your RowSource you
want to display.

You can use the ColumnWidths property to specify the widths of the columns
(or even to hide columns by giving a width of zero).
 
thx, for every bit of your advice.

George Papadopoulos


Ï "Graham Mandeno said:
Hi George

You don't need to add the items from a recordset individually to the
listbox. Just set the RowSourceType to "Table/Query" and set the RowSource
to a SQL statement.

Also, you need to get the syntax correct for the WHERE clause in your SQL
statement:
Instead of:
"...WHERE 'Kwdikos_episkeyhs = Kwdikos'"
you need to substitute the actual value of Kwdikos:
"...WHERE Kwdikos_episkeyhs = " & Kwdikos
or, if Kwdikos_episkeyhs is a text field, include quotes:
"...WHERE Kwdikos_episkeyhs = '" & Kwdikos & "'"

So then, after the "strselect = " line, all you need is:
[Spares_list].RowSource = strselect

--
Good Luck!

Graham Mandeno [Access MVP]
New Zealand - Home of Lord of the Rings :-)

George Papadopoulos said:
Hello everybody

I have a form with a push-button on it. I need another form to open when I
press that button. The second form contains a list box which is to be
populated by running a programmatic query. Firstly, I show the code I am
using for the on_click handler of the button.

Private Sub List_spares_Click()
On Error GoTo Err_cmdGoHere_Click

Dim stDocName As String

stDocName = "LISTA_ANTALLAKTIKWN"
DoCmd.OpenForm stDocName, OpenArgs:=Me.[Kwdikos_episkeyhs]

Exit_cmdGoHere_Click:
Exit Sub

Err_cmdGoHere_Click:
MsgBox Err.Description
Resume Exit_cmdGoHere_Click

End Sub

The code opens the second form and passes a parameter to the second form.
The after_update handler for the second form is shown below :

Private Sub Form_Load()

Dim dbEPEMBATHS As Database
Dim rsSpares As Recordset
Dim Kwdikos As Long
Dim objvar As Object

If Not IsNull(Me.OpenArgs) Then
Kwdikos = Me.OpenArgs
End If

' Set dbLib to the current database
Set dbEPEMBATHS = CurrentDb

' Create a query
strselect = "Select * FROM ANTALLAKTIKA WHERE 'Kwdikos_episkeyhs =
Kwdikos'"

Set rsSpares = dbEPEMBATHS.OpenRecordSet(strselect)

For Each objvar In rsSpares
[Forms].[LISTA_ANTALLAKTIKWN].[Spares_List].AddItem objvar
Next

End Sub

When I run the code I get the error 'The function is not supported by this
type of object'. I believe the problematic line is the one where I add new
items.

The general idea is that I want to add each row of the selected recordset
to the listbox. What am i missing?

Thanks in advance

George Papadopoulos
Click to expand...
Click to expand...
 
Back
Top