Form to populate a new table

[Moops]

I have a DB that has three tables. Two of the tables have data without a
relationship. These tables populate a form and subform. When the user
selects a "add" button on the form, I want to then take all the data from the
form and subform and put it into the third table.
Is this possible?
I can get the form and subform to populate, but I cant get the data to a new
table.
Please help.
Steve

 

[Duane Hookom]

When you are attempting to do something this unconventional, you should
provide some background information so we can understand why.

 

[John Vinson]

I have a DB that has three tables. Two of the tables have data without a
relationship. These tables populate a form and subform. When the user
selects a "add" button on the form, I want to then take all the data from the
form and subform and put it into the third table.
Is this possible?
I can get the form and subform to populate, but I cant get the data to a new
table.
Please help.
Steve

So you want to store data redundantly, without any logical structure
or relationships...!? Since there's no relationship, the data in the
Subform would be the entire second table, no matter which record you
select on the main form.

Please explain the nature of the data. What you're asking to do is not
sensible, or else I'm completely misunderstanding it; if you could
describe the real-life problem you are trying to solve, perhaps we
could help.

John W. Vinson[MVP]

 

[moops via AccessMonster.com]

I apologize for the lack of detail.

Here's the E-ticket version...

I have two tables:
1contains information about available courses (CIN, Course_Code, CDP,
Corse_Name)
the other contains location info (Location_Number, Location_Name,
Location_Address, Location_Phone). These tables should never change (with
exception of adding a new class or location...which will be very rare)

The user will be a person creating the "master schedule" for all the classes
and locations.

I plan to build a form and subform that the user will enter the CIN for the
course info and the remainder of the course info will populte and then the
user will enter the Location_Number and the remainder of the location info
will populate.

After this is complete, the user will select a button to add the course info
(class data and location data) into a new table (Master_Schedule) that will
eventually be exported to an excel spreadsheet that gets sent to the gods
above to somehow get put into a mainframe someplace.

I'm certain there is a better way/process to do all this but my hands are
tied. Currently we are building the excel spreadsheet by hand (this means
entering all the data everytime). There are probably 75-100 additions each
month.

I hope this makes it more clear.

Thanks for the help

Steve

I have a DB that has three tables. Two of the tables have data without a
relationship. These tables populate a form and subform. When the user

[quoted text clipped - 5 lines]

Please help.
Steve

So you want to store data redundantly, without any logical structure
or relationships...!? Since there's no relationship, the data in the
Subform would be the entire second table, no matter which record you
select on the main form.

Please explain the nature of the data. What you're asking to do is not
sensible, or else I'm completely misunderstanding it; if you could
describe the real-life problem you are trying to solve, perhaps we
could help.

John W. Vinson[MVP]

 

[John Vinson]

I apologize for the lack of detail.

Here's the E-ticket version...

I have two tables:
1contains information about available courses (CIN, Course_Code, CDP,
Corse_Name)
the other contains location info (Location_Number, Location_Name,
Location_Address, Location_Phone). These tables should never change (with
exception of adding a new class or location...which will be very rare)

The user will be a person creating the "master schedule" for all the classes
and locations.

Then you need A THIRD TABLE. If each Class may occur (at different
times) in several locations, and each Location may (over time) host
different classes, then you have a very basic many to many
relationship.

This might be named Schedule, and have fields CIN (link to the Courses
table), Location_Number (link to Locations), and probably some
date/time fields to indicate when this class will be held in this
location.

I plan to build a form and subform that the user will enter the CIN for the
course info and the remainder of the course info will populte and then the
user will enter the Location_Number and the remainder of the location info
will populate.

If you use a Form based on the Course table and a Subform based on
this third Schedule table it can have a Combo Box to select the
location. You would not need or want to redundantly store the other
location information; it can be displayed on the subform if you wish
by including the location fields in the combo box, and adding
textboxes with control sources like

=cboLocation.Column(n)

where cboLocation is the name of the combo box and n is the zero-based
index of the field you want displayed.

