creating forms with more then three tables

[Mick]

Hi all,
I am trying to make a database form that will allow me to
update data in more then three tables. for some reason
access doesn't like that.
I need to have the following tables updated:
users
phones
mods
tech
buildNumber

thanks

 

[Eric Butts [MSFT]]

Hi Mick,

It's dependent on whether the RecordSource of your Form is updateable or
not.

Try changing the Form's property "RecordSet Type" to "Dynaset (Inconsistent
Updates)"

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights


--------------------
| Content-Class: urn:content-classes:message
| From: "Mick" <[email protected]>
| Sender: "Mick" <[email protected]>
| Subject: creating forms with more then three tables
| Date: Mon, 16 Aug 2004 14:20:32 -0700
| Lines: 12
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcSD1tz+gb2QTTOkRUqTsl5PD+zXVw==
| Newsgroups: microsoft.public.access.gettingstarted
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.gettingstarted:153724
| NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
| X-Tomcat-NG: microsoft.public.access.gettingstarted
|
| Hi all,
| I am trying to make a database form that will allow me to
| update data in more then three tables. for some reason
| access doesn't like that.
| I need to have the following tables updated:
| users
| phones
| mods
| tech
| buildNumber
|
| thanks
|

 

[John Vinson]

Hi all,
I am trying to make a database form that will allow me to
update data in more then three tables. for some reason
access doesn't like that.
I need to have the following tables updated:
users
phones
mods
tech
buildNumber

Since you don't indicate how these tables are related, all I can
suggest is that the usual technique is to use a Form for the "one"
side of a relationship and Subforms for the "many". Each form (or
subform) would have a single table as its recordsource; it's possible
to have two- or even three-table queries which are updateable, but it
gets to be a real chore and confusing for the users!

 

[Mick Chambers]

First off, thanks for responding.
here is some more info:
- I am trying to use the form wizard to create a form that
updates multiple linked tables
Relationship
1 user can have many phones
1 build can have many phones
1 tech can have many phones
1 phone can have many mods

I would like a form that looks like this....

user name: Mick Chambers
Build Number: B1
phones:
a) model:12345 type:12345 techID: Bob Smith
mod: a123, b123

b) model: 22222 type: 33333 techID: Bob smith
mod: a123 b123

you can see that I want to have it listed by user, so that
you only need to look at the user and see what phones they
have and what mods are done to the phone.

Please let me know if you can help

Thanks

Mick

 

[John Vinson]

1 user can have many phones
1 build can have many phones

Is there any relationship between users and buildings? Does a user
have a phone by virtue of being in a building; or can a building have
phones not assigned to any user; or can a phone be shared by many
users? I'll SPECULATE that you can get by with a relationship

Buildings --1:n-- Users --1:n-- Phones --1:n-- Mods

and

Techs --1:n-- Phones

1 tech can have many phones

But might one phone be serviced by multiple techs?

1 phone can have many mods

I would like a form that looks like this....

user name: Mick Chambers
Build Number: B1

Base a Form on the Users table, with a combo box for buildings bound
to the BuildingID field in the Users table...

phones:
a) model:12345 type:12345 techID: Bob Smith
mod: a123, b123

b) model: 22222 type: 33333 techID: Bob smith
mod: a123 b123

and base a Subform on the form on a query joining Phones to Techs,
with a sub-subform based on Mods. Or you may want two side by side
subforms (search http://www.mvps.org/access for "correlated subforms"
to see how to make the Mods subform link to the selected record of the
Phones subform).

 

[Mick]

John, I really appreciate your help, I honestly do however
you are talking over my head and I have a MS Access
advanced traing course - from 4 years ago though.
I will take your advise though and search my text books
for all the terms you are talking about.
btw build = build not building. Phones are made in
several stages and those are called builds ex. Build 1 =
first time a phone is made in the factory build 6 means
the final hardware for production.

so you can see that build 1 may have many phones and each
phone may have many mods(modifications done by technicians
after the build) Therefore the information that needs to
be archived on all phones is:
who has the phone?
What build is it?
What mods are done on it?
Who was the Technician that did the mods?


-