Bizarre behaviour/bug with subform

[julienrobert]

I have a very strange bug happening in a form that uses subform. It's not happening all the time but often enough to try to solve it.

I have a subform (chèques) linked to its parent form (livretDepot) with the id livretId (parent ID, subform idLivret). It works normally but at some point, I open another window with VBA to add some checks to the deposit (subform chèques). When that latter window closes and we get back to the form livretDepot, the records into the subform are all wrong (like you can see in the snapshot attached) and their idlivret doesn't fit with the id of the parent form. How can that happen? Anyone has a clue how this could happen?

Thanks a lot.

[ssanfu]

Welcome to the forum.....


Are the linking fields set between the main form and sub form?

[julienrobert]

Absolutly.

[julienrobert]

By the way, I'm not able to reproduce the problem myself; my client is getting the problem and sent me the snapshot.

[ssanfu]

Any chance you could make a copy of the dB, delete all but a few records, change any sensitive data, do a "Compact and Repair", then compress/zip the dB and post it here??

[julienrobert]

There it is. Hope it helps. The form is called livretDepot.
Please tell me if you need more explanation on how the database works.

Thanks a lot

[June7]

Bit of a language barrier. What process should we follow to try and replicate issue? Which button do we click? I tried ajouter de cheques but just get a message box.

[Gicu]

Can you compile your db without any errors? I can't and because of the different regional settings and knowing it is a stripped down version I didn't try too hard, but having compile errors in the VBA project will always cause problems. I would try to look over the code in the ajouter_Click() on the selectionCheques form specifically calling the public sub calculer of the main form without requerying the cheques subform to get the new ones.

Forms![livretDepot]![subChequesEncaisses].reguery
Call forms_livretDepot.calculer 'vlad

Cheers,
Vlad

[julienrobert]

Bit of a language barrier. What process should we follow to try and replicate issue? Which button do we click? I tried ajouter de cheques but just get a message box.

With pleasure. Once the form livretDepot is opened, you click on "ajouter des chèques" to open another window that will allow you to select and add checks to the deposit. But, in order to open the window to eventually modify the deposit (the message box you're getting), you have to unlock (if locked) the deposit by clicking on the lock just bellow the button "imprimer". From that new opened window, the user would select the checks and click on "ajouter les chèques". When that window closes to come back to the deposit window, there might come the problem. Again, I'm not able to reproduce the problem myself so I'd be surprise you'll get it.

Thanks

[julienrobert]

Can you compile your db without any errors? I can't and because of the different regional settings and knowing it is a stripped down version I didn't try too hard, but having compile errors in the VBA project will always cause problems. I would try to look over the code in the ajouter_Click() on the selectionCheques form specifically calling the public sub calculer of the main form without requerying the cheques subform to get the new ones.

Forms![livretDepot]![subChequesEncaisses].reguery
Call forms_livretDepot.calculer 'vlad

Cheers,
Vlad

Hi Vlad, I don't see errors when I open the database myself. Did you link the frontend to the backend?

I'll try your suggestion (removing the line "Forms![livretDepot]![subChequesEncaisses].reguery"). Thanks

[Gicu]

What I mean was to go into your VBA project window and try to Compile it.
Vlad

[June7]

As Vlad said, be sure to run Debug > Compile.

I see no need for member ID on deposit slip.

Why bother with a lock/unlock if users can unlock at will?

Unlike the posted image, livretid field isn't even displayed on the subform. I had to modify form to show it.

Tried your process. The form opens filtered and no records show. There is nothing to select. I am not able to help any further.

[julienrobert]

I removed the errors when compiling and displayed the livreId in the problematic subform.

Lock/unlock is there to avoid manipulation error while leaving a certain amount of flexibility to the user.

I added some checks to add to the last livret (livret Id 15) so you can select and test yourself.

Thanks again.

[June7]

Went to ID 15 (last record), click button, form opens blank. ID 15 already has checks listed on deposit slip. I attempted to remove one with button and get error message. So I manually deleted from table, change the encaisse status in other table. Records now list. Selected and added to deposit. ID numbers match. Nope, cannot replicate issue.

[Gicu]

Hi Julien,

I think you need to rethink the design/structure. I opened the second form (selectionCheques) but it is not updatable for me, probably because you use lookup fields (member ID in membre) and use that in an equijoin query as the source of the form. Most Access developers avoid using lookup fields (and captions) in tables as they mostly cause problems and mask the root causes of problems. You only need the lookups in the forms and reports as an easy way to display the related info. I would try to avoid the filter you use and replace the recordsource for selectionCheques with an updatable select query taking the individual filter components as parameters.

I would also pay attention to what is happening to the main form before you open the second one:

Private Sub Command478_Click()
If IsNull([id]) Then
'MsgBox "new"
Dim strSQL As String, rs As DAO.Recordset
    
    strSQL = "SELECT * FROM coordonneesEmergo"
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
    folio = rs!noCompte
    nomCompte = rs!nomCompte
    noSuccursale = rs!noSuccursale
DoCmd.RefreshRecord


End If


If Not securiser Then
DoCmd.OpenForm "selectionCheques", , , "(type='Chèque' OR type = 'Mandat poste') AND encaisse =false AND dateCheque <=# " & dateDepot & "#"
Else
MsgBox "Ce bordereau de dépôt a déjà été imprimé. Pour l'imprimer à nouveau, veuillez le dé-sécuriser en cliquant sur le cadenas"
End If


End Sub

Why use recordsets to poppulate the three fields for the new (empty) record when you could use the default value of the controls?

Just some observations for now, looks like you put a lot of time in this so can't really offer much more but maybe they can push you in the right direction. I will include my updated front-end where I made some changes to the closing code of selectionCheques.

Cheers,
Vlad