After this is complete, the user will select a button to add the course info
(class data and location data) into a new table (Master_Schedule) that will
eventually be exported to an excel spreadsheet that gets sent to the gods
above to somehow get put into a mainframe someplace.

It is not necessary to create this table. You can instead create a
Query joining all three tables - Courses joined to Schedule by CIN,
and Schedule joined to Location by Location_Number; this *query* can
be exported to Excel.

I'm certain there is a better way/process to do all this but my hands are
tied. Currently we are building the excel spreadsheet by hand (this means
entering all the data everytime). There are probably 75-100 additions each
month.

You are not prohibited from doing the job correctly I hope!!!


John W. Vinson[MVP]

 

[moops via AccessMonster.com]

John, thanks...I think I get it.

In the case of how the form acts, I'm guessing I need to build the
form/subform with "locked" fields so the class and location tables are't
effected. Also, I believe I need to add an onclick event to the button that
will extract the data from the form/subform to populate the third table with
the CIN and Location_Number.
I then need to build query, or a form to run a query ,(based upon so general
criteria) to extract the data from the class and location tables based upon
the Schedule table to create my excel spreadsheet...

Am I close?

Steve

I apologize for the lack of detail.

[quoted text clipped - 9 lines]

The user will be a person creating the "master schedule" for all the classes
and locations.

Then you need A THIRD TABLE. If each Class may occur (at different
times) in several locations, and each Location may (over time) host
different classes, then you have a very basic many to many
relationship.

This might be named Schedule, and have fields CIN (link to the Courses
table), Location_Number (link to Locations), and probably some
date/time fields to indicate when this class will be held in this
location.

I plan to build a form and subform that the user will enter the CIN for the
course info and the remainder of the course info will populte and then the
user will enter the Location_Number and the remainder of the location info
will populate.

If you use a Form based on the Course table and a Subform based on
this third Schedule table it can have a Combo Box to select the
location. You would not need or want to redundantly store the other
location information; it can be displayed on the subform if you wish
by including the location fields in the combo box, and adding
textboxes with control sources like

=cboLocation.Column(n)

where cboLocation is the name of the combo box and n is the zero-based
index of the field you want displayed.

After this is complete, the user will select a button to add the course info
(class data and location data) into a new table (Master_Schedule) that will
eventually be exported to an excel spreadsheet that gets sent to the gods
above to somehow get put into a mainframe someplace.

It is not necessary to create this table. You can instead create a
Query joining all three tables - Courses joined to Schedule by CIN,
and Schedule joined to Location by Location_Number; this *query* can
be exported to Excel.

I'm certain there is a better way/process to do all this but my hands are
tied. Currently we are building the excel spreadsheet by hand (this means
entering all the data everytime). There are probably 75-100 additions each
month.

You are not prohibited from doing the job correctly I hope!!!

John W. Vinson[MVP]

 

[John Vinson]

John, thanks...I think I get it.

In the case of how the form acts, I'm guessing I need to build the
form/subform with "locked" fields so the class and location tables are't
effected. Also, I believe I need to add an onclick event to the button that
will extract the data from the form/subform to populate the third table with
the CIN and Location_Number.

No.

YOU DO NOT NEED any code.
If the Subform is based on the third table, then selecting a location
(or entering any other data) on the subform will automatically,
effortlessly enter that data into the Attendance table.

The mainform would be based on the Class table (if you want to assign
locations to a class), or perhaps on the Locations table (if you want
to look at a location and then assign classes to it). The Subform
would be based on the third table. If the mainform is based on the
Class table then you don't need the Location table visible on the form
at all (you would probably have a separate maintenance form to edit or
add new locations).

You're making it much harder than it needs to be!

I then need to build query, or a form to run a query ,(based upon so general
criteria) to extract the data from the class and location tables based upon
the Schedule table to create my excel spreadsheet...

That you will need to do, but it's pretty easy.

John W. Vinson[MVP]