Continuous form including list boxes

[Kayleigh]

Hi,
I have a main form for each session. Within this I have a subform which is a continous form - it lists all days the session takes place (data from jtblSessionWeekday). I have another two levels below this which I would like to show as list boxes and if possible allow new values to be added to all levels. (All links are many-to-many so many junction tables were required.)
I have the time each session starts and ends (from jtblSessionDayTimes) and finally the people involved in session (jtblSessionDayPeople).

I tried a query for data source of my list box with criteria of (FK) fldSessionWeekdayID to equal forms!sbfrmSessionTimes!fldSessionWeekdayID but this would bring up an error - I'm assuming because it is a subform. Not sure how else to link the data?

 

[Ranman256]

the subform combobox must be bound to a field, say StartTime, EndTime
then user can pick from the list.

note: an unbound combo in a continuous form (on the detail line), has no use.

 

[Kayleigh]

How is this possible if the subform's recordsource is the parent table ie fields startTime and endTime are in subtable of sessionWeekday?

 

[Minty]

I think you might need to show us some sample data and mock up a picture of what you are trying to achieve.
Describing complex data structures is not easy, and as someone may once have said "a picture can say a 1000 words!"

 

[Kayleigh]

Not so easy to isolate that part of my database. However I have enclosed the main form and subform as it currently is in datasheet view inc subdatasheets.
See screenshot of view I'm hoping to achieve.

 

[MajP]

If you are going to add to these why not continuous subforms vice listboxes? That would be easy. Is this what you mean. You would click on a day and see the times in a "list", same with people for that time? You did not provide the people junction. I would do this with linked subforms, very easy if this is meets your needs. In this case I click on Thursday and can add edit the times (forgot to format to a time field). In truth I would use a continuos subform instead of a data sheet with a real time picker. Then below this subform I would have a way to add people to those times. I assume the people are to a time not to the day or session?

It this meets your needs you can look at this example I did recently. It is a little to wade through and more complicated, but shows how to do linked subforms.

Conditional formatting based on the values in another table

You might want a full calendar view to see all the activities at once. Take a look at the Peter Hibbs calendar with my year view to add codes. You could add codes for MDT, PM. etc. Then you could have visibility of everything going on. You would just have to do an union query on your multiple...

www.access-programmers.co.uk

 

[Kayleigh]

Yes that is the idea I had in mind. Just not sure how to go about doing it.
Looked at the database you enclosed in above post and can see the dates/times on main form however it is not clear how the queries are linked as I don't know the context of the data. Can you give an idea of how I would query my data to show a similar view?
It looks like you are showing in above screenshot two subforms of dates and times??
Would like all data in one subform as there will be other data in the other parts of the form. I have above this subform a combo to select students so this would be a simple dropdown.

 

[MajP]

Your table is structured to allow multiple sessions in a day, but I noticed no where in your data do you have move then one time period per day. Is you intent to just have a single session per day? If so your table structure and thus the form would be different. I assumed you could have multiple sessions in a day. That is why why the linked subform shows all the times (I made 2) for Thursday. If it can only be one session per day then I would change my tbl structure and form. If you rarely add multiple times per day then I think your first sub form would be.

Monday 10:00 11:00
Tuesday 10:30 11:30
and if you do have multiple times then show the just show the day twice for those rare cases. Not sure why you would have a subdatasheet in the first subform. I would not want to click that just to see the time.

 

[Kayleigh]

We did add that possibility for the option of having a session multiple times a day. I agree with you about opening the subdatasheet for the times so would like another more user-friendly option...

 

[MajP]

So would like this work. Most of the time you would not have 2 times a day. Here you can add edit and delete a day and time. Like I said I would do a tab form with a time picker so you do not have to type times. If you want I have one. I hate typing times.

 

[MajP]

You could do in a data sheet with a double click in the box. I doubt you need any minutes besides increment of 5 so you could get rid of a lot of the minute options too.

 

[Kayleigh]

Yes I like the option of a tab picker for the time. Thanks

 

[MajP]

Here is the demo. You never showed the junction table for people. If the subform idea works for you just do the same as this subform.

