Select Distinct To Populate Combo Box

error#9 · Jan 11th 2007

Hi all,
I want to populate 2 combo box's with SQL query'd values, so i do it like so

Code

'***************************************************************************'                                                                          '
'   Functions To Populate Data to search combo boxs                         '
'***************************************************************************'


Option Explicit
Public rsComb As ADODB.Recordset


Public Function SelectCombVal(ByRef rsComb)
   
On Error GoTo errorhandler
   
   Dim sSelect As String


   Set rsComb = New ADODB.Recordset
   bNoBreaks = False
   sBreakName$ = frmLogin.cmbUserID
   sSelect = "' AND [COMPLETED] = 0) ORDER BY [DEPOT]"
   
    Select Case sBreakName$
        Case sBreakName$
            rsComb.Open "SELECT [DEPOT],[STOCK],[COMPLETED],[TIME_FLAG] FROM tblBreaks WHERE ([ALLOCATED_TO] = '" & sBreakName$ & sSelect, cn, adOpenKeyset, adLockOptimistic
    End Select
        
    Do While Not rsComb.EOF
        frmAgent.txtDepotSearch.AddItem rsComb!DEPOT
        frmAgent.txtStockSearch.AddItem rsComb!STOCK
        rsComb.MoveNext
    Loop
    
Exit Function


errorhandler:
    MsgBox Err.Description, vbCritical, "Contact Software Developer!!"
        
End Function

Display More

BUT...........

Even though this works a treat, the combo box display all the duplicates aswell.

e.g. [DEPOT] D,D,D,D,HHI,HHU,HHH,HHH,HHH,I,I,E,R,R,E
I want
e.g. [DEPOT] D,HHI,HHU,HHH,I,E,R

e.g [STOCK] sdf6546,asd456,zxc123,lpo456,sdf6546,asd456,zxc123,lpo456
I Want
e.g [STOCK] sdf6546,asd456,zxc123,lpo456

I've tried putting the DISTINCT after the SELECT but this wont work because i need to DISTINCT it AFTER i've query'd the recordset, otjherwise i wont get the results from the SQL staement i need.

Does anyone know how to re-order this or add something to get the results

Thanks
Error#9

gollem · Jan 15th 2007

Re: Select Distinct To Populate Combo Box

Hi,

you can try to loop through the combobox to check if the stock is already added. If it's in the combobox => don't add otherwise add.

error#9 · Jan 15th 2007

Re: Select Distinct To Populate Combo Box

Sorry i dont know what you mean?
I already loop through the recordset to retrieve the data, so wont that fall over on the first loop?

gollem · Jan 15th 2007

Re: Select Distinct To Populate Combo Box

You have to loop through the combobox:

Code

...


dim intCounter  as integer 
Do While Not rsComb.EOF 
        frmAgent.txtDepotSearch.AddItem rsComb!DEPOT 
        
        'Here you loop the combobox
        for intcounter = 0 to txtStocksearch.listcount-1
             if frmAgent.txtStockSearch.list(intcounter) = rsComb!STOCK then exit for
        next


        if intcounter < txtStocksearch.listcount then frmAgent.txtStockSearch.AddItem rsComb!STOCK 
        


        rsComb.MoveNext 
 Loop 


...

Display More

Hope this gives you an idea.

error#9 · Jan 15th 2007

Re: Select Distinct To Populate Combo Box

Sorry, i really dont understand.
DEPOT is a 3 Character field so i dont so how looping and comparing to the counter number is going to dedupe the combo boxs.

my appolgies on being thick if its just staring me in the face

gollem · Jan 15th 2007

Re: Select Distinct To Populate Combo Box

Ok, I'll try to explain a little more. The example I provide is only for 1 combobox, but you can use this for the other as well.

The global thing that I do is, before I add a new element in the combobox I check if the item I want to add already exists in the combobox. If the item is founded in the combobox I don't add the item. If the Item isn't found in the combobox I add the item.

Code

'Here you loop the combobox:
'Start to search the combobox from item 0 until last item
For intcounter = 0 To txtStocksearch.listcount-1 
        'Check if item is equal to current record-item from the recordset
        'If the item is equal we exit the loop because we found the item
        If frmAgent.txtStockSearch.list(intcounter) = rsComb!STOCK Then exit For 
    Next 
     
 'Correction < should be =
'If our counter is equal to the total items in our box we have looped the box without finding the item => so we add the item
    If intcounter [COLOR="Red"]=[/COLOR] txtStocksearch.listcount Then frmAgent.txtStockSearch.AddItem rsComb

Display More

I hope this helps you to understand what the code actually does: checking before adding the item, if the item is in the combobox.

error#9 · Jan 15th 2007

Re: Select Distinct To Populate Combo Box

Ahhhhhhhhh

Thank you vmuch, it works a treat, how to i get it to loop through both combo boxs, do i need to create another sub routine?

gollem · Jan 15th 2007

Re: Select Distinct To Populate Combo Box

Yes,

you can do just the same for the other combobox. Just add another loop and do the same.

error#9 · Jan 15th 2007

Re: Select Distinct To Populate Combo Box

Ah good skills worked a Gem!

Another things though, is it now possible that when they select a DEPOT can it you populate the other combo box's values by the filter of DEPOT?

gollem · Jan 16th 2007

Re: Select Distinct To Populate Combo Box

Yes this is possible. Just use the same code as you use to populate the comboboxes. Only you populate 1 combobox an d you have have to se the filter in your sql-statement, something like this:

Quote

