Edit/Add Records intermediately linked tables in a single form

Is it possible to use a single form to edit records/add records to intermediately linked tables in a single form.

I have not had any lock finding answers on my own.

The attached PDF represents an overview of the situation.

I have also attached the database.  I have created a Query "Qry_MasterAllCatergories" and corresponding Form that brings all the fields together.  However, neither will allow me to edit/add.

What is simplest fix.  I have complete flexibility with database design.  Data has been collected but not yet entered except that to the GeneralData table.

I hope I provided enough information.

Timothy
AddUpdateChallenge.pdf
PSAssm-2013-0924-143211.accdb
TimothyAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What's the purpose of your database?

And why isn't Little Horse Island in the mix (my dad lived there for many years, just outside of Beaufort, SC).

Your design appears to be off - generally speaking, anytime you see columns with numbers in the names that's a dead giveaway - but it's hard to know this without knowing what your database is for, and how these data items are related.

IMO you've got some work ahead of you before you start worrying with form design, but in general you cannot update multiple tables from a single form. You can use subforms, if the tables are all linked to a single "master" table, and if everything is related together.
0
TimothyAAuthor Commented:
Thanks for your quick reply.

I understand about using numbers:  
I used Autonumbers for speed and accuracy in the primary indexes and links to the Autonumber generated.  I had hope the foreign indexes would be automatically added by primary index as it advanced.  The numbers in table names  was used injunction with alpha for simple sorting in the directory.

I have to tack data by date, PS, and categories, tbl01 through 13.  Data will be entered in the same way in the multiple fields.

Data with in each table is a number if it is a score or a calculation or other wise text memo in most cases.

No for the big issue.  Presently Date table is not linked directly to GenData table.  General Data is non changing data each PS there for it does NOT have a date ID.  All other tables have DateIDs and PSIDs.

I can certainly build queries/form for PS data and Date data.  Are you say for example a form based on Date Data with subform build on PS data will actually allow edits/addition of records in All 15 tables?

Timothy
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I think you're confusing my comment. I didn't really mention AutoNumbers, and it seems as if you've used them correctly (for the PK of your tables).

I'm referring to tables named with numbers. While this is not really a problem in and of itself, in general when you see tables (or fields) with numbers in their names, it indicates problems with the design of the table.

With that said: It's impossible to say if your design is right or wrong without knowing what your database is intended to do.

But to answer your question:

Yes, you could perhaps create an updateable query with multiple tables, but the more complex your query the more difficult you'll find the update. For example, if you have tables related in a 1-to-1 fashion, you could build an updateable query with those. If you have tables related in a 1-to-Many fashion you could not ... this is where you'd use a Form - Subform.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

TimothyAAuthor Commented:
Thank you for your continued interest in my challenges.

To help you understand my issue I have attached a completed database "PSAssm-2013-0917" of one segment of the work.  It works ok.  There are a number of issues needing improvement.  Major is the 13 input tables do not have a date reference.  So the next time data is gathered an obvious problem will arise.  Data is inputted into all tables by the single tabbed form, "frmTotal".

As for the databases themselves the "PSAssm" attached and the current database attached earlier in this log and under discussion, they both will have multiple score and memo fields.  Score field are weighted and averaged by category (each of the 13 tables is a category), aggregated divided by the sum of all categories used in each PS with additional internal and end calculation.

So I have two issues.  One new  work needs to correct the lack of a date issue.  Secondly, in not to distant future, up date original Database to incorporate dates already stored in a date table.

In an effort to focus on the main problem I see, which I fully understand my not be the real issue,  I have developed sample database, called "TA_Learn".  I have not hand any luck getting a query to create a record in all three tables and input information.  This database has not data.  One way I understood to work through the unrelated date issue was to create and concatenated field to develop a unique ID based on PS_ID and Date.  I have done this as you will see in tbl_AssDate.  However, not mater what I try I am not able to develop a one to one relationship with tbl_01.

