Access 2013 VBA Run-Time error 28 Out of Stack space

[fearmichaele]

[FONT=&quot]I have created a database that takes one query that has a blank field in a predetermined field and finds a record in a second query that matches name and date of birth criteria. it pulls the value into the blank field that is supposed to be there.

MR missing in query one....find record in query two that matches additional predetermined variables and pull MR from query two and add to query one.

I have a cumbersome VBA that accomplishes this but after every 19 records i get error message pop up that says "run-time error 28 out of stack space"
I have search all of the forums and have not found a viable solution. there is my code. to set up the scenario i have a form that i open first. using Onload the form opens a query and another form for that query where it has found the matching record. the second form that is opened uses this code where the error occurs.[/FONT]
[FONT=&quot]Private Sub Form_Load()
On Err GoTo errhand
Dim mr As String
Dim queryname As String
Dim dbs As DAO.Database
Dim rec1 As DAO.Recordset
DoCmd.Close acForm, "blank MRN Records", acSaveNo
Set dbs = CurrentDb
queryname = "refill_Requests Query"
Set rec1 = dbs.OpenRecordset(queryname) ' dbOpenDynaset, dbSeeChanges)
mr = Me.FTMedRecNo
rec1.Edit
rec1("Patient MRN") = mr
rec1.Update
rec1.Close
DoCmd.Close acQuery, "mmdblist", acSaveYes
DoCmd.Close acForm, "mmdblist", acSaveNo
DoCmd.OpenForm "blank MRN Records", acNormal

errhand:
Err.Clear

DoCmd.Close acQuery, "mmdblist", acSaveYes
DoCmd.Close acForm, "mmdblist", acSaveNo
DoCmd.OpenForm "blank MRN Records", acNormal

End Sub[/FONT]
[FONT=&quot]my on error doesn't catch error 28 because it stops mid code.

the procedure works well for 19 records then boom it stops.
I have currently a total of 3500 records to repair with a MR. how can i get this to run all the way to the end without causing error 28?[/FONT]

 

[Ranman256]

You don't need any of this code. Just run an update query.

 

[sonic8]

[FONT=&quot]I have a cumbersome VBA that accomplishes this but after every 19 records i get error message pop up that says "run-time error 28 out of stack space"[/FONT]

The "Out of stack space"-error is most likely caused by a recursive calls of a VBA procedure in your code.
Hard to say where and why that happens. You can best analyze it by stepping through you code executing it line by line. If you recognize some sort of recursive loop, you probably found the culprit.

But anyway, Ranman256 is right, you can do the same more efficiently with an update query.

 

[MarkK]

You can view the call stack. When that error appears, does it break into the debugger? If so, hit ctrl+L, or go to the view menu, and open the call stack viewer, and you can see the list of routines that have been run and are still executing. Follow them back to the initial call, and you can see where it all started.

 

[Cronk]

I agree with #2, use an update query.

As to the stack overflow, it seems a funny place to be updating records one by one in a Form load event. If you really want to edit records one by one, use a loop.

 

[fearmichaele]

thank you all for your quick responses. I am self taught with using VBA and still learning. I looked up update query and what i saw was if you want the same information in the field or update information in a field to change to another description. i have a blank field that should have a number that is unique to the persons name in that field. update query sounds like a better way to do it, but the turorials online are not clear how to match the persons name in destination query with the persons name found in the look up table where i can pull that unique number.

I am all about easy and efficient. I tried creating a loop at first but after the record was updated, i could not get it to advance to the next record. it basically dumped the first unique number in all of the records which is not what i wanted.

the current code i built in stages. i got the logistics worked out for finding the correct record and data, then got it to replace the blank field with the correct data.

would someone mind giving me an example how to build an update query that will match criteria of first name, last name and birthdate with a record in the lookup table then pulling only the MR (med rec number) and save it in the query table please?

 

[fearmichaele]

once again thank you all for your replies. the loop suggestion worked and was able to have it work as i intended. thank you for the suggestions on update query. i am going to research that option for any future projects.

 

[DataAmatuer]

You can view the call stack. When that error appears, does it break into the debugger? If so, hit ctrl+L, or go to the view menu, and open the call stack viewer, and you can see the list of routines that have been run and are still executing. Follow them back to the initial call, and you can see where it all started.

This was an much, much more helpful direction than the one provided by Microsoft (almost always too technical and too vague to be of any help) at https://docs.microsoft.com/en-us/of...er-interface-help/out-of-stack-space-error-28. Thank you, Mark K. You helped me find the cause of a similar problem with Run-time error '28'.

 

[MarkK]

Glad you found it useful. All the best,