Need help connecting to database and filling combo box

I made this VBA macro and it worked until I added this code

Private Sub UserForm_Initialize()
    
    'Add list entries to combo box.
    

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset

    ' db_file contains the Access database's file name.
    ' Open a connection.
    Set objConn = New ADODB.Connection
    objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\DATA1\sample.MDB;"
    objConn.Open

    ' Get the records.
    objRS.Open "Select * FROM tblCust", objConn, adOpenKeyset, adLockOptimistic

    ' Load the customer field into the ComboBox.
    Do While Not objRS.EOF
        
        cboCustomer.AddItem objRS("Customer")
        
        objRS.MoveNext
    Loop

    ' Close the recordset and connection.
    objRS.Close
    objConn.Close

Set objRS = Nothing
Set objConn = Nothing
    
    'Use drop-down list
    cboCustomer.Style = fmStyleDropDownList
    'Combo box values are ListIndex values
    cboCustomer.BoundColumn = 0
    'Set combo box to first entry
    cboCustomer.ListIndex = 0


    cboCustomer.Left = 78
    cboCustomer.Top = 6
    cboCustomer.Width = 216
    cboCustomer.ListWidth = 216
    
    'Initialize CommandButton1
    cmdEnterText.Left = 300
    cmdEnterText.Top = 6
    cmdEnterText.Height = 18
    cmdEnterText.Width = 54
    
End Sub

Private Sub cmdEnterText_Click()
    
Dim today As Date
Dim statement As String
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim incNo As Double

today = Date

    ' db_file contains the database's file name.
    ' Open a connection.
    Set objConn = New ADODB.Connection
    objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\DATA1\sample.MDB;"
    objConn.Open

    ' Select the data.
    statement = "SELECT id, customer FROM tblCust "
    ' Get the records.
    Set objRS = Conn.Execute(statement)

    ' Load the customer field into the ComboBox.
    Do While Not objRS.EOF
        cboCustomer.AddItem objRS!Customer

    incNo = 0
    
    Select Case cboCustomer.Value
        Case i
            txtContact.Text = NA
            txtTel.Text = objRS("Phone")
            txtFax.Text = objRS("Fax")
            txtWON.Text = "NA"
            'txtFile = "NA"
            txtDate.Text = today
            txtDrawn.Text = "Campbell"
            txtRev.Text = "Rev. Date"
        End Select
    
    objRS.MoveNext
    incNo = incNo + 1
    Loop

    ' Close the recordset and connection.
    objRS.Close
    objConn.Close

Set objRS = Nothing
Set objConn = Nothing
    

End Sub

what am I missing? any help would be appreciated.

Thanks

[Alex]

Probably you should start by describing the problem you are having?

What do you expect the code to do and what it actually does for you?

Originally i had a combobox filled with a list of my customers I had written this directly into my code. But with every new customer I had to enter this data into the code.

What I want is to connect to an access database of my customers, fill a combo box with the name of the customer. When the customer was selected it would fill in the appropiate textboxes on my form.

Now the problem I get is:

"Run time error '91':
Object variable or With block variable not set

I hope this helps.

Thanks

[Alex]

Where exactly does this error occur (what line)? Also you might want to put Option Explicit at the top of your code module to catch any misspelled object variables or some variables which you used without declaring, etc. Usually helps troubleshoot such kind of problems.

[shelbym]

Try:

Dim objConn As New ADODB.Connection
Dim objRS As New ADODB.Recordset

You also might want to add a couple lines to make sure you are connecting to the database:

objConnState = objConn.State
If objConnState = 1 Then
    MsgBox "The connection to this database is working!", vbInformation
Else
    MsgBox "You were unable to connect to the database!", vbInformation
End If

If you are not connecting with the database you probally need to add a reference to the "Microsoft ActiveX Data Objects 2.x Library. Do that under Tools| References

Good afternoon,

I have tried all your suggestions :?

I added Option Explicit
Made sure "Microsoft Activex Data Object 2.xx" was referanced.

Macro runs, tells me I Database is connected, but combobox(and even listbox) does not fill with contents from database. If iI type a letter in the combobox it will fill in the textboxes with info from the first record in the database.

Thanks for all your help so far.
If you any more suggestions, they are most welcome.

[Alex]

I don't see anything obvious in the code, however you might want to debug it a bit more. For example, put a breakpoint in UserForm_Initialize function and run the macro. See if it breaks in the Initialize message handler when you about to show the form. If it does use F8 to step through the code and see if you query returns any results and if the combo box is actually filled with the data you expect.

If nothing else, attach your whole project (macro and the database) and I'll take a look at it.