insert records into multiple tables from ONE form? ? ?

[kyserkess]

I am pretty sure this is the whole reason for databases and forms right?

Goal: Be able to add new records into multiple tables and/or select existing records (mix-match) so I can update the database easily and preform spesific quarries later.

Problem:
When I add information to a page in the form and contniue to the next page, no information is stored in the tables. I tried to add this "save" embeded-marco below, but it has no effect. I have not created a database in Access in a long time.

screen shots:

cbsanswer.com/dataimages/martial_arts.html



Thanks in advance.

 

[jzwp22]

Welcome to the forum!

We will need some information on your tables (table names, field names and how the tables relate to one another) and what your application is designed to do. Could you provide some more detail?

 

[kyserkess]

Thank you for the welcome. I hope I can find some help here.

I spent alot of time taking screen shots and building a webpage to show all of my tables, forms, and relationships to save from all of the confusion of explaining and understanding what I am trying to do. It is certainly not going to be as clear for me to type every thing and I am not sure how to type everything here in any comprehensive way.

I want a form page where I can fill out the form and all of the information goes back into each specified table. In this case, I am accessing three tables.

Once I have a database of records, I will be able to access instructors by name, or location, or martial art style, and be able to calculate the cost of holding an event by calculating the instructor's fee and venue fee. But that is irrelevant to my current problem, isn't it? I cannot update records in multiple tables with one form. If it helps to see my work, please visit the web address I shared on my original post.

 

[jzwp22]

I received an error message when trying to go to the URL you provided. If it is easier, you could just attach a copy of the database to your post. You can create a new database and import only the tables with no data. That should allow us to see the tables with which you are working.

 

[boblarson]

Is this a regular Access form or are you looking at using a Data Access Page? I can't visit your link as I am not allowed to do that at work but with your explanation it seems to me that you are referring to a web page form for input. Is that correct or did I misinterpret?

 

[kyserkess]

Aw crap, sorry I mistyped the address: it is

cbsanswer.com/martial_arts.html

I tried to attach the database file, but I received the message that it exceeds the uploading limit, even though it is practicably an empty database.

Yes, Bob, it is a regular Access form.

 

[boblarson]

Yes, Bob, it is a regular Access form.

You would normally then have, depending on the table structure and relationships, a main form and subforms for the child data. Unless the tables are a one-to-one-to-one relationship, you would not include 3 tables into a single form. With subforms you can have 3 tables updated from a single location - the mainform with subforms, but you would not have 3 tables as the record source of a single overcompassing form.

 

[kyserkess]

Is there a way to make the subforms input boxes like the main form instead of mini tables? And can I select existing rows within a 3-part-form like this?

For instance, say I am entering a new Martial Art Instructor's information, but his Martial Art Style is already in my MAStyles table, can I add his information and select the existing information "Karate, Shotokan"? If so, do the following dependant columns narrow upon each selection, such as: KARATE, narrowing Substyles into the existing choices Okinawan, Shotokan while excluding various substyles of other martial arts such as KUNG FU, Wing Chun?

You can imagine what I am trying to accomplish here, I hope.

 

[boblarson]

Yes, you can use a subform as either Single form view or Continuous form view. If you use single form view then you can EASILY use the cascading combo boxes to add the information.

 

[kyserkess]

Thanks so much Bob, i will try it and tell you how it goes.

 

[kyserkess]

OK, it didn't go so well. I created a form in the Wizard, but it did not give me the option to create subforms. If I selected either of the other two tables, it allowed me to create subforms, but I wanted the form with the most records to be the main form.

Of course, when I update a record now, it tends to only update one of the tables. One of the other tables it updated once, but it has mostly ignored the other tables. It's kind of strange because the information must be storing somewhere because it stays in the form pages.

What am I doing wrong?

 

[boblarson]

but I wanted the form with the most records to be the main form.

That is not how you choose. You choose based on the RELATIONSHIPS of the data. There are parent records (the ONE side of a one-to-many relationship or, if many-to-many, you choose the side which is more logical) and child records (the Many side). The parent records go in the main form, the child records go in the subform.

So, post a copy of your database table structure so we can see what fields you have and how they interrelate.

 

[kyserkess]

Bob, my database relationship diagram screenshot is at the URL I posted earlier: cbsanswer.com/martial_arts.html

It seems like the parent table and the most logical thing to do.

You can ignore the Venue and Seminar Registration table, they are not used in this form.

 

[boblarson]

Bob, my database relationship diagram screenshot is at the URL I posted earlier: cbsanswer.com/martial_arts.html

Like I said, I can't go to that URL while at work and I'm not likely going to be able to take a look at home tonight because I have too many things I need to do.

 

[kyserkess]

Like I said, I can't go to that URL while at work and I'm not likely going to be able to take a look at home tonight because I have too many things I need to do.

This forum will not let me post an image here until after I have posted 9 times. What else can I do?

 

[kyserkess]

I guess I can post twice more?

 

[kyserkess]

one more so i can post an image

 

[kyserkess]

 

[John Big Booty]

Put your image in a zip file and attach that to your post.

 

[jzwp22]

You said the following in an earlier post:

For instance, say I am entering a new Martial Art Instructor's information, but his Martial Art Style is already in my MAStyles table, can I add his information and select the existing information "Karate, Shotokan"? If so, do the following dependant columns narrow upon each selection, such as: KARATE, narrowing Substyles into the existing choices Okinawan, Shotokan while excluding various substyles of other martial arts such as KUNG FU, Wing Chun?

If a style can have many substyles, then you have a one-to-many relationship. Based on the structure you presented, that part is not structured properly. It would look something like this:

MAStyles
-pkStyleID primary key, autonumber
-txtStyleName

tblSubStyles
-pkSubStyleID primary key, autonumber
-fkStyleID foreign key to MAStyles table
-txtSubStyleName


Now, if an instructor can have many substyles (one-to-many relationship), you would use a separate but related table. If they have only 1 substyle you would include a foreign key field (fkSubStyleID) in the instructor info table.

The following shows how you would structure the one-to-many relationship:

tblInstructorInfo
-pkInstrInfoID primary key, autonumber
-txtFirstName
-txtLastName

tblInstructorSubStyles
-pkInstrSubStyles
-fkInstrInfoID foreign key to tblInstructorInfo
-fkSubStyleID foreign key to tblSubStyles

Also, it is better not to have spaces or special characters in your table and field names.

In terms of forms, I would hold off on them until your table structure is fully developed.

What are origin 1 and origin 2 and subtitle1 and subtitle2? Generally, when you have sequentially numbered fields, it is a sign that your table is not normalized (see this site for an overview of normalization)

Do the origin 1 and origin 2 and subtitle1 and subtitle2 fields refer to the instructor or the martial arts style?