Code works as Query, but not as VBA...?

[Stang70Fastback]

Hello all. Here is another issue I'm having. I have the following in query form:

SELECT *
FROM Timesheets
WHERE Nz(DLookup("Username", "USER", "Badge = " & Nz(DLookup("Manager", "Engineers", "Badge = " & [Badge]), 0)), "NoResult") = fOSUserName();

I realize it's a bit "messy" in how it does what it is doing, but it works. Can someone explain to me why that works perfectly fine when I build a Query in Access, but why this version returns NO records when I try to use it to set a form's recordsource via VBA code?

Me.RecordSource = "SELECT * FROM Timesheets WHERE '" & Nz(DLookup("Username", "USER", "Badge = " & Nz(DLookup("Manager", "Engineers", "Badge = " & [Badge]), 0)), "NoResult") & "' = '" & fOSUserName & "'"

Both run without errors, but one returns 4 records as it is supposed to (the top one, formed as an actual query) whereas the bottom one returns NO records.

I actually built the top one FROM the bottom one to test it as a proper query when I couldn't get it to work, and after removing the various single and double quotes, it simply worked, so I'm at a loss as to why the original one is giving me different, and incorrect, results.

Yes, I know I could just set the QUERY as the RecordSource at this point, but I'm trying to be consistent with my code, and not have one random one that points to an Access Query.

 

[pbaldy]

You can use this technique to see how the finished SQL is coming out.

http://www.baldyweb.com/ImmediateWindow.htm

 

[Stang70Fastback]

Holy $%&# how have I made it this far in life without that glorious snippet of code?!?!

EDIT: Do you know if it's possible to have it iterate the output for each record it looks at in the Timesheets table?

 

[pbaldy]

LOL!

I'm not clear what you mean.

 

[Stang70Fastback]

Like, the resulting SQL statement will look different for every record it looks at. Maybe that's where my problem lies... now that I think about it, I don't think that makes any sense.

For example, for each record in the Timesheets table, I would expect to see:

SELECT * FROM Timesheets WHERE 'mjane' = 'jdoe'
SELECT * FROM Timesheets WHERE 'ofranklin' = 'jdoe'
SELECT * FROM Timesheets WHERE 'mstevens' = 'jdoe'
SELECT * FROM Timesheets WHERE 'jdoe' = 'jdoe' <- this record should come through
SELECT * FROM Timesheets WHERE 'agrace' = 'jdoe'
SELECT * FROM Timesheets WHERE 'plee' = 'jdoe'

I suppose how I have it shown there is not really accurate, as it doesn't run the select statement more than once, but that one section of the statement changes for each record it looks at, as it looks for matches to the WHERE statement. I'm wondering if there is a way to "see into" that process.

 

[pbaldy]

What does the table actually contain? I would have assumed that there's a field with a user name in it, so the criteria would simply be:

WHERE FieldName = fOSUserName()

I'm not clear on the purpose of the domain functions.

 

[Stang70Fastback]

It gets a little bit complicated, but there are several tables involved here for various reasons. Here are the relevant fields:

Table 1: Timesheets

- Employee Badge

Table 2: Employees

- Employee Badge
- Employee's MANAGER'S Badge

Table 3: USERS

- Manager's Badge
- Manager's Windows CORP ACCOUNT username

I realize it's a bit complex, but that's how the tables are laid out. As you might have surmised by now, I'm trying to filter the form so that when a manager looks at it, they see only the timesheets of the employees who have them designated as their manager. So I'm basically trying to bounce through some tables to get the data. The path is something like this:

Timesheets.Badge => Engineers.Badge -> Engineers.ManagerBadge => User.ManagerBadge -> User.Username = fOSUserName

If they match, then the record should be included in the recordsource.

 

[pbaldy]

I need a ride in the stang, because my eyes glazed over trying to follow that.

Is this a test db you can attach here? I'm thinking a query can join those all together so the criteria can be a simple comparison. It would be more efficient anyway.

 

[Stang70Fastback]

If I use a query, can I still edit the data in the form, though? I thought that at that point the data would become read-only?

 

[pbaldy]

Depends on the joins:

http://allenbrowne.com/ser-61.html

 

[Cronk]

So it looks like Employee Badge has the employee's name with sign in times recorded in table 'Timesheets'

'Employees' is the join table linking Employees with managers.

Very convoluted and confusing field naming, however maybe a query like

select timesheet.* from timesheet inner join [Employees] on timesheet..[employee badge] = employees.[Employee's MANAGER'S Badge] where
employees.[Employee's MANAGER'S Badge] ='" & fOSUserName & "'"

will select all employee timesheet records for the manager with fOSUserName