Record Navigation

[EndersG]

Take the following as an example: 10,10,10,10,20,20,30,30,30,40,50....

These #s represent employee id# sorted numerically in ascending order in a query I maintain (QueryA). However, I would like to create command button with VBA code that would allow me to advance to the record which has the next sequence of numbers (i.e., 1st, 5th, 7th, 10th, 11th, etc.). I'd like it to be a Function. I believe I'd probably need a For Next Statement, RecordCount, and a GoTo statement but I don't know how. If there is an easier way than this, please let me know... Thanks.

 

[chrismcbride]

I am not sure this will work, but you might try populating your combo box with something like ....
SELECT DISTINCT emp_no FROM tblEmployee;

 

[EndersG]

Pretty cool. But I need the function to move to the next instance of the new number in sequence. I don't know if this does the trick. I appreciate your tip, though. Thanks.

 

[chrismcbride]

I think I get it. Let us say the we have a bunch of records. They have a Record Id (RID) and a Employee ID (emp_no). You have all these records grouped by emp_no and you want to move from one group of emp_no's to the numeric group of emp_no's, is that right?
If this is so then you forget about Select Distinct. Establish a recordset using the same syntax as your query with the exception that you only select records where the emp_no >= current emp_no. ( you will need an error trap here in case you are at the highest emp_no already) Loop through the records and compare each iteration's emp_no to the current emp_no. Since the emp_nos are grouped together and ordered (Ascending), then the first time you find an emp_no different that the current one you ought to be on the next highest one. Then you can use the Recordsetclone property to move to the record (RID) that you found in the loop. Have I got it right?
Chris

 

[EndersG]

Rightamundo, Chris. Now I just need someone who can help be construct it. Think you're up to the task. Sorry to keep bothering you with this. It's just that I'm at my wits end. Any help is appreciated.