If you wanted a listbox instead of a subform. I would do it in the mainforms on current event.

Private Sub Form_Current()
  Dim strSql As String
  strSql = "SELECT jtblSessionWeekday.fldSessionID, tblWeekdays.fldDay, jtblSessionDayTimes.fldStart, jtblSessionDayTimes.fldEnd, jtblSessionDayTimes.fldStart "
  strSql = strSql & "FROM tblWeekdays INNER JOIN (jtblSessionWeekday INNER JOIN jtblSessionDayTimes ON jtblSessionWeekday.fldSessionDayID = jtblSessionDayTimes.fldSessionDayID) ON tblWeekdays.fldWeekdayID = jtblSessionWeekday.fldWeekdayID "
  strSql = strSql & "WHERE jtblSessionWeekday.fldSessionID = " & Me.fldSessionID
  strSql = strSql & " ORDER BY tblWeekdays.fldWeekdayID, jtblSessionDayTimes.fldStart"
  Debug.Print strSql
  Me.lstTimes.RowSource = strSql

End Sub

 

[Kayleigh]

I have implemented your fab solution!
However I had in mind a continuous form as I would like to include the students in each session underneath if possible?
See attached...

 

[MajP]

I do not understand that picture based on the junction tables. The junction table jTblStudentSession is related to the session, not the session day of the week. Am I missing something? Then regardless of the day of the week it is the same students. So would it not look like this instead? It you put the names under (you would have to concatenate) they would repeat for each record the same names.

 

[Kayleigh]

That is correct. However we have a junction table jtblSessionWeekdayStudent for the option of different students attending each session. This would by default display all those registered for the lesson - in jtblStudentSession with the possibility to select which students are attending.
See ERD diagram below for more info
Although now rethinking my idea, perhaps your layout would work and only those sessions which show different students to those in list are displayed?

 

[MajP]

This allows you to assign students to a specific, day and time for a session. I was thrown off because the name of your junction table would be better
jTblSessionTimeStudent and not jTblSessionWeekdayStudent since you are not linking to a day, but a specific time on that day. Although as you pointed out most days would only have a single time.
1. I did not modify the rowsource of the Students per time. This should be limited to the students available to that session I assume. I will leave that to you.
2. If you want to show a concatenated list of students below each time period you can do that using a concatenate function. You would need to make a continuous form instead of a datasheet.
http://allenbrowne.com/func-concat.html and use the subform to add/edit/delete. I will leave that to you.

So on this form you could have a list to assign available students to a session. A way to assign from that list to a specific time. Show the names in the subform under the time.

 

[Kayleigh]

Just to say that this is a fab solution once again @MajP
Thanks for the time it must have taken you to work on this! The query you used was super useful in other functions as well!

Quick question - when I open a new record I get the attached message though not sure what is triggering it?

 

[MajP]

Thanks for the time it must have taken you to work on this!

Trust me I build so many dbs that it is a fraction of what you would think. There is not much new, just apply the same old techniques.

I would have to see the updated DB, but I have a good guess. If you are using the concat function. If you pass that function a NULL value for the sessionDayID it will fail. That looks like what is happening from the message. Make sure to wrap that field value in a NZ. Some like
nz([SessionDayID],0). Now there is no sessiondayid with 0 so it will return no concatenated values.

 

[Kayleigh]

I can imagine! The deeper I dig in this forum, the more I feel that I'm at tip of the Access iceberg!

Sorry not sure exactly where to add in nz([fldSessionDayID],0)?

Now back to above query, have extended your approach and created continuous form to display all related data. (Though the students textbox doesn't seem to show the relevant data in enclosed DB not sure why...)
I have created a pop-out form which should show ONLY students registered for session in jtblStudentSession and allow as many as required to be selected and added to jtblSessionWeekdayStudent. Problem is subform based on tblStudents so don't know how to filter to only show records in jtblStudentSession. (Tried changing the arguments on the openForm command but doesn't work as it is unbound form). Although I think once records ticked, should work to add to jtblSessionWeekdayStudent...

Also how do I add by default all students registered to the session-day-time after the record has been created ie when day and times selected?