Solved Filtered Meetings Query By Members

[Sarah.M]

Hi, Plz note, My VBA is blocked by IT for security reason.
I can use only Expression builder, Macros, Queries, Tables, Forms and Reports only.
------------------------------------------------
I have database for Meeting
I have some problem on it
1. [All Mettings Form] its query filtered by Metting ID but the query show me only records 5 it should show me 5 and 6, if someone added me in a meting this metting should show up in my first page at [All Mettings Form]

I will attach DB to make it easy to understand my questions

plz help me to fix my problems

 

[June7]

1. form RecordSource is an SQL statement with filter criteria that limits to a single metting ID. If you want to return records for a user then change criteria

2. there is no filter criteria so DLookup just returns first record it encounters:
DLookup("Email", "UserT", "UserName='" & [User] & "'")
Better options:
1. include Email field in User combobox (SELECT ID, UserName, Email FROM UserT) and reference that column index in textbox (=[User].Column(2))
2. join tables to pull related info and use that as RecordSource. Set textboxes bound to UserT fields as Locked Yes and TabStop No.

3. as far as I can tell, just opens email message so you can edit its body (there is no default body text - it is blank), doesn't require attachment. If you don't want message to open for edit, change Edit Message argument to No.

4. this would be easy with VBA because requires looping (probably a recordset) to build a comma separated string. I think macros can do some looping but I don't use macros. I expect if it can, then this will involve TempVars to hold the string. And instead of opening a recordset and looping, physically move through records on form.

Maybe should change spelling of "metting" to "meeting".

Really should have an ID field in MembersT and save that as foreign key in UserT instead of names. Why even have two tables?

 

[Sarah.M]

1. form RecordSource is an SQL statement with filter criteria that limits to a single metting ID. If you want to return records for a user then change criteria

2. there is no filter criteria so DLookup just returns first record it encounters:
DLookup("Email", "UserT", "UserName='" & [User] & "'")
Better options:
1. include Email field in User combobox (SELECT ID, UserName, Email FROM UserT) and reference that column index in textbox (=[User].Column(2))
2. join tables to pull related info and use that as RecordSource. Set textboxes bound to UserT fields as Locked Yes and TabStop No.

3. as far as I can tell, just opens email message so you can edit its body (there is no default body text - it is blank), doesn't require attachment. If you don't want message to open for edit, change Edit Message argument to No.

4. this would be easy with VBA because requires looping (probably a recordset) to build a comma separated string. I think macros can do some looping but I don't use macros. I expect if it can, then this will involve TempVars to hold the string. And instead of opening a recordset and looping, physically move through records on form.

Maybe should change spelling of "metting" to "meeting".

Really should have an ID field in MembersT and save that as foreign key in UserT instead of names. Why even have two tables?

Thanks for replying
I give the object blank report to avoid pop up warning message

I add ID to MemberT
I add combo box but still I have issues

plz help me

 

[Sarah.M]

1. form RecordSource is an SQL statement with filter criteria that limits to a single metting ID. If you want to return records for a user then change criteria

2. there is no filter criteria so DLookup just returns first record it encounters:
DLookup("Email", "UserT", "UserName='" & [User] & "'")
Better options:
1. include Email field in User combobox (SELECT ID, UserName, Email FROM UserT) and reference that column index in textbox (=[User].Column(2))
2. join tables to pull related info and use that as RecordSource. Set textboxes bound to UserT fields as Locked Yes and TabStop No.

3. as far as I can tell, just opens email message so you can edit its body (there is no default body text - it is blank), doesn't require attachment. If you don't want message to open for edit, change Edit Message argument to No.

4. this would be easy with VBA because requires looping (probably a recordset) to build a comma separated string. I think macros can do some looping but I don't use macros. I expect if it can, then this will involve TempVars to hold the string. And instead of opening a recordset and looping, physically move through records on form.

Maybe should change spelling of "metting" to "meeting".