I generally, seem to have forgotten what I thought I knew.  Your continued help is appreciated.

Timothy
PSAssm-2013-0917-0906.accdb
TA-Learn.accdb
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So it looks as if you're storing data collected from wells or something of that nature?

and I assume the various tables represent various "metrics" you wish to store? For example tbl_05_Capacity stores various "capacity" measurements?

If so, what's your goal in this?

You mention "Dates" - what is the relevance of your Date? Is it an Inspection Date, for example?
0
TimothyAAuthor Commented:
"If so, what's your goal in this?"    Yes, each table, 01 through 13, stores asset conditions with some calculations and comments .

The "Date" is mentioned is the inspection date.  Since this is a multi date project, the date field drives the "PS_ID" away from a one to one relationship, if "PS_ID" is directly tied to the "date".

As I now see the issue tying the "date" to the "PS_ID" which is a unique valve.   Additionally, each "PS_ID" ties all 13 tables and "GenData" together uniquely.

Again thanks for your continued help.

Timothy
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You didn't really answer my question regarding your goal ...

It seems that tblPS_GenData stores information about the Well Location (assuming these are wells).

Is that correct?

And then tblPS_Master stores ... what exactly?

And tblVisitDate is used to store (apparently) random dates, which would presumably be the date a Well Location was visited?

And then your other tables would store measurements and such your field techs would gather when they make the visit?

And you want to be able to store multiple Visits to each Well Location?
0
TimothyAAuthor Commented:
"It seems that tblPS_GenData stores information about the Well Location (assuming these are wells).  You are Correct "tblPS_GenData " store non-changing asset information.

The "PAssm-2013-2013-0917-0906.accdb" is for Sewer Pump Station,  " PAssm-2013-0924-143211.accdb"  is for Water Booster Pump Stations.  I have since changed the database names to reflect their function.

However, Asset Condition is expanding to many other non Pump Station items.

"And then tblPS_Master stores ... what exactly?"  It had meaning in ("PAssm-2013-2013-0917-0906.accdb" Sewer Pump Station).  However, present structure of (" PAssm-2013-0924-143211.accdb" Water Booster Pump Stations) is not using it.  "tbl_GenData" has taken over its function.  I should have deleted "tblPS_Master " in Water Booster PS database.

"And tblVisitDate is used to store (apparently) random dates, which would presumably be the date a Well Location was visited?"  That is correct.  This random date is to be tied to each of the 13 numbered tables which house the assessed condition.

"And you want to be able to store multiple Visits to each Well Location?"  We may have a semantic issue here.  "Well Location" if tied to "PS_ID" yes.

I hope the above is helpful.

I greatly appreciate you working with me.

Timothy
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Okay ... so you have Assets (Water Booster Pumps, Sewer Pumps) and you need to store (a) information about those assets and (b) information about inspection visits to those locations.

If so, you need a structure something like this:

tAssets (your Sewer or Water Booster Pumps)
-------------
AssetID
AssetName
AssetLocation
etc

tInspections
--------------------
InspectionID
InspectionDate
InspectionDetails
etc etc

And then you would create a Join table:

tAsset_Inspections
----------------------------
AssetInspectionID
AssetID
InspectionID
AssestInspectionNotes
etc etc

And you would then relate all of your "inspection" tables (the ones where you store your data gathered by the field techs) to a specific AsssetInspection record:

tbl_03_WPipe
---------------------
ID
AssestInspectionID
etc etc

You don't need to store the Date_ID or the MS_PS_ID - those would come from the Asset_Inspection table.

To represent this in a form, you'd create a form based on Asset_Inspection, and then create subforms based on all of your inspection tables. Add your subforms to the main form, and relate them all on the AssetInspectionID fields.
0
TimothyAAuthor Commented:
Thanks, thanks.

