What wrong is it? MN 11/25/2008 8:45:01 PM Hi - Please help with this: I have a form open, when the user click button search for a Lastname in in the link table dbo_A. the problem is: it's alway FOUND the record, but actually it doesn't. Set RsA = CurrentDb.OpenRecordset("dbo_A", dbOpenDynaset, dbSeeChanges) With RsA If Not IsNull(Me.lname) Then strWhere = strWhere & "([lname] = """ & Trim(Me.lname) & """) " End If .FindFirst strWhere If .NoMatch Then MsgBox "NOT-FOUND" Else MsgBox "FOUND!" End If Debug.Print strWhere End With

RE: What wrong is it? Beetle 11/25/2008 8:55:01 PM If you're getting the message box "FOUND", then it did find a matching last name. If you mean that your form is not moving to the matching record, that's because you are not telling it to do so in your code. You need to use the Bookmark property to move the form to the appropriate record; Set RsA = CurrentDb.OpenRecordset("dbo_A", dbOpenDynaset, dbSeeChanges) With RsA If Not IsNull(Me.lname) Then strWhere = strWhere & "([lname] = """ & Trim(Me.lname) & """) " End If .FindFirst strWhere If .NoMatch Then MsgBox "NOT-FOUND" Else MsgBox "FOUND!" Me.Bookmark = .Bookmark End If Debug.Print strWhere End With -- _________ Sean Bailey "MN" wrote: [Quoted Text] > Hi - Please help with this: > I have a form open, when the user click button search for a Lastname in in > the link table dbo_A. the problem is: it's alway FOUND the record, but > actually it doesn't. > > Set RsA = CurrentDb.OpenRecordset("dbo_A", dbOpenDynaset, dbSeeChanges) > With RsA > If Not IsNull(Me.lname) Then > strWhere = strWhere & "([lname] = """ & Trim(Me.lname) & """) " > End If > .FindFirst strWhere > If .NoMatch Then > MsgBox "NOT-FOUND" > Else > MsgBox "FOUND!" > End If > Debug.Print strWhere > > End With

RE: What wrong is it? Beetle 11/25/2008 8:59:06 PM Actually, my other post may not be relevant because I was thinking of using a RecordsetClone, which you are not doing. What do you want to happen when the match is found? -- _________ Sean Bailey "MN" wrote: [Quoted Text] > Hi - Please help with this: > I have a form open, when the user click button search for a Lastname in in > the link table dbo_A. the problem is: it's alway FOUND the record, but > actually it doesn't. > > Set RsA = CurrentDb.OpenRecordset("dbo_A", dbOpenDynaset, dbSeeChanges) > With RsA > If Not IsNull(Me.lname) Then > strWhere = strWhere & "([lname] = """ & Trim(Me.lname) & """) " > End If > .FindFirst strWhere > If .NoMatch Then > MsgBox "NOT-FOUND" > Else > MsgBox "FOUND!" > End If > Debug.Print strWhere > > End With

RE: What wrong is it? MN 11/25/2008 9:15:01 PM Hi Beetle, - Verify: The form open with a different records set (RsB) - The problem is there was no record with same that lastname in RsA ? - Using .bookmark it hung up :-( - If the match FOUND then I open another form for that record. Thanks for reply. MN "Beetle" wrote: [Quoted Text] > Actually, my other post may not be relevant because I was thinking of > using a RecordsetClone, which you are not doing. > What do you want to happen when the match is found? > -- > _________ > > Sean Bailey > > > "MN" wrote: > > > Hi - Please help with this: > > I have a form open, when the user click button search for a Lastname in in > > the link table dbo_A. the problem is: it's alway FOUND the record, but > > actually it doesn't. > > > > Set RsA = CurrentDb.OpenRecordset("dbo_A", dbOpenDynaset, dbSeeChanges) > > With RsA > > If Not IsNull(Me.lname) Then > > strWhere = strWhere & "([lname] = """ & Trim(Me.lname) & """) " > > End If > > .FindFirst strWhere > > If .NoMatch Then > > MsgBox "NOT-FOUND" > > Else > > MsgBox "FOUND!" > > End If > > Debug.Print strWhere > > > > End With

RE: What wrong is it? Beetle 11/25/2008 10:54:01 PM [Quoted Text] > - Using .bookmark it hung up :-( I realized that wasn't going to work right after I sent the first post, that's why I reposted. Are you saying that it ALWAYS returns the message box FOUND, even when you know there is no matching name in RsA? -- _________ Sean Bailey "MN" wrote: > Hi Beetle, > - Verify: The form open with a different records set (RsB) > - The problem is there was no record with same that lastname in RsA ? > - Using .bookmark it hung up :-( > - If the match FOUND then I open another form for that record. > Thanks for reply. > MN > > "Beetle" wrote: > > Actually, my other post may not be relevant because I was thinking of > > using a RecordsetClone, which you are not doing. > > What do you want to happen when the match is found? > > -- > > _________ > > > > Sean Bailey > > > > > > "MN" wrote: > > > > > Hi - Please help with this: > > > I have a form open, when the user click button search for a Lastname in in > > > the link table dbo_A. the problem is: it's alway FOUND the record, but > > > actually it doesn't. > > > > > > Set RsA = CurrentDb.OpenRecordset("dbo_A", dbOpenDynaset, dbSeeChanges) > > > With RsA > > > If Not IsNull(Me.lname) Then > > > strWhere = strWhere & "([lname] = """ & Trim(Me.lname) & """) " > > > End If > > > .FindFirst strWhere > > > If .NoMatch Then > > > MsgBox "NOT-FOUND" > > > Else > > > MsgBox "FOUND!" > > > End If > > > Debug.Print strWhere > > > > > > End With

RE: What wrong is it? MN 11/26/2008 1:45:02 PM Hi Beetle - Thanks for reply. Yes, ... I trying to use diferrent way ??? Now I am stuck - Help Please. MN "Beetle" wrote: [Quoted Text] > > - Using .bookmark it hung up :-( > > I realized that wasn't going to work right after I sent the first post, that's > why I reposted. > > Are you saying that it ALWAYS returns the message box FOUND, even > when you know there is no matching name in RsA? > > > -- > _________ > > Sean Bailey > > > "MN" wrote: > > > Hi Beetle, > > - Verify: The form open with a different records set (RsB) > > - The problem is there was no record with same that lastname in RsA ? > > - Using .bookmark it hung up :-( > > - If the match FOUND then I open another form for that record. > > Thanks for reply. > > MN > > > > "Beetle" wrote: > > > Actually, my other post may not be relevant because I was thinking of > > > using a RecordsetClone, which you are not doing. > > > What do you want to happen when the match is found? > > > -- > > > _________ > > > > > > Sean Bailey > > > > > > > > > "MN" wrote: > > > > > > > Hi - Please help with this: > > > > I have a form open, when the user click button search for a Lastname in in > > > > the link table dbo_A. the problem is: it's alway FOUND the record, but > > > > actually it doesn't. > > > > > > > > Set RsA = CurrentDb.OpenRecordset("dbo_A", dbOpenDynaset, dbSeeChanges) > > > > With RsA > > > > If Not IsNull(Me.lname) Then > > > > strWhere = strWhere & "([lname] = """ & Trim(Me.lname) & """) " > > > > End If > > > > .FindFirst strWhere > > > > If .NoMatch Then > > > > MsgBox "NOT-FOUND" > > > > Else > > > > MsgBox "FOUND!" > > > > End If > > > > Debug.Print strWhere > > > > > > > > End With

RE: What wrong is it? "tkelley via AccessMonster.com" <u47368[ at ]uwe> 11/26/2008 2:14:52 PM If you step through the code and do: debug.print RSA!lname (that's a ! in there ... hard to see) debug.print strWhere ... after your: .FindFirst strWhere ... what does it say? MN wrote: [Quoted Text] >Hi Beetle - Thanks for reply. >Yes, ... I trying to use diferrent way ??? Now I am stuck - Help Please. >MN > >> > - Using .bookmark it hung up :-( >> >[quoted text clipped - 35 lines] >> > > > >> > > > End With -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1

RE: What wrong is it? Beetle 11/26/2008 6:18:14 PM I don't have an answer as to why it appears to find a match even when you know there isn't on. I'm not sure how that woul be possible. If you can post some more details about what exactly you want to do, including the relevant tables, forms, etc. someone may be able to give you advice on what to do. -- _________ Sean Bailey "MN" wrote: [Quoted Text] > Hi Beetle - Thanks for reply. > Yes, ... I trying to use diferrent way ??? Now I am stuck - Help Please. > MN > > "Beetle" wrote: > > > > - Using .bookmark it hung up :-( > > > > I realized that wasn't going to work right after I sent the first post, that's > > why I reposted. > > > > Are you saying that it ALWAYS returns the message box FOUND, even > > when you know there is no matching name in RsA? > > > > > > -- > > _________ > > > > Sean Bailey > > > > > > "MN" wrote: > > > > > Hi Beetle, > > > - Verify: The form open with a different records set (RsB) > > > - The problem is there was no record with same that lastname in RsA ? > > > - Using .bookmark it hung up :-( > > > - If the match FOUND then I open another form for that record. > > > Thanks for reply. > > > MN > > > > > > "Beetle" wrote: > > > > Actually, my other post may not be relevant because I was thinking of > > > > using a RecordsetClone, which you are not doing. > > > > What do you want to happen when the match is found? > > > > -- > > > > _________ > > > > > > > > Sean Bailey > > > > > > > > > > > > "MN" wrote: > > > > > > > > > Hi - Please help with this: > > > > > I have a form open, when the user click button search for a Lastname in in > > > > > the link table dbo_A. the problem is: it's alway FOUND the record, but > > > > > actually it doesn't. > > > > > > > > > > Set RsA = CurrentDb.OpenRecordset("dbo_A", dbOpenDynaset, dbSeeChanges) > > > > > With RsA > > > > > If Not IsNull(Me.lname) Then > > > > > strWhere = strWhere & "([lname] = """ & Trim(Me.lname) & """) " > > > > > End If > > > > > .FindFirst strWhere > > > > > If .NoMatch Then > > > > > MsgBox "NOT-FOUND" > > > > > Else > > > > > MsgBox "FOUND!" > > > > > End If > > > > > Debug.Print strWhere > > > > > > > > > > End With

Re: What wrong is it? "Mike Painter" <mddotpainter[ at ]sbcglobal.net> 11/26/2008 6:59:47 PM MN wrote: [Quoted Text] > Hi Beetle - Thanks for reply. > Yes, ... I trying to use diferrent way ??? Now I am stuck - Help > Please. MN > > "Beetle" wrote: > >>> - Using .bookmark it hung up :-( >> >> I realized that wasn't going to work right after I sent the first >> post, that's why I reposted. >> >> Are you saying that it ALWAYS returns the message box FOUND, even >> when you know there is no matching name in RsA? <snip> What is StrWhere equal to at this point? >>>>> If Not IsNull(Me.lname) Then >>>>> strWhere = strWhere & "([lname] = """ & Trim(Me.lname) & >>>>> """) " End If If lname is null at this point then "([lname] = """ & Trim(Me.lname) does not get appended and strWhere is ???? what? It might just be returning the first record so found will always be returend >>>>> .FindFirst strWhere >>>>> If .NoMatch Then >>>>> MsgBox "NOT-FOUND" >>>>> Else >>>>> MsgBox "FOUND!" >>>>> End If >>>>> Debug.Print strWhere >>>>> >>>>> End With

