Access continuous form combo/list box multi tables

[RockKandee]

I am using Access 2013 on Windows 8.

I have been stuck now for days trying to figure out how to make this work. I have looked at the articles for form filtering and cascade filtering. Code is very new to me and I am having difficulty understanding the examples in code. The situation I am working on right now, I haven't been attempting code use at all.

I need to make a continuous sub form work while not duplicating fields in tables.

The main form is one record from a separate table and the sub form is all the parts connected to that main record.

The sub form involves 3 tables connected by a name field.

Table 1 - Name, type, location info/details
Table 2 - days, hours availability (separate record for each day and time slot)+ name, type
Table 3 - all of table 2 plus other info

The sub form is bound to table 3.

If I keep the tables as above, then I have no issues with the sub form.

I enter type and date then a combo box with the below code gives me matching names to choose from.

SELECT Table2.Facility, Table2.DayofWeek FROM Table2 WHERE (((Table2.Type)=Forms!MainForm!SubForm.Form!Type) And ((IIf((WeekdayName(Weekday([Date]))<>[DayofWeek]) And ([Type]="Pool"),True,False))=False));

It makes more sense to have name and type in one record of table 1, and ID fields in table 2 and 3, but then I can't get the form to work. It does not like it when I use an ID field instead of the name.

I am lost as to how to make this work without duplicate fields in tables.

I would like to keep it as a continuous form so I can see all of the parts I am adding to the main form.

There is more to the sub form, but this is where I am stuck right now.

I hope I provided enough information and was clear. My apologies if not.

[time2hike]

RockKandee, assuming that your table 3 contains the ID Field for Table 1 and 2 and that you are only trying to update data in Table 3 you should be able to have a continuous sub form that contains combo boxes for the selection of information from Table 1 or 2. Is this what you are doing?

[RockKandee]

I have it working - not sure it is the best solution though.

In table 3 I gave the type a different name.
I removed the type from table 1 (not sure this effects the form)
The name is only in table 1

SELECT Table2.ID, Table1.Name, Table2.DayofWeek FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID WHERE (((Table2.Type)=Forms!MainForm!SubForm.Form!NEWnameType) And ((IIf((WeekdayName(Weekday([SessionDate]))<>[DayofWeek]) And

Maybe the fix was changing the name?

[RockKandee]

time2hike: Yes, your description sounds accurate for what I am doing.

[RockKandee]

I still need to work on getting type out of table 3 it should connect with the id field for table 2 (which is a separate id than the one discussed above) - I will focus on this now that I have less things running around in circles in my head.