I somehow feel that this is an extremely simple issue that I'm making far more complicated than it needs to be. I'm not a complete beginner with Access, but I'm also not an expert or much of a programmer.

I have a form on which I've created tabbed pages, each of which contains a subform so that the user can enter data in various fields across a number of different tables. All the forms seem to work correctly in terms of the fields and actually typing data in them, but I can't figure out how to create a button that, when pressed, will (1) update all the tables with the proper data, (2) clear the information from all the fields, and (3) return the user to the first of the tabbed pages. I basically want to recreate the "Save and New" functionality that shows up in many of the Access 2007 Microsoft-provided templates (such as Students), but while I've studied the various forms a great deal, I just can't seem to figure out how to do it.

Another (related) question: In one table, I have a primary key autonumber field that generates a unique identifier for each person to be entered the way described above. How can I pass that value to number fields in the other tables in my database when the information updates, as described in the paragraph above? I really want to make sure each record in those other tables is identified as coming from that specific person.

I have some other issues, too, but these will do to start with. Thanks for any help.

Hi and Welcome to UA,

You are referring to Student DB 07. All or most of Events in there are driven by Embedded Macros.
What I would suggest is to open the Macros one at the time and save it with meaningful name to which button or control it applies.
Then when the Macro is saved you can convert it to VBA and see the lines of code behind Button or Control and its
Event.
It's a great way to learn VBA.

Welcome to UA!

You may have to describe your table structure, so that we can give you better answers to your questions. If you established the table relationships in your subforms properly, the primary key should be created automatically for you when you enter data in your subforms. Also, if your subforms are bound to your tables, the data is automatically saved to the tables once you moved to a new record. Moving to a new record empties the textboxes on your subform and the forms are ready for a new entry.

HTH.

You can see a JPEG of my table relationships at http://www.matthewmurray.net/Database.html

To create the subforms on the main form, I created the tabbed pages, and then just dragged the forms over from the pane on the left. I really thought it was just that simple--I didn't go through any other steps to intentionally bind anything to anything else, because I didn't think I had to here. Maybe it's really more complicated than this... I was just trying to replicate the Students example, but realized as soon as I tried to enter information that I hadn't succeeded.

Thanks for any help.

Correct way to put Sub on Main in 07

Click Design Tab
Click Subform/Subreport in Controls Section of Ribbon
Click on Main where you want that Sub
When Subform Wizard opens check radio button "Use existing form" select subfrm below and click next
You'll see Choose from List and Define my own. I ussualy select Define My Own so do that.
In a form/report field expand combo box and choose ID PK of Main
In a subform/subreport field expand combo box and choose ID FK of Sub and click next
Name you sub and click finish
Now you have Parent/Chils relationship established between Main and Sub

HTH


Edited by: jurotek on Sun Aug 5 12:48:18 EDT 2007.

I tried this, but my ID fields don't show up in either drop-down when I'm trying to link the forms. Why does that happen?

When Subform Wizard opens check radio button "Use existing form" select subfrm below you want to put on Main and click next

Edited by: jurotek on Sun Aug 5 12:53:09 EDT 2007.

I did select "Use existing form," but I'm given four fields (plus None) to use to connect the two forms, but none of them are the fields I want. The AutoNumber field in the Main table is called Actor ID, and I want to use that to connect to regular Number fields (also called Actor ID), but Actor ID isn't listed as an option.

Compact/Repair,Zip your DB and make sure is under 500KB and post it.

How did you create the subforms? Are they based on a table or a query? If based on a query, make sure the PK or FK are included in the fields of the query.

Standing by to see your db, so we can assist you better. HTH.

Here it is. I'm trying to add ActorGeneral to the General tab, ActorAgeAppearance to the Age/Appearance Tab, and ActorAbility to the Abilities tab. I have an Actor ID field in all tables, and that's what I want to use to connect them, but when I add ActorGeneral to that tab, it's not an option, and it's an option with the other two but doesn't accept Actor ID if I select it. I really just don't understand what's going on.

Which forms are going to be Main and Sub?

Looks like DBguy is going to fix you so I just stand by so we both don't work on same thing.



Edited by: jurotek on Sun Aug 5 13:18:36 EDT 2007.

The main form is Actor Entry. ActorGeneral will go into the General Tab, ActorAgeAppearance into the Age/Appearance Tab, and ActorAbility into the Abilities tab.

Jurotek,

I didn't mean to push you out, just trying to assist. Actually, I just realized that I don't have access to Acc2007, so I won't be able to open his attachment. Please continue to assist the OP, and I will stand by just in case only if I can provide additional help.

Thanks for your help.

Oh no DBguy no problem. You were on this first, but I didn't see you online so I wanted to give him hand.
So it should be me saying that.

Actor,
There are some major issues how it is constructed. Give me little time here.

That bad, huh? Oh man. Thanks in advance...

Why there is a need for 3 photos in tblActors?
Just curious

Actor,
You did so much tweaking in form properties and controls and made lot of controls invisible including your
PKs that it threw me off here for while, but it's done now.
Do your formating the way you want. I didn't play with that much.
See ATT

I also re-did all tables,removed spaces in names etc.

Edited by: jurotek on Sun Aug 5 14:46:42 EDT 2007.

Thanks for helping with this... I'll take a look and get back to you.

Regarding the photos, I wanted each person entered in the database to be able to have multiple photos, with (preferably) all of them visibler. Is there a better way of doing that than this?

Ok. Whatever your needs are. Just wondered why 3 photos per one person.

Okay, sorry for the confusion.

Anyway, can you tell me what you did to this? It mostly seems to be what I want, and when I tossed in the Add Record button, it worked just fine, but I just want to make sure I understand the process so I can replicate it later without having to ask a question here. Also, I notice that when you're entering information like the address, e-mail address, and so on, that if you hit tab, you don't go to all the fields in order... I really want to get to Street before city, state, and so on, rather than after. How can I fix that?

I said you need to format this anyway you want.
Add record button not needed. You have navigation buttons on form Bottom Left
Quote"
Also, I notice that when you're entering information like the address, e-mail address, and so on, that if you hit tab, you don't go to all the fields in order.

Answer:
Right click Detail section and organize tab order.

Thanks so much for all your help. Believe it or not, I actually had a fair amount of experience with databases and queries in the past, but I'm really rusty, and have never used Access to quite the degree I'm trying now, so things are coming a bit slowly to me. Anyway, I have a different, though still related, question now.

Since this whole database has to do with actors, I also need to worry about their agents, and I'd like that information to be listed on another tab (like the Abilities and Age/Appearance stuff). I created another table for Agents and a form for entering their office and contact information, which was no problem. What I'm not sure how to do at this point is to link the actors to the agents. I added a Number Agent field in the Actor table, which I then linked to the ID field in the Agent table, but I don't know the best way to specify on the Actor entry form which agent currently represents them. Plus, since actors can change agents, I want to be able to design this so that's really easy to put in here, too. What's a good way to go about this?

Hi,
I am here. I didn't ignore you. I laid down to watch TV and fell a sleep.
Ok. Back to your agents. Put ActorID to Agent table as a FK and relate this to Actor on Main.

HTH

Hello Jurotek,

Sorry for jumping in.

I am interested to see your DBSample, would you please convert to 2003 format.

Many thanks,
rj

Hi rj,
Ask westernactor for that. I deleted it after I was done. I had no use for that.