query in navigation form and subform to display results

[cstickman]

Happy Superbowl Sunday everyone!!

I have a navigation form and on one of the forms within the navigation form is a subform. I was asked if I could have that subform which is based off of a query display only the results back specific to that user. The form that is attached to the navigation form has hidden tracking fields on it for auditing. One of the fields is txtuser which is populated by a get username function. That works fine and the query runs fine with no criteria from the user. What I am trying to accomplish is since the table has the user who created the record, the manager of the user and then the senior manager of the user. The manager and senior manager can make changes to the user they manage. So the navigation form is called Start Here, the main form is called update calendar and then subform is called frmcalendarSub. Below is my query:

 SELECT travelcalendar.ID, travelcalendar.auditid, travelcalendar.closingcall, travelcalendar.finalreport, travelcalendar.docfiling, travelcalendar.changedby
FROM travelcalendar
WHERE (((travelcalendar.changedby)=[Forms]![Start Here]![NavigationSubform].[Form]![txtuser])) OR (((travelcalendar.mgrracfid)=[Forms]![Start Here]![NavigationSubform]![txtuser]) AND ((travelcalendar.senmgrracfid)=[Forms]![Start Here]![NavigationSubform]![txtuser]));

The table is travelcalendar and I am grabbing certain fields from the table. I have it currently as [Forms]![Start Here]![NavigationSubform].[Form]![txtuser]

The text box is actually on the form called update calendar. I have an OR and AND statement as well and I was using that so the manager who viewed this form can see his records and his staff records. Then the final part is the senior manager when he logs in he can see his records and all of his users as well.

I know I am doing one wrong thing and I have been looking at this since late last night and gave up. I need another set of eyes to tell me what I am doing wrong. I also have this on form load event and all of that works great, but it does not grab the right information. If I run the query on its own and fill in the missing parameters it works like a charm.

Here is the Load Event Code:

 Me.frmcalendarSub.Form.RecordSource = "SELECT * FROM travelcalendar "
    Me.frmcalendarSub.Form.Requery

That part works great as far as I can tell, but maybe not. That is why I felt I should put it down.

So the background on this form is that it has many inputs for the user. They can search the subform by an audit id, select it, (populates unbound text fields), make the changes and then update a new table with the new dates. It then in the background sends the changes to the management team. I was originally using a query that had no criteria and everything worked perfectly, but now that I want the query to load by username I am having issues.

On another page with reports I have two fields with date ranges and the WHERE portion is [Forms]![Start Here]![NavigationSubform]![txtustart] - so I thought I was onto something, but I guess not.

Sorry for typing so much, but I wanted to be thorough so you knew what I was trying to accomplish. Any help or suggestions would be greatly appreciated. Thanks!!

 

[cstickman]

So I was able to figure this out and I took a different approach to it. I thought I would come back here and explain what I did just in case someone else is looking to do the same thing.

So the project was to display information based on the username of the user. We wanted to narrow it down to the current user information only.

Step 1 - start with a blank form
Step 2 - create text boxes and labels for each field
Step 3 - on the Forms control source build an sql with the fields needed for the subform
By case I used ID, Filing, Doc, Closing and Username

Then link all the text boxes with the appropriate column name.

Then I put this in the code:

 Dim User As String
Dim Task As String
 User = VBA.Environ("UserName")
Task = "Select * from travelcalendar Where (changedby = '" & User & "' OR mgrracfid = '" & User & "' OR senmgrracfid= '" & User & "')"
Me.RecordSource = Task

I hope this helps someone else that maybe struggling with the same idea.