OberonPlace.com Forums  

Go Back   OberonPlace.com Forums > Developer Forums > VBA > CorelDRAW/Corel DESIGNER VBA

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 10-12-2004, 13:51
CamWest
Guest
 
Posts: n/a
Default Need help connecting to database and filling combo box

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

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
Reply With Quote
  #2  
Old 10-12-2004, 14:58
Alex's Avatar
Alex Alex is offline
Administrator
 
Join Date: Nov 2002
Posts: 1,940
Blog Entries: 4
Default Re: Need help connecting to database and filling combo box

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?
Reply With Quote
  #3  
Old 10-12-2004, 15:35
CamWest
Guest
 
Posts: n/a
Default

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
Reply With Quote
  #4  
Old 10-12-2004, 15:38
Alex's Avatar
Alex Alex is offline
Administrator
 
Join Date: Nov 2002
Posts: 1,940
Blog Entries: 4
Default

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.
Reply With Quote
  #5  
Old 10-12-2004, 15:53
shelbym's Avatar
shelbym shelbym is offline
Senior Member
 
Join Date: Nov 2002
Location: Cheyenne, WY
Posts: 1,787
Blog Entries: 11
Send a message via ICQ to shelbym Send a message via AIM to shelbym Send a message via MSN to shelbym Send a message via Yahoo to shelbym
Default

Try:

Code:
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:

Code:
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
Reply With Quote
  #6  
Old 13-12-2004, 14:09
CamWest
Guest
 
Posts: n/a
Default tried all suggestions... still frustrated

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.
Reply With Quote
  #7  
Old 22-12-2004, 13:30
Alex's Avatar
Alex Alex is offline
Administrator
 
Join Date: Nov 2002
Posts: 1,940
Blog Entries: 4
Default Re: tried all suggestions... still frustrated

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.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 10:53.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright © 2011, Oberonplace.com