filling Combobox with SQL Data

[sambrierley]

Hi all,

hopefully this is quite easy for someone in the know, I have a select statement (AlphaName and StaffName are variables) in a module that woks fine, its been tested with a basic insert. what i want to do is get this result into a combo box without creating another table?

any help is great thanks

strSQl = "SELECT [Week No] FROM [" & AlphaName & "_Hours] WHERE [Alpha Name] = '" & StaffName & "';"           
Me.Week_Cmb.RowSource = strSQl

 

[plog]

You've got issues bigger than a combo box.

strSQl = "SELECT [Week No] FROM [" & AlphaName & "_Hours] WHERE [Alpha Name] = '" & StaffName & "';"

You shouldn't store data in table or field names. You shouldn't have a bunch of _Hours tables to choose from, you shoudl only have one. Then if you need to assign data to an AlphaName (whatever that is) you create a field to hold the AlphaName value. You shouldn't be dynamically creating the FROM clause, you should be dynamically creating the WHERE clause.

 

[sambrierley]

Dont worry about that, the reason its done like that is becuase during an earlier stage a tempory Hours table is created that is unique to that person,this table is later deleted, it therefore makes sense to include that persons name in the table.

anyway is it possible to modify whats been written to populate the combobox?

 

[JHB]

..
anyway is it possible to modify whats been written to populate the combobox?

What problem do you've with that?

 

[sambrierley]

Ive solved through via a different method i.e. looping down a table with dlookup, not the most efficient way of doing it but there you go.
if anyone does work out to do this i will monitor here and would be grateful for a response.
JHB- the problem i was having is that when it was ran the combobox would be populated with strSQl the string and not the results of the query.

 

[JHB]

..
JHB- the problem i was having is that when it was ran the combobox would be populated with strSQl the string and not the results of the query.

It should show the result, do you have a sample database with the same issue, then post it, (zip it)?

 

[sambrierley]

Ive just found my own issue i didnt realise you had to set the .RowSourceType = "table/Query".

inexperience is all but thanks

 

[JHB]

Okay - good you got it solved.