Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using DataCombo to Navigate Records (VB 6 SP5)

    Hello all,

    I'm using a few ADO data connections on a form in VB. I would like to use a DataCombo control to navigate though the records of a datasource, in addition to using the ADO object. My efforts so far have just led me to inadvertently change one field of my record.

    How can I use the DataCombo control to navigate through the records of a datasource?

    Thanks in advance,
    Rex

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using DataCombo to Navigate Records (VB 6 SP5)

    The DataCombo is normally bound to an ADO DataControl or at least to a data environment. So how are you trying to navigate records using the datacombo?
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using DataCombo to Navigate Records (VB 6 SP5)

    Charlotte,

    Thanks for the fast response.

    I'd like to have the user select a name from the DataCombo and then move have the form respond to that selection. Say I have a the DataCombo list names on a form with addresses. By picking a name on the list, I want the form to show the proper address.

    Thanks,
    Rex

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using DataCombo to Navigate Records (VB 6 SP5)

    So what are your other controls bound to, or are you using an unbound form? The fact that you originally said you had wound up changing a field of the record suggests that you bound the DataCombo, but you don't use a bound combo for the kind of operation you're describing.

    You need a combo that has a hidden column with a unique key in it along with a visible column that displays something user friendly like a customer name, but the combo should not be bound to a field in the underlying recordset. otherwise, when the user makes a selection, they will also enter that value in to the current record--not what you want to do.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using DataCombo to Navigate Records (VB 6 SP5)

    Ok,

    I think I understand how to make an unbound combo box to list the names. Once I get the name in the combo box, what is the language I can attach to the change event to navigate to that record? You'll have to forgive me. I'm pretty new to programming with DB's.

    Thanks,
    Rex

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using DataCombo to Navigate Records (VB 6 SP5)

    Are you sure you want to use the Change event? That fires on every keystroke. I'd suggest you wait until they finish typing something in and then use the Validate event. You didn't answer the question about whether the form was bound to a datasource or not. That determines the code you use to populate it.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using DataCombo to Navigate Records (VB 6 SP5)

    Charlotte,

    Thanks for the validate tip. I'll take a look at figuring out how that looks.

    To answer the question, the entire form isn't bound to a datasource. Controls on the form bind to an ADO control on the form.

    Thanks,
    Rex

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using DataCombo to Navigate Records (VB 6 SP5)

    Actually, that's what I was asking. The only way you *can* bind a form in VB is using a DataControl or ADO DataControl or a data environment and binding the controls individually (One of the many reasons I prefer Access when working with databases).

    By the way, I mislead you by referring to the Validate event. If was thinking of values being typed in, but if you're just using the dropdown, the Change event is the correct one to use.

    Here's a rough event procedure that will help you get started. In this case ADODat is the name of the ADO Datacontrol and dcFind is the name of the DataCombo on the form. What you're doing is telling VB to search through the datacontrol's recordset for a record with a key matching the one selected in the DataCombo.

    <pre>Private Sub dcFind_Change()
    Dim rst As ADODB.Recordset
    Set rst = ADODat.Recordset
    With rst
    .Find "[CustomerID]=" & dcFind.Text
    If .EOF Then
    MsgBox "Record not found"
    End If '.EOF
    End With
    Set rst = Nothing
    End Sub 'dcFind_Change()</pre>


    You should be aware though, that Find is essentially a findnext, so if you have multiple values scattered throughout the recordset, you'll have to take other steps to find a specific record.
    Charlotte

  9. #9
    New Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using DataCombo to Navigate Records (VB 6 SP5)

    Thanks Charlotte,

    Exactly what I was looking for!

    Rex

  10. #10
    New Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using DataCombo to Navigate Records (VB 6 SP5)

    Charlotte,

    Everything looked good, but I'm now receiving the error "3001 Arguments are of the wrong type, out of acceptable range, or are in conflict with each other." When I hit debug, it flags the .find line. Any suggestions?

    'establist the connection

    Dim rst as ADODB.Recordset

    Set rst = adoVampire.Recordset

    With rst
    .Find "[Name]=" & cboName.Text
    If .EOF Then
    MsgBox "Record not found"
    End If
    End With

    Thanks in advance

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using DataCombo to Navigate Records (VB 6 SP5)

    If the Name field is text (and by the way, Name is an established property name in Access, so I'd suggest you avoid it as a field name), then you need to wrap the value in delimiters. Try it this way:

    <pre> .Find "[Name]='" & cboName.Text & "'"</pre>

    Charlotte

  12. #12
    New Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using DataCombo to Navigate Records (VB 6 SP5)

    Thanks again Charlotte!

    I' m now searching using a combo box, but.... the search is only going one way. Outside of using movefirst, how can I start searching from the first record each time. Could I use the Start argument? If so, what's the syntax?

    Thanks,
    Rex

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using DataCombo to Navigate Records (VB 6 SP5)

    You can, but if you have multiple instances of the same value in the data, all you'll ever get is the first one. Is that what you want? Using .MoveFirst *is* the way to start from the first record each time.

    Interestingly enough, the VB help from VB doesn't give an example of the usage, but the VB help from Access does, although it's oriented towards a SQL Server provider.
    <hr>Public Sub Main()
    FindX
    End Sub

    Public Sub FindX()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim mark As Variant
    Dim count As Integer

    count = 0
    cnn.Open "DSN=Pubs; Provider=MSDASQL; uid=sa; pwd=;"
    rst.Open "SELECT title_id FROM titles", cnn, _
    adOpenStatic, adLockReadOnly, adCmdText

    ' The default parameters are sufficient to search forward
    ' through a Recordset.

    rst.Find "title_id LIKE 'BU%'"

    ' Skip the current record to avoid finding the same row repeatedly.
    ' The bookmark is redundant because Find searches from the current
    ' position.

    Do While rst.EOF <> True 'Continue if last find succeeded.
    Debug.Print "Title ID: "; rst!title_id
    count = count + 1 'Count the last title found.
    mark = rst.Bookmark 'Note current position.
    rst.Find "title_id LIKE 'BU%'", 1, adSearchForward, mark
    Loop

    rst.Close
    cnn.Close
    Debug.Print "The number of business titles is " & count

    End Sub<hr>
    Hope that helps.
    Charlotte

Posting Permissions

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