Combobox read from multiple table columns? 1

[Woodman650]

Hey guys,
I was just wondering if a combobox can be setup to read from multiple table columns through a query? I've got 4 fields I want to combine in the combo and display (cast1, cast2, cast3, cast4). the SQL looks like this right now, and the combo returns an error:

SELECT qryClientData.Cast AS Expr1, qryClientData.Cast2, qryClientData.Cast3, qryClientData.Cast4
FROM qryClientData
WHERE ((Not ([qryClientData].[Cast]) Is Null))
ORDER BY qryClientData.Cast;

thanks

 

[ThomasLafferty]

I'm making three assumptions:
1. You have a table called ClientData with the following fields:
Cast, Cast2, Cast3, Cast4
2. You have a query called qryClientData
3. The SQL shown below is not is not the SQL statement you are using in your query called qryClientData:

SELECT qryClientData.Cast AS Expr1, qryClientData.Cast2, qryClientData.Cast3, qryClientData.Cast4
FROM qryClientData
WHERE ((Not ([qryClientData].[Cast]) Is Null))
ORDER BY qryClientData.Cast;

If all of the above is true, what is the structure of qryClientData?

Tom

Live once die twice; live twice die once.

 

[Woodman650]

hey Tom,
your assumptions are all correct. I am using that code in my combobox RowSource. The structure of the query is very simple... for right now, the table columns are each their own seperate entities in the Query. There are no relations. Fields in the query being read from the main table are ID, Title, Cast, Cast2, Cast3, Cast4.

 

[TheAceMan1]

How are ya Woodman650 . . .

[red]You can't negate a Null! . . . there is no value.[/red]

If [blue][qryClientData].[Cast][/blue] is Null then [blue]Not ([qryClientData].[Cast])[/blue] is producing your error.

If your attempting to remove records with nulls from the listing it would be:

[blue] WHERE ([qryClientData].[Cast] Is Not Null)[/blue]

See Ya! . . . . . .

 

[Woodman650]

hey Aceman,
I'm doing pretty well, thanks. =D My girlfriend just came back home from a year studying abroad yesterday, so I can't complain.

So I understand about using the "Is Not Null" attribute. What I'm wondering is... I've got 4 different fields in my main table. I've got 4 comboboxes on a form, each reading and writing from and to one of the fields. I was wondering if I can read from all the fields for each combobox, while still having each combo write data to a specific field? Think of concatenating all the fields for reading purposes. Hope that makes sense.

 

[ThomasLafferty]

I think I am able to duplicate the error you are encountering. Take a look at the link below to screenshots of what I have done so far and let me know if I am on track.

http://www.geocities.com/thomas_e_lafferty/index.html?1150823009359

Let me know if I am on to what you would like to accomplish.

Tom

Live once die twice; live twice die once.

 

[Woodman650]

hey Tom,
I used the wizard as well, but I don't want multiple columns in the combobox. I was hoping to be able to combine all the data from different columns in the table, into one column in the combobox.

 

[ThomasLafferty]

That's an entirely different issue... Guess I misunderstood!

Sorry! Just for clarification, do you want to concatenate the displayed item from each combo, or the entire list for each combo?

Displayed items is an easy deal to concatenate in say a textbox:

=[Combo1].[Value] & ", " & [Combo2].[Value] & ", " & [Combo3].[Value] & ", " & [Combo4].[Value]

But I don't think this is what you are after. Looks like I will watch and learn from the Aceman again...

Tom

Live once die twice; live twice die once.

 

[Woodman650]

I would like to have all the of fields from all 4 columns read and displayed as one list, in one combo box.

 

[ThomasLafferty]

You might try something like this:

1. Use a select query to pull the records you want.
2. Use an append query and some VBA to write the records to that table, pulling from first Cast, then Cast2, etc.
3. Point your combo to the temp table.
Here's some code to play with/modify/trouble shoot...

If nothing else, maybe it will get you started in the right direction...

