Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2005
    Posts
    20

    Unanswered: recordset returns bookmark

    I have a form onopen event that creates an ADO recordset then binds a combobox to that recordset. After the recordset is bound to the combobox i can see the records in the combobox. Problem is I get a new field called bookmark as the first field in my combobox. If I check the number of fields in the recordset I get the correct number which does not include bookmark. I understand what bookmark is, but how do I get rid of it?
    Thanks.

  2. #2
    Join Date
    Mar 2005
    Posts
    20
    Can anyone help me out on this?

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    So - what do you expect of us when you're being so secretive about what you're doing

    We will need to see the code to reproduce the error/give advice. Also a bit more info on the data, both the actual and expected result.
    Roy-Vidar

  4. #4
    Join Date
    Jul 2003
    Posts
    81
    what you chose form.recordset.clone and not a select query for your cbox?

  5. #5
    Join Date
    Mar 2005
    Posts
    20
    Code below.

    When I loop through the recordset and identify the fields returned, I get 8 just as I specified in my sql statement.
    Problem is that after I bind my combobox to the recordset, I get a 9th column which is the bookmark and ends up being the first field in the combobox.

    At the very least I need to be able to make the first column the name field that I returned from the recordset in order for the user to be able to pull up the appropriate entry by starting to type the name.

    Ideally, I would like to get rid of the bookmark altogether.
    Thanks.

    Private Sub Form_Open(Cancel As Integer)

    Dim cnProvList As ADODB.Connection
    Dim rstProvList As ADODB.Recordset
    Dim strProvListConn, strProvListSQL As String

    strProvListConn = "connection string to oracle"
    strProvListSQL = "SELECT distinct 8 fields FROM tables WHERE criteria "

    Set cnProvList = New ADODB.Connection
    cnProvList.Open strProvListConn
    Set rstProvList = New ADODB.Recordset
    rstProvList.Open strProvListSQL, cnProvList, adOpenStatic, , adCmdText

    Set Me.cmbProviderSelect.Recordset = rstProvList

    Set rstProvList = Nothing
    Set cnProvList = Nothing

    End Sub

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I'm sorry, I haven't seen anything like that. Unfortunately, I'm not able to test with Oracle, but with both Jet and SQL, I get results as I should (no extstra field).

    You have set the correct number of columns in the combo? And adjusted the columnwidths accordingly?

    Does it have to be a recordset? Could for instance the following do?
    Code:
    With Me!cmbProviderSelect
        .RowSourceType = "Value list"
        .RowSource = strProvListSQL.GetString(adClipString, , ";", ";")
    End With
    Does a debug.print strProvListSQL.GetString give the original 8 or 9 records?

    There are some limitations, hovewer, on value list. For Access 2000, it's 2048 characters, for later versions, I think it's 32 750.
    Roy-Vidar

  7. #7
    Join Date
    Mar 2005
    Posts
    20
    I did the GetString on the recordset and it returns what it is supposed to. Everything about the recordset checks out.
    I can't use the value list as I have too much data.

    The problem has to be with the binding to the combobox. Maybe someone out there knows why it would add the bookmark to the combobox when binding to an ado recordset.
    It does the same thing when binding to a listbox.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your mystery field is, well, a mystery.

    but this is wrong:
    At the very least I need to be able to make the first column the name field that I returned from the recordset in order for the user to be able to pull up the appropriate entry by starting to type the name.

    it's not the first column that has autocomplete-magic,
    it's not the bound column that has autocomplete-magic
    but it is the first non-zero-width column.

    ...properties/columnwidths 0cm;3cm;3cm etc etc etc

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah yes - I've played with this before. Didn't notice a bookmark though. I confess I didn't look.....

    I notice your recordset cursor is at the server (default).

    What happens if you:
    Code:
    ...
    rstProvList.Open strProvListSQL, cnProvList, adOpenStatic, , adCmdText
    
    rstProvList.CursorLocation = adUseClient
    
    Set Me.cmbProviderSelect.Recordset = rstProvList
    .....


    More info:
    http://msdn.microsoft.com/library/de...tml/sa01l8.asp
    (towards the bottom of the page).

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2005
    Posts
    20
    Quote Originally Posted by izyrider
    your mystery field is, well, a mystery.

    but this is wrong:
    At the very least I need to be able to make the first column the name field that I returned from the recordset in order for the user to be able to pull up the appropriate entry by starting to type the name.

    it's not the first column that has autocomplete-magic,
    it's not the bound column that has autocomplete-magic
    but it is the first non-zero-width column.

    ...properties/columnwidths 0cm;3cm;3cm etc etc etc

    izy
    not sure if we're talking about the same thing...my combobox will begin searching on the first column every time regardless of what column is bound.

  11. #11
    Join Date
    Mar 2005
    Posts
    20

    Thumbs up

    Quote Originally Posted by pootle flump
    Ah yes - I've played with this before. Didn't notice a bookmark though. I confess I didn't look.....

    I notice your recordset cursor is at the server (default).

    What happens if you:
    Code:
    ...
    rstProvList.Open strProvListSQL, cnProvList, adOpenStatic, , adCmdText
    
    rstProvList.CursorLocation = adUseClient
    
    Set Me.cmbProviderSelect.Recordset = rstProvList
    .....


    More info:
    http://msdn.microsoft.com/library/de...tml/sa01l8.asp
    (towards the bottom of the page).

    HTH
    Thanks a million for the suggestion! It worked when I set the cursor location!

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by hayesbcajh
    Thanks a million for the suggestion! It worked when I set the cursor location!
    Glad it worked

    BTW - I think you missed a crucial bit of Izy's post - basically that you could have accounted for this with your combo box settings (although it would have been inelegant) by making the first column width 0cm. This is independent of the bound column and means that the first column returned by the recordset would have been ruled out of the whole equation.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2005
    Posts
    20
    my apologies to izyrider...I was not paying close enough attention.

Posting Permissions

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