populating combobox using vba recordset in access

[ln098]

i am trying to use vba to populate a combo box based off of the selections of two other comboboxes in my vba userform. the code i have so far is:


equalscombobox. Value = ""
For indexvalue = 1 To 100
sql = "SELECT " & sqlattribute & " FROM " & sqltable & ";"
Debug.Print sql
Set datadb = CurrentDb
Set rs = datadb.OpenReco rdset(sql, dbOpenDynaset)
equalscombobox. SetFocus

If equalscombobox. ItemData(indexv alue) Then
equalscombobox. Value = sql
Else: equalscombobox. Value = equalscombobox. Value + sql
End If


so the equals combo box should be populated from the sqlattribute and sqltable combo box selections. i am running into an error at the set datadb = currentdb, and am not sure if the for loop will run correctly once i fix the first problem. thanks in advance!

[ln098]

also, sorry but i don't know how to code tag?

[Seth Schrock]

To use code tags, you click the [CODE/] button at the top of where you type your post. That will put in the starting and ending code tags. You just put your code between them and it will format the code text to be more easily readable.

As for your question, are you wanting to see the SQL code in your combo box or are you wanting to see the data returned by the SQL query?

Try reading Before Posting Code, especially the first A part. I have a feeling that this will fix the error that you are getting with the line

Set datadb = CurrentDb

[twinnyfo]

ln098,

I see several problems with your code, which jump out at me, but Seth is correct that some of your question may be answered there.

However, before your code will even begin to work properly, you should look at these things:

1. I have assumed that you are including your entire procedure--which may not be the case. If not, please include the entire block of code, as this will help us understand your entire procedure and better trouble shoot the problems with it.

2. There is no need to use the ".Value" proerty of a combo box, as this it he default. equalscombobox = "" should work fine.

3. Are sqlattribute and sqltable variables (it is unclear from your code)? If they are variables, then this looks correct, and since you have a Debug.Print directly after that and have not found anything awry, I assume it is operating correctly.

4. Your error on "Set datadb = CurrentDb" could be that you have not declared the variable datadb as a Database. I always use "Set datadb = CurrentDb()", but I think it has to do with how your database is using recordsets (someone ehlp me out here--isn't there something about ADO and DAO recordsets???).

5. You use a For Statement, but there is no Next Statement, so this will break your code also.

6. Why are you setting the focus on equalscombobox?

7. If you are looking to evaluate the equalscombobox. ItemData(indexv alue), you will be missing the first row of the combo box, as the rows in a combo box begin at 0. indexvalue starts at 1 and goes to 100. Also, unless you have a 100 rows of data in the combo box, your code will eventually break. If there are exactly 100 rows of data, your code will break at the 100th indexvalue, for reasons already stated. index value should go from 0-99 or use equalscombobox. ItemData(indexv alue -1). However, I don't really think this is what you are trying to do.....

8. Your line: equalscombobox. ItemData(indexv alue) is used in an If...Then statement. However, it is not set to evaluate itself against anything. If equalscombobox. ItemData(indexv alue) has any value, then it will evaluate to True. So, in essence, what you are saying is if equalscombobox has a rownumber indexvalue, then perform the following.

9. Your line equalscombobox. Value = sql is invalid, as you cannot (or shouldn't) set the value of a combo box to a SQL string. I think what you want to do is set the row source of the combo box to the SQL string? Then it would look like this:

equalscombobox.RowSource = sql
equalscombobox.Requery

10. Your line Else: equalscombobox. Value = equalscombobox. Value + sql will not work, for similar reasons to #9 above. You cannot take the value of a Combo Box and add a SQL string to it. Honestly, I have no idea what you are intending to do with this line of code, as I can only assume that your SQL statement will return multiple rows (or at least should--if it is being used in a combo box).

11. Assuming that you do want to actually loop through 1-100 of indexvalue, all your code will do is assign the row source of your combo box 100 times--no matter how browken your code may or may not be at this point. So.........

12. I must ask, exactly what is it that you are trying to do with this code? It is not very complex, but, on the surface, it does not make too much sense. Perhaps if we understood the intent, which you kind of sort of explain in your initial post, we could guide you to a better solution.