search records from databse failed

[vinanti]

hi there,

I am working in access 2002.
my code is:-
for i=1 to tempcnt
chksav=Dlookup("[invoice no]","mast","[supplier name]'"=tempsupp"'"
msgbox "chksav"
if(chksav=Me.invoice_no)then
Exit For
endif
next
i=i+1
end

in the above code i want to find recrods from my dbase where the supplier name field=the supplier name chosen by the user.but i am able to retrieve the same record every time the loop runs.i have different no. of records satisfy the condition.there are minimum 3 records for every such condition but always only the first find is returned.
could anybody plz. help me.

thnks in advance

bye vinanti

 

[KenReay]

Hi

Yes, that is becuase DLookup finds the first occurence every time,

You need to create a recordset based on your criteria then interate thru the recordset

You do not say which version of access you are using, and/or if you are using ADO or DAO, in DAO it would be

Dim Db as DAO.Database
Dim Rs as DAO.Recordset
'
Set Db = CurrentDb()
Set Rs = db.OpenRecordset("SELECT * FROM mast WHERE [Supplier name] ='" & tempsupp & "' ORDER BY [INVOICE NO];&quot
If Rs.REcordCount > 0 Then
Do Until Rs.EOF
MsgBox Rs![Invoice No]
Rs.MoveNext
Loop
End If
Rs.Close
Set Rs = Nothing
Set Db = Nothing

Another small point - life is easier if you avoid having embedded spaces in column names so InvoiceNo instead of Invoice No.

Hope this helps



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[vinanti]

Hi ken,
thnks for the help but i m new to this concept of recordset and dao or ado and hence unable to understand it..still i m giving it a try,


thnks a lot ;if not solved i shall get back


bye,
vinanti

 

[vinanti]

hey,
it worked wonders too good


thnks a lot
vinanti