Go to new record on a form 1

[hansretallick]

I have a form which opens a report. When the report closes, I want to go to the next record on the underlying form which remains open while the report is generated.

I have used the following command in the "on Close" event for the form, but I get an error "The command or action 'GoToRecord' isn't available now".

DoCmd.GoToRecord , , acNewRec, "Forms!frmInvoiceLandlord"

 

[hansretallick]

I have just noticed that I stated "I have used the following command in the "on Close" event for the form" - I meant the Report rather than the Form.

 

[randy700]

Private Sub ShowReport_Click()
    DoCmd.OpenReport "G600", acViewPreview, , , [b][red]acDialog[/red][/b]
    DoCmd.GoToRecord , , acNext
End Sub

Randy

 

[TheAceMan1]

How are ya hansretallick . . .

[blue]randy700[/blue] has you except for one thing ... what do you intend to do [blue]if your on the last record![/blue] Surely you can't goto next!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hansretallick]

Hi Randy700 and TheAceMan1. Many thanks for your replies. I must admit that I was in a rush and didn't explain properly what I am trying to do, and also made some mistakes in my original post. I'll start again. The form is used to open a report based on a record which is selected by means of a combo box. Whilst the report is open (I also have code which saves the report as a PDF etc), the form remains open. When the REPORT is closed, I need to save the record on the form (I have already got the code in place for that) and when the user is returned to the form, a NEW RECORD needs to be selected. I'm sorry for the confusion caused by my poor initial posting, and I trust that this clarifies what I'm trying to do.
The whole code for the 'on close' of the REPORT is as follows - it all works OK except for the last line:

DoCmd.Close acForm, "frmMaintenanceInvoice"

DoCmd.Close acReport, "rptsubMaintenanceTemp"

DoCmd.OpenQuery "qryMaintenanceTempEmpty"

DoCmd.Close acQuery, "qryLandlordInvoiceLastDate"

DoCmd.Save acForm, "frmInvoiceLandlord"

Forms!frmInvoiceLandlord.Maintenance = Forms!frmInvoiceLandlord.MaintenanceResult

Forms!frmInvoiceLandlord.SetUpFee = Forms!frmInvoiceLandlord.SetUpResult

Forms!frmInvoiceLandlord.Rent = Forms!frmInvoiceLandlord.RentResult

Forms!frmInvoiceLandlord.Commission = Forms!frmInvoiceLandlord.CommissionResult

Forms!frmInvoiceLandlord.VATAmount = Forms!frmInvoiceLandlord.VATResult

Forms!frmInvoiceLandlord.PrevBal = Forms!frmInvoiceLandlord.PrevBalResult

Forms!frmInvoiceLandlord.Deposit = Forms!frmInvoiceLandlord.DepositReturn

DoCmd.GoToRecord , , acNewRec, "Forms!frmInvoiceLandlord"

 

[PHV]

DoCmd.GoToRecord acDataForm, "frmInvoiceLandlord", acNewRec

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hansretallick]

Thanks for your reply, PHV. Unfortunately I get error message "Run-time error '2046': The command or action 'GoToRecord' isn't available now. I am using Access 2010 by the way.

 

[TheAceMan1]

hansretallick . . .

In order for [blue]DoCmd.GoToRecord[/blue] to work frmInvoiceLandlord has to have the focus. Also ... using a form object would make your code more readable as follows:

[blue]   Dim frm As Form
   
   Set frm = Forms!frmInvoiceLandlord
   
   DoCmd.Close acForm, "frmMaintenanceInvoice"
   DoCmd.Close acReport, "rptsubMaintenanceTemp"
   DoCmd.OpenQuery "qryMaintenanceTempEmpty"
   DoCmd.Close acQuery, "qryLandlordInvoiceLastDate"
   
   [purple][b]frm.SetFocus
   DoCmd.RunCommand acCmdSaveRecord[/b][/purple]
   
   frm.Maintenance = frm.MaintenanceResult
   frm.SetUpFee = frm.SetUpResult
   frm.Rent = frm.RentResult
   frm.Commission = frm.CommissionResult
   frm.VATAmount = frm.VATResult
   frm.PrevBal = frm.PrevBalResult
   frm.Deposit = frm.DepositReturn
   
   DoCmd.GoToRecord acDataForm, frm.Name, acNewRec
   
   Set frm = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hansretallick]

That was brilliant, TheAceMan1. Works perfectly now. Very many thanks for your help, and also to everyone else who tried to help me make sense of this.