Append record (1 Viewer)

[margt_a]

I have a form where you click a command button, and Access appends the selected (proposal) record to the (projects) table. The code then loads the form for the new table. What I can't make the (Projects) form do is jump to the newly appended record on load. It always loads to record (1). The projects form is a general use form, which can be loaded from the switchboard as well, so the code needs to be on the proposal form somewhere. Any suggestions?

Margaret

 

[Mile-O]

In the form's Load event put this line

DoCmd.GotoRecord , , acLast

 

[margt_a]

Not quite what I need

It didn't quite work. I pasted the code after the code to open the form on the (proposals) command button (see below). The (projects) form opened to a record in the middle of the table. Would using the DoCmd.GoToRecord with link criteria work?

Thanks for your help!
Margaret

Code for the command button:
---------------------------------------
Private Sub cmdProposalAppend_Click()
On Error GoTo Err_cmdProposalAppend_Click

Dim stDocName As String
Dim stFrmName As String

stDocName = "qryProposalAppend"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stFrmName = "frmProject"
DoCmd.OpenForm stFrmName, acNormal, , , acFormEdit
DoCmd.GoToRecord , , acGoTo

DoCmd.SelectObject acForm, "frmProposal"
DoCmd.Close


Exit_cmdProposalAppend_Click:
Exit Sub

Err_cmdProposalAppend_Click:
MsgBox Err.Description
Resume Exit_cmdProposalAppend_Click

End Sub

 

[Mile-O]

Try this instead:

Private Sub cmdProposalAppend_Click() 
On Error GoTo Err_cmdProposalAppend_Click 

Dim stDocName As String 
Dim stFrmName As String 

stDocName = "qryProposalAppend" 
DoCmd.OpenQuery stDocName, acNormal, acEdit 

stFrmName = "frmProject" 

DoCmd.OpenForm stFrmName, acNormal, , , acFormEdit 
DoCmd.Close acForm, "frmProposal"


Exit_cmdProposalAppend_Click: 
Exit Sub 

Err_cmdProposalAppend_Click: 
MsgBox Err.Description 
Resume Exit_cmdProposalAppend_Click 

End Sub

 

[margt_a]

Thanks, Mile-o-phile, for the tip. But it didn't solve the core problem, which is making my main (projects) form jump to the new record. I think the source of the problem is that the (projects) form opens with the data sorted by a Mid() expression. That would cause a new record to appear in the middle of the form, and acLast wouldn't work.

Could I bookmark the Project Number from the (proposals) form somehow and carry that number into the main form? A GoTo expression may work then.

Help, Please!
Margaret.

 

[Pat Hartman]

Use the where argument of the OpenForm method.

 

[margt_a]

Thank you, thank you, thank you, Pat. As usual, your answer was right, and so simple!

margaret.