Using the field on one form to append another table

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

Hi,

I have a slight problem. I have a main form to track all
my records. I want to be able to push a button and the
tracking ID from my main form will create another record
in a different table while transmitting the value of the
tracking id to the second form. Then I can use the
DoCmd.OpenForm stDocName, , , stLinkCriteria to open to
the newly created record on the second form. After several
buggy attempts to do this, I would like to know if someone
has a suggestion.

Thanks,

MARC.
 
Marc,

Dim db As Database
Dim rs As DAO.Recordset
Dim PK As Long

'Normally, you would use db.Execute to save the record, but
'since you want the primary key of the record you're about to
'save, you have to use a Recordset.
Set db = CurrentDb
Set rs=db.OpenRecordset("tblMyTable",dbOpenDynaset)
rs.AddNew
rs!Field1 = Me!txtTextBox1
rs!Field2 = Me!txtTextBox2
rs.Update

'Get the Primary Key of the record you've just saved
PK = rs!PrimaryKeyField

'Clean up
rs.Close
Set rs = Nothing
Set db = Nothing

'Open the new form
DoCmd.OpenForm "frmMyForm", , , "[MyPrimaryKey] = " & PK

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Hi,

I have a slight problem. I have a main form to track all
my records.
Click to expand...

To be more precise: you have a main TABLE containing all your records.
The form doesn't contain any records - it displays them.
I want to be able to push a button and the
tracking ID from my main form will create another record
in a different table while transmitting the value of the
tracking id to the second form. Then I can use the
DoCmd.OpenForm stDocName, , , stLinkCriteria to open to
the newly created record on the second form. After several
buggy attempts to do this, I would like to know if someone
has a suggestion.
Click to expand...

Two:

- Make the second form a Subform and set its Master/Child Link Field
to TrackingID.

- If you really need a pop-up second form you need to use some VBA
cod. The stLinkCriteria will display any *already existing* records
with the current tracking id, but (unlike the subform) will not
automatically create new records with that ID.

To do so, use the OpenForm's OpenArgs property to pass the ID; and in
the second form's Open event, set the DefaultValue property of the ID
field to OpenArgs.
 
Back
Top