Relate Popup form to Main Form

[Ramtrap]

Hi all,



I am still new to access, and even newer to VBA so I'll try and explain this the best I can.

I'm creating a conference scheduling tool for work that is going to track upcoming conferences, how many days the conference is, guest lists, speakers, etc. A fairly large undertaking for me being a newbie.

I have a form (frm_Create_Conference) created from my table (tbl_Conference_Main). The table has a bunch of combo boxes that are yes/no boxes (they don't like the checkboxes) that are meant to identify what has and has not been done (i.e. has the room been reserved? is the agenda complete? do we have agenda slides? etc.).

What I did when I created the frm_Create_Conference is I created a separate popup form called frm_Conference_Checklist. I hadoriginallycreated one big form with all the information on it, but I needed more space for the subform. I experimented with hiding the information and allowing the user to toggle this on or off, but it just didn't look that great. So what I did instead was I created the checklist form, taking the formatted sections of checklist yes/no questions, and putting that into its own form (again, frm_Conference_Checklist). I set the record source on said form to tbl_Conference_Main given that that is the location where all the information was born, but it does not want to link up. Everything is linked up correctly in the Relationships, and everything was working, obviously, when the checklist boxes were still part of the frm_Create_Conference form, but now I'm stuck.

I have three sample conferences in there. On the first one, I clicked the popup form, entered "Yes" for all the fields (there are 34 yes/no boxes), and saved. I checked the table, it works perfectly. I close that form.
I navigate to the second record and click the popup form and it opens the same form I'd just closed, meaning it is starting at what is essentially record 1 of that form.

TL;DR, how do I link my popup form, whereby all the information was taken from the same originating table, to my main form because the Record Source link to the table doesn't seem to be working. Is there an Event that needs VBA to ensure it marries up the popup form to the main form?

Thanks for your help, all!

[aytee111]

Before you begin on forms, the table structure needs to be designed. It looks like you have one table for everything, not the way Access works. Checkboxes to say data exists or not is duplicating information which belongs in queries, not tables.

For instance, you would need:
- a master table to carry conference name and ID
- a table to store the conference date
- a table to denote what is required - you could have a master table of everything and then when you add a new conference you can say we need this and we need that, thereby tailoring each conference to its own requirements
- a table to carry these requirements, one record for each, then yes it has been done or not, maybe with person responsible, date it was done, the actual room number, etc.

Once you have the data normalized, displaying the information of what's been done and what hasn't becomes a simple task using queries.

[Ramtrap]

I think I see the logic in normalizing the Yes/No fields I had, and making it essentially a modular form, but I don't think I'll need that. But I can re-design for that part.

What I have now are the following tables:
Conference_Main (this is essential because I'd need to know the conference ID, name, start date, end date); I also temporarily have the Yes/No boxes, but again, I see the logic in just getting the FIELDS for these set and setting the yes/no to a query
Conference_Day (this will end up being a subform to the main which will include information such as conf day ID, date, start time, end time, building, room
Conference_Checklist (these are the yes/no fields that were duplicated from the Conference_Main table)
Conference_Locations (this is just a table of the locations around the area where conferences may take place)
Persons (these are the people we input to the conference)
Person_to_Conference (this table tracks what people are going to what conference, and is really used as a junction table between Person and Conference_Main, and by extension, Conference_Day)

If I understand you correctly, you want me to split these six tables up into many more tables (one for the name and ID, one for dates, seemingly one for location, etc) but that seems superfluous to me and only opens the door to more confusion (again, a newbie)

All I want to do is have an effect that when I'm on conference_ID number 13, it will open the corresponding checklist popup form and allow me to select yes/no fields that apply to just THAT conference, rather than it opening the checklist with conference_ID number 1.

Best I could find is something like this:

Private Sub Conference_Checklist_Click()
Dim ChecklistConfID As Integer
ChecklistConfID = Me.Conference_ID
DoCmd.OpenForm "frm_Conference_Main", , ,"Conference_ID = " & ChecklistConfID
End Sub

I had it working for a hot second and now I'm getting a parameter box. Frustrating. Would post it, but it's on a completely other network.

[aytee111]

The difference between a good database and one that falls over is table design, not forms. The number of tables you have is of no importance whatsoever, normalization is the key and that determines how many tables you have.

Your first table, conference main, means that every time you repeat a conference you will have to enter it again. Now you have repeating data, the same conference name being entered over and over again, every time you hold it, month after month, year after year. Each one with a different ID. And spelling differences in the name. You will never be able to validate and report on a single conference.

But you're getting there!

All I want to do is have an effect that when I'm on conference_ID number 13, it will open the corresponding checklist popup form and allow me to select yes/no fields that apply to just THAT conference, rather than it opening the checklist with conference_ID number 1.

Exactly what I suggested. These need to be held on a table.

[aytee111]

You may want to look for existing database templates for conferences, there are a number of them (free) on the web.