open from from newly entered record before save

In Access 2007. We enter a new record on a form. Then we click a button which has a macro action to open another form. What can I do to make the form open but linked to the new record? I understand that I need the primary key on the new form etc. If the record was saved I can make the second form open with that data. But My Question is when it's still a new record/form w开发者_JS百科e need to navigate directly onto the linked form...

Please share some direction on this..

If I am understanding your question you need to do the following:

1. Add a new record on Form1
2. Click a button on Form1 to open Form2
3. When Form2 opens, it contains the info from Form1

I have a process that does something similar in my database and we do things this way.

1. The users have a form to enter new records
2. They use a button click to post the new data to the table. During this process, I return the primary key to the new record. Then the final process of the button click, I tell it to open the new form, and populate it with the primary key I just grabbed.
3. New form opens using the PK for the record.

My code for the button click is in VBA:

Private Sub OK_Click()
  Dim rst As DAO.Recordset
  Dim rst1 As DAO.Recordset
  Dim sqlStr As String
  Dim RptID As Variant

  Set rst = CurrentDb.OpenRecordset("tble_Investigations", dbOpenDynaset, dbSeeChanges)
  ' here you need to add each of your fields from the form
  rst.AddNew
      rst![Table.Column1] = Me![FormField1]
      rst![Table.Column2] = Me![FormField2]
      rst![Table.Column3] = Me![FormField3]
  rst.Update

  ' my sql string to return the new ID of the record I just added
  sqlStr = "Select Max([ID]) as [MaxOfID] from tble_Investigations;"
  Set rst1 = CurrentDb.OpenRecordset(sqlStr, dbOpenDynaset, dbSeeChanges)
  rst1.MoveFirst

  RptID = rst1![MaxOfID]

  'here we open the Form2 with the new id.
  DoCmd.OpenForm "Frm_Details", acNormal, , "[ID]= " & RptID, acFormEdit, acWindowNormal
  DoCmd.Close acForm, "Frm_New", acSaveYes

End Sub

EDIT:

Based on what you are telling it sounds like you are doing the following:

Private Sub OK_Click()
  Dim rst As DAO.Recordset
  Dim rst1 As DAO.Recordset
  Dim sqlStr As String
  Dim RptID As Variant

  Set rst = CurrentDb.OpenRecordset("t_Evaluation", dbOpenDynaset, dbSeeChanges)
  ' here you need to add each of your fields from the form
  rst.AddNew
      rst![ExecutionLeadOrg] = Me![ExecutionLeadOrg] 'the field from your form that matches the table column
      rst![TitleID] = Me![TitleID]
      rst![t_Evaluation.EvalTypeID] = Me![t_Evaluation.EvalTypeID]
      rst![SectionID] = Me![SectionID]
      rst![LOBEvaluation] = Me![LOBEvaluation]
      'you need to continue doing this for each field on your form
  rst.Update

  ' my sql string to return the new ID of the record I just added
  sqlStr = "Select Max([EvaluationID]) as [MaxOfID] from t_Evaluation;"
  Set rst1 = CurrentDb.OpenRecordset(sqlStr, dbOpenDynaset, dbSeeChanges)
  rst1.MoveFirst

  RptID = rst1![MaxOfID]

  'here we open the Form2 with the new id.
  DoCmd.OpenForm "f_LOBevalPopUpEntry", acNormal, , "[EvaluationID]= " & RptID, acFormEdit, acWindowNormal
  DoCmd.Close acForm, "F_EvalNew", acSaveYes

End Sub

You only need to save the current record, and then launch the next form to the SAME record. As long as the form launched is model then you be fine. And to advoid any confusing here, a model form is HUGE difference then launching that of a dialog form.

However, you can also consider launching a dialog form.

When you return to that previous form, Access as a general rule will show any updates.

The code you need is this:

If me.Dirty = True then Me.Dirty = false
Docmd.OpenForm "name of next form",,,"id = " & me!id

Furthermore, when launching another form in a series of operations, it is a good idea to force a record save as per above anyway.

So, only the above two lines of code is required.