Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

Form/Subform

I am editing an existing databse to make it more user friendly and have run into a snag. Here is my table layout:

tblCourses
CourseID
Prefix (Part of composite PK) Text
Number (Part of compoite PK) Text
Title Text
Video Number
Media Text
Type
Length

tblUsers
UserID PK Autonumber
LastName
FirstName
Office
Phone
EmployeeNumber

tblSession
SessionID PK Autonumber
UserID FK to tblUsers
CourseID FK to tblCourses
DateBegin
TimeBegin
DateComplete

I am tring to create a form and subform for users to sign-in and complete training. It doesn't supply the training, it only prints a list of training to be completed and user information.

The main form it for user information and is based on a parameter query that requests the users employee number on form open. If they have used the system before then their information shows in the form, if not they type in the information and are loaded for the next time. This part works right now.

I can't figure out how to get the subform to work, it needs to allow a user to select courses from tblCourses and put that ID in tblSession. One user may take more than one course in each session. I think I may need another table to make this work but I am not sure. Please help!
0
jwleonard1
Asked:
jwleonard1
  • 3
  • 2
1 Solution
 
harfangCommented:
Try the following:

Select the table tblUsers and choose "Insert / Auto Form" from the menu. This creates a basic form for users.
Switch to design view and drag the table tblSessions onto the form. This will create a "subform object" displaying the Sessions table. Observe the properties "Link Master Fields" and "Link Child Fields" to understand how the subform object is linked to the main form.
If you need more fancy subform options, select the table "tblSessions" and choose again "Insert / Auto Form". Switch to design view and change "Default View" and "Views Allowed" to "Datasheet". Save the form as "fsubSessions" and drag -that- onto the main form. You will notice the same properties for the linking of the subform.

Basically, the subform will display only sessions with the same UserID as the main form (this is an automatic filter) and also fill the field UserID from the main form whenever you create a new line (this is an automatic "default value" mechanism). Note that this works whether you display the field UserID or not, because it is based on -fields-, not on the -control- displaying the field.

Naturally, you can also create more sophisticated ways to subscribe to a new course (a command button displaying a list of courses with OK and Cancel buttons, which will then add the selected course at the end of the subform), but this will get rather complex and involves some VB programming.

Hope this gets you started!
Good Luck
0
 
jwleonard1Author Commented:
The problem I am having is if a user logs in again the previous courses show up with this configuration.  The only way I see to stop this is to create another table.  I have done this and named it tblSessionDetails, here are the changes I made:
tblSessionDetails
SessionDetailsID PK Autonumber
SessioID FK to tblSession
CourseID FK to tblCourses
DateBegin
TimeBegin
DateComplete

tblSession
SessionID PK Autonumber
UserID FK to tblUsers

Now I need to make the main form show user info and the subform show the courses they are taking in this session.  Then I need to create a report based on that information and print it.
0
 
harfangCommented:
So you do not want to show previous sessions in the subreport, only allow the entering of new data?
If that is the case, simply set the subform's property "Data Entry" to Yes. This will show an empty subform at first every time.
Another solution is  to add a date-based filter on the subform, i.e. show only records where DateComplete is Null or higher than a given date (based on today's date or based on a user-selected date on the main form).
Good Luck
0
 
jwleonard1Author Commented:
I tried setting the subform to data enty, but that prevents things that I want to show up from doing so.  In the subform I have the following course information:  Prefix, Number, Title, Media, Video, and Type; they are all combo boxes that look up their column in tblcourses and are bound to tblSessionDetails CourseID field.  I want a user to be able to select an item from these and have it also show in the other boxes.  For instance a user knows the video number they want and select it, then the remaining course info shows up in the other boxes along with the course id being loaded to tblDetails.  With data entry the other informtion won't show up.  

The date idea won't work either, it is common that someone will have more than one session in a given day!

Here is the entire process:
I need a form where a user can type or select their employee number and have it show their information to verify it.

Then their user id needs to go into tblSession in the UserID field with a new SessionID.

Then that session id needs to go into tblSessionDetails in the SessionID field and create a new SessionDetailsID.

Lastly the user needs to be able to select the course from comboboxes by title, type, or by cascading prefix/number, the course id for the selected course will need to go into tblSessionDetails in the CourseID field, but I want all of the info for that couse to show up in the form when they select a course. Also, they need to be able to select more than one course at a time. If I am understanding this right tblSessionDetails should show a line for each course selected but it will have the same session id!

The million dollar question; Is it possible to do this and if so how?
Thanks again.
0
 
harfangCommented:
i'll get back at you... no time for this right now ;)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now