Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Angry Unanswered: Combo Box or Indexing Problems?

    I have a Combo Box on a Form that looks up an ID # and returns the related record information... This problem is strange because 1. when the form is opened, it ALWAYS comes up with the same ID #... ID # 100 is always displayed, if I enter in 33, 44 or 66 or any # below 100 it always comes up with ID 100,,, now I go over 100 up to ID 150, anything over 150 and it will bring up the record it is supposed to display...!!

    ID # is an autonumber and I have reindexed [repair and compacted] the database but still the same problem...

    Here is the code behind the ID # control, CAN ANY ONE HELP!!

    ______________

    Private Sub Find_Book_AfterUpdate()

    Me.RecordsetClone.FindFirst "[Book_ID] = " & Str(Me![Find_Book])
    Me.Bookmark = Me.RecordsetClone.Bookmark
    Me!Find_Book.Requery

    Mainform_Lock True

    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is the datatype of the column you are wanting to retrive. My gues would be that you have a problem with you explicit conversion to string.

    What happens if you debug the SQL statement in your code (IE put a watch / halt condition) and find explcitly what the SQL statement is asking the SQl engine for? That way round you can diagnose whether the SQL engine is returning what it is asked for, or if your SQL query is being set correctly (ie as you expect).

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Well, the ID # is an autonumber...

    I have never set a stopwatch in the VBA code... can you tell me what you mean?

    Thanks

  4. #4
    Join Date
    Nov 2003
    Posts
    300
    I have tried to take a look at the records in the table to see if there is any corrupted fields but the data looks good. I don't understand, it was working fine before in the FORM but now I am having this problem...

    any suggestions???

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Theres a real nifty feature in Access - Press F1, search for 'Watch'

    You can set a breakpoint by clicking to the left of the relevant line of code.

    you can then examine any varaible in that block (or any globals
    you can evaluate any expression by prefixing the exression with a '?' in the immediate window

    I'd set a breakpoint on the " Me.RecordsetClone.FindFirst "[Book_ID] = " & Str(Me![Find_Book])" line

    then type ?"[Book_ID] = " & Str(Me![Find_Book])

  6. #6
    Join Date
    Nov 2003
    Posts
    300
    I tried what you mentioned and it reports a value of 100..


    Like I said before, when I open the form.. ID # 100 is always displayed, it is like that is stuck on the ID 100...

    I can enter in let's say 155 and it opens record 155.. 255 and it opens 255..
    BUT.. on ALL the records below 100, if I put in 3,, it will open ID # 100...

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so when you selected a record that you expected to retrieve a row containing say "3" and got "100" what did the debug on the SQL actually say it was looking for?
    ie did it say
    [Book_ID] = 3
    First off check that you are asking the SQL engine the correct question - SQL ain't psychic - psychotic maybe. If you are asking the correct (expected question) then the next step is to ensure what ther SQL engine is meant to answer with (ie is it the correct record set)

    have you bound the combo box - that to can cause problems

    have you checked to make sure you are using the correct value from the combo / list box

    its possible , but unlikely that the indexes are shot - try a compact and repair

    when you do you the 'find first', what index / order are you using - unless the index / sort order is explicitly defined you may be pulling the first record equal to the specified value

  8. #8
    Join Date
    Nov 2003
    Posts
    300
    so when you selected a record.................................what ther SQL engine is meant to answer with (ie is it the correct record set) ===== I do not think this is the problem here, Like I said, this is an AUTONUMBER, indexed, no duplicates, it is working on the majority of the ID #s. I have 5000 records, and it is giving me problems with ALL the records less than # 100... so if I type/or select 55 from the combo box,, 100 pops up, 22 and 100 pops up again, but if I do 199, then I get 199, 322 and I get 322... There are still some exceptions, if I select a 932 it may still go back to 100...

    I have looked for reasons in the table, in the record of #100 but I am not finding anything out of the norm... I have had problems with some nosey users going in the tables and placing a sort order on the customer name field, when everything should be in the Book_ID ascending order... I don't know how this would affect the form or table...??

    have you bound the combo box - that to can cause problems====no, it is unbound

    have you checked to make sure you are using the correct value from the combo / list box=====yes, it is limited to the list

    its possible , but unlikely that the indexes are shot - try a compact and repair=====yes, did this many times and did not help

    when you do you the 'find first', what index / order are you using - unless the index / sort order is explicitly defined you may be pulling the first record equal to the specified value============now this IS perpexing to me.. when I open the form, it shows the first record or at least it ALWAYS shows the Bokking_ID of #100.. when I created the combo box [ and I did re-create another one, but it still behaved the same way] I asked Access to "remember" the value for later use... maybe I should be asking how I can clear that from memory, or really SHOW the FIRST record in the table on the FORM...??

    Am I missing some other code on the form to clear that value??

    I really appreciate your help with this problem!!!!!---THANKS!!!!

  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Here, I modified it to mimic what my ComboBox is coded as which works fine. Not sure if the "Mainform_Lock True" is the culprit or not as I have never seen that before. Might try commenting that out too just to test and see. So Cut and Paste this code into your ComboBox and see what happens. To keep it easy and not lose your original, just comment out all of the code and paste this into it. Also, what does your Form_Current code have in it? Should be something like Find_Book = Book_ID

    Code:
    Private Sub Find_Book_AfterUpdate()
    
        Dim rs As Object
        
        Set rs = Me.Recordset.Clone     
        rs.FindFirst "[Book_ID] = " & Str(Me![Find_Book])
        Me.Bookmark = rs.Bookmark
        Me!Find_Book.Requery
        
        Mainform_Lock True
           
    End Sub
    see how it goes and have a nice one,
    BUD

Posting Permissions

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