472,146 Members | 1,267 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,146 software developers and data experts.

Ms Access-Dlookup next record


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
0 2403

Post your reply

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

Similar topics

63 posts views Thread by Jerome | last post: by
13 posts views Thread by Simon Bailey | last post: by
92 posts views Thread by Jeffrey P via AccessMonster.com | last post: by
17 posts views Thread by Mell via AccessMonster.com | last post: by
37 posts views Thread by jasmith | last post: by
reply views Thread by Saiars | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.