Query 1 table to then populate another!

[cjmitton]

I'm new to Access 2010 and still waiting for delivery of my Access book so my apologies if this is a daft or easy question

I have set up a Business Database with all the usual fields in a table. I have then set up another table for the contacts with-in the business (i.e. name, number, email address). Done the relevent links (One to Many) and that happly works. If I manually add in a business and then add on the first contact in the same query screen it all works fine.

I now want to set up forms for my users, So I set up a form to add a business in (Which is fine and works no problem), I then want to do one of two things.

1st Click a button to take the business details just entered then go to a form to add on the additional contact(s).
2nd To add a form to search for a business then add on contacts.

I've been able to do a basic Criteria search from a query but not able to use that to add the relevent contacts.


Your thoughts please...

 

[Ziggy]

if your table is bound to the form then it would already be entered? But You mentioned query, so I would not be doing that.... You probably want to set up a main form for business, with a sundown for the contacts....

You ca also make a form with unbound controls (not tied to a table)' and then use VGA/SQL to load table

Do a google search on subforms and see if that makes sense to your scenario

 

[cjmitton]

Sub forms!!!!!!

Thanks thats great, Googled and got this this link: http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-HA101872705.aspx

I was trying to use a query to then create the form, which as I now know is wrong!

Thanks very much Ziggy for the guide in the correct direction.

 

[Ziggy]

.... with a sundown for the contacts....

Yikes... "sundown", I gather you DID figure out from the later sentence I meant subform I really have to turn off the spellcheck/autocorrect on my Ipad.

Glad you figured it out, just post back if you are stuck...

make a mental note of the term "Foreign Key"[b/] you will see this mentioned... it refers to the ID in the subform that will match the ID of the main form... so if main form is ID#1 (autonumber), then every record added to the subform will have a foreign Key ID of 1 (number type), (the subform table itself can also have an ID of its own)

 

[cjmitton]

Thanks Ziggy, the whole sub form thing is now working a treat! Many years ago (at least 12) I used to do a bit of DB stuff in SQL nothing major so the structure was fine. I've never used access for creating the 'programme' part before! so its all new to me...

No doubt I will have lots of questions and my book from amazon seems to be taking an age!

Think I another post will be coming soon as my own terminology for searching with google is a little off... so I dont get too much back thats handy / relevent!