I am assuming that using the form with its sub-forms will allow creation/edits of Assets, inspection dates as well as enter inspection data into the various tables.  This has been the  objective.   If not please suggest ways to accomplish this objective seamlessly.

It will take me a while to set this up.

I hope not to have many more questions.

Thanks again.

Timothy
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, that would allow you to add a new Inspection (i.e. a new "Date"), and you'd then be able to add as many records to each subform as needed.
0
TimothyAAuthor Commented:
Thank you so very much.

You help is and has been greatly appreciated.

Timothy
0
TimothyAAuthor Commented:
I have begun to work on your suggestions.

I am sure I do not understood  more than I would like to admit.  

First in your instructions:

--- tInspections
--------------------
InspectionID
InspectionDate
InspectionDetails
etc etc

And then you would create a Join table:

tAsset_Inspections -----

How do I create an interactive "join table"?   I thought you may have meant join query. I  that but cannot get it to work.

I have developed multiple simple "Trials" database structure as I understood you suggested.  I know I am missing something very simple but I cannot develop the system to even enter all Autonum IDs.

I have attached one version of this simple database structure.  Would you please correct it  and send it back to me repaired.

Frustrated and out of time.

You help is greatly appreciated.

Timothy
Trial01.accdb
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your structure is right. You'd add Assets, and then you'd add Inspections (to tInspections), and then you'd add a new record to tAsset_Inspections when you needed to create a new inspection for that asset.

In general, you would have a form that would allow you to add a new Asset, another to add a new Inspection, and then another to add a new Inspect for an Asset.

Once you add the new Inspection, you'd then open another form where all of your inspection findings would be entered. This is the form where you'd have multiple subforms, and all of those subforms would be linked back to the tAsset_Inspection table by storing the value of tAsset_Inspection in the various subtables - just like you've done with tbl_01, where you added a field named "AssetInspection". This would store the value from tAsset_Inspection.AssetInspectionID.
0
TimothyAAuthor Commented:
I have done as you suggested.  

I have attached a PDF file and accdb database that shows the tables I have put together.

Is there a less cumbersome way to populate the  "tAssInspec" table with the ID data from "tAsset" and "tInspection"?  I  have tried to create through both Comboboxes and subforms to both pick  existing IDs and/or create new IDs for " tAsset"  and "tInspection" while generating new record in "tAssInspec".  I have not been able to get this to work.

