Populating List Box from dynamic query

  • Thread starter George Papadopoulos
  • 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
 
G

Graham Mandeno

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
 
G

George Papadopoulos

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...
 
G

George Papadopoulos

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...
 
G

Graham Mandeno

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).
 
G

Graham Mandeno

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).
 
G

George Papadopoulos

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...
 

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

Dynamic Query creation 3
Expressin an SQL statement under DAO 2
Syntax Error In DAO Code 4
Using Database Access Objects (DAO) 4
Adding a new record by popping up a new form 1
NULL records problem 2
updating bounded text box in pop-up form 2
Accessing fields of record using DAO 3

Top