2

I'm trying to assign a list of Names to a combobox control in Access VBA.

My problem is that the output string of names is not correct.

Here is my code:

Private Sub command186_click()
    Dim firstName As String
    Dim lastName As String
    Dim rst As Object
    Dim rowSourceText As String
    Dim fullName As String

    Set rst = CurrentDb.OpenRecordset("Pool_Contact")

    Do While Not rst.EOF
        firstName = rst("FName").Value
        lastName = rst("LName").Value
        fullName = firstName + " " + lastName
        rst.MoveNext
    Loop

    Forms(FrmDaysAvailable).Controls("Combo202").rowSource = fullName
    Debug.Print fullName
End Sub

I know that the error is somewhere inside of my loop, where the variable fullName is written over by the second result.

how can I fix this loop to produce a string that looks like fullName , fullName, fullName ...

Thanks for all your help

Improve this question
3
  • Isn't rst("FName").Value would be rst.fields("FName").Value? Commented Aug 14, 2012 at 15:10
  • That's not the problem. If I place the debug inside of the loop right after the rst.MoveNext, debug.print fullName gives me the list of names separated by a line feed. Commented Aug 14, 2012 at 15:14
  • I see you've named the form "frmDaysAvailable" it would also be good practice to rename your combobox to something other than "Combo202" for when your reading the code back. Commented Aug 15, 2012 at 13:09

2 Answers 2

Reset to default
5

This can be a lot simpler:

 Forms(FrmDaysAvailable).Controls("Combo202").rowSource = _
           "SELECT ID, FName & ' ' & LName FROM Pool_Contact"

Or 

Forms!FrmDaysAvailable.Combo202.rowSource = _
           "SELECT ID, FName & ' ' & LName FROM Pool_Contact"

Or

Me.Combo202.rowSource = "SELECT ID, FName & ' ' & LName FROM Pool_Contact"

Furthermore, use & not + to concatenate. Plus (+) will give you problems with nulls.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This doesn't seem to work. rather than names in my combobox, I have "Select ID" and "FName & ' ' & LName FROM Pool_Contact". It is interpreting what you wrote as a string and not an index to the FName and LName of pool_Contact. Am I doing something wrong?
Have you set the row source type to value list? It should be table/query for the above.
still not working. I changed it to table/query, and now my debug for Debug.Print Me.Combo202.Value gives me null. I want to use this method for its simplicity, but its giving me problems
Did you really change my sample line to "Select ID" and "FName & ' ' & LName FROM Pool_Contact"? It should read "Select ID, FName & ' ' & LName FROM Pool_Contact". It is an SQL string, exactly the same as your recordset. This is the usual way to populate a combo in MS Access, and the way the combo wizard will build it.
4

You must add each item to the ComboBox, instead you are only adding the last one

Do While Not rst.EOF
    firstName = rst("FName").Value
    lastName = rst("LName").Value
    fullName = firstName & " " & lastName
    Me!Combo202.AddItem(fullName)
    rst.MoveNext
Loop

Also you should declare rst as DAO.Recordset not as object. (You might have to add a reference to the DAO dll).

Optionally you can assign a table or a query directly to the row source of the ComboBox and drop the loop completely

Me!Combo202.RowSource = "Pool_Contact"

But you can do that in the form designer and do not need to do it in VBA at all. If you need to refresh the contents of the ComboBox you can simply write

Me!Combo202.Requery

Note: I assume that Forms(FrmDaysAvailable) is the current form. In that case you can simply address it through Me. Further, the Controls collection is the default property of a form. Forms(FrmDaysAvailable).Controls("Combo202") can be simplyfied to Me("Combo202") or even to Me!Combo202 with the VBA collection access operator !.

I also suggest you to give your ComboBox (and other controls) meaningful names like cboFullName. This makes the code more readable. I usually make a query corresponding to the ComboBox with the same name prefixed with "q": qcboFullName and assign this to the RowSource of the combo in the properties window. A query has the advantage over a table that you can apply a "ORDER BY" and select exactly the columns needed for the ComboBox.

Typically you would have a hidden ID column (enter 0 in the column width property) as result of the user selection and a string column for display.

SELECT PersonID, firstName & ' ' & lastName AS Display
FROM tblPerson
ORDER BY firstName, lastName
Improve this answer

5 Comments

I wouldn't use + to concatenate. Also, why loop through the recordset? This is MS Access.
Do you think it is a good idea to set the row source to a table? How will you control the column count, size and order?
@Remou: In most cases you will have a query as row source; however, in simple cases where you have a lookup table with non changing values (e.g. month numbers and month names) or a table with temporary results that are sorted, a table would work.
I am aware of that, but the OP made it clear that the names were to be concatenated to give a full name, so the table is not limited to an ID and description, or a single column, therefore setting the RowSource to a table name cannot be correct in this instance. You have drawn a lot on my answer, you may as well use that as well.
@Remou: I looked at your answer only after I had written my own. Obviously the OP does not use an ID at all. The question is what he wants to do with the selected name. In most scenarios a name alone is of no use. If you want to select a person, you need to know its ID to process it further. This might be a design error of the OP.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.