visual basic code:


Private Sub Populate_City_Search()
    Dim sStr As String
    Dim adoRec As ADODB.Recordset
    
    sStr = "SELECT * FROM City"

Set adoRec = mRecordSet(sStr)

    Do Until adoRec.EOF
        cboSearchCity.AddItem adoRec!city
        adoRec.MoveNext
    Loop

Set adoRec = Nothing

End Sub

Function:

Public Function mRecordSet(sSql As String) As ADODB.Recordset
Dim adoConnection As ADODB.Connection
Dim adoRecSet As ADODB.Recordset
Dim connectString As String

    
' Create new connection
    Set adoConnection = New ADODB.Connection

' Build connection string
    connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                & "Data Source=" & App.Path & "\data.mdb"
        
    adoConnection.Open connectString
    Set adoRecSet = adoConnection.Execute(sSql)
 
Set mRecordSet = adoRecSet

'adoRecSet.Close
'adoConnection.Close

Set adoRecSet = Nothing
Set adoConnection = Nothing
End Function


  

  

    

    

      

        

        

          

            

              

              

                
Wokawidget
Naughty Sniffing 
                  Shark

                  
Registered: Nov 
                  01
Location: Baghdad, Iraq Occupation: Classified
Posts: 
                  5686
                

                  
Similar to 
                  what I use...
I have modified you code slightly...Hope this 
                  helps.

                  
visual basic code:

Option Explicit

Private Sub Populate_City_Search()
Dim strSQL  As String
Dim adoRec  As ADODB.Recordset
    Screen.MousePointer = vbHourglass
    cboSearchCity.Clear
    strSQL = "SELECT * "
    strSQL = strSQL & "FROM City "
    Set adoRec = OpenRecordset(strSQL)
    With adoRec
        Do While Not .EOF
            cboSearchCity.AddItem .Fields("city").Value
            .MoveNext
        Loop
    End With
    Set adoRec = Nothing
    Screen.MousePointer = vbDefault
End Sub

Public Function OpenRecordset(ByRef pstrSQL As String) As ADODB.Recordset
Dim adoRec      As ADODB.Recordset
Dim strConn     As String
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\data.mdb"
    Set adoRec = New Recordset
    With adoRec
        .CursorLocation = adUseClient
        .Open pstrSQL, strConn, adOpenForwardOnly, adLockReadOnly
        Set .ActiveConnection = Nothing
    End With
    Set OpenRecordset = adoRec
    Set adoRec = Nothing
End Function

Adios,

Woka
                  

                  

                  
__________________

Taliban 
                  strategy of hiding "not fair and probably illegal" says US 
                  military...

How to multi thread in VB 
                  6

Warning: Do NOT download 
                  and run any compiled code that users post on VBF asking you to 
                  test. Always ask for the source 
                  code.


                  

                  

                  
Report 
                  this post to a moderator | IP: Logged
              

                
 04-29-2003 02:49 
                PM
                

                  

    


  

  

    

    

      

        

        

          

            

              

              

                
CoderNewbie
Addicted Member

                  
Registered: Apr 
                  02
Location: Anywhere but here
Posts: 153
                

                  

                  
quote:
                    

                    here U have 2 options, 

pass an adodb.connection 
                    object to the function as well do you can do the close after 
                    you have finished with the recordset or you could store the 
                    recordset in an array.......... so you pass the function an 
                    array. 

John
                    

                  


Hey the array thing doesn't sound to bad. 
                  Thanks I might do that.


                  
quote:
                    

                    
you can check to seee if it is open before closing: 
                    

If adoRecSet.State = adStateOpen Then 
                    adoRecSet.Close


                    

                  


Thanks for the advice. 


Hey 
                  Wokawidget... I like it... Just have a few questions if you 
                  dont mind me askin. Strictly looking to learn.

* What 
                  are the advantages of seperating up the sql statement into 2 
                  lines then putting them back to gether?

* What are the 
                  advantages of using the Fields("City") over the bang 
                  field?

* and last but not least does Set 
                  .ActiveConnection = Nothing close the database... Will this 
                  give me any problems after calling it say 20+ 
                  times?


Thanks again for the code and suggestions 
                  everyone.
                  

                  

                  
__________________
-------------------------
My 
                  name says it all!
                  

                  

                  
Report 
                  this post to a moderator | IP: Logged
              

                
 04-29-2003 05:42 
                PM
                


  

  

    

    

      

        

        

          

            

              

              

                
Wokawidget
Naughty Sniffing 
                  Shark

                  
Registered: Nov 
                  01
Location: Baghdad, Iraq Occupation: Classified
Posts: 
                  5686
                

                  
No 
                  probs...

                  


                    
The SQL query is split up because if, further down the 
                    line, it becomes a bit more complicated then already having 
                    the SELECT, FROM and WHERE clause split up then it makes the 
                    code easier to read and maintain instead of having it all on 
                    one line. Habbit I suppose.

                    
I use .Fields(fieldname).Value 
                    because it's easier for a coder to understand, no short 
                    cuts. The line of code is self explanatory. Actually, the 
                    100% correct way, no short cuts, would be 
                    .Fields.Item(fieldname).Value.

                    
Set .ActiveConnection = Nothing terminates the 
                    connection to the database. This will release memory and 
                    will not give you any problems if you run the code 20, 100 
                    or even 3427 times. Actually, just to be 100% correct you 
                    could use:


                  
visual basic code:

    Set adoRec = OpenRecordset(strSQL)
    With adoRec
        Do While Not .EOF
            cboSearchCity.AddItem .Fields.Item("city").Value
            .MoveNext
        Loop
        .Close 'Add this line here
    End With
    Set adoRec = Nothing