Search code examples
formsms-accesswhere-clause

MS Access Messenger App with user filtering of messages

I am in the making of an internal short message app in our MS Access "ERP system".

The table is called "tbl_0_messages"

Field Name          DataType        Lookup
message_id          AutoNumber
message_sender_id   Number          emloyee_id from employees table
message_receiver_id Number          emloyee_id from employees table
message_text        LongText
message_created     DateTime
message_received    Yes/No

Employee_id 1 is a number I use for ALL employees, numbers above 1 is of course all the employees.

I have a Continuous Form called "frm_0_messages" With the same Field Names as the table and an unbound Combo Box in the Continuous Form Header with the name "cmb_message_sender_id", used for filtering and creating new messages.

When we create a new message, it uses the value in the ComboBox as the "message_sender_id" as default value.

I have made a record source/query on the Continuous Form:

SELECT tbl_0_messages.message_id, tbl_0_messages.message_sender_id, tbl_0_messages.message_receiver_id, tbl_0_messages.message_text, tbl_0_messages.message_created, tbl_0_messages.message_received FROM tbl_0_messages;

Which of course runs fine.

What I need is to be able to filter the messages, so employees see all the messages they have created by themselves, messages where they are the receiver, and messages sent to All employees (employee_id=1).

When I add a WHERE Clause to my record source:

WHERE (((tbl_0_messages.message_sender_id)=[Forms]![frm_0_messages]![cmb_message_sender_id])) OR (((tbl_0_messages.message_receiver_id)=IIf([Forms]![frm_0_messages]![cmb_message_sender_id]>1,[Forms]![frm_0_messages]![cmb_message_sender_id],"*")))

I get a Run-time error '3071'

I've tried several other WHERE clauses with no luck, so I'd really appreciate some help with this problem.

Thanks ;-)

Solution

  • It could be this:

    WHERE (message_sender_id In (1, [Forms]![frm_0_messages]![cmb_message_sender_id])) OR (message_receiver_id = [Forms]![frm_0_messages]![cmb_message_sender_id])