Form or table set-up problem?

[barit]

I am not sure if this is a table structure issue or if I am just being clueless... but here goes.

I currently have three tables in my database.
Table1(tblPerson) includes all info about the person including PersonID, LName, FName,Address, MLName(MailingListName)etc. (NOTE: MLName can refer to more than one person in the data base. For example; Jane Doe and John Doe are each separate individuals in the data base, they do however share the mailing list name Mr. and Mrs. John Doe.

Table 2(tblEvent) includes all info about different events and includes EventID, EventDate, Occassion,EventType etc.

The third table(tblAttendees) is currently a junction table and includes PersonID, EventID as well as fields for OnMailList,LabelRequired,NInvited,AttendanceStatus, etc.

Here is the problem

I need to be able to create an event specific mailing list. I want the end user to be able to identify specific individuals from the whole data base and link them:
- first to an event mailing list which will be created. This will require filling in additional field information
- second to save and print the mailing list. I want the end user to be able to refer back to this mailing list and edit it as required.
- third to identify the specific individuals that the mailing list represents and track attendance status.

Using a form based on the tblAttendees, I have been able to track individuals attendance status at a specific event. (ie. I can identify the person, link them to a specific event, identify and update their status). I cannot make this same concept work for the mailing list.

What am I doing wrong? A better question is probably,are there other ways of structuring the data base, relationships etc. or is it that I am just clueless about how to set up a form to accomplish the required tasks.

I am now so frustrated and seem to going around in circles. I really would appreciate some advice and suggestions.

 

[kjv1611]

Do you have the form bound to the table where you are storing mailing list info? (Under Form Properties, what does "Record Source" under the "Data" tab show?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[barit]

The form(CreateMailingList) is bound. Below I have identified some the key information you may require to help

To elaborate just a bit. When I use this form, I am provided with mailing list names for all events not for the specific event required. I have applied a filter to the form that reads [Forms]![EventPlanning]![EventID]. This process works with other forms, but I cannot get it to work with the mailing list form.

I cannot get it to work no matter what I try. I am not sure if it is how I have structured the tables, relationships, etc. or if I am doing something wrong in the form itself. I really would appreciate any comments or suggestions.

Relevant Info from CreateMailingList form
CreateMailingLabels Row Source
SELECT Person.MailingListName, Attendees.OnMailList, Attendees.NumberInvited, Attendees.LabelRequired, Attendees.[Event ID]
FROM Person LEFT JOIN Attendees ON Person.[Person ID] = Attendees.[Person ID];

FIELD –MailingListName
Control Source - MailingListName
RowSource MailingListName
SELECT Person.MailingListName, Person.LastName
FROM Person
ORDER BY Person.LastName;

FIELD – OnMailList
ControlSource OnMailList

FIELD-NumberInvited
ControlSource NumberInvited

FIELD-Event ID
ControlSource Event ID
RowSource Event ID
SELECT [Event Info].[Event ID]
FROM [Event Info]
WHERE ((([Event Info].[Event ID])=[Forms]![EventPlanning]![EventID]));

 

[kjv1611]

I've not messed with filters much myself to date, but one thing I've come accross in other coding is that sometimes the problem can be something as simple as using a '.' instead of '!' or vise versa, or using [] where not required, or changing something in parenthesis to no parenthesis, and vises versa.. Just some possible ideas.

Also, to be more specific, are you having any error messages, or just data not going where you want it to go? How is it not working?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[barit]

There are no error messages. I am just not getting the records I want on this continuous form.

I want to be able to select mailing list names from the full data base of names, however:

- if mailinglistname1(Mr. and Mrs. John Doe) has been identified on another mailing list(ie Event1 mailing list) I do not want to see that record with eventID1 as the user is creating a mailing list for a new eventEventD2. I do however want to be able to select mailing list name1(Mr. and Mrs John Doe for the new mailing list2.

In other words the form should look like

Mr. and Mrs. John Doe event2
Mr. and Mrs John Smith event2


NOT

Mr. and Mrs John Doe event1
Mr. and Mrs.John Doe event2
Mr. and Mrs. John Smith event2

Does this make any sense?

Any suggestions?

 

[norwood]

It could be that you don't have all info necessary on your query. Your form should be based on the query you created, not on the table. If you base your form on the query and the query itself gets a parameter from another form (where you'll choose the event you want to see, for instance), it will filter all records for you. (hope this does not confuse you more...)

Another thing to think about is using a form and a subform. On your main form you'll have the event info, while the subform has the attendees list. This way you can link the two by the EventID so that when you go to another event, you only see the list of attendees for that particular event.

Can you post your database here? If we take a look at it I'm sure we can help you better. This sounds like a simple solution.

daniel