(2) tables and DoCmd.GoToRecord,,acNewRec woahs 1

[rcoutts]

I have a Form that indexes a table of invoices ("tblInvoices&quot. My invoice form has a subform corresponding to a scratch table ("tblInvoiceScratch&quot for generating the invoice report. I'm currently doing all of my invoice calculation in VB in "frmInvoices," and I'd like to populate "tblInvoiceScratch" with the results, e.g.,

    Category        Description                  Subtotal
    --------        ---------------------------- ---------
    BalanceFwd      Previous Balance             $1,000.00
    BalanceFwd      Amount Received                 500.00
    BalanceFwd      Interest @ 1.5% per month         2.00
       ...               ...                        ...

I'm having a heck of a time trying to do this. In subfrmInvoiceScratch I have a simple subroutine to add a new record:

  Public Sub pubAddRec(NewCategory, NewDescription, NewSubtotal)
    DoCmd.GoToRecord , , acNewRec
    Category = NewCategory
    Description = NewDescription
    Subtotal = NewSubtotal
  End Sub

Which I call from frmInvoices like

  Form_subfrmInvoiceScratch.pubAddRec "Balance", "Previous Balance", "$1000.00"

But the GoToRecord Action adds the new record to tblInvoices, not to tblInvoiceScratch (though the data is getting written to the 1st record of tblInvoiceScratch correctly). When I tried to be more explicit in my GoToRecord Action, e.g.,

    DoCmd.GoToRecord acDataTable, "tblInvoiceScratch", acNewRec

I get errors that say "the object 'tblInvoiceScratch' is not open." So, as a test I open it manually which stops the error, but still no new records. I tried changing the "RecordSource" Property of the tables, but that didn't help.

Someone mentioned that RecordSets are the way to accomplish what I want to do, but I looked into them and they made my head spin.

Can someone recommend a course of action here?

Thanks!
Rich

 

[MangroBongacello]

Before you call pubAddRec procedure move focus to subform

DoCmd.GoToControl "subfrmInvoiceScratch"
Form_subfrmInvoiceScratch.pubAddRec "Balance", "Previous Balance", "$1000.00"

 

[rcoutts]

"GoToControl" worked great. Many thanks. I have a new related problem, however. Before repopulating the subtable, I run a simple Delete Query that looks like this.

  DELETE tblInvoiceScratch.*
  FROM tblInvoiceScratch;

It works, but in my Datasheet view of the table, I get this:

    Category     Description  Subtotal
    --------     -----------  ---------
    #Deleted     #Deleted     #Deleted
    #Deleted     #Deleted     #Deleted
    #Deleted     #Deleted     #Deleted

I call "DoCmd.GoToRecord , , acFirst" before writing new records, but everything getting appended. E.g.,

    Category     Description  Subtotal
    --------     -----------  ---------
    #Deleted     #Deleted     #Deleted
    #Deleted     #Deleted     #Deleted
    #Deleted     #Deleted     #Deleted
    Balance      Amt Forward     $1000
       ...          ...          ...

But, when I open the table, everything looks fine (no "#delete" values). I thought maybe running the Refresh Action might get rid of the deleted values, but it didn't.

Any suggestions?
Thanks again,
Rich

 

[MangroBongacello]

You should use the Requery method