If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Autonumber foreign keys and subforms
Hi,
Hope someone can help. Here's the scenario: I have two MSAccess tables MainTab and SubTab. MainTab has an autonumber 'id' column as primary key. Subtab has this id column as a foreign key. There is a one-to-many relationship defined on MainTab and Subtab using the id column. The join type is include all from MainTab and only rows with matching value from SubTab. Referential integrity enforced; cascading updates and deletes. I have a form MainForm based on MainTab. It has a subform based on SubTab. Linked child and master fields are 'id'. Open the form. (a) Put some data into MainForm, and its id gets set to 1, and the default value for new rows in the subform displays as 1, as desired. (b) I press the |* button to create a second new record in MainForm, but don't put any data in it. The default value for id displays as "(AutoNumber)". I go straight to the subform and enter some data. The id field in the subform stays blank, presumably because the linked master row doesn't exist. If I now go back to the MainForm and enter some data, the new row in the subform disappears because its id value doesn't match the one that now gets generated from the AutoNumber. So my question is, is there any way to get insertion of a new row in the subform to force generation of a matching row in the master MainForm, and get the autonumber id from that MainForm back to the subform data? - Ed. |
#2
|
|||
|
|||
Autonumber foreign keys and subforms
I think that if you put
if Me.NewRecord then Me.Update into the Current event of the main form, the autonumber will be generated immediately after the new record is created. BUT, you can create a lot of empty records in the main table that way. Pavel Ed Havelaar wrote: Hi, Hope someone can help. Here's the scenario: I have two MSAccess tables MainTab and SubTab. MainTab has an autonumber 'id' column as primary key. Subtab has this id column as a foreign key. There is a one-to-many relationship defined on MainTab and Subtab using the id column. The join type is include all from MainTab and only rows with matching value from SubTab. Referential integrity enforced; cascading updates and deletes. I have a form MainForm based on MainTab. It has a subform based on SubTab. Linked child and master fields are 'id'. Open the form. (a) Put some data into MainForm, and its id gets set to 1, and the default value for new rows in the subform displays as 1, as desired. (b) I press the |* button to create a second new record in MainForm, but don't put any data in it. The default value for id displays as "(AutoNumber)". I go straight to the subform and enter some data. The id field in the subform stays blank, presumably because the linked master row doesn't exist. If I now go back to the MainForm and enter some data, the new row in the subform disappears because its id value doesn't match the one that now gets generated from the AutoNumber. So my question is, is there any way to get insertion of a new row in the subform to force generation of a matching row in the master MainForm, and get the autonumber id from that MainForm back to the subform data? - Ed. |
#3
|
|||
|
|||
Autonumber foreign keys and subforms
Ed, this is a very important question.
As you found, Access permits a Null value in a foreign key, even if you have Referential Integrity enforced. Occassionally that is very useful, but in most tables you need to prevent that from happening. The solution is very simple: 1. Open your related table (SubTab) in design view. 2. Select the foreign key field. 3. In the lower pane, set its Required property to Yes. There is now no way that Access can save a record into this table if the foreign key field is null. At this point, the user gets the message that the record cannot be saved after they have gone to the trouble of filling it out in the subform. To give them the message when they begin filling in the subform record, cancel the subform's BeforeInsert event: Private Sub Form_BeforeInsert(Cancel As Integer) If Me.Parent.NewRecord Then Cancel = True MsgBox "Fill in the main form first." End If End Sub This issue is one of six discussed in article: Common Errors with Null at: http://allenbrowne.com/casu-12.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ed Havelaar" wrote in message s.com... Hope someone can help. Here's the scenario: I have two MSAccess tables MainTab and SubTab. MainTab has an autonumber 'id' column as primary key. Subtab has this id column as a foreign key. There is a one-to-many relationship defined on MainTab and Subtab using the id column. The join type is include all from MainTab and only rows with matching value from SubTab. Referential integrity enforced; cascading updates and deletes. I have a form MainForm based on MainTab. It has a subform based on SubTab. Linked child and master fields are 'id'. Open the form. (a) Put some data into MainForm, and its id gets set to 1, and the default value for new rows in the subform displays as 1, as desired. (b) I press the |* button to create a second new record in MainForm, but don't put any data in it. The default value for id displays as "(AutoNumber)". I go straight to the subform and enter some data. The id field in the subform stays blank, presumably because the linked master row doesn't exist. If I now go back to the MainForm and enter some data, the new row in the subform disappears because its id value doesn't match the one that now gets generated from the AutoNumber. So my question is, is there any way to get insertion of a new row in the subform to force generation of a matching row in the master MainForm, and get the autonumber id from that MainForm back to the subform data? |
#4
|
|||
|
|||
Autonumber foreign keys and subforms
Just to be clear - you're not using an Autonumber *AS A FOREIGN KEY*, are you? That will not work; the foreign key to an autonumber primary key must be a Long Integer (or a GUID, if it's a GUID autonumber).
-- John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public "Ed Havelaar" wrote: Hi, I have two MSAccess tables MainTab and SubTab. MainTab has an autonumber 'id' column as primary key. Subtab has this id column as a foreign key. |
Thread Tools | |
Display Modes | |
|
|