Navigating subform issues (loading recordset ?)

[Nyanko]

Hi,

I'm trying to work out how to fix an error, but can't quite get my head around loading recordsets. Any assistance would be appreciated.

I have a button on the switchboard to "Add New Invoice". When pressed this opens "frm_MainInput" and hides navigation buttons :

Private Sub btn_NewInvoice_Click()
'------------------------------------------------------------
' Switchboard - Add New Invoice
'------------------------------------------------------------
Dim btn_RepeatInvoices As Object

On Error GoTo btn_NewInvoice_Click_Err

    
    DoCmd.OpenForm "frm_MainInput", acNormal, "", "", , acNormal
    Forms![frm_MainInput].AllowAdditions = True
    Forms![frm_MainInput].NavigationButtons = False
    DoCmd.GoToRecord , "", acNewRec
    DoCmd.Maximize
    
    'Hide navidation buttons
    'show submenu buttons
    Forms!frm_MainInput.btn_RepeatInvoices.Visible = True
    'button5.Visible = True

    DoEvents          'execute any pending events, to make sure the button 4 and 5 are really visible
    Forms!frm_MainInput.btn_RepeatInvoices.SetFocus  'change the focus to a now visible control
    DoEvents          'execute any pending events, to make sure that button4 really has the focus

    'now you can hide the other buttons

    'hide main buttons
    Forms!frm_MainInput.btn_NewInvoice.Visible = False
    Forms!frm_MainInput.btn_Search.Visible = False
    Forms!frm_MainInput.btn_First.Visible = False
    Forms!frm_MainInput.btn_Previous.Visible = False
    Forms!frm_MainInput.btn_Next.Visible = False
    Forms!frm_MainInput.btn_Last.Visible = False

btn_NewInvoice_Click_Exit:
    Exit Sub

btn_NewInvoice_Click_Err:
    MsgBox Error$
    Resume btn_NewInvoice_Click_Exit

End Sub

The frm_MainInput form has a tab control with threes subforms (Datasheet view) : frm_MainInputSub, frm_CommissionSub and frm_DebtSub.

Each record in MainInput can have several rows saved in MainInputSub (line items on an invoice). but when I run the above code and select any field in the MainInputSub I get the error :

You entered an expression that has an invalid reference to the property Form/Report

From my research I believe it is because the recordset of the subform (s?) has not been loaded correctly.

How do I do this ?
Do I need to load the other subforms as well ?

 

[sneuberg]

I'd check the links between the main form and subform for starters. Also suggest commenting out the line On Error GoTo btn_NewInvoice_Click_Err temporarily so that the debugger tells you which statement is causing the problem. Please let us know.

 

[Nyanko]

Hi
Thank you for your fast response. I've checked all the child/parent links for all the subforms and they are working as expected. I don't get a vba error so there is nothing for the debugger to show, it's an Access dialogue error and it happens as soon as I select any field in the subform.

When searching previously, it seems this error is associated with trying to perform an action before loading recordset(s). This is my first time dealing with recordsets and I'm not sure I fully understand what part they play in the subform process so I don't know how or where to add the load.

 

[sneuberg]

This is my first time dealing with recordsets and I'm not sure I fully understand what part they play in the subform process so I don't know how or where to add the load.

How are you dealing with recordsets? Normally the subform has a record source, a query or table and that gets loaded based on the linking and becomes the record set for the subform. Are you doing something different?

Also is this code causing the problem? If you just open the form directly do you get the same error?

If you can upload your database I'd be glad to try to figure this out.

 

[Nyanko]

Hi,

Thank you so much for your offer of help. I took a version of my database and cleared it down to make a simpler and annoymous version of my data - and it worked fine !

I therefore went through all the code that was different to work out the issue. I figured out that I had an audit trail macro that was being called on the main and sub form and it was the main form Call that was causing the error.

So in a roundabout way you were able to assist me in solving the issue

 

[sneuberg]

Glad to hear you found it.