I have found lots of help on retrieving remotely related data, but nothing on how to systematically efficiently input the data in such situations.   If you know where I might fine such help, just let me know.  Or would you detail the next step in this process. (Don't forget how limited are my database skills.)

As always, your help is appreciated.

Timothy
TrialTable-structure.pdf
Trial1003.accdb
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access can populate those fields for you. Just set the Link Master Field and Link Child Field on the Subform control, and Access will automatically populate that data for when you select a Child record.

So in your example database, in Form "FtAssInspec_Asset_tAssInspec", you'd set this in the "FtAsset_Asset Subform":

Master Field: tAsset_AssetID
Child Field: AssetID

And in the FtInspection_Inspection Subform:

Master Field: InspectID
Child Field: InspectID

To do this, select the Subform control, then select the Data tab in the Properties. Click in the Link Master Field, and then click the Build button (the button on the right that appears when you select that field). Access should auto-suggest those fields.
0
TimothyAAuthor Commented:
I  thought I followed your inspections but must not have.  I still do not have a working system.

I created MainForm_tAssInspec_ComboLink.  
A:  Changing an existing AssInspec Record works wonderfully.  Creating a new AssInspec record is another mater.  
B: Creating from an existing tables fields also works fine, but I have NOT been able to have the Combobox  show a second column during selection.  Seeing only the ID is not enough for a human to make the right selection.  I know this must be simple, but not for me.
C: Adding new record through the Subforms to tAsset and tInspection is problematic.  Adding records to these tables is not bad, but once I add a new field to tAssInspec on the Mainform using the combobox, both subfoms reset themselves to New.  This is not terrible, but then one must remember the ID of the not yet-entered field because everything is now blank.  I have attached MainFormLinked.pdf showing this.  I have also attached the working accdb structure file should you wish to look at it.

I then created Mainform02_tAssInspec_CombNOLink.  I disassociated the Subforms form the Main form.  I then continued to use the comboboxs.  This gives more flexibility but is messy.

If I could get your issues resolved with your solution, I know I would be better off.

Thanks for your continued help.

Timothy
MainFormLinked.pdf
Trial0031.accdb
0
TimothyAAuthor Commented:
While waiting for a response above, I converted the database with the first run of input to the to the structure of "Trial0031" above.

Now I have two additional challenges which are most likely connected.
1.   I can no longer edit or add records through the form.
2.  I have somehow isolated  table "tAssInspec" from the form because its Primary Index is allowing duplicates in the form, but they are not being stored in the table tAssInspec".  I cannot figure how the separation accrued or where the duplicate data is stored.

I have attached a Snapshot of the duplicate problem in the form of a PDF file.  I have also attached the beginning working Database.

Any help will be greatly appreciated.

Timothy
Snapshots1-2-3.pdf
PSAssm-2013-1011-1600.accdb
0
hnasrCommented:
TimothyA,

So many comments up to now.
You need to re explain the issue and upload the latest database.
This is of course if more experts other than LSMConsulting need to comment.
0
TimothyAAuthor Commented:
hnasr,

I agree there are many comments.  Here goes.

Open issues:
Database:  Trial0031.accd
Develop a stream line way to enter records and edit records through Form: "Mainform_tAssInspec_CompLink"  Note this requirement carries over to the working database to flollow.
Database: PSAssm-2013-1011-1600.accdb
Form: "MainForm_AssInspec_Linked" - Edit properties so that using this from records may be added and existing records may be edited.
I have reattached both databases for convince.

I eagerly await help.

Timothy
Trial0031.accdb
PSAssm-2013-1011-1600.accdb
0
hnasrCommented:
1st issue.
Switch main and subforms.
If you open table tAsset you see a + sign that means it is a parent table, with tAssinspec records, that suggest tAssinspec is a child table.

Main formt Asset,  record source tAsset
Su bform tAssinspec subform, recordsource tAssinspec

Check attached. tAsset_form and tAssinspec subform.

See second issue later.
Trial0031-2.accdb
0
TimothyAAuthor Commented:
hnasr,

Thanks for working with me on this.  

It seem where I messed up was in setting my parent child relationship.  That being said the problem seem to arise from my desire to create a child from dual parent tables. All works as long as there is only a single parent.
In order to  develop a new record in tAssInspec  (the child)  the parents (tAsset and tInspection) furnish either data from existing records or new record(s) are created in the parent tables) which is then used to build the new child record in tAssInspec.

Am I going about this all wrong?  May I ask what is the most logical way to construct an add/edit  form which creates or modifies records  on two unrelated parents (tAsset and tInspection) and then creates a record to the relating table (tAssInspec)?

Out of my league.

Timothy
0
hnasrCommented:
" dual parent tables"
In order to communicate, two have to unify their concepts.
I cant grasp the idea of dual parent tables.

In general, relational database means: Multiple tables (relations) are used to form one big table (relation).
Assume tables A(a, r, x), B(a,b, r, x), C(b, c, r, x)
A and B are related with field a, and B and C tables are related with field b.
A
a      r      x
1      a1      1
2      a2      1

B
a      b      r      x
1      1      b11      1
1      2      b12      1
2      1      b21      2

C
b      c      r      x
1      1      c11      1
1      2      c12      1
2      1      c21      1

A is a parent of table B and B is a Parent of table C, the 3 tables are linked as:
A ----(A.a=B.a)--------------B------(B.b=C.b)----------C
select * from A join B ON A.a=B.a Join C ON B.b=C.b;

Open in new window

