What wrong is it?

  • Thread starter Thread starter MN
  • Start date Start date
M

MN

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
 
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
 
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?
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
- 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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Hi Beetle - Thanks for reply.
Yes, ... I trying to use diferrent way ??? Now I am stuck - Help Please.
MN

Beetle said:
- Using .bookmark it hung up :-(
Click to expand...

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 said:
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 said:
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


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Hi Beetle - Thanks for reply.
Yes, ... I trying to use diferrent way ??? Now I am stuck - Help Please.
MN

Beetle said:
- Using .bookmark it hung up :-(
Click to expand...

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 said:
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

:
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


:

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
Click to expand...
Click to expand...
Click to expand...
 
MN said:
Hi Beetle - Thanks for reply.
Yes, ... I trying to use diferrent way ??? Now I am stuck - Help
Please. MN

Beetle said:
- Using .bookmark it hung up :-(
Click to expand...

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?
If Not IsNull(Me.lname) Then
strWhere = strWhere & "([lname] = """ & Trim(Me.lname) &
""") " End If
Click to expand...
Click to expand...
Click to expand...

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
 
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 said:
Hi Beetle - Thanks for reply.
Yes, ... I trying to use diferrent way ??? Now I am stuck - Help
Please. MN

Beetle said:
- 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?
If Not IsNull(Me.lname) Then
strWhere = strWhere & "([lname] = """ & Trim(Me.lname) &
""") " End If
Click to expand...
Click to expand...

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
Click to expand...
 
Back
Top