Really should have an ID field in MembersT and save that as foreign key in UserT instead of names. Why even have two tables?

I did this, I got no reslut I put Defult value for Combo box [ID] I got only nancy
plz help

 

[June7]

Using UserT ID field as combobox value does not provide criteria to filter by user.

If you want to return Nancy's records then need to reference user name as combobox RowSource (SELECT UserName, Email FROM UserT).

Adding ID field in MembersT serves no purpose if you don't save that value as foreign key in UsersT instead of UserName. Save MembersT ID into UserT and reference that value in combobox RowSource (SELECT UserID, Email FROM UserT). Then if you want to have users name in combobox list, JOIN tables (or don't have two tables to begin with) as Row Source:
SELECT UserID, Email, User FROM UsersT INNER JOIN MembersT ON MembersT.ID = UserT.UserID;

 

[Sarah.M]

I am sorry I don't understand
Plz help

 

[Sarah.M]

Using UserT ID field as combobox value does not provide criteria to filter by user.

If you want to return Nancy's records then need to reference user name as combobox RowSource (SELECT UserName, Email FROM UserT).

Adding ID field in MembersT serves no purpose if you don't save that value as foreign key in UsersT instead of UserName. Save MembersT ID into UserT and reference that value in combobox RowSource (SELECT UserID, Email FROM UserT). Then if you want to have users name in combobox list, JOIN tables (or don't have two tables to begin with) as Row Source:
SELECT UserID, Email, User FROM UsersT INNER JOIN MembersT ON MembersT.ID = UserT.UserID;

Please can you show me what you said in my sample DB I attached

 

[June7]

Sorry, let me start over because I misunderstood purpose of MembersT and I rushed my previous responses. I now understand MembersT is junction table to associate users with meetings.

If you want AllMeetingsForm to show all of a specific user's meetings, then why does its RecordSource (All MeetingsQ) have filter criteria for a single meeting? Try:
SELECT MeetingT.*
FROM MeetingT
WHERE (((MeetingT.Creator)=[Forms]![LogInForm]![ComboUserName]));

Suggest not using spaces in naming convention.

 

[Sarah.M]

Sorry, let me start over because I misunderstood purpose of MembersT and I rushed my previous responses. I now understand MembersT is junction table to associate users with meetings.

If you want AllMeetingsForm to show all of a specific user's meetings, then why does its RecordSource (All MeetingsQ) have filter criteria for a single meeting? Try:
SELECT MeetingT.*
FROM MeetingT
WHERE (((MeetingT.Creator)=[Forms]![LogInForm]![ComboUserName]));

Suggest not using spaces in naming convention.

Thanks for replying

I don't want filter by MeetingT.Creator, He will only see the meetings he created.

I want to filter by Members who joined the meetings, for example if one member removed from a meeting he is not going to see that meeting in his MeetingsQ
So If member is part of meeting he can see the meeting if he is not member he is not going to see the meeting

Please help

 

[Sarah.M]

How can I move this thread to query section?

 

[June7]

Moderators can move threads.

Okay, change All MeetingsQ to:

SELECT MeetingT.*
FROM MeetingT
WHERE ID IN (SELECT Meeting FROM MembersT WHERE User=[Forms]![LogInForm]![ComboUserName]);

Are you sure you want to pull MeetingT records and not MembersT?

 

[Sarah.M]

Moderators can move threads.

Okay, change All MeetingsQ to:

SELECT MeetingT.*
FROM MeetingT
WHERE ID IN (SELECT Meeting FROM MembersT WHERE User=[Forms]![LogInForm]![ComboUserName]);

Are you sure you want to pull MeetingT records and not MembersT?

Thanks for replying
I think it is working but not sure
I make 3 queries and 3 forms
All Meetings Form No Filter (working good)
Meeting by Creator Form Filtered by Creator (working good)
Meeting by Members Form Filtered by Member ( I am not sure plz help me on this Query)
plz see the attachment

 

