Bizarre behaviour/bug with subform

[ssanfu]

I don't understand a lot because of the language barrier, so I am going to back out.

I did see a lot of things that concerns me:

- There are Look up FIELDS in many tables.
- Only 14 out of 52 form modules had "Option Explicit" at the top of the module.
- Only 7 out of 12 standard modules had "Option Explicit" at the top of the module.
- Only 4 out of 30 report modules had "Option Explicit" at the top of the module.
- there are a couple of 1-to1 relationships
- there are spaces/ special characters in some field names.


- there are years in the name of fields. This is bad because every time the year changes, the design of the table must be changed, then the queries, the form, the code and the reports. (specifically the table membre)
- table "membre" has 114 fields, 20 of the fields are duplicated twice. These fields should be broken out into at least 3 more tables.
Example:


- EVERY PK field is named "ID"! Most are Autonumber type, a couple are Long Integers.(Good - but the PK/FK field should never be visible on a form.)
I name the PK field the name of the table, with "ID" if the field is a number type and with a suffix of "_PK" (if PK field) or "_FK" (if FK field). No two fields names have the same, but it is easy to tell which fields are related..... for example, the table membre PK field I would name "membreID_PK" and a foreign key field in table paiement I would name "membreID_FK". Some developers don't use the underscore, others use the PK/FK as a prefix ... example "PKmembreID" and "FKmembreID".
Each table should have a different PK field name.




Good luck with your project..........

[julienrobert]

Hi Vlad

Thanks for your input!

I tried your code and the line Forms![livretDepot]![subChequesEncaisses].SourceObject = "subChequesEncaisses" you added is getting me the error "You entered an expression that has no value.". So I'm using Requery instead but keeping the Echo trick. We'll see if it helps.

If not, I'll give it a try with your suggestion to replace the recordsource for selectionCheques with an updatable select query taking the individual filter components as parameters. I'm not sure what's the best way to achieve it; passing the source in an argument of the method open form?

DoCmd.Echo False
Forms![livretDepot]![subChequesEncaisses].Requery
'Call Forms("livretDepot").calculer




'vlad March 2, 2019


    'Forms![livretDepot]![subChequesEncaisses].SourceObject = "subChequesEncaisses" 'forces a requery and navigation to the record in the main form based on the join fields


    'MsgBox "echo"
    Call Form_livretDepot.calculer
DoCmd.Echo True




'vlad March 2, 2019


DoCmd.Close

[julienrobert]

Hi Steve

Thanks for your thoughts and tips! Since I didn't program the database myself from the beginning, I think I'll keep these things like that since I don't think they'd help my actual problem. But I'll keep them in mind for the future!

[julienrobert]

I have new procedures to reproduce the problem. It seems it's not related specifically to the form Selection Cheques because today we got the problem after the calculation has been done without calling the form selection chèques.

So the trick with Echo.false-true doesn't seem to help. I tried to input the recordsource through openargs instead of using filter, we'll see.

So, I think the problem must resides inside that chunk of code:

Public Sub calculer()
Dim chequeTotal As Double
    MsgBox "Calcul"
    'DoCmd.RefreshRecord
    Forms![livretDepot]![subChequesEncaisses].Requery
   'DoCmd.RunCommand acCmdSaveRecord
   If Me.Dirty = True Then Me.Dirty = False 'remplace acCmdSaveRecord
    'MsgBox DCount("montant", "chequesEncaisses", "livretId =" & Me.[idCtl] & "")
    If DCount("montant", "chequesEncaisses", "livretId =" & Me.[idCtl] & "") = 0 Then
    'MsgBox "nothing"
    chequeTotal = 0
    Else
    chequeTotal = Nz(subChequesEncaisses![totalCheques], 0)
    End If
    totalEspeces = Nz([5] + [10] + [20] + [50] + [100] + [sous] + Nz([sous2], 0), 0)
    'MsgBox chequeTotal
    montant = chequeTotal + totalEspeces
    'DoCmd.RefreshRecord
    If aDeposer < 0 Then
    MsgBox "Attention, il n'y a pas assez de comptant à déposer. Veuillez diminuer le montant d'espèces à déposer."
    End If

End Sub