By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,838 Members | 2,222 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,838 IT Pros & Developers. It's quick & easy.

Access continuous form combo/list box multi tables

P: 89
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.

Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 16 '13 #1
Share this Question
Share on Google+
4 Replies

P: 68
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?
Dec 16 '13 #2

P: 89
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

Expand|Select|Wrap|Line Numbers
  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?
Dec 16 '13 #3

P: 89
time2hike: Yes, your description sounds accurate for what I am doing.
Dec 16 '13 #4

P: 89
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.
Dec 16 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.