Modify table values from a MultiQuery Form

[Alex Alvarado]

Hello,

I created a From from a Query that contains 6 tables linked data and I need
to be able to Modify the values from the Tables by using the Form but I cant,
I don't really know if this is the easyest way to do it. I really need your
feedback.

Thanks!

 

[John W. Vinson]

On Thu, 11 Jun 2009 14:31:01 -0700, Alex Alvarado <Alex

Hello,

I created a From from a Query that contains 6 tables linked data and I need
to be able to Modify the values from the Tables by using the Form but I cant,
I don't really know if this is the easyest way to do it. I really need your
feedback.

Thanks!

As a rule, the more tables in a query the less likely that it will be
updateable AT ALL, and the more confusing it will be for the user: you'll see
duplicate records if the "one" table is joined to a "many" table, you'll see
each field in the "one" table repeated.

Typically you would base a form on *ONE* table; the form may well have Combo
Boxes to "look up" values from lookup tables (*don't* use Lookup Fields in
your table design though), and Subforms to display multiple records from
related "many" side tables.

Perhaps you could post a description of your six tables, their meaning, and
how (if at all) they are related.

 

[Alex Alvarado]

Hello John, thank you for answering, I know I’m not doing this well, that’s
why I requested help, thanks!

Regarding my tables:

I’m trying to create a Form that contains a summary of a record but the
reason why I have to many tables is due to I need to have a lot of
information from a single record, so what I want is that from my “main†From
I can access to any other information available in the other tables, so if I
click a button in the “main†table, it should open a different form with the
information related to the record shown in the “main†form, once I see the
other information I need to consult, I can take decisions and update/modify
records from the “main†form (the records I’ll modify are going to be ONLY
from a single table which is going to be my “main/working†table, the other
tables are just to consult information linked to specific field from the
“main†table). I hope I’m explaining my self, here are the tables:

1. Main List  “Main / Working†Table: The key fields from this table are
“Unique IDâ€, “Dep Codeâ€, “Part Numberâ€, “Contact Nameâ€. This table contains
several other fields and I need to be able to modify like 5 different fields
from it including a “Comments†field so I can put several comments with
different dates (Format for each comment: dd/mm/yy + Comment, “Commentsâ€
field contains several comments in the same record).
2. Requests  The key field from this table is “Part Number†and “Dep Codeâ€.
This table contains several information regarding a request I receive that I
need to have in hand while working with the “Main List†Table information, so
I can review it and take decisions based on that.
3. “Data Base 1† Key field is “Unique IDâ€. This table includes information
from one of the 2 data base systems I use, so it includes important
information related to the records “Main List†Table contains.
4. “Data Base 2† Key field is “Unique IDâ€. This table includes information
from one of the 2 data base systems I use, so it includes important
information related to the records “Main List†Table contains.
5. “Contacts† Key field is “Contact Nameâ€. This table includes all the
contact information such as emails, address, phones, etc.
6. “Assignments† Key field is “Dep Codeâ€, This table includes all info
related to what dept is in charge of doing something related to the request.
7. “Department Code vs Reportable Code† Key field is “Dep Codeâ€.

I know it is a lot of information but the issue I have is that I have all
this information in a single Excel file in different tabs (All with vlookup()
and some macros, so it is getting really BIG and excrementally slow), also
the integrity of my information is not safe in an excel file. I have really
good bases and knowledge in VBScript, I was thinking in create an excel file
and make a connection with my tables in access so it can be more
user-friendly and more customizable (but I have never done a connection
between excel and Access thru VBScript). I really appreciate your comments
based on the information I just gave you.

Thank you!