Result:
Columns are referred to as:
A.a  A.r  A.x    B.a  B.b  B.r  B.x      C.b  C.c  C.r  C.x
a      r      x      a      b      r      x      b      c      r      x
1      a1      1      1      1      b11      1      1      1      c11      1
1      a1      1      1      1      b11      1      1      2      c12      1
1      a1      1      1      2      b12      1      2      1      c21      1
2      a2      1      2      1      b21      2      1      1      c11      1
2      a2      1      2      1      b21      2      1      2      c12      1
0
TimothyAAuthor Commented:
hnasr,

Thank you so much for so plain an explanation.  I believe I understand the concept.  I will try to modify your example to explain what I am try to achieve.

Assume tables       A(*a, k, m), B(*b, n, p), C(*y, a, b, x)  - * indicates PK
A and B are not related.  A and C are related with field a, and B and C tables are related with field b.
A
*a       k       m
1       a1       1
2       a2       1

B
*b       n       p
b1       1       b11
b2       2       b12
b3       1       b21

C
*y       a       b       x
y1       1       b1      x100
y1       2       b3      x101
y2       1       b1      x102

The relationship that exist are as follows:
•      “A-*a” is in a one to many relationship to “C-a”
•      “B-*b” is in a one to many relationship to “C-b”

Scope of challenge is to streamline data entry.
•      Enter data into C where there is not yet an “A-*a” record.
•      Enter data into C where there is not yet a “B-*b” record.
•      Enter data into C where there is already an “A-*a” record.  Pick “A-*a” from existing records.
•      Enter data into C where there is already a "B-*b" record.  Pick “B-*b” from existing records.

Again I greatly appreciate your help.

So you have a little understanding of my knowledge.  I have used Access on and off over the years.  I have never had any training in its design.  I do go to the web often but usually am not able to ask the question in terms that will yield a meaningful answer.

That makes your help all the more appreciated.

Timothy
0
hnasrCommented:
Can you clarify the scope showing new entered record in C after each step?
0
TimothyAAuthor Commented:
Before I start keep in mind that table "C" is a representation of some 13 similar tables that will have as many as 20 entry fields plus a few calculated fields.

Now for you request.  I hope I understood and am answering the correct question.

I will try to document flow, not mechanics at this time.

1.

Open a from that will show all existing records in existing table "C".  From this form one could:
modify any fields in table C.
Add a new record in table C.

If an existing table "A" record is to be used, view it in datasheet form and  select its PK into new table "C" record, but if no record exist in table A.

access table A in datasheet view to create new record in "A" for use in the new "C" record.
Table "B" record is also needed, it would be handled as in table "A" before.

02.

Move to a new table "C" record and process through it as in 01 above.
Now as continuation of the table started above it would look something like this:
Scope of challenge is to streamline data entry.
•      Enter New data record into C where there is not yet an “A" record.  New record C-y3:  Create/select in “A” *a=3,      k=a3,      m=m3
•      Enter New data record into C where there is not yet a “B” record.  New record C-y3:  Create/select in “B” *b=b4,      n=5,      p=W
•      Enter New data record into C where there is already an “A” record.   New record C-y3.  Pick “A-*a” from existing records.  Select A*a=1      k=a2      m=1
•      Enter New data record into C where there is already a "B" record.   New record C-y3. Pick “B-*b” from existing records.  Select B*y=b2            n=2      P=b12



Note one other thing, the concatenation of Record A-*a and B-*b is always a unique value.  This is not obvious from information thus far documented and could be C-*y PK.

Your continued help is greatly appreciated.

Timothy
0
TimothyAAuthor Commented:
I hope someone will have time to answer my last two questions shortly.

Timothy
0
hnasrCommented:
A
*a       k       m
1       a1       1
2       a2       1

B
*b       n       p
b1       1       b11
b2       2       b12
b3       1       b21

C
*y       a       b       x
y1       1       b1      x100
y1       2       b3      x101
y2       1       b1      x102

