Using DAO Populate a Combo Box with Delimited String 2

[tomhughes]

I need to know how to populate a Combo Box with a Delimited String using DAO, not ADO. Can anyone help me?

 

[PaulBricker]

What does your string look like? An example would probably help get this solved.

Paul

 

[tomhughes]

data1,data2,data3,data4,etc.

 

[HarleyQuinn]

What have you tried so far?

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PaulBricker]

I would say a Value List is the best way to go. What I'm not understanding is where this delimited list of yours is.
You can set the properties for this in the Open event for the form.

Me.ComboName.RowSourceType = "Value List"
Me.ComboName.RowSource = "data1, data2, data3"

The instructions in help say the value needs to be separated by a semicolon (, but commas seemed to work ok as well when I tested it. If that isn't the case for you, then you would have to use the replace function to change your commas to semicolons.

Shouldn't be too difficult, but where your delimited string is isn't clear at this point.

Paul

 

[tomhughes]

PaulBricker
I am attempting to populate a Combo Box with Table field information. Since I have Access 2000, I cannot use the AddItem method, so I am attempting to use another method.
The delimited string will just be a normal string with delimiters included to separate the values of the fields. It will be no problem to use ";" instead of ",".

 

[dhookom]

There is a generic concatenate function in the FAQs faq701-4233 for this forum that returns a value using either ADO or DAO. The value returned could be used to set the Row Source property.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tomhughes]

Thanks for all your help - I appreciate it very much

 

[tomhughes]

PaulBricker

I still need some help with this code. It is not populating the Combo Box. What am I missing ?

Private Sub Combo2_AfterUpdate()
Dim str As String
str = "Mon;Tue;Wed"
Me.Combo2.RowSourceType = "Value List"
Me.Combo2.RowSource = str
End Sub

 

[PaulBricker]

I think it is the event you are running it from. I have my code in the On Open event for the form. You have it in the AfterUpdate event for the combo box you want to set the row source for.
That doesn't seem like the place to put it.

Paul

 

[dhookom]

I'm not sure why you don't divulge more information. You stated earlier "populate a Combo Box with Table field information". Your recent code has nothing to do with table field information. We shouldn't have to aim at a moving target.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tomhughes]

PaulBricker
I thought it might have been something like that. Is there any way I can make it work without having to open the Form since it will be changed after the Form is loaded ???

dhookom
When developing code I always try to do something simple, and a step at a time. The code that you are referring to is only a small piece of the procedure. I'm glad you responded, since I couldn't get the code to work that you referred me to either. I tried putting it in as a module, but it could not recognise the last piece of the code i.e. [ID]. Then I put it in the Form. Here is the code I put in.

Function Concatenate(pstrSQL As String)
Dim pstrDelim As String
pstrDelim = ", "
          
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb


    pstrSQL = "SELECT tblData.TopicHead" & Forms!Configure.Combo1.Column(1) & "   FROM tblData WHERE ID =  " & [ID]
   
    Set rs = db.OpenRecordset(pstrSQL)
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                    .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With

    Set rs = Nothing
    Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
            Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
   
End Function

I tried this for the query string but it didn't like the first SELECT

   pstrSQL = "SELECT tblData.ID,Concatenate("SELECT tblData.TopicHead" & Me.Combo1.Column(1) & "FROM tblData WHERE ID =" & [ID]"

Then I tried this for the query string but it gave me the error that it couldn't open any more data bases.

   pstrSQL = Concatenate("SELECT tblData.TopicHead" & Forms!Configure.Combo1.Column(1) & "FROM tblData WHERE ID =" & [tblData.ID])

 

[dhookom]

I'm not sure why you didn't leave the Concatenate function just as it was. You are sending in pstrSQL as a parameter but then making your own assignment of a value within the function... The following line of code will be missing a space after TopicHead.

 pstrSQL = "SELECT tblData.TopicHead" & Forms!Configure.Combo1.Column(1) & "   FROM tblData WHERE ID =  " & [ID]

Also, the code line expect ID to be numeric. If it is text, you would need something like:

 pstrSQL = "SELECT tblData.TopicHead[COLOR=Red Yellow] [/Color Red Yellow] " & Forms!Configure.Combo1.Column(1) & "   FROM tblData WHERE ID = [Red]""[/Red]" & [ID][Red] & """ "[/Red]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PaulBricker]

Well, if I understand you, you could try something like the OnGotFocus event for the combo box. I haven't tested it yet, but will this afternoon and see if it works for me.

Paul

 

[tomhughes]

dhookom

The name of the field will be TopicHeadA, TopicHeadB,TopicHeadC, etc. so there should be no space between tblData.TopicHead and the Quotation marks.
As I mentioned the reason I could not get the SQL statement to work when Concatenate was included in the SQL string was because I get error number 3048 that says "Cannot open any more data bases". Here is the code with the Concatenate added, that gives me the error.

 pstrSQL = Concatenate("SELECT tblData.TopicHead" & Forms!Configure.Combo1.Column(1) & "FROM tblData WHERE ID =" & [tblData.ID])

This SQl string only shows the first record.

 pstrSQL = SELECT tblData.TopicHead" & Forms!Configure.Combo1.Column(1) & "FROM tblData WHERE ID =" & [tblData.ID]

I don't see how ID could be a string since it is the Autonumber in the Table.

 

[tomhughes]

dhookom

I finally got it to work.. Here is the SQL string that works correctly:

pstrSQL = "SELECT tblData.TopicHead" & Forms!Configure.Combo1.Column(1) & "   FROM tblData "