CODEWITHSUNDEEP
ms-accessms-access-2007vba
Dragonsen
Dragonsen

Reputation: 145

Out of Stack Space & Requery Errors

I've been modifying my Access DB to clean it up and make it more user friendly. As such, I've been changing the names on form labels and controls to include _lbl and _Ctrl instead of the generic names Access assigned them. Previously, I had three separate forms (ListingsForm, ListOffersForm, ListDetailsForm) that I could access by using buttons to call the form. Since the forms were directly related to the primary form (ListingsForm), I changed two of the forms into subforms. Now I keep getting errors when I try to add a record.

On the ListingsForm form, there is a control field for Property Address (Address_Ctrl). This control uses a combo box that is linked to a separate table called Properties. If the property address is not listed in the drop down box, it used to bring up the PropertyForm form to input new properties. After, it requeried to get the new list with the new record. After making the subforms and changing control names, I'm getting an error whenever I try to add a new property address.

Run-time error '2473':

The expression On Not in List you entered as the event property setting produced the following error. Out of stack space.

'Add Address Form Script
Private Sub Address_Ctrl_NotInList(NewData As String, Response As Integer)
    DoCmd.OpenForm "PropertyForm", , , , acFormAdd
    Call Address_Ctrl_AfterUpdate
End Sub

'Requery Address List Script
Private Sub Address_Ctrl_AfterUpdate()
     Forms!MLSListForm.Dirty = False
     Me!Address_Ctrl.Requery

End Sub

It highlights the Forms!MLSListForm.Dirty = False line. I've tried Me.Dirty = False but I get the same error. If I remove the line entirely, I get 'Run-time error '2118': You must save the current field before you run the Requery action.'

Having the forms as subforms helps with user-interface so I really do not want to go back to having three separate forms. Any help would be appreciated.

Upvotes: 0

Views: 1549

Answers (2)

Dragonsen
Dragonsen

Reputation: 145

So I finally got it working correctly. Turns out I had to set some default values for the controls in the form design. After that, it worked out just fine.

Upvotes: 0

jacouh
jacouh

Reputation: 8741

Normally, your comment Address_Ctrl_AfterUpdate() event handler,

And you can try this event handler:

Private Sub Address_Ctrl_NotInList(NewData As String, Response As Integer)
'
' add in Properties tables for NewData:
'   By calling SQL Server via ADO:
'   INSERT INTO Properties (City) (NewData)...
' Or
'
'
' Now NewData Exists, do this:
'....
' and then set new value and status:
'
   Response = acDataErrAdded
'
' here you can then modify new city:
'
  DoCmd.OpenForm "PropertyForm", , , "City='" & NewData & "'", acFormPropertySettings
'
End Sub

I've re-debugged in our Access Database VBA, with Response = acDataErrAdded, Access will Requery automatically Address_Ctrl.Requery() After the Address_Ctrl_NotInList() returns.

Upvotes: 0

Related Questions