Combo Boxes and Sub forms

[Amanda Kelly]

I have a form that acts like a switchboard called frm_Forms_Switchboard. The
first combo box lets you select a user, and then it should automatically open
the user form (frm_user_projects) only pulling the selected users records.

Here is the background:
The problem is that now we have multiple users, and its not working... Here
is how I have it set up: ( sorry if this really detailed - but the
directions say to be)
I have a table (tbl_owner_activity) - it has the project id and userid.
Then there is a query (qry_owner_activity) that links tbl_owner_activity by
userid and owner_id to a query named qry_owner that contains owner_id, first
name, last name, and sp (first name and last name combined, ie John Doe)
I have a projects table (tbl_projects) that has all the project information
in it.
I have a form called (frm_user_projects) and heres the problem:
--there is a child/subform whose foundation is qry_owner_activity, and
the userid is a combo box that pulls its data from the owner_id from
tbl_owner and populates userid
--there is a combo box on the parent form that looks up the project
title and navigates to that record...it pulls data based on the following
statement.
SELECT [qry_user].[Project_Title] FROM [qry_user] WHERE
(((forms![qry_owner_activity subform]![combo19])=[forms]![frm_forms
switchboard]![Combo55])) ORDER BY [qry_user].[Project_Title];

....combo19 is userid
....combo55 is the combo box that the user selects their name and the form
opens


I would like for the user to be able to select their user name from the
frm_Forms_Switchboard and it to open up frm_user_projects filtering by userid
since userid and owner_id are the same thing.

Since there are multiple users, i need the filter to pull that users
projects and any other projects that he/she is multiple user on.

 

[Allen Browne]

The core idea here is to set the RecordSource of the projects form, so it
contains only the records for the user in the combo on the main form.
Setting the RecordSource (rather than just filtering) has the advantage that
the user can't just turn the filter off; they can use their own filters, but
when they remove the filter they still have only their own records.

Below is an example of the kind of thing you might put in the Open event of
your projects form. If your "switchboard" form is not open, or if no user is
selected on your switchboard, it cancels the event (i.e. the projects form
won't open.) Once the user is known on the switchboard, the code assigns the
form's RecordSource and it opens.

If you look at a query in design view, you notice that the WHERE clause goes
after the FROM clause, and before the ORDER BY. We've therefore broken the
query statement into two parts (stub and tail), so we can patch the WHERE
clause in between them.

You will need to modify this to match your specifics, depending on your
actual field names and data types, but here's the idea.

Private Sub Form_Open(Cancel As Integer)
Dim frm As Form
Dim strWhere As String
Dim strMsg As String
Const strcSourceForm = "frm_Forms_Switchboard"
Const strcStub = "SELECT * FROM Table1 " & vbCrLf & "WHERE "
Const strcTail = vbCrLf & "ORDER BY SomeField;"

If CurrentProject.AllForms(strcSourceForm).IsLoaded Then
With Forms(strcSourceForm)
If IsNull(!cboUserID) Then
Cancel = True
strMsg = "Select user"
Else
strWhere = "([User_ID] = " & !cboUserID & ") "
Me.RecordSource = strcStub & strWhere & strcTail
End IF
End With
Else
Cancel = True
strMsg = "Select user"
DoCmd.OpenForm strcSourceForm
End If

If (Cancel) And (strMsg <> vbNullString) Then
MsgBox strMsg, vbExclamation, "Cannot open form"
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form that acts like a switchboard called frm_Forms_Switchboard.
The
first combo box lets you select a user, and then it should automatically
open
the user form (frm_user_projects) only pulling the selected users records.

Here is the background:
The problem is that now we have multiple users, and its not working...
Here
is how I have it set up: ( sorry if this really detailed - but the
directions say to be)
I have a table (tbl_owner_activity) - it has the project id and userid.
Then there is a query (qry_owner_activity) that links tbl_owner_activity
by
userid and owner_id to a query named qry_owner that contains owner_id,
first
name, last name, and sp (first name and last name combined, ie John Doe)
I have a projects table (tbl_projects) that has all the project
information
in it.
I have a form called (frm_user_projects) and heres the problem:
--there is a child/subform whose foundation is qry_owner_activity, and
the userid is a combo box that pulls its data from the owner_id from
tbl_owner and populates userid
--there is a combo box on the parent form that looks up the project
title and navigates to that record...it pulls data based on the following
statement.
SELECT [qry_user].[Project_Title] FROM [qry_user] WHERE
(((forms![qry_owner_activity subform]![combo19])=[forms]![frm_forms
switchboard]![Combo55])) ORDER BY [qry_user].[Project_Title];

...combo19 is userid
...combo55 is the combo box that the user selects their name and the form
opens


I would like for the user to be able to select their user name from the
frm_Forms_Switchboard and it to open up frm_user_projects filtering by
userid
since userid and owner_id are the same thing.

Since there are multiple users, i need the filter to pull that users
projects and any other projects that he/she is multiple user on.


-----------------------------------------

How do I this? If there is a better way than what i have, please any
advice
would be greatly appreciate.