Re: What wrong is it? Steve Sanford 11/26/2008 9:24:10 PM I didn't find anything wrong with the code. I put a button on a form (DoSearch) and a text box (lname). I pasted in the code from MN's first post. The only things I changed was the table name - "dbo_A" to "Table1" and the name of the field - "([lname] to "([last_name] . If I type in "George' or "Matt" (no quotes), I get FOUND! If I type in "qwerty" I get NOT-FOUND. If I leave the text box blank (NULL), I get an error. So the code seems to be OK. But since I can't leave well enough alone, I changed the code a little........ :D '----------------------------- Private Sub DoSearch_Click() Dim RsA As DAO.Recordset Dim strWhere As String Set RsA = CurrentDb.OpenRecordset("dbo_A", dbOpenDynaset, dbSeeChanges) With RsA If Len(Me.lname & "") = 0 Then MsgBox "Please enter a name" Exit Sub End If strWhere = strWhere & "([lname] = """ & Trim(Me.lname) & """) " .FindFirst strWhere If .NoMatch Then MsgBox "----NOT-FOUND----" Else MsgBox "FOUND!" End If Debug.Print strWhere End With End Sub '----------------------------- HTH -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Mike Painter" wrote: [Quoted Text] > > > MN wrote: > > Hi Beetle - Thanks for reply. > > Yes, ... I trying to use diferrent way ??? Now I am stuck - Help > > Please. MN > > > > "Beetle" wrote: > > > >>> - Using .bookmark it hung up :-( > >> > >> I realized that wasn't going to work right after I sent the first > >> post, that's why I reposted. > >> > >> Are you saying that it ALWAYS returns the message box FOUND, even > >> when you know there is no matching name in RsA? > <snip> > What is StrWhere equal to at this point? > > >>>>> If Not IsNull(Me.lname) Then > >>>>> strWhere = strWhere & "([lname] = """ & Trim(Me.lname) & > >>>>> """) " End If > > If lname is null at this point then "([lname] = """ & Trim(Me.lname) does > not get appended and strWhere is ???? what? > It might just be returning the first record so found will always be returend > > >>>>> .FindFirst strWhere > >>>>> If .NoMatch Then > >>>>> MsgBox "NOT-FOUND" > >>>>> Else > >>>>> MsgBox "FOUND!" > >>>>> End If > >>>>> Debug.Print strWhere > >>>>> > >>>>> End With > > >