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!!
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).
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...
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
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!!!!
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
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