Solved Create Entry in second table from entry in first table (1 Viewer)

[kobiashi]

hey all

not used access in a while so im a little rusty, was wondering if someone could point me in th right direction.

im trying to create a new entry in a table from an entry in another table


Table 1 (TblEvent) Form 1 (FrmEvent_New)
Event_ID
Date
unit_IDFK
Vehicle_IDFK
NontrackPart_IDFK


Table 2 (TblNonTrackParts) Form 2 (FrmNonTrackPart_New)
NonTrackPart_ID
Date
PartNumber
PartName


So i have a form (Form 1) bound to a query that created new entries into table 1. (this works fine)

what im trying to do is, there is a button in Form 1, that opens Form 2. but create a relationship between the two entries via NonTrackPart_ID and NontrackPart_IDFK, but i cant seem to be able to create an entry in the second table.

 

[ypma]

Just a thought, could you not use the following in the after update event of the entry field you wish to pass to Form 1

[Forms]![Form 1]![txtbox1] = txtbox2

Just a user not an expert, so others may well have other solutions

Regards Ypma

 

[jdraw]

Can you tell us in plain English in terms of your business what these tables represent and how they relate to one another? That is, what is the business this proposed is intended to support?

Your Table1 appears to be a central entity in your business/database because of the number of foreign key type names.

Also Date is a reserved word in Access. I suggest you adjust the Date names to reflect their true meaning. Eg PurchaseDate, ServiceDate, OrderedDate...

 

[kobiashi]

Can you tell us in plain English in terms of your business what these tables represent and how they relate to one another? That is, what is the business this proposed is intended to support?

Your Table1 appears to be a central entity in your business/database because of the number of foreign key type names.

Also Date is a reserved word in Access. I suggest you adjust the Date names to reflect their true meaning. Eg PurchaseDate, ServiceDate, OrderedDate...

i work for a train maintenance company, im attempting to build an asset management database.


so table 1, is an event tracker, for events such as planned maintenance, failures, exam work etc. table 2 is the ability to add parts to a specific task, so for example on an exam lighting tubes need to be replaced if they are defective i need to be able to track to replacements


thanks for the info regards Date, i am aware of this and do exactly what you have suggested

 

[isladogs]

I suggest you add the new record direct to Table2 rather than trying to update it via the form.

I suggest you use the relationships window and create a relationship between the two tables joining on NonTrackPart_ID
Impose referential integrity and tick cascade updates
See Relationships1 - Mendip Data Systems for more info if that isn't clear to you

Alternatively, just use code (CurrentDb.Execute "INSERT INTO....") or an append query to add a new entry to the second table

 

[Uncle Gizmo]

In my Blog Here:-

Move Info from One Table to Another - Nifty Access

Move Info from One Table to Another A brief summary summary of the "OP's" (Original Posters) question HERE. I've done a set of YouTube videos to answer the question. The first answer demonstrates the INSERT INTO method of adding records to another table. This method is where you update the...

www.niftyaccess.com

I demonstrate the method Colin (Isladogs) mentioned, the insert INTO, and a method using a form.

The form method has the advantage that the record is not saved unless you want to save it.

This helps prevent accidental updates. Also you can add/edit the info if required.

 

[jdraw]

I agree with Colin and Tony. However, since you posted your question in terms of table1 and table2 ( sort of abstract analogies), and because of my own curiosity, could you provide a graphic of your relationships window or a data model of your overall approach. I've seen models for vehicle maintenance, parts manufacture etc. , but your undertaking seems broader in scope in that you have events, trackRelated and noTrackRelated parts you will Inventory/Manage.

Here are a couple of links that may be helpful or serve as a reference.
Auto repair model
Best practices/tips for Asset/Inventory Management

Good luck.

 

[kobiashi]

I agree with Colin and Tony. However, since you posted your question in terms of table1 and table2 ( sort of abstract analogies), and because of my own curiosity, could you provide a graphic of your relationships window or a data model of your overall approach. I've seen models for vehicle maintenance, parts manufacture etc. , but your undertaking seems broader in scope in that you have events, trackRelated and noTrackRelated parts you will Inventory/Manage.

Here are a couple of links that may be helpful or serve as a reference.
Auto repair model
Best practices/tips for Asset/Inventory Management

Good luck.

Would you like a relationship report?

 

[jdraw]

Sure. Just looking to put your materials into an overall context.

 

[kobiashi]

Sure. Just looking to put your materials into an overall context.

 

[kobiashi]

ive resolved the issues i had, i had the relationship the wrong way round in the tables,


Thanks for everyones help

 

[jdraw]

Glad you resolved the issue.