Tables that must be updated by different forms

[LATECHmatt05]

Hello,

I have a rather odd problem I have one table that has a primary key and ofcourse some other fields. Now I have another table with the same fields ,but no primary key. What I need to do is to create a record in the table with the primary key and have that record go into the table without the primary key. Now I tried creating a one-to-many relationship to acccomplish this but it didn't do that. I need the record in both tables because later on I will take the record in the second table (no primary key) add edit it and hope to have it be saved as a modified record and save the existing record. I know this sounds very convoluted but I really don't know how else to do this.

Thank you for any help
Matthew

 

[mpastore]

Add the primary key to the second table.

Mike Pastore

Hats off to (Roy) Harper

 

[LATECHmatt05]

I tried that and it didn't work I probably did something wrong. all I want is whatever I type as a record in one table to wind up in the other table. I am sorry for the inconvenience but I am very new to Access.

 

[willir]

Matthew

There are various ways of attacking this problem. The gapping hole is that you have not described what your primary key is - autonumber, manually generated number, text code??? The type of primary key will decide the best approach.

Regardless, you still need the primary key - this is fundemental.

Having one form grab data from another form is do-able. The trick here is to make sure one form is updated so the primary key is accessible.

...Or using a centralized module is also do-able - both forms call the same update module.

If you can post back on the type of primary key you are using, and the format of the data you are writing, we can provide more specific advice.

Richard

 

[LATECHmatt05]

I have one table with a primary key that is autonumber I call that field RedispatchNo.

Below is how my two tables look
TABLE1
(Primary key)RedispatchNo. Date Time CurrentMW RequestedMW

TABLE2
RedispatchNo. Date Time CurrentMW RequestedMW

Table 2 needs to take records for each RedispatchNo. from Table 1 and put them in Table 2 with same structure, but no primary key because in table 2 redispatch records will be duplicated with different values in the time field thus creating a new record in Table 2. In other words TABLE 1 keeps track of events on an incident by incident basis where table 2 catalogs the incidents as the progress through time.
Sorry so long but rather odd problem wouldn't you say.

Cheers
Matthew

 

[willir]

Mathew

You have two issues here

First, the primary key
I suspect the problem is that the one record on the main form may not have been updated.

Access generates the autonumber as soon as the "insert" record event. In otherwords, as soon as you start typing in a new record, Access generates the autonumber. But this record with the autogenerated number has not been committed / written to the table.

This creates two problems
- Under some conditions, the same autonumber can be generated by different new record activities. (More likely under a multi-user application.)
- Related records that need the primary key do not have easy access to this number.

Before addressing "how to" for this problem, you actually have a bigger problem.

Your second table, from my perspective, is a "history" table, and you actually have a one-to-many relationship.

As part of the normalization process, each record should be uniquely identified. You could do this by RedispatchNo + Date + Time. But this may be awkward.

Your second table should be designed as...

ReDispatchHistoryTbl
HistoryID - primary key
RedispatchNo - foreign key ReDispatchTbl
DispatchDate
DispatchTime
CurrentMW - **
RequestedMW - **

** I am not sure if you actually need these two fields since it is on the master table - unless you are capturing different information. They should be in one location or the other.

Now getting back to your problem - grabbing the primary key from one form for use in the other.

If I am correct in that you have a one-to-many relationship, then a common approach is to use a form / subform approach. If this solution is acceptable to you, access to the primary key is extremely easy.

I have a "Call" tacking system used to track call resolution. The approach I use is as follows...

The main form captures basic information including the CallID. At the bottom of the form, I have tab form that captures other related information. One of the tabs has a subform that displays the captured "history" in a contineous subform. The subform displays basic info - date, time, brief descritpion. On the subform, a small command button can be clicked to open up the details for the specific history record.

When a new record is created, the end user fills in the basisc info - this generates (but does not commit) the CallID. The end user clicks a button and the same form associated to the command button on the history subform, opens up as a new record. The CallID is passed as the OpenArg (OpenArgument) when the form is opened, and assigned as to the foreign key, also called CallID, to the history record.

To recap...
- Main form for the primay table
- Contineous form (possibly as part of a tab form) display a brief record of the history.
- A single form for the history record.
- The single form for the history record is opened in two ways. A command button on the main form opens the history for a new record where the OpenArg passes the RedispatchNo. On the history contineous form, a command button opens up the single history form for the specific record. The command button wizard will do the grunt work for you.

Richard