Add new record from a form to a table, then record new ID

[Darcy Highton]

I have a form [frmSubject]based on "qrySubject" with field [SubjectName].
In the after update event of [SubjectName] I would like to have:

- the [SubjectName] value Inserted into "tblSubjectDetail(InfoReq)"
-The newly created autonumber ID from "tblSubjectDetail(InfoReq)" recorded
-Record new ID number on the [frmSubject] form.

I've tried researching this on the discussions group but still can't seem to
get my head wrapped around how to do it. Do I use insert into, addnew,
bookmark, rsmove0...

I'm new to Access (Access 2003) and VBA, any help would be greatly
appreciated. Thanks in advance.

 

[Klatuu]

I am not sure of what you are asking, but if qrySubject is based on
tblSubjectDetails and the form is a bound form and all the controls where
you enter values are bound to fields in the form's record source
(qrySubject), you don't have to do anything other than create a new record
and even then, no code may be required depending on how you navigate the
form.

If you use the form's built in navigation buttons, clicking on the button
with the * on it will create a new empty record. If you table has an
Autonumber field, it will be populates as soon as you type the first
character into the first field, but it may not be visible until the record
is saved.

If your question is how to populate a field in a different table with data
on your form, then you first need to question why you would be duplicating
data in another table. But, the time and place to do that would be in the
form's after update event.

 

[Darcy Highton]

Thanks for your reply Klatuu. I was able to piece together the code I needed.
Here it is (If someone else is trying to do something similar):

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngID As Long
Dim strSQL As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblBidDetail(SubjectInfoReq)")
If Me.[BidDetail(SubjectInfoReq)ID] = 0 Then
With rs
..AddNew
!SubjectName = Me!SubjectName
..Update
..Bookmark = .LastModified
lngID = ![BidDetail(SubjectInfoReq)ID] ' this is the autonumber field
..Close
End With
Me.BidDetail_SubjectInfoReq_ID = lngID
Set rs = Nothing
Set db = Nothing
Else
strSQL = ("UPDATE [tblBidDetail(SubjectInfoReq)] SET
[tblBidDetail(SubjectInfoReq)].SubjectName = '" & Me!SubjectName & "'" & _
" WHERE ((([tblBidDetail(SubjectInfoReq)].[BidDetail(SubjectInfoReq)ID]) = "
& Me![BidDetail(SubjectInfoReq)ID] & "));")
db.Execute strSQL, dbFailOnError
End If
DoCmd.RunCommand acCmdSaveRecord
Exit Sub
End Sub

I am not sure of what you are asking, but if qrySubject is based on
tblSubjectDetails and the form is a bound form and all the controls where
you enter values are bound to fields in the form's record source
(qrySubject), you don't have to do anything other than create a new record
and even then, no code may be required depending on how you navigate the
form.

If you use the form's built in navigation buttons, clicking on the button
with the * on it will create a new empty record. If you table has an
Autonumber field, it will be populates as soon as you type the first
character into the first field, but it may not be visible until the record
is saved.

If your question is how to populate a field in a different table with data
on your form, then you first need to question why you would be duplicating
data in another table. But, the time and place to do that would be in the
form's after update event.

I have a form [frmSubject]based on "qrySubject" with field [SubjectName].
In the after update event of [SubjectName] I would like to have:

- the [SubjectName] value Inserted into "tblSubjectDetail(InfoReq)"
-The newly created autonumber ID from "tblSubjectDetail(InfoReq)" recorded
-Record new ID number on the [frmSubject] form.

I've tried researching this on the discussions group but still can't seem
to
get my head wrapped around how to do it. Do I use insert into, addnew,
bookmark, rsmove0...

I'm new to Access (Access 2003) and VBA, any help would be greatly
appreciated. Thanks in advance.