What happens for the following C records when entered:

*y       a       b       x
y4       3       b1      x103
y5       2       b4      x104
y6       4       b5      x105
0
TimothyAAuthor Commented:
Thank you so much for working on this for me.


*y       a       b       x
y4       3       b1      x103
y5       2       b4      x104
y6       4       b5      x105
 When these values are added into C, both A and B will be updated.

1.

Updated:
A
*a       k            m
1       a1            1
2       a2            1
3     New_a3   New_m
4     New_a4   New_m

2.

Update
B
*b       n              p
b1       1              b11   There would be no change in b1 n or p fields as this record exist.
b2       2              b12
b3       1              b21
b4      New_        New_
b5      New_        New_

As values in A: k, & m and B: n, & p are not related to C, input of these values need to take place on entry of their new auto PKs.


Again thanks so much for your continued work on my challenge.

Timothy
0
hnasrCommented:
Try this database.
Check form C
Tables A, B, and C.

This works for adding new C record.
Little modification is required if modifying C record.
Trial0031-3.accdb
0
TimothyAAuthor Commented:
hnasr,

I must be missing something.  When I add a new record to C that requires a New A and B records it does not automatically update these tables A and B.

I do not see any relationships between A and C or B and C in the relationships or in the form properties of C.  

Has possibly the wrong version of the database been sent?

I appreciate your continued help.

Timothy
0
hnasrCommented:
Yes was the untested  version.
Try this sample.
It should work even if you modify a or b.
Trial0031-4.accdb
0
TimothyAAuthor Commented:
Thanks so much.

I will look it sometime today and get back to you.

Timothy
0
TimothyAAuthor Commented:
I have begun to  look at the simple programing used to make this form work so smoothly.  

First, Thank you again.

Second, I have never worked with Code directly. I have been using the design function of Access and some of the SQL code.

Third, I guess it is time for me to begin learning Code.

Fourth, since I am not presently even a beginner using code but am willing to learn as required,  How would you suggest I accomplish the following:

1.

I miss lead you earlier "New" in A and B tables were just place holders.  These fields can not be populated from C.  

2.

I will have to view tables A and B to determine if a new record is required or and existing may be used.   If a new record will be added at this point a.k, a.m, b.n, and b.p would be added to tables A and B.

3.

While viewing A and B tables, if a new record is required, how would you suggest it be added?  Remember if it is added directly into table A or B then from the new open record in C  one will wish to pick these new records from A and/or B.
I have to run now.

Thanks for your continuing consideration.

Timothy
0
TimothyAAuthor Commented:
Will you be able to respond to the last questions anytime in the near future?

Your help has been greatly appreciated.  Thanks.

Timothy
0
hnasrCommented:
"Will you be able to respond to the last questions anytime in the near future?"
Sure.

How to do that?
1. agree on a sample database, it may be the last copy I uploaded if you think appropriate.
2. Give the scenario for each step required. Like:
         run form C,
         enter new values ... ,
         if .... then do ....
         if .... then do ....
          .... and so on.
0
TimothyAAuthor Commented:
Here goes:

Open C,
add new record,
Field y, PK, for C should self-populate, - when this accrues does not matter (OnOpen, OnClose, or anytime in-between)
When focus is changed to field a, existing a values, ordered by – to be determined, should display as input is added.  A.a are in a one-to-many relationship with C.a.
If an A.a exist, select an A.a so that A.a = C.a, else create new record in A and insert an appropriate A.a into this record.  Code you have presently set up works fine because it also inserts the generated A.a field into C.a
If a record was added to A, then A needs to be opened to complete data entry to k, and m.
Work flow suggest data entry into k and m should take place data entry wise adjacent to inserting A.a.
When focus is changed to field C.b, steps for C.a are repeated for C.b.


I hope this is what you were asking.

