Addnew to both sides of a One-to-Many (find autonumber)

[Guest]

I am having problems here and I know it's something simple and stupid that
I'm missing but here goes. I have a form that creates a new record into the
table "ASSET". The table "ASSET" has a one-to-many relationship with
"INTERVAL". I am able to add the record to "ASSET" no problem, but I need to
add a record into "INTERVAL". My "INTERVAL" table uses the ASSET_ID as a
foriegn key from "ASSET". The follow is my code:

Private Sub cmdSAVE_Click()
Dim MYDB As DATABASE
Dim rstINTERVAL As Recordset
Dim strASSET_NAME As String
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Set MYDB = OpenDatabase("test.mdb")
Set rstINTERVAL = MYDB.OpenRecordset("INTERVAL")
With rstINTERVAL
.AddNew
!ASSET_ID = Me.txtASSET_NAME
.Update
End With
MYDB.CLOSE
End Sub

I think the problem is the statement
!ASSET_ID = Me.txtASSET_NAME
I think I need to find the autonumber that was generated when I save the
record into the "ASSET" table. Please help me out. Thanks.

 

[Guest]

Hi,

Do you have a field on your form (hidden perhaps) that shows the AssetID?
If not, add a field called lngAssetID and bind it to the AssetID autonumber
field. Then, instead of using
!ASSET_ID = Me.txtASSET_NAME
you would use
!ASSET_ID = Me.lngAssetID

Of course, I'm not sure why you would want to muck around opening a database
etc etc - if you have the tables linked to your front end (or they are in a
single file), why not just use an Insert query to add the record?

Hope this helps.

Damian.

 

[Rick A.B.]

I think the problem is the statement
!ASSET_ID = Me.txtASSET_NAME
I think I need to find the autonumber that was generated when I save the
record into the "ASSET" table. Please help me out. Thanks.

Don't quite understand where txtASSETT_NAME comes from or what it is
but if you put your PK ASSET_ID somewhere on your form and make it
hidden just do,

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("INTERVAL")
rst.AddNew
rst!ASSET_ID = Forms!NAMEOFFORM!ASSET_ID
rst.Update
Set rst = Nothing
Set db = Nothing