By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,824 Members | 1,592 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,824 IT Pros & Developers. It's quick & easy.

Ms Access-Dlookup next record

P: 41

I have few problem with Dlookup condition. I need to retrieve next record or previous record based on certain condition. The conditions are set in in the combo box. Here, I am trying to display the records in the text box based on my selection. I have given my selection in the combo box.

For example from the student table, I am trying to display as below...

a). Who are all coming from "Newyork" city in the 7th standard class.
b). Who all are having date of birth as "10-JAN-2000" in the 7th standard class
c). Who all are having grade as "Distinction" in the 7th standard class

The fields are City,DtofBirth,Grade in the student table. All these THREE fields are listed each in combo boxes. When i select each combox box, the respective records should display in the text boxes. Assume that there are 10 row in the form. The each row contains 5 text boxes namely Stud_Name,Stud_Eno, Stud_City,Stud_DOB & Stud_Grade.

I have used below Dlookup function in the text box. Assume that the fields types are number.

To retrieve the first record
=DLookUp("[Stud_Name]","Stud_Tbl","[Stud_City]=" & [City] & "And [Stud_DOB]=" & [DOB] & "And [Stud_Grade]=" & [Grade])

To retrieve the next record
=DLookUp("[Stud_Name]","Stud_Tbl","[Stud_City]=" & [City] & "And [Stud_DOB]=" & [DOB] & "And [Stud_Grade]=" & [Grade] +1)

(Added +2,+3 etc., for the next records in the rest of text boxes.)

The problems are..
a). The first problem is that it display the records irrelevantly for few condition, like when i change the city from "Newyork to Texas" it shows irrelevant records.

b). The 2nd record doesn't display in the next text box. It display in the 3rd text box. The 2nd textbox is showing empty.

c). When the form load, the text boxes shows "#Error" as no values selected in the combo boxes - How can I avoid this error message.

What is the problem over here..? ..Is there anything wrong in the way i try to display the records. Is there anyother way to do like this...?

I guess since the records are stored randomly it gives the problem. But, no other way as the record will be stores always random only.

I have spent more time for this. But, not able to find out the solutions.

I WOULD BE VERY HAPPY if anyone helps me to find solutions.

Your solution would be HIGHLY APPRECIATED.

Thanks in advance.

Jun 22 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.