feed a parameter to the RowSource of a listbox based on a query 1

[jamaarneen]

Hi to all of you

I have an 'unbound' listbox on my form ('bound'), with as RowSource a Parameter-query.

What I would like is, that in the 'OnCurrent' event of the form, the 'Parameter' should be entered by code.

Some facts:
The parameter is expecting a number (representing a weekday: 1 for Sunday, ...). In the recordset of my form there is a field (named: ReqDel_date) containing a date. When I open the form or navigate to another record (so 'ReqDel_Date' gets a new date), I would like to requery the query of the listbox's RowSource, and entering a new value for the parameter.

I tried to make it with QueryDefs Something like that:

    Dim db As Database
    Dim qdf As QueryDef
    Dim rst As DAO.Recordset
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryVolunteerRelevantList")
    qdf.Parameters("[enter weekday]") = Weekday(Me.ReqDel_Date)
    Set rst = qdf.OpenRecordset()
    
    Me.lstVolounteersRelevant[red].RowSource [/red]= rst

at the last line - the red, it is getting an error: Type mismatch.

I have no idea if I'm almost there, or am I totally in the wrong direction...
Thanks in advanced for your help

Ja

 

[PHV]

what about this (no need of rst) ?
Me.lstVolounteersRelevant.RowSource = qdf.SQL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jamaarneen]

Thanks PHV

It is working without error, but am getting the "Enter Parameter Value" box, while I want that he should get automatically the parameter from the new date

Ja

 

[PHV]

And this ?

Me!lstVolounteersRelevant.RowSource = Replace(qdf.SQL, "[enter weekday]", Weekday(Me!ReqDel_Date))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jamaarneen]

GREAT!!! it works! Thanks a lot!

And by the way I have learned a new unknown (for me) function

Ja

 

[jamaarneen]

Hi PHV,

I have noticed that while I wrote:

Me[b][red].[/red][/b]lstVolounteersRelevant

you wrote:

Me[b][red]![/red][/b]lstVolounteersRelevant

I would really like to know, why did you changed it (it seems that both are working).

And also, it seems to me that after i started playing with the listbox-RowSource in the Current Event, the application is working very hard (it's taking about 40-50 percent of the processor). What can be the reason?

Thanks, Ja

 

[PHV]

[!]why did you changed it[/i]
It's just an habbit, dot for properties and bang for members of default collection.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886