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.

















Thanks in advance.

[ajetrumpet]

you don't need a button to save info. it's automatic, if it's updateable.

are you sure that your form's query is actually updateable?

[kyserkess]

Its not updating as I continue onto the next pages. How can I be sure? It's not working.

[ajetrumpet]

what is your form's recordsource? a query, right?

if it's an actual sql statement, make a query with the same statement and try to add data there. if it doesn't take, it's non-updateable.

if the source is an actual query already built, go to it and try to add data there. same second rule as above.

it you run into a brick wall, maybe see here: http://allenbrowne.com/ser-61.html

[kyserkess]

Aje, I never created any query, I am just trying to create a form to input records. Someone on another forum is saying I need to create a main form with subforms, but I do not get that option from the Forms Wizard when I add everything from the thee tables.

It just creates a single form and when I add records, they only update in one table. What am I doing wrong?

I want to be able to add new Instructors and select their Martial Art styles if they already exist, OR add the styles if they do not. Same goes for his title and ranking. Can you see what I want to build? It should make perfect sense. A simple way to add information.

[ajetrumpet]

it does make sense, trust me.

but I will say two things at this point:

1) posting on more than one site and listening to two different people give you advice will confuse the heck out of you, especially if you're a newbie. It's a fact. It's proven about every day.

and 2) I'm interested in saving people time, and I can't see that happening in this thread by me explaining things away, because the click is not there in this situation. Upload a file for me, and I'll show you how it's done. That's really what I can offer at this point.

Otherwise, good luck in the other thread!

[kyserkess]

sorry aje, I posted on the other thread because my original post never posted here.

I don't know how to change or write quarries. I hope I will be able to see what you do to make this work.

Thank you

[ajetrumpet]

kyser,

take a look at the sample. the query is what I'm talking about. but there's a problem, if you enforce your keys like you have it now, that query will not work either. the reason being, you have to create parent records before childs can be created. make sense?

so what I would suggest to you, is maybe make a primary form to enter an instructor, and then make a popup form to enter in the details about him or her.

you can do that, or you could delete the pk's out of both the martial arts table and the title table. They are irrelevent. the only time keys should be related to other tables and their fields is when there is a parent-child relationship between the two sets of data.

I know that doesn't make sense, so just delete the keys. those two tables are basically lookup tables for you, because the data doesn't serve any other purpose but that. You would relate the instructor table, for example, to another table that has student names and information for the instructor.

start with that first. the way you enter data into that main table is to use combos and other lookup tools. That's all.

Try entering a record into the new form in the sample. It'll throw a message on the status bar telling you that a parent record must be entered first. Proof!

[kyserkess]

Aje, can the pop up form also let me insert new records if I need to?

What is the parent record? I am having trouble understanding what to do to make your example work.

The reason the other tables have keys is because I want to be able to look up Instructors BY Martial Art styles. Also, for the Title and Rank to save together, doesn't each row have to have an ID number?

Eventually I want to be able to quarry Venue cost with Instructor cost to calculate the cost to organize a Martial Art Seminar. That is the reason I am trying to create this.

Does that help you?

[kyserkess]

For the form, I want to be able to find a martial art instructor in my local community and open a form on my computer to fill out all of his information in one place without having to go to multiple tables/forms.

[ajetrumpet]

yes you can. as far as I know, you can open as many forms as need be with the same data source. It's not a big deal. If I remember right, you can. you simple have to refresh the others after data is added or changed, that's all.

and again, regarding the keys. they are irrelevant in any table except for the instructor table. I surely don't want to say that non-instructor data is irrelevant, but speaking about relationships, it really is. every table should have a pk as an autonumber. that's a basic non-documented rule about access. it's a practice.

in general, the only time you have to do any work getting ID's into an instructor's record is when you enter them, and both of the IDs that you once had linked to that table, you can populate those simply by looking them up, by built in functions at the time you enter an instructor's new record.