Filtering blocks 1 single record

[Guest]

I have a form opened by the switchboard with openargs setting a filter on the records. I'm using 'CurrentUser' to identify the employee logged in to my front end. (Yes there is a separate back end.

The form shows employees only those "Work Orders" assigned to them in a join table with their employee ID and the "Work Order" number

One employee has 3 "Work Orders" assigned to him and views all of them fine. Similarly, for another employee who has 6. But one employee has only 1 "Work Order" assigned to him and the form opens blank,ie no records and no navigation buttons are shown. If he turns filtering off, then he can see all records

How can I avoid this problem with other new users who may have only 1 "Work Order" assigned to them?

 

[Douglas J. Steele]

This doesn't sound right.

Are you sure there's nothing unusual about the record with his ID? (For that
matter, are you sure CurrentUser is returning the correct ID for him?)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I have a form opened by the switchboard with openargs setting a filter on

the records. I'm using 'CurrentUser' to identify the employee logged in to
my front end. (Yes there is a separate back end.)

The form shows employees only those "Work Orders" assigned to them in a

join table with their employee ID and the "Work Order" number.

One employee has 3 "Work Orders" assigned to him and views all of them

fine. Similarly, for another employee who has 6. But one employee has only
1 "Work Order" assigned to him and the form opens blank,ie no records and no
navigation buttons are shown. If he turns filtering off, then he can see
all records.

How can I avoid this problem with other new users who may have only 1

"Work Order" assigned to them?

 

[Guest]

Ok

I did some checking and found out the Employee table has a missing Employee ID number in an otherwise sequential autonumber field. (The employee with only one record is number 24; after he was added I deleted the entry for employee with ID #23)

I tested out the front end by having it report back to me the ID it found for the employee who logged in. This worked fine; it reports back ID #24

As I'm writing I recall my code for checking who is logged in cycles through the table of employees trying out each one for a match with "CurrentUser". Mabe my code doesn't allow for gaps in the sequence of Employee ID numbers. I'll be right back....

 

[Adrian Jansen]

While your code is ok, there are MUCH faster ways of doing searches than
this. If you open a recordset on the table, and do a 'findfirst', you will
get the matching record virtually instantly, and/or set the 'nomatch' flag
if it doesnt exist.

Look up the relevant terms in help.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

This following is tested and works fine on any employee. I use loops so

much I take it for granted they can be picky.

empindex = 0

Do
empindex = empindex + 1
guyid = LCase(DLookup("[LAST NAME]", "[EMPLOYEES]", "[EMPLOYEE ID]=" &

empindex)) & LCase(DLookup("[FIRST NAME]", "[EMPLOYEES]", "[EMPLOYEE ID]=" &
empindex))