filtering a form

[Guest]

I have a form where the user enters new purchase orders. Since clients send
lots of modifications for existing orders, PO numbers have to be checked
whether already in the table or not. I wrote this code:

Private Sub PONumber_AfterUpdate()

'checks whether purchase order really new or not
'if not opens old record for modification

Dim db As Database
Dim rs As Recordset
Dim cond As String
Dim Msg, Style, Title, Response

'opens table
Set db = CurrentDb
Set rs = db.OpenRecordset("PurchaseOrders", dbOpenDynaset)

'gives the condition for the search which is the po number the user entered
cond = Me.PONumber

'sets the message
Msg = "This purchase order number is already registered. Do you wish to
modifiy the record?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Existing order number"

'searches for the order number entered
rs.MoveFirst
Do Until rs.EOF
If rs!PONumber = cond Then
Response = MsgBox(Msg, Style, Title)
If Response = 6 Then 'user pushed yes
DoCmd.RunCommand acCmdUndo 'cancels the number entered

DoCmd.ApplyFilter , "PONumber = cond" 'displays old record

etc.
The problem is that instead displaying the existing record a panel pops up
and asks the user to enter the parameter cond. While debugging it became
clear that the code knows the value of cond.

What can be wrong?
Thanks in advance for your help

 

[Guest]

You messed up and included your variable in the quotes... it should be;

DoCmd.ApplyFilter , "PONumber = '" & cond & "'"

 

[Rob Oldfield]

If PONumber is numeric then

DoCmd.ApplyFilter , "PONumber ="& cond

and if text then

DoCmd.ApplyFilter , "PONumber ='"& cond &"'"

A couple of other points... there's no point setting msg, style and title
until you know you need them. Just slightly inefficient. Better to do it
just before the Response = line.

Looping through all the records is definitely very inefficient. Better to
use rs.findfirst...

with rs
.movelast
.findfirst "PONumber ='"& cond &"'"
if .nomatch then
'do whatever
end if
end with

....and you could also just use a dlookup..

if dcount("[PKField]","TableOrQuery","PONumber ='"& cond &"'")>0 then

 

[Guest]

Thanks, it works
Would you mind explaining why so many quotes? If cond was a date should I
use # instead of &?

 

[Rob Oldfield]

Nope. If it was a date then you'd use a # instead of '

Numeric field... "Field=1"
Text field.... "Field='whatever'"
Date field.... "Field=#1/1/5#"

 

[Guest]

Thanks Rob. Numeric and text is clear now although I do not understand why to
use that much quotes.

I rewrote the code as you suggested and it works fine. Thanx indeed.