Creating a new record from another form and passing values

[duthiedon]

Have a form that contains fields AssociatedProject, AssociatedRelease and then the user hits a button to create a new record and a new form opens to display this newly created record which sets the fields and also sets a field called Type. There is no issue with setting the field values and the new record is created. However, when the form opens to display the record it displays another record instead (usually the one before it). I think it could be related to the fact that the db resides on a shared drive which periodically has slow connectivity. However, I'm looking for a solution, perhaps modifying the code? Any suggestions? Any help is greatly appreciated!

Here's the code:

Private Sub Command17_Click()

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant

On Error GoTo Err_Command17_Click

Set db = CurrentDb()
Set rs = db.OpenRecordset("Tbl_Main", dbOpenDynaset, dbAppendOnly)

'add selected value(s) to table
rs.AddNew
rs!AssociatedProject = Me.[AssociatedProject]
rs!AssociatedRelease = Me.[AssociatedRelease]
rs!Type = 1
rs.Update

DoCmd.OpenForm "Frm_ManageQuestionsAnswersEdit", acNormal, "", "[AssociatedProject]=" & [AssociatedProject], , acNormal
DoCmd.GoToRecord acForm, "Frm_ManageQuestionsAnswersEdit", acLast

ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select

Err_Command17_Click:
MsgBox Err.Description
Exit Sub

End Sub

 

[Frothingslosh]

Instead of going to the last record, how about opening the form with a filter limiting it to the PK value of the new record?

 

[duthiedon]

That would work I think, I'm not an expert coder, do you have an example I could follow?

 

[Frothingslosh]

Basically, in this line:

DoCmd.OpenForm "Frm_ManageQuestionsAnswersEdit", acNormal, "", "[AssociatedProject]=" & [AssociatedProject], , acNormal

In the WHERE parameter, replace "[AssociatedProject]=" & [AssociatedProject] with the new primary key value for this record. I really can't say more without knowing how your table is laid out - if AssociatedProject and AssociatedRelease combine to form the primary key, then it would be something like this:

DoCmd.OpenForm "Frm_ManageQuestionsAnswersEdit", acNormal, "", "[AssociatedProject]=" & [AssociatedProject] & " AND [AssociatedRelease]=" & [AssociatedRelease], , acNormal

Once you get that part set, you won't need to manually move to the last record, either.

 

[Frothingslosh]

You know, it also occurs to me that you might, instead, be able to just open the new form in Data Entry mode, and then update the controls on that form directly before passing the focus to that form.

 

[duthiedon]

Thank you very much, you triggered an idea so I filtered on the creation date of the record. Guess sometimes a second set of eyes does help For future use, the original code now looks like:

Private Sub Command17_Click()

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant

On Error GoTo Err_Command17_Click

Set db = CurrentDb()
Set rs = db.OpenRecordset("Tbl_Main", dbOpenDynaset, dbAppendOnly)

'add selected value(s) to table
rs.AddNew
rs!AssociatedProject = Me.[AssociatedProject]
rs!AssociatedRelease = Me.[AssociatedRelease]
rs!Type = 1
rs.Update

DoCmd.OpenForm "Frm_ManageQuestionsAnswersEdit", acNormal, "", "[AssociatedProject]=" & [AssociatedProject] & " AND [QuestionCreationDate]= now()", , acNormal


ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select

Err_Command17_Click:
MsgBox Err.Description
Exit Sub

End Sub

 

[Frothingslosh]

Just a minor nitpick - if you're filtering on date, it might be better to use the Date() function rather than the Now() function.

In most cases, admittedly, it won't actually make a difference - I'm just a purist like that.