-----------UPDATE---------
I figured out how to get the focus on my subform. If it is a subform then :
ParentFormName = frm.Parent.Name
SubFormName = frm.Name
Forms(ParentFormName).Form(SubFormName).SetFocus
This seems to work (although Andre451's post is more effective). But this resulted in the underlying problem of my subform causing the suspended state. My subform is relatively big (194 records) and when doing a MoveFirst/Last on the recordset of larger records I get the error 3021 (no current record). This probably has something to do with the entire recordset of the subform isn't loaded on the load event of the main form. Smaller recordsets can be looped faster so won't generate the error (this is just an assumption though).
The new question is: How can I avoid the 3021 error and let the subform records load entirely so I can move through them with either:
- DoCmd.RunCommand acCmdRecordsGoToLast
OR
- frm.RecordsetClone.MoveLast
----------------------------------------------------------------------------------
A while a go we migrated our Access data to a SQL server with still using the Access as the frontend. We did this by linking the SQL Server tables with Access and this all works (almost) fine. Recently however we encountered a problem with Access queries getting in suspended state on the SQL server (mainly happens on queries in combo boxes).
After some research we figured out that this could be solved by looping through the query records on the load of the form (so by doing a simple acCmdRecordsGoToLast and acCmdRecordsGoToFirst) and this seems to work.
So to counter this suspended state we added to each forms load an init that runs through the records of the recordsource and rowsource (the main forms recordsource, the comboxes rowsource etc.).
So on a forms load:
modFunctions.InitForm(Me)
This init is in a module and looks like this:
Dim InitFormResult As Boolean
InitFormResult = InitFormRecordSource(frm) ' Initialize the recordsource of the form
InitFormComboBoxes frm ' Initialize all the comboboxes on the form
InitFormCommandButton frm ' Initialize all the buttons on the form
The first init of the forms RecordSource (where the error happens) looks like this:
Public Function InitFormRecordSource(frm As Form) As Boolean
If frm.RecordSource <> "" Then ' if recordsource found
On Error GoTo ErrHandler
frm.SetFocus
DoCmd.RunCommand acCmdRecordsGoToLast
DoCmd.RunCommand acCmdRecordsGoToFirst
GoTo EndSuccess
ErrHandler:
MsgBox (Err.Number)
Debug.Print "ERROR during InitFormRecordSource in form : " & frm.Name
InitFormRecordSource = False
Exit Function
EndSuccess:
InitFormRecordSource = True
Else
InitFormRecordSource = True
End If
End Function
To run through the forms recordset with First and Last it needs to have the focus. This all works great for one form but it doesn't for Subforms in a main form. In subforms we want to counter the suspended state as well by also running through the subforms records, though when using the above code we keep getting the error 2449.
After some research I encountered this post: Module Function frm.setfocus runtime 2449 error which is more or less the same problem. So I figured out that a subform needs to be given it's focus in a different way, yet all my efforts to do this won't work.
What I tried so far:
If frm.RecordSource <> "" Then ' if recordsource found
On Error GoTo ErrHandler
Dim HasParent As Boolean
Dim sParentForm
HasParent = TypeName(frm.Parent.Name) = "String"
If HasParent = True Then 'The form is a subform
'sParentForm = frm.Parent
'sParentForm.frm.SetFocus
'frm.Parent.frm.SetFocus
'sParentForm!frm.SetFocus
Else
frm.SetFocus
End If
DoCmd.RunCommand acCmdRecordsGoToLast
DoCmd.RunCommand acCmdRecordsGoToFirst
GoTo EndSuccess
The tried fixes are all commented out in the above code. The HasParent does work, with this I can check if the form is a subform (it has a parent) or not and setting the focus differently.
Though I can't seem to get the focus on the subform and I don't quite know how to get it fixed!
