Solved Create Two tables (1 Viewer)

[Teri Bridges]

Guys I need to create two tables Each table will have an event ID.
When I create a form for Table 1 Event ID how do I get that event ID to populate into Table 2 event ID?
I might be trying to apply the wrong logic.

I have events the user must complete stored in Table 1 based on the event ID. I then have review cycles in Table 2 based on the event ID. Each event in Table 1 has its own review cycle.

I was going to create a relationship for the two tables based on event ID but I cannot get the event ID to populate to table 2.

 

[Gasman]

Use a mainform for Table1 and subform for Table2

 

[plog]

Presumably your relationship is 1-many. As forms you use a form (for the 1) and a subform ( for the many) to accomplish this. Here's a good link in how to do that:

Create a form that contains a subform (a one-to-many form) - Microsoft Support

Learn how to create a form in Access that contains a subform (also known as a one-to-many form, a parent/child form, or a hierarchical form).

support.microsoft.com

 

[Teri Bridges]

Presumably your relationship is 1-many. As forms you use a form (for the 1) and a subform ( for the many) to accomplish this. Here's a good link in how to do that:

Create a form that contains a subform (a one-to-many form) - Microsoft Support

Learn how to create a form in Access that contains a subform (also known as a one-to-many form, a parent/child form, or a hierarchical form).

support.microsoft.com

Yes, I just am not able to get the Event ID to populate into table 2

 

[theDBguy]

Yes, I just am not able to get the Event ID to populate into table 2
View attachment 109934

As was already mentioned, you would use a form to auto populate the Event ID in Table 2. You don't do it directly with tables.

 

[plog]

Have you created the form and subform? If so, have you worked through the link I gave you that shows how to put the subform onto the form? What specifically with my link are you having an issue with?

 

[Teri Bridges]

As was already mentioned, you would use a form to auto populate the Event ID in Table 2. You don't do it directly with t

Have you created the form and subform? If so, have you worked through the link I gave you that shows how to put the subform onto the form? What specifically with my link are you having an issue with?

Yes, I am able to create the form and sub-form. I think I may be using the wrong approach. I have been watching some videos but I am getting confused. I have five tables. Each table is related to its predecessor. I have; Course, Lesson, Topic, Event, and Review cycle. If I use the sub-form approach the main form gets really messy.

The course form has the lesson form, the lesson form has the topic form, the topic form has the event form, and the event form has the review cycle form. This approach makes for a scrolling mess.

So then I tried a tab control and made pages for each form, but I could only match the master field to the child field by course.

So Now I was going to try buttons. Click the button and the associated form opens based on the current record. But finding help topics is a process.

I am trying to make sure that the records relate properly when entered. One course many lessons, one lesson many topics, one topic many events, one event, many review cycles.

Thank you for the advice, again I am fairly new and am still learning the concepts. I think I have the basics, tables, forms, and queries down now it's on to relationships, and connecting the data meaningfully.

 

[plog]

You are trying to do to much at once.

I have; Course, Lesson, Topic, Event, and Review cycle

Let's just work with Course and Lesson for now. After I show you that you should be able to extrapolate how to make the whole thing work.

1. Make a continous form based on CourseT (Properties->Default View->Continuous) named 'CourseList'. This will look very much like the table itself--just a bunch of rows of each Course record. It will have scroll bars you can scroll down to see more records.

2. Make a single view form based on CourseT (Properties->Default View->Single Form) named 'CourseSingle'. This will show just one record at a time for every record in CourseT. You will use the record selectors at the form footer see more records.

3. Make a continuous form based on LessonT named 'LessonList'. Similar to #1 above but for Lessons.

4. Make a single view form based on LessonT named 'LessonSingle'. Similar to #2 above but for Lessons.

Here's where the magic starts;

