Results 1 to 8 of 8

Thread: visual basic 6/Access

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2006
    Posts
    16

    visual basic 6/Access

    In form1, I am listing customerids in a combobox,
    I want to select a customerid in form1 and submit, and
    in form2 I want to display info related to this specific
    customerid, like contact, address, phone, fax, etc.

    I am using Northwind.mdb and customers table
    with visual basic 6, windows 2000. Any help please.

    Thanks in advance,

  2. #2
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: visual basic 6/Access

    How are you connecting to the database?

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Apr 2006
    Posts
    16

    Re: visual basic 6/Access

    Dim oConn As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim sSQL As String
    Set oConn = New ADODB.Connection

    With oConn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open "c:\db\northwind.mdb"
    End With

  4. #4
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: visual basic 6/Access

    Outstanding! You are using code.

    First, move these to a module and change Dim to Public
    VB Code:
    1. Dim oConn As ADODB.Connection
    2. Dim oRS As ADODB.Recordset
    3. Dim sSQL As String
    4. Set oConn = New ADODB.Connection
    You need to do this because you will be building a recordset on one form and using it on another.
    VB Code:
    1. 'on form1
    2. Private Sub cmdGetData_Click()
    3. Set oRS = New ADODB.Recordset
    4. sSQL = "SELECT contact, address, phone, fax FROM customers WHERE "
    5. sSQL = sSQL & "customerid = " & CInt(Combo1.List(Combo1.ListIndex)
    6. oRS.Open sSQL, oConn
    7. Form2.Show
    8. End Sub
    9.  
    10. 'on Form2
    11. Private Sub Form_Load()
    12. txtContact.Text = oRS.Fields.Item("contact").Value
    13. txtAddress.Text = oRS.Fields.Item("address").Value
    14. txtPhone.Text = oRS.Fields.Item("phone").Value
    15. txtfax.Text = oRS.Fields.Item("fax").Value
    16. oRS.Close
    17. Set oRS = Nothing
    18. End Sub
    This is just an example. You will need to tweak it to fit your exact needs.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Apr 2006
    Posts
    16

    Re: visual basic 6/Access

    Dear Mr. Hack,
    I am sorry to bring this back. I did exactly what you said, but ended up with errors,
    like, cannot put 'set' in module, etc..

    The following is actual code, [I can see I am vb learner], taking from here
    I want to display selected records. Please help me out.
    Thank you sir,
    VB Code:
    1. Option Explicit  
    2.  
    3.  
    4. Private Sub Form_Load()
    5.  
    6. Dim oConn   As ADODB.Connection
    7. Dim oRS     As ADODB.Recordset
    8. Dim sSQL    As String
    9. Set oConn = New ADODB.Connection
    10.  
    11. With oConn
    12.     .Provider = "Microsoft.Jet.OLEDB.4.0"
    13.     .Open "c:\db\northwind.mdb"
    14. End With
    15.  
    16.  
    17. sSQL = "select custid, CompanyName from customers order by CustomerID"
    18.  
    19. Set oRS = New ADODB.Recordset
    20.  
    21. oRS.Open sSQL, oConn, adOpenForwardOnly, adLockReadOnly, adCmdText
    22.  
    23.  
    24. With oRS
    25.  
    26.     If Not (.BOF And .EOF) Then
    27.  
    28.         Do Until .EOF  
    29.             Combo1.AddItem .Fields("CompanyName").Value
    30.             Combo1.ItemData(Combo1.NewIndex) = .Fields("custid").Value
    31.  
    32.     .MoveNext
    33.     Loop
    34.     Else
    35.         MsgBox "No records found"
    36.  
    37.         End If
    38.  
    39. End With
    40.  
    41. CloseRecordset oRS
    42. CloseConnection oConn
    43.  
    44. End Sub
    45.  
    46. Public Sub CloseConnection(oConn As ADODB.Connection)
    47.  
    48.     If Not oConn Is Nothing Then
    49.     If (oConn.State And adStateOpen) = adStateOpen Then
    50.         oConn.Close  
    51.     End If
    52.         Set oConn = Nothing
    53.     End If
    54.  
    55. End Sub
    56.  
    57. Public Sub CloseRecordset(oRS As ADODB.Recordset)
    58.  
    59.     If Not oRS Is Nothing Then
    60.         If (oRS.State And adStateOpen) = adStateOpen Then
    61.         oRS.Close
    62.  
    63.     End If
    64.         Set oRS = Nothing
    65.     End If
    66.  
    67. End Sub
    Last edited by Hack; Apr 24th, 2006 at 12:56 PM. Reason: Added [vbcode] [/vbcode] tags for more clarity.

  6. #6
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: visual basic 6/Access

    Correct, you do not use Set in the declarations section of a module. The only thing in your module should be your Public variable declarations.

    Please note my original post. There is code that goes on FORM1 (which includes the SET command) and code that goes on Form2.

  7. #7
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: visual basic 6/Access

    Moved your variable declarations like this
    VB Code:
    1. 'in a module
    2. Option Explicit
    3.  
    4. Public oConn As ADODB.Connection
    5. Public oRS  As ADODB.Recordset
    6. Public sSQL As String

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Apr 2006
    Posts
    16

    Re: visual basic 6/Access

    Mr. Hack,
    Thank you.

    May be you are going see more often questions from you in this forum.
    Thank you agian.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width