Full Version: rs.findFirst combobox not working
UtterAccess Forums > Microsoft® Access > Access Forms
trapperalexander
I used the combobox wizard to create a 'third option' combo box that will find a record on my form. The combobox has worked fine for a few months, but now it only works sometimes. I have checked the recordset of the form and the combobox and both are the same. The error I am getting that only happens on the same certain records is "No Current Record". I open the debug window and the highlighted code is "Me.Bookmark = rs.Bookmark". the whole code line is not highlighted, only this portion. I have compacted and repaired and used the wizard to create a new combo to no avail. My only thought was to check the data in the table and compare between the records that work and the ones that dont work, but I cant figure it out. I have copied an entire record that works and pasted into a new record in the table and the new record doesnt work. any ideas?
Here is the code:
CODE
  
Private Sub Combo192_AfterUpdate()
    ' Find the record that matches the control.
    
    Dim rs As Object
    
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[LoanID] = " & Str(Nz(Me![Combo192], 0))
    If Not rs.EOF Then [color="red"] Me.Bookmark = rs.Bookmark [/color]
  
    
End Sub

Thanks in Advance.
Alan_G
Hi Trapper
If the procedure has worked OK for a few months it would suggest that it's something to do with the data, but if a record that worked OK suddenly doesn't when it's been pasted as a new record that kind of stops that suggestion dead in it's tracks.
Not sure if this will help or not, but maybe try (assuming LoanID is numeric)
CODE
Private Sub Combo192_AfterUpdate()    
' Find the record that matches the control.        
Dim rs As Object        
Set rs = Me.RecordsetClone    
rs.FindFirst "[LoanID] = " & Nz(Me![Combo192], 0)    
If Not rs.NoMatch Then  
   Me.Bookmark = rs.Bookmark
   Else
   MsgBox "No current record"
End If
rs.Close
Set rs = Nothing
End Sub
datAdrenaline
You are using the incorrect method to check for a successful match. You are using a DAO recordset (as indicated by the use of FindFirst) and the proper method for varifying a match is with the .NoMatch property, just as Alan has indicated to do. In addition, there really is no need to declare an object variable as you have done. Please note that if you learned this technique from using a combo box wizard to find a record (which I assume you did since it is virtually identical to the wizard code), the code generated by the wizard is in error (this has been a bug since A2000) as the wizard tries mix the use of .FindFirst and .EOF to validate a successful find, which is completely invalid.
Assuming LoanID is a Numeric datatype, like Number/Long or Autonumber (which is Number/Long too)}
CODE
Private Sub Combo192_AfterUpdate()
' Find the record that matches the control.
    With Me.RecordsetClone
        .FindFirst "[LoanID] = 0" & Me.Combo192  'Fancy way to coerce Nulls to 0
        If Not .NoMatch Then  Me.Bookmark = .Bookmark
    End With
End Sub
jurotek
Hi,
Here's yet another variation that I use in my apps.
im rst As Recordset
Dim strMyName As String
Set rst = Me.RecordsetClone
strMyName = Str(Me!MyCombo)
rst.FindFirst "MyPK = " & strMyName
Me.Bookmark = rst.Bookmark
datAdrenaline
Hello Juro! ....
The caution I want to toss out with your code is the fact that you do not verify that you have a match, and without the verification, if your DON'T have a match, you are setting your forms bookmark to an unknown record, which is not so good.
jurotek
Hi Brent,
Yes and you right. But I never thought there was a need for If Not .NoMatch .....Then Call MsgBox ...
since the recorsource for the cbo comes via qry from underlying table which is also a source for frm Records
there will always be a match no matter what user selects.
Is true that user can type something which is not on the list and click enter, but in this case we still get default prompt of incorrect selection.
Since the bookmark property is only valid for the duration when form is opened and are not saved then I see not a really big problem with that.



