Help - populating a Listbox from a query with parameters?

[bensonince]

Hi,

Refernce information: An access 2003 database, all work is internal to the project in access.

I have created a query that is a simple select statement with one parameter. I would like to be able to populate my listbox with the information the query returns, How do i call the query (with a parameter).

I can set the rowsource to the query but this doesn't allow me to pass through a parameter.

HELP! please?

Cheers,

 

[bensonince]

Furhter information

Hi,

I have managed to get this far as it would appear addeding a parameter to a query makes things really difficult (very strange), any way this is what i have.

Dim db As Database
Dim qd As QueryDef
Dim LstEvent As Recordset

Set db = CurrentDb
Set qd = db.QueryDefs("BugsEvents")
qd.Parameters![BugID] = lstBugList.Value
Set LstEvent = qd.OpenRecordset(dbOpenSnapshot)

However the Last line gives a type mismatch. Any idea's?

PS the query works if manually run.

 

[BCullenward]

in the properties of the listbox, can't you do your query in the RowSource property?

 

[ScottGem]

I have no issue with this. I will use the following as a RowSource:

SELECT field1 FROM table WHERE field2 = Forms!formname!controlname;

This will list Field1 for all records where Field2 is equal to the value currently in the referenced control onthe referenced form. No code used at all unless you count the SQL.

 

[bensonince]

Hi,


Rowsource can only excpe the Query without the parameters. I don't won't to write the SQL directly into the forms, All the SQL should be handled by the database so it can be transferable in the future.

Cheers,

 

[ScottGem]

Hi,


Rowsource can only excpe the Query without the parameters. I don't won't to write the SQL directly into the forms, All the SQL should be handled by the database so it can be transferable in the future.

Cheers,

Huh? None of that makes any sense. What was excpe suposed to be? If you are using a combobox you have to include a RowSource. You can make that RowSource a parameter based query or build the query behind the combo. Makes no difference.

 

[workmad3]

Hi,


Rowsource can only excpe the Query without the parameters. I don't won't to write the SQL directly into the forms, All the SQL should be handled by the database so it can be transferable in the future.

Cheers,

and transferable to what exactly? if you mean to a different db system, then you will need to rewrite the SQL anyway, if you mean to a different access system, then it will be fine where ever the SQL is. If you want SQL that accepts parameters, the best solution is to set row source in the forms, or set querydefs in forms, anything else is just going to get horribly muddled, complicated and make any kind of transfer harder as its less likely to be compatible with anything else

 

[bensonince]

Hi,

Your correct, my statement didn't make much sense. sorry.

I'll try again.

I have a select query in my access DB.

If i set the rowsource to that query it will populate the Listbox perfectly.
How ever i have a parameter that needs to be passed to my query which seems to mean that the whole thing becomes really complex.

I have got some information into the listbox by doing this.

    'Sets Up the LstEvent Listbox with the events logs for the selected Bug
    'Defnes the variables to be used
    Dim db As Database
    Dim qd As QueryDef
    Dim LstEvent As dao.Recordset
    Dim Strlist As String
    
    'attaches to the database query and updates the LstEvent Variable with the records
    Set db = CurrentDb
    Set qd = db.QueryDefs("BugsEvents")
    qd.Parameters![BugID] = lstBugList.Value
    Set LstEvent = qd.OpenRecordset()
    
    'Sets up the titles of the ListBox LstEventList
    Strlist = "DateAdded';'Event Information';'Added By"
    'modifies the string StrList to contain all the records using ; to seperate each field and places this information into the rowsource of the listbox
    With LstEvent
        Do While Not .EOF
            Strlist = Strlist & "';'" & LstEvent(0) & "';'" & LstEvent(1) & "';'" & LstEvent(2)
           .MoveNext
        Loop
    End With
    Strlist = "'" & Strlist & "'"
    LstEventList.RowSource = Strlist
    qd.Close 'Closes the connection to the datbase query

The Idea is to avoid having these sorts of statements all over through my code

ListProducts.RowSource = "SELECT Products.Product, Products.Id, [Show Bugs].Id FROM Products INNER JOIN ([Show Bugs] INNER JOIN BugsProducts ON [Show Bugs].Id = BugsProducts.BugID) ON Products.Id = BugsProducts.ProductId WHERE ((([Show Bugs].Id)=" & lstBugList.Value & "));"

 

[ScottGem]

Hi,

Your correct, my statement didn't make much sense. sorry.

I'll try again.

I have a select query in my access DB.

If i set the rowsource to that query it will populate the Listbox perfectly.
How ever i have a parameter that needs to be passed to my query which seems to mean that the whole thing becomes really complex.

The Idea is to avoid having these sorts of statements all over through my code

ListProducts.RowSource = "SELECT Products.Product, Products.Id, [Show Bugs].Id FROM Products INNER JOIN ([Show Bugs] INNER JOIN BugsProducts ON [Show Bugs].Id = BugsProducts.BugID) ON Products.Id = BugsProducts.ProductId WHERE ((([Show Bugs].Id)=" & lstBugList.Value & "));"

My question is; why do you want to avoid that? Why write several lines of code when you can use a simple SQL stmt? If you are using the same Rowsource on several forms I can understand why you might want to use one query and just set the Rowsource to that query. Otherwise it makes no sense. Just build your RowSource to reference a control holding the parameter.