Code won't work with subform

[GulfImages]

Hello All,

I have this bit of code that was derived from a lot of help here (Thanks AceMan1) and it works great on a main form but for some reason when I try to run it from a button on a main form, for data in a subform I get a Compile Error: Expected Function or Variable. When I compile the code I get no error, only when I try to run it.

I don't know if it's a syntax thing with the subform or what. Any help is greatly appreciated.

Sub AddNewRecords()

Dim db As DAO.Database, rst As DAO.Recordset, ctl As Control
   
Dim myform As Form
Set myform = Forms!frmInvoices!ItemsSub.Form
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("tblInvoicesItems", DB_OPEN_TABLE)
   
     
  rst.AddNew
     For Each ctl In myform.Controls
             If ctl.Tag = "D" Then rst(ctl.Name) = ctl
     Next
  rst.Update
  rst.Close
   
    Set rst = Nothing
    Set db = Nothing

End Sub

 

[formerTexan]

Hello back,

Where is your sub routine 'AddNewRecords' located? Do you intend for it to be public or private? And how are you calling it?

Cheers, Bill

 

[GulfImages]

Hi Bill,

It is a public sub in a module. I have another module that is specific to the form but not a class module to the form. It will do some validation then call this sub. I have my invoice items in a temporary table so I can verify things and such before sending them on to the table. I use all unbound forms.

Thanks.

 

[GulfImages]

I've been testing it in the immediate window for now if that matters.

 

[formerTexan]

It is good general coding practice to explicitily indicate whether routines are public or private. And since this sub is specific to a particular form/subform, it seems best to keep it in the module of one of these forms.

That aside, I don't see any inherent problem with the code or form reference. The sub should work wherever it is called from. Perhaps place a breakpoint on the button's click event and step through the subsequent code to see where the error arises.

Cheers, Bill