I think there were few time when I also recommended using DoCmd Object with AfterUpdate for quick and simple find record like:
DoCmd.GoToControl "MyPK"
DoCmd.FindRecord cboMyCombo
DoCmd.GoToControl "MyPK"
Me.cboMyCombo = Empty

Edited by: jurotek on Sun Aug 16 1:54:03 EDT 2009.
datAdrenaline
Hello Juro ...
>> But I never thought there was a need for If Not .NoMatch .....Then Call MsgBox ... <<
No need for a message box, just a branch to bypass the setting of the forms bookmark.
strMyName = Str(Me!MyCombo)
rst.FindFirst "MyPK = " & strMyName
If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark
>> there will always be a match no matter what user selects. <<
What about a Null (blank) ... in the case of Null you will error out .FindFirst in your code, so you should account for that too smirk.gif ... but if you accounted for Null in the .FindFirst expression, then the setting of forms bookmark will still execute, which will set the forms bookmark to an unknown record simply because the find will probably fail.
>> Since the bookmark property is only valid for the duration when form is opened and are not saved then I see not a really big problem with that. <<
I am unsure of how comment plays into the thought that .NoMatch is not needed dazed.gif ... EVERY record is assigned a Bookmark value (its unique and system assigned --- an temporary). Setting a forms bookmark property will navigate the form to the record that has that bookmark value, if you are navigating to an unknown record, that can be VERY misleading to the user.
jurotek
Hi Brent,
I will not disagree with anything you stated my friend. I just didn't experience any of the problems yet you mentioned so your advice will certainly be counted and considered. thumbup.gif
datAdrenaline
Hey Juro ...
thumbup.gif ... Always good to chat with you, and join in the threads!
>> I just didn't experience any of the problems yet you mentioned <<
Its the rare things that jab you in the side when you least expect it!!! ...
... FYI ... I used to NOT check for .NoMatch too ... for the same reason ---- the user could not pick an invalid value smirk.gif ---- but, the Null and records getting deleted in the multi-user scenario proved the impossible was possible ...
trapperalexander
Thanks guys for all of the help. Very interesting stuff that I have not heard about before.
dissected further into my form and subforms and I found that my real problem was elsewhere. I recently converted a pop-up form to a subform without changing any code; within the subform in the OnClose event I was re-querying the parent form. I deleted this line of code and now the combobox works great for all records. Not sure why the subform's close event was firing upon switching parent records...or why it would cause an error? I am sure someone can explain why the problem was occurring.
thanks again. thumbup.gif
datAdrenaline
>> I dissected further into my form and subforms and I found that my real problem was elsewhere <<
reat! thumbup.gif ... but a word of warning .... if you have not changed your code in response to the AfterUpdate of the combo to use .NoMatch instead of .EOF, you're code is incorrect and can/will produce errors for the end user.
It would be my thought that changing to .NoMatch would address the issue you experienced with the close event.
>> Not sure why the subform's close event was firing upon switching parent records... <<
... I think I would probably need a lot more info to figure that out ... dazed.gif ...
trapperalexander
Brent,
per new insight in this post I have changed all of my 'find record' combo boxes in all of my databases to the 'correct' code that you posted... it has created quite a workload for me doing all of this and sending out updates to all of my clients. But I am very thankful for this as I would have never known until the dreadful day I get the 'its not working' telephone call. notworthy.gif
>>>> Not sure why the subform's close event was firing upon switching parent records... <<<<
>>... I think I would probably need a lot more info to figure that out ... ...<<
the subform used to be a pop-up form, so in the OnClose of the pop-up i had
code:
------------------
forms!formName.requery
------------------
Osimply added this pop-up as a subform to the parent. the combo on the parent form started not working correctly. When I deleted this code, the combo on the parent worked correctly. I havent looked into the data but I would assume that the 'certain records' that were causing the error did not have child records in the subform, causing it to 'close' and the event to fire, causing the error. Not sure why it would cause an error tho. oh well...
Thanks again!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.