Recordset as rowsource 2

[NVSbe]

1. Is it possible to have a recordset as rowsource for a dropdown list?

2. If not, is there a way to fill the dropdownlist with the values from the recordset using VBA? --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[LittleSmudge]

1. No, well not EXACTLY

2. Yes, - quite easily


Dim strSQL As String
strSQL = "SELECT * FROM tblTableName WHERE field = 42"

rst.Open strSQL

Appart from the few necessary line of code that I've missed out - the above opens a recordset.


Instead use

Dim strSQL As String
strSQL = "SELECT * FROM tblTableName WHERE field = 42"

cboControlName.RowSource = strSQL
cboControlName.Requery


and you have what you want.

QED.









G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[oharab]

If you already have a recordset open from earlier in the routine and want to use it as the rowsource you can use:

cboControlName.RowSource = rst.Name

but it is pointless opening a recordset just to populate a combo, just pass the SQL as GLS mentions.

HTH

Ben ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[NVSbe]

Well...

That' what we've been using so far, and the problem with it is that it works too slow.

Here's what we have

1 - A dropdownlist of departments which fills another dropdownlist (2) with employees of that department when clicked on.

2 - A dropdownlist of employees which display the details of the clicked employee in (3)

3 - A bunch of textfields

Now, the problem is that the filling of (2) is too slow, until a record is loaded in (3).. After that, (2) is really fast. We don't know what's causing it, but since (3) is filled using a recordset, I thought perhaps we could fill (2) with a recordset as well... so what I need to know is how to open a recordset, and move it's values manually to (2), which I think would work faster...

Hope this makes sense... --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[LittleSmudge]

so what I need to know is how to open a recordset, and move it's values manually to (2), which I think would work faster...

oh man

If you have a lifetime to wait you can try it that way - but I won't still be about by the time it finishes !

one row at a time has GOT to be THE slowest way going.



Is it REALLY that (2) is faster once (3) is populated -
OR
is it that (2) refreshes quicker once it has been populated before ?



What is the SQL string that populates (2) at the moment ?




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[NVSbe]

It varies

There's a button that populates it with all employees

ddlwerknemers.RowSource = "select [veld2],[veld3] from [expo-wkn] where [veld11] is null and [veld16] = " & ddlAfdelingen.Value * 100 + 5 & " order by 2"

There's the listbox, which populates it with all employees form a department (example, dept 16 has all employees of veld16 between 1600 and 1700)
ddlwerknemers.RowSource = &quot;select [veld2],[veld3] from [expo-wkn] where [veld11] is null and [veld16] >= &quot; & ddlAfdelingen.Value * 100 & &quot; AND veld16 < &quot; & ddlAfdelingen.Value * 100 + 100 & &quot; order by 2&quot;

AND there's buttons which populate it with all employees of a shift (veld16 = 1605, for example)
ddlwerknemers.RowSource = &quot;select [veld2],[veld3] from [expo-wkn] where [veld11] is null and [veld16] = &quot; & ddlAfdelingen.Value * 100 + 5 & &quot; order by 2&quot; --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[NVSbe]

That firdst query was wrong... Supposed to be
ddlwerknemers.RowSource = &quot;select [veld2],[veld3] from [expo-wkn] where [veld11] is null --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[NVSbe]

I might also need to mention that the problem doesn't occur when running the program in Access 97 on windows 98 but only when it runs on Acces 97 on Windows XP... Think that might be it? --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[NVSbe]

I just saw Ben's post and using a recordset to fetch the data and then coupling it to the combobox actually worked faster than just using a query on th combobox...

This is really weird, but it worked.

Thanks both for your suggestions... Stars have been awarded --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[LittleSmudge]

That's not wierd.

It is just that the recordset is able to &quot;go off&quot; and collect the data while you're not waiting for it - then when you want to populate the list box it has all the info available & in it goes.

If that works okay for you then move on to more productive challenges. Leave this one along.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D