Minimized form loses bind

[Jacopo]

Dear guys,
I am having problems minimizing and restoring forms. I am using Access 2003 on WXP.
In my DB I am using just unbound forms. The typical form has a main area with controls to edit new records or modifying existing ones, and a subform in datasheet view with all or part of the records in the related table.

On opening the form I bound both form and subform with the following function:

Function GeneralRequeryForm(CurrFormName As String, QueryConnName As String) As Boolean
Dim cn As ADODB.Connection
Dim rsmain As ADODB.Recordset
Dim frmActive As Form
Dim strsql

On Error GoTo Err_Handler
Set frmActive = Forms(CurrFormName)
Set cn = CurrentProject.AccessConnection
Set rsmain = New ADODB.Recordset
With rsmain
Set .ActiveConnection = cn
.CursorLocation = adUseClient
.Source = "exec " & QueryConnName
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
Set frmActive.Recordset = rsmain
Set frmActive![ThisSubForm].Form.Recordset = rsmain
GeneralRequeryForm = True
Set rsmain = Nothing
Set frmActive = Nothing
Exit Function
Err_Handler: Call LogError(Err.Number, Err.Description, CurrFormName, "GeneralRequeryForm", , True)
End Function

Everything works fine until I minimize the form. When I do it and thenafter I restore it, the form is still bound but the subform recordsource is null and therefore the control is blank. Of course any reference within the code to the subform gives back an error saying that the object referred by the instruction is closed.
It could be an idea reloading the subform and in case resynchronizing it with the main form, provided there was something like a restore event for the form which, to my knowledge, there is not.

I am sure this is a common problem encountered by other people before but at the moment I don't know the solution.
Have you any idea?
Thank you for any help you can give me.
Jacopo

[FishVal]

Hi, Jacopo.

Actually the Recordset is being destroyed in your code before you minimize the form. Just when Form window needs to be repainted Access refreshes controls' values from Form.Recordset. I guess the same will be if you put the form window behind other window and then activate it again.

Concerning your code I would like to say that to my mind it is a bit overkill. Did you try to set Form.RecordSource property?

Forms(CurrFormName).RecordSource = "exec " & QueryConnName
 

Regards,
Fish

[Jacopo]

Dear Fish,
thank you very much for your kind reply.

I think that the recordset is definitely not destroyed because, and maybe i wasn't clear in my explanation, the problem affects just the subform which loses its recordsource, not the controls in the form. I still can edit, save and delete records, and using the navigation buttons I browse with no problem the whole table. Just the subform control is a white box (not bound).
Also this doesn't happen as you thought when I lose focus going on a different form (or on another application for that matter). It happens just minimizing it.

About the suggestion on how binding the form, it is absolutely sensible and I know that my code looks uselessly convolute. Matter is that the line you suggest gives back the error 3129 "SQL instruction not valid". I think it is because the query is a stored one and not a SQL instruction. Stored query give a lot of problems, but have the advantage that when this program will be converted to SQL SERVER I won't need to go through the code changing the SQL statements to the proper dialect.

Thank you again for any further idea.
You suggestions made sense to me.
regards,
Jacopo

[FishVal]

Hi, Jacopo.

Its weird. I've tried a code similar to your's with no problem.
Main form: on Load event Form.Recordset changed to ADODB.Recordset having local query as a source
Subform: stays the same - DAO.Recordset.

Is your situation other?

[Jacopo]

Dear Fish,
again thank you. I fire the code in the on open event and not in the on load event but I don't think that that is the problem because on_open occurs later than on_load (but you tell me if I am wrong).
I am afraid I don't understand when you refer to a DAO.recordset for the subform.
There is no DAO at all (ok almost) in my DB. Just ADODB and I use the same ADODB recordset for both main and sub.

I made a tiny DB for you with all my modules and everything hoping I could attach it to the message so that you could try it but I can't.
Please let me know if there is a way to do it.
Thank you,
Jacopo

[FishVal]

Ok. Having submitted a post click on [Edit/Delete], then click on [Manage attachments] button and attach the sample packed into zip or rar.