5. Edit CourseList form (#1 above) and put a button to the left of the data. Put a label on it that says 'View' and the below code to it:

    DoCmd.OpenForm "CourseSingle, , , "[CourseID]=' & Me!CourseID

Close and save.

6. Open CourseList again and click a button next to a record. This will open CourseSingle to that specific record you selected.

7. Using the link I gave you above, add LessonList to CourseSingle. Pay particular attention to linking parent and child fields, this ensure that just the related Lessons show for each specific record you are viewing on CourseSingle.

8. Edit LessonList form (#3 above) and put a button to the left of the data. Put a label on it that says 'View' and the below code to it:

    DoCmd.OpenForm "LessonSingle, , , "[LessonID]=' & Me!LessonID

Close and save.

9. Open CourseList again and click a button next to a record. This will open CourseSingle to that specific record. Then at the bottom click a button next to a LEsson, this will open LessonSingle to that specific record.

You have now built forms to travel down your heirarchy from all courses to a specific lesson. Keep repeating that form building pattern until you can travel all the way to data in ReviewEventStatusT.

Actually, I wouldn't build any more forms until you get everything working like you want with Courses and Lessons (add/edit/delete, etc.). Don't try to do everything at once, get these working first and it will be easier to build the rest of your forms.

 

[Teri Bridges]

Thank you for this. I will try.

What I had before I ran into the issue was a CourseT form with a course selection list. The user selects the course and the form opens to that form. Then I created a button and applied

Private Sub btnLesson_Click()
DoCmd.OpenForm "Lessons_frm", , , "CourseID = " & Me!CourseID
End Sub

This opened the lesson form referencing the course. I could see the Course ID in the Course ID field. Works, expected results
Then I added a button to the lesson form and applied

Private Sub btnLesson_Click()
DoCmd.OpenForm "Topic_frm", , , "LessonID = " & Me!LessonID
End Sub

This opened the topic form referencing the lesson ID. I could see the lesson ID in the lesson ID field. Works, expected results

Here is where things went wrong
I used the same steps I used above: I created a button on the topic form and applied

Private Sub btnLesson_Click()
DoCmd.OpenForm "Event_frm", , , "TopicID = " & Me!TopicID
End Sub

This opened the form but the topic field is empty. I am not sure what I have messed up.
So currently Course, Lesson, and Topic work fine, expected result. I cannot get Event to work thus I cannot get Event review to work.
I was trying not to have too many forms that the users could mess with. But I guess I can manage access to forms with the splash page.

I do appreciate your help!!

 

[plog]

Are there any events associated with that Topic in the Event table?

 

[Teri Bridges]

Are there any events associated with that Topic in the Event table?

Yes I entered one event to validate and I cannot get the Topic ID to populate

Table Record:

And here if the form entry

 

[plog]

There's no TopicID so that event isn't related....Which brings us full circle to your first post...and back to everyone's advice--use subforms.

With a main form/subform system your form "knows" which main record/ID to use for a new record in the subform. With your method you cannot communicate that to a new record. You essentially need to have your Events form be a subform to a main form based on Topics which is limited to just one Topic.

You don't really have to change much. Create a new Single view form for Topics which shows just one topic at a time. You really don't need have it show anything from Topics (although I would show TopicID to verify you have the right one), it just needs to be based on it and be Single view. Then to it you add your current Events form as a subform (linking the child/parent relationship appropriately) and it will know which Topic ID to use when you add a new Event.

 

[Teri Bridges]

There's no TopicID so that event isn't related....Which brings us full circle to your first post...and back to everyone's advice--use subforms.

With a main form/subform system your form "knows" which main record/ID to use for a new record in the subform. With your method you cannot communicate that to a new record. You essentially need to have your Events form be a subform to a main form based on Topics which is limited to just one Topic.

You don't really have to change much. Create a new Single view form for Topics which shows just one topic at a time. You really don't need have it show anything from Topics (although I would show TopicID to verify you have the right one), it just needs to be based on it and be Single view. Then to it you add your current Events form as a subform (linking the child/parent relationship appropriately) and it will know which Topic ID to use when you add a new Event.

I will give this a try. I was just stumped as to why it worked for Lessons and topics but not events.

 

[plog]

I don't think it does. Add a new record and see if it takes the correct CourseID.

 

[Teri Bridges]

I don't think it does. Add a new record and see if it takes the correct CourseID.

You are correct. I sometimes wonder why I thought I needed to Learn Access. LOL

Thank you for your help. I will not give up, I will keep at it.

 

[Teri Bridges]

You are correct. I sometimes wonder why I thought I needed to Learn Access. LOL

Thank you for your help. I will not give up, I will keep at it.

I guess I could put everything in one very long table...LOL

 

[The_Doc_Man]

When you have a parent/child table, the USUALLY ideal method involves a parent/child form/sub-form. IF you have a proper link established so that the PK of the parent table is part of the sub-form and child table, entering data to the sub-form automatically brings in the ID from the parent. You just have to tell the forms which fields are the parent and child links and Access should do that for you. Setting up the linkage is all you have to do. You should NOT have to do anything else to create properly linked parent/child records.

 

[mike60smart]

I guess I could put everything in one very long table...LOL

Hi Teri
Are you able to upload a zipped copy of the database, or a copy with no Confidential data?

 

[Pat Hartman]

When I create a form for Table 1 Event ID how do I get that event ID to populate into Table 2 event ID?
I might be trying to apply the wrong logic.

You appear to be attempting to create a 1-1 relationship. Why? This relationship is extremely rare in the wild and most people create it for the wrong reasons.

The advice to use a subform for the "child" table is the best way. You never want to create "empty" records by force. By setting the master/child links, Access will automatically propagate the FK to the child record as soon as you type something into the subform.

 

[Teri Bridges]

Hi Teri
Are you able to upload a zipped copy of the database, or a copy with no Confidential data?

I was trying to make my course details form page cleaner. I still have 2 subforms to add. Changes and Issues these need to link to the course ID and also the event ID. Please don't judge I am still learning and know that things could be cleaner. Any advice is greatly appreciated.