I have attached “Trial003-41” to this sending for quick access.  I played with the code to inserted blank data into A.k, A.m, B.n, and B.p.  I am, of course, not at all sure that needs to be done or is the best way if it is required.

Again I mention, how much your help is appreciated.

Timothy
Trial0031-41.accdb
0
TimothyAAuthor Commented:
Has this little issue stumped everyone?

Timothy
0
hnasrCommented:
Open C - done
add new record - done

Field y, PK, for C should self-populate: can be done with Autonumber.

The rest if then is confusing.
I expect to enter anything in y, later can be self populate. Then enter a: this value exists it should be ....

You need to think all this and give few records to enter and what we will see and what to expect. Explain the logic on how to do that as if you are working it manually.

"Has this little issue stumped everyone?"
Little issue, might me, but difficult to resolve if we don't understand the logic of the problem. As you can see this is the 40th comment to this issue.
0
TimothyAAuthor Commented:
Hnasr,

Thank you for looking into my issues and for your work thus far.

"I expect to enter anything in y, later can be self populate. Then enter a: this value exists it should be ...."
Old wording:
If an A.a exist, select an A.a so that A.a = C.a, else create new record in A and insert an appropriate A.a into this record.  Code you have presently set up works fine because it also inserts the generated A.a field into C.a.

If a record was added to A, then A needs to be opened to complete data entry to k, and m.
I have tried a rewrite.
If an A.a exist, then select A.a so that new C.a value is A.a = C.a, else create new record in A, then enter A.a to this record, then insert newly created A.a into opened record of C as C.a
Change focus to C.b and go through same process as in C.a field.

Let me know if this helps.

Thanks again.
0
hnasrCommented:
Ok try this updated sample.
Hope this improves our understanding.
Trial0031-41-1.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TimothyAAuthor Commented:
Thanks so much.  The solution is exactly what I have been trying to obtain.  

It seem the key must be that the MainForm is directly linked to only tAssInspec.  This seem to be why I could never get both subforms to work on my own.

Again thanks for your help.

Timothy
0
hnasrCommented:
Welcome!
0
TimothyAAuthor Commented:
Hnaxr,

I may responded to quickly.

I am using  MainForm_tAssInspec_ComboLink as my test.  If I open a new record, then add a new tAsset record in the subform the new AssetID populates up into the MainForm wonderfully.

However, if I then go to Subform_Inspection to add a new record,  it will not allow me to work in the SubForm_tInspections.  If I enter an Existing InspectionID field it will populate the MainForm.  

The same thing happens if I work from Subform_tInspection then go to Subform_tAssets.

 So in neither way am I allowed to add new Asset and new Inspection to new tAssInspec record.

Any suggestions?

I have attached the file for your convenience.

-Timothy
Trial0031-41-1-Help.accdb
0
hnasrCommented:
Timothy,

There is a design issue here. In relations you have:
tAsset 1-------------M
                                     tAssInspec 1 --------------------1 t01Catag
tInspection 1 ------M

With referential integrity.
This implies using the main form as the subform.

If you want to use it as the Example given (Form C), you need to remove the relationships.

The example in code then can be modified to deal with the respective object names.
0
TimothyAAuthor Commented:
Hnasr,

Thanks!

I will work on it and get back to you.

Timothy
0
hnasrCommented:
Other thing
tables tAsset  and tInspection  keys are autonumber, which needs to be changed to number.
Autonumber field, fills automatically, and not allowed to be filled by code.

If you want to implement the idea on your design, I suggest you open a new question.
Your design needs changes, and you may use the same subforms to reflect the behavior without opening the forms as in example.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
not allowed to be filled by code.
You can write to an AutoNumber field just as you do other fields, if you're using SQL. I'm not sure about that if you're using Recordsets.
0
TimothyAAuthor Commented:
Hnasr,

I appreciate your continued advice.

As I proceed, I will gladly open a new question.

I understand the best way to work on design is in SQL, but I am still more familiar with Access.

Timothy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.