Sub PopulateCombo()
    Dim SQLText As String
    Dim intCast As Integer
    Dim strCast As String
    
    For intCast = 1 To 4    'four loops for four fields
        While Not EOF

            'insert records into a table named TempTable
            If intCast = 1 Then
                 strCast = “Cast”
            Else
                 strCast = “Cast” & intCast
            End If

          SQLText = “INSERT INTO [TempTable] ([Cast]) SELECT [ID] _
          & “, [“& strCast & ”] FROM qryClientData;”
          
         Wend
    Next intCast
End Sub

Hope this makes a little sense, and if not, I'm sure one of the gurus can.

Hope this helps.

Tom

Live once die twice; live twice die once.

 

[ThomasLafferty]

Wow! Lots of stuff missing from my code above.
For starters, add this line just above Wend:

Docmd.RunSql SQLText

There are probably some other issues with the above, but it's an idea that might trigger you to try something else.

Tom

Live once die twice; live twice die once.

 

[ThomasLafferty]

Also, setup a variable to contain a reference to the recordset you are working with. The EOF statement above needs an argument or it will return an error.

Something along these lines:

Sub PopulateCombo ()
    On Error GoTo Err_BtnPopTable_Click
    Dim SQLText As String
    Dim intCast As Integer
    Dim strCast As String
    Dim rs As Recordset
    
    Set rs = New ADODB.Recordset
    rs.Open "Select ID, Cast From ClientData", _
        CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        [COLOR=red][b]'code hangs at this line with error: Method Open of object recordset failed[/b][/color]
    For intCast = 1 To 4    'four loops for four fields
        While Not rs.EOF

            'insert records into a table named TempTable
            If intCast = 1 Then
                 strCast = "Cast"
            Else
                 strCast = "Cast" & intCast
            End If

          SQLText = "INSERT INTO [TempTable] ([Cast]) SELECT [ID]" _
          & ", [" & strCast & "] FROM qryClientData;"
          
          DoCmd.RunSQL SQLText
         Wend
    Next intCast
    
Exit_PopulateCombo_Click:
    Exit Sub
    
Err_PopulateCombo_Click:
    MsgBox Err.Description
    Resume Exit_BtnPopTable_Click

End Sub

I have the above set to the Click event of a command button and it hangs when it tries to open the recordset. I'm still fairly new at this, but my thought was (and maybe you or one of the other gurus can debug the above to make it work for you)

1. Return the records you need (ID plus four columns cast, cast2 etc.
2. Loop through til EOF (end of file)
3. On first pass, you will reference Cast, on second pass Cast2
4. At the end of each pass, append the records to a temp table
5. Point your combo at that table.
6. When you exit the form, you will need to drop the table so that it doesn't keep growing and growing with repeated items. You will probably load the combo on the open event of the form.

I'm about as far as I can go on this one, and I may be barking up the wrong tree, but maybe this will help.

Tom

Live once die twice; live twice die once.

 

[PHV]

What about this ?
Dim rs As [!]ADODB.[/!]Recordset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Woodman650]

oh you guys are right on the money. =D
Tom, I will tweak the code a little later tonight and see if I can't get it working with my queries. But I know what you're doing. thanks a bunch!

 

[ThomasLafferty]

Hi PHV! How the heck are ya? I tried it, and it still hangs on the line highlighted above. Incidentally, I haven't heard back from the original author of this post, so I'm not even sure I'm on the right track. As far as the general direction I'm heading with this, does it make sense?

Tom

Live once die twice; live twice die once.

 

[ThomasLafferty]

there are a few other lines you will need to fix, ie look at the error handling very closely (I was sloppy with my names and not consistent) Thanks for the star!

Live once die twice; live twice die once.

 

[Woodman650]

haha, sorry... I've been out of the office all afternoon! but yes, I am playing with the code and trying to troubleshoot the error. I don't have the most experience with code at this point, but I can run it by my buddy later today... he is a wiz with this stuff. But he doesn't let me play around on my own... he just "fixes" things without telling me how he does it. haha... thus I always post in the forum first... so I can attempt to learn! thanks again Tom... you deserve the star!

 

[PHV]

Try to dump the content of the CurrentProject.Connection.Errors collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886