[SHANEMAC51]

Meeting by Members Form Filtered by Member ( I am not sure plz help me on this Query)

SELECT MembersT.ID AS IDMEM, MembersT.User,
MembersT.Meeting, MembersT.DateTime,
MeetingT.Creator, MeetingT.Subject, MeetingT.ID AS IDMEE

FROM MeetingT
INNER JOIN MembersT
ON MeetingT.ID = MembersT.Meeting;

�

ID MEM	Mee ting	DateTime	Creator	ID MEE	User	Subject
4	8	04.03.2022 19:44:56	Andrew	8	Andrew
7	8	04.03.2022 19:44:57	Andrew	8	Jan
9	8	04.03.2022 19:45:02	Andrew	8	Mariya
6	7	04.03.2022 19:44:26	Mariya	7	Jan
8	7	04.03.2022 19:44:24	Mariya	7	Mariya
3	5	04.03.2022 19:43:29	Nancy	5	Andrew	Please Join
1	5	04.03.2022 19:43:23	Nancy	5	Nancy	Please Join
5	6	04.03.2022 19:44:09	Nancy	6	Jan
2	6	04.03.2022 19:44:07	Nancy	6	Nancy

 

[Sarah.M]

SELECT MembersT.ID AS IDMEM, MembersT.User,
MembersT.Meeting, MembersT.DateTime,
MeetingT.Creator, MeetingT.Subject, MeetingT.ID AS IDMEE

FROM MeetingT
INNER JOIN MembersT
ON MeetingT.ID = MembersT.Meeting;

�

IDMEM	Meeting	DateTime	Creator	IDMEE	User	Subject
4	8	04.03.2022 19:44:56	Andrew	8	Andrew
7	8	04.03.2022 19:44:57	Andrew	8	Jan
9	8	04.03.2022 19:45:02	Andrew	8	Mariya
6	7	04.03.2022 19:44:26	Mariya	7	Jan
8	7	04.03.2022 19:44:24	Mariya	7	Mariya
3	5	04.03.2022 19:43:29	Nancy	5	Andrew	Please Join
1	5	04.03.2022 19:43:23	Nancy	5	Nancy	Please Join
5	6	04.03.2022 19:44:09	Nancy	6	Jan
2	6	04.03.2022 19:44:07	Nancy	6	Nancy

Thanks for replying
No I want Meeting by Members Form its query to be filtered by the user who logged in

 

[SHANEMAC51]

No I want Meeting by Members Form its query to be filtered by the user who logged in

this is secondary -you need to remember the user when entering the program, and then 2 ways
1- what topics did the user create (if created) and who is invited to these topics (list)
2- what topics of other creators is this user invited to (send invitations)

 

[June7]

Meeting by Members Form Filtered by Member ( I am not sure plz help me on this Query)

Uses the query I suggested. Looks fine to me. But how do you intend to open the form? There is no button on Login form.

 

[Sarah.M]

Uses the query I suggested. Looks fine to me. But how do you intend to open the form? There is no button on Login form.

Hi, it works! Thanks
How can I put it in Macro Action RunSQL? becuse I want to put it On Load event
I past the code inside RunSQL Macro action he does not accepted it
plz help!

 

[June7]

I don't use macros, only VBA.

SELECT queries are not run, they are opened or used as source for form/report. Paste what code, the SQL statement? I think macro would call query object.

Action queries are run - DELETE, INSERT, UPDATE.

 

[Sarah.M]

Her I am trying to think out of the box, I do not want to make 2 forms for each query, I want to make 1 form for 2 queries
I will explain to you my need I have 1 Main Form I want this main form to use 2 queries based on the Check box on the Log in form
So in the Main Form On Open as shown in the picture
Sample Attached
plz help me

 

[June7]

SetValue is to set value into a control (like a textbox) not set properties. RecordSource is a form/report property. Try SetProperty.