Continuous form including list boxes (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:01
Joined
May 21, 2018
Messages
8,463
You call the function in the current event of the subform, and to make it work you pass a sessionDayID. However when you go to a new record it is null.
2 ways to fix
1. NZ
Code:
Me.sbfrmSessionDay.Form!txtStudents = ConcatRelated("Student", "qryStudentSessionTimeConcat", "fldSessionDayID = " & Nz(Me!sbfrmSessionDay.Form!fldSessionDayID, 0))
2. If then
Code:
IF not isnull(Me!sbfrmSessionDay.Form!fldSessionDayID) then
  Me.sbfrmSessionDay.Form!txtStudents = ConcatRelated("Student", "qryStudentSessionTimeConcat", "fldSessionDayID = " & Me!sbfrmSessionDay.Form!fldSessionDayID
end if
Let me see if I can decipher the other questions.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:01
Joined
May 21, 2018
Messages
8,463
1) You are using the concat wrong. It goes in the query.
2) Your sql strings in the pop up were all wrong
3) I could not get openargs to work (no idea it would always be null whatever I passed). I instead pull the sessiondaytimeid in the code for the insert/delete
4) I filter the correct personnel using a forms reference in the query.
 

Attachments

  • SessionTest15.accdb
    1.3 MB · Views: 84

Kayleigh

Member
Local time
Today, 09:01
Joined
Sep 24, 2020
Messages
706
Fantastic! You have really tied my loose ends together:)

Just some queries if you don't mind...
1. Why is the refresh/recalc/requery all necessary??
2. Was the command button on main form necessary or just there for updating subform?
3. Message boxes are a little disconcerting - can I disable?

You are right - probably is better to put concat into query but I was just concerned it would affect the ability to add records if query is not-in updateable form.

However my biggest challenge is that at moment I can't enter new records into subform as an error appears with regards to duplicate values! How do I resolve that??
 

Attachments

  • trouble adding records.png
    trouble adding records.png
    9.6 KB · Views: 75

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:01
Joined
May 21, 2018
Messages
8,463
1. Why is the refresh/recalc/requery all necessary??
2. Was the command button on main form necessary or just there for updating subform?
3. Message boxes are a little disconcerting - can I disable?
That was all test stuff.
1. It took me forever to figure out why the concatenated students was not refreshing. Until I realized you did not have it as part of the query. I was trying everything to make it refresh. That can be deleted.
2. Command button was another test
3. message boxes allowed me to see that your insert and delete were not correct and the values were incorrect.
 

Kayleigh

Member
Local time
Today, 09:01
Joined
Sep 24, 2020
Messages
706
Aah I see! Well that seems to be working now:)

By the way, your code in afterUpdate event of txtEnd doesn't work immediately (to copy students from listbox). How to resolve?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:01
Joined
May 21, 2018
Messages
8,463
That code is not mine, but there is something WRONG with this database. That event is not firing as far as I can tell. Put a msgbox and it never fires as far as I can tell. I would start saving backups because this is the second sign of corruption. Openargs would not work. You need to decompile the DB and insert objects into a clean db.

However, that form was also jacked up. You could not even add a new day.
 

Attachments

  • SessionTest15.accdb
    1.3 MB · Views: 99

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:01
Joined
May 21, 2018
Messages
8,463
I would kill that Day time form and make a new form I decompiled the database. Imported into a new database and still no luck. The only way I could get the after update event to fire was to delete the txtend and add a new one in. There is still the issue with open args.
FYI your tables are designed so that you can have multiple times per day. If you do not want that then you can get rid of a whole junction table. But your rules are set up so this is impossible. You have made the day key unique. If you try to enter a second time period it will not let you.

Also google a composite index vs a composite key. The following is really jacked up.
key.jpg

FldsessionDayID should be your PK.
And in fact if you want to allow more than one time period per day, that does not even need to be a composite key. If you do not want more than 1 period a day then you have made the design overly complicated.

This table could simply be
fldSessionDayID
fldSessionID
fldWeekdayID
fldTimeStart
fldTimeEnd

If your not having more than 1 time period per day per session then get rid of the seperate time table. Pick one or the other, you designed for more than one, but you restricted to only 1.
 
Last edited:

Users who are viewing this thread

Top Bottom