SELECT [DEPOT],[STOCK],[COMPLETED],[TIME_FLAG]
FROM tblBreaks WHERE (DEPOT='" & txtDepotSearch.text & "'
[ALLOCATED_TO] = '" & sBreakName$ & sSelect

error#9 · Jan 16th 2007

Re: Select Distinct To Populate Combo Box

Hi,
The syntax is correct but it gets lots in an infinate loop

Code

Public Function SelectCombFilterVal(ByRef rsComb)
   
   Dim sSelect As String
   Dim intCounter  As Integer
   
   Set rsComb = New ADODB.Recordset
   bNoBreaks = False
   sBreakName$ = frmLogin.cmbUserID
   sSelect = "' AND [COMPLETED] = 0) ORDER BY [STOCK]"
   
    Select Case sBreakName$
        Case sBreakName$
            rsComb.Open "SELECT [DEPOT],[STOCK],[COMPLETED],[TIME_FLAG] FROM tblBreaks WHERE ([DEPOT] = '" & frmAgent.txtDepotSearch.Text & "' AND [ALLOCATED_TO] = '" & sBreakName$ & sSelect, cn, adOpenKeyset, adLockOptimistic
    End Select
        


Do While Not rsComb.EOF


    For intCounter = 0 To frmAgent.txtStockSearch.ListCount - 1
        If frmAgent.txtStockSearch.List(intCounter) = rsComb!STOCK Then Exit For
    Next


    If intCounter = frmAgent.txtStockSearch.ListCount Then frmAgent.txtStockSearch.AddItem rsComb!STOCK


Loop
Set rsComb = Nothing


End Function

Display More

error#9 · Jan 16th 2007

Re: Select Distinct To Populate Combo Box

I dont know if this makes a differnece but STOCK is a Number and Text field
it has numbers like

BFD34342
73844312
99454201
BSA93499

gollem · Jan 16th 2007

Re: Select Distinct To Populate Combo Box

Now you can use distinct and you don't have to use the loop I've provided.

Code

rsComb.Open "SELECT [COLOR="Red"]DISTINCT[/COLOR] [DEPOT],[ .......
Do While Not rsComb.EOF 
        frmAgent.txtStockSearch.AddItem rsComb!STOCK 
        rstComb.movenext
Loop

error#9 · Jan 16th 2007

Re: Select Distinct To Populate Combo Box

GOLLEM YOUR A TOP MAN!!!!!!!!!!!!
NICE 1

You dont mess about...... i like it

Cheers

error#9 · Jan 16th 2007

Re: Select Distinct To Populate Combo Box

do you know how to export a spreadsheet from access using sql in excel?

gollem · Jan 16th 2007

Re: Select Distinct To Populate Combo Box

I don't understand your question.

Do you mean:
- get data from an access table into excel
- or get data from excel into access
...

Can you be more specific?

error#9 · Jan 16th 2007

Re: Select Distinct To Populate Combo Box

Transfer data from an Access table into a new excel spreadsheet using Excel VBA via a button press.

Somehow using ADO cause i want it run in the background.

gollem · Jan 16th 2007

Re: Select Distinct To Populate Combo Box

Hi this can be a global procedure:

Code

Dim cnn As ADODB.Connection
    Dim rstSQL As ADODB.Recordset
    Dim lngIndex As Long
    Dim lngRow As Long
    
    'Connection
    Set cnn = New ADODB.Connection
    cnn.Provider "Microsoft.jet.oledb.4.0"
    cnn.ConnectionString = "c:\tmp\test.mdb"
    cnn.Open
    
    'Recordset
    Set rstSQL = New ADODB.Recordset
    rstSQL.Open "SELECT * FROM tblTest", cnn, adOpenDynamic, adLockReadOnly
    lngRow = 2 'Start writing on row 1
    Do While rstSQL.EOF = False
        For lngIndex = 0 To 2 '2 if you have 3 fields(start counting from 0)
            ActiveSheet.Cells(lngRow, lngIndex + 1).Value = rstSQL.Fields(lngIndex)
        Next
        lngRow = lngRow + 1
        
        rstSQL.MoveNext
    Loop
    
    'Close connection
    rstSQL.Close
    Set rstSQL = Nothing
    cnn.Close
    Set cnn = Nothing

Display More

This part is perhaps easier for you, instead of using:

Code

...
 Do While rstSQL.EOF = False
        For lngIndex = 0 To 2 '2 if you have 3 fields(start counting from 0)
            ActiveSheet.Cells(lngRow, lngIndex + 1).Value = rstSQL.Fields(lngIndex)
        Next
        lngRow = lngRow + 1
        
        rstSQL.MoveNext
    Loop


...

Display More

Use:

Code

Do While rstSQL.EOF = False
        ActiveSheet.Cells(lngRow, 1).Value = rstSQL.Fields!Name                
        ActiveSheet.Cells(lngRow, 2).Value = rstSQL.Fields!Des                
        ActiveSheet.Cells(lngRow, 3).Value = rstSQL.Fields!Test
        lngRow = lngRow + 1
        rstSQL.MoveNext
    Loop

error#9 · Jan 16th 2007

Re: Select Distinct To Populate Combo Box

Oh, i know this way, cool,done it before, but i'm gonna have to create a excel spreadsheet with the headers on it already.

no worries, good skills!

Cheers

gollem · Jan 16th 2007

Re: Select Distinct To Populate Combo Box

To get the name of a field(col-header):

Code

rstsql.fields("test").name

This should help you to get the col-names.

To see how you can add sheets, workbooks, ... you can take a look at the macro recorder. Record a macro and see what code is used.

Hope this helps.

Select Distinct To Populate Combo Box

Participate now!

Share

Users Viewing This Thread