Add New Record to Table

[wonderwhy]

Hello,

I have two tables which have a one to one relationship (enforce referential integrity, cascade update/delete). When in the form (frmProtocol) for the main table (tblProtocol) and the user clicks on a button to open the form (frmBRFReview) based on the related table (tblBRFReview), I would like a new blank record to be added to the related table (tblBRFReview) if there is not a related record already there (and automatically assign the key field (ProtocolNumber)to the same as on the main form). If there is a related record already then that record would display.

I just can't figure out how to make this work. I know it should be easy, but...

Thanks!

Julia

 

[wonderwhy]

I haven't heard from anyone yet on this...

Basically, I am just trying to create a record in the related table with the same key field everytime a new record is created in the main table. I am having trouble doing this because of (I think) the one-to-one relationship between the two tables.

A button on the main form (based on main table) opens another form (based on related table) based on the key field in the main form. But, if I don't have record in the related table (I have added them manually in the table itself so far) then the form for the related table does not open, or rather it opens but has just a border and title but no fields.

?

Julia

 

[Eupher]

When do you want the record in the related table to be created? As soon as a new record is created in the main table? Or when the user clicks the button on the main form to open frmBRFReview?

Ken S.

 

[wonderwhy]

Thanks for your reply Ken.

I would like the record in the related table to be created, once, when the record in the main table is created. I figure since I have enforce referential integrity, cascade update/delete all set that after that any changes to the main record would be made to the related record.

I tried to do some if statement that checked for new record and if there was a new record then add a record to the related table with the same key field but it didn't work (I'm not real good with VB). I don't even know if that woud be the best way....

Any help is certainly appreciated.

Julia

 

[Eupher]

How is ProtocolNumber created in your main form/table?

Ken S.

 

[wonderwhy]

It is text field, manually created.

Julia

 

[Eupher]

Okay, create an event procedure in the Before Update event of frmProtocol and put in this code:

If IsNull(Me!MyKeyField) Then
    MsgBox "Oops!  You've left the key field blank.  Try again."
    Cancel = True
    Me!MyKeyField.SetFocus
End If

Then create an event procedure in the form's After Update event:

Dim CurDB As DAO.Database
Dim Rs As DAO.Recordset
Dim SQLStmt As String

On Error GoTo ErrHandler

Set CurDB = CurrentDb()
SQLStmt = "SELECT * FROM tblBRFReview WHERE MyKeyField = '" & Me!MyKeyField & "'"
Set Rs = CurDB.OpenRecordset(SQLStmt, dbOpenDynaset)

If Rs.EOF Then
    Rs.AddNew
    Rs!MyKeyField = Me!MyKeyField
    Rs.Update
End If

Exit_ErrHandler:
    Rs.Close
    Set Rs = Nothing
    Set CurDB = Nothing
    Exit Sub

ErrHandler:
    MsgBox "Error Number:  " & Err.Number & Chr(10) & Err.Description
    Resume Exit_ErrHandler

If you're using Access 97 or later (or is it 2K and later? I can't remember...) and haven't already done so, you'll need to add a reference to the DAO library. This should get you started. Post back if you need more help.

Ken S.

 

[wonderwhy]

Thank you, Ken. I will give this a try and let post back if I need any more help.

Julia