How to prevent unnecessary form refreshes when using late binding (1 Viewer)

[Marnix]

Hi,
I'm reconstructing an access database having linked tables to Azure SQL Server.
One of the techniques I use is setting a recordsource only when needed. THis makes loading a page with tab-control much faster.

The following code shows part of the sub where the recordsource is set. The form is a bound subform so I need to set the master and childfields.

The following code works fine, but after each of the 3 lines the form is redrawn which makes is flashy and a bit slow (not too...).

Is it possible to pause repainting and doing these 3 commands at once?

The code:

        With Me.SFRNotes
          SQL = "SELECT * FROM dbo_SQLView_Notes where dbo_sqlview_notes.projectid=" & Me.Id
          If .Form.RecordSource <> SQL Then   'don't requery if query is already in place for this project
            .Form.RecordSource = SQL          '.Requery after this not necessary. Is done on changing recordsource
            .LinkChildFields = "ProjectID"    'Linkfields must be set again because a change of recordsource
            .LinkMasterFields = "ID"          'Resets them and Access sets them to 'Id' 'Id' or other wrong content
          End If
        End With

Thanks!

 

[theDBguy]

Hi. I can't remember exactly, but look up Echo and Repaint methods with DoCmd.

Sent from phone...

 

[Marnix]

Hi. I can't remember exactly, but look up Echo and Repaint methods with DoCmd.

Sent from phone...

Thanks. That's helpfull! At least the screen is not as busy and it looks more neat. But it seems to cost the same amount of time. For now: thanks!

 

[theDBguy]

Thanks. That's helpfull! At least the screen is not as busy and it looks more neat. But it seems to cost the same amount of time. For now: thanks!

Hi. Sorry if I wasn't clear. My comment was just to address the flashing problem, nothing else.

Good luck with your project.

 

[Minty]

Are you sure that the following is necessary:

 .LinkChildFields = "ProjectID"    'Linkfields must be set again because a change of recordsource
.LinkMasterFields = "ID"          'Resets them and Access sets them to 'Id' 'Id' or other wrong content

I remove and reset sub form record sources all the time, and never have had to reset these items?

Also SQL is a poor choice of variable name as it is a reserved word. I would change it strSQL or similar.

 

[Marnix]

Hi. Sorry if I wasn't clear. My comment was just to address the flashing problem, nothing else.

Good luck with your project.

Yes, totally clear and really helpfull. Just curious of anyone looks at the code and thinks 'Hey... this can be done smarter '. But I implemented the echo and hourclass and that works fine to stop flashing.

Are you sure that the following is necessary:

 .LinkChildFields = "ProjectID"    'Linkfields must be set again because a change of recordsource
.LinkMasterFields = "ID"          'Resets them and Access sets them to 'Id' 'Id' or other wrong content

I remove and reset sub form record sources all the time, and never have had to reset these items?

Also SQL is a poor choice of variable name as it is a reserved word. I would change it strSQL or similar.

Hi, I checked this before. When the recordset is changed and I read the values of the master and childfields they are changed automatically based on the new recordset. I I don't change them, they are set incorrect.

And yes: totally agree that SQL is not a good name. Remainder of old code. Thanks for reading the code.

 

[Minty]

Have you got name autocorrect on?
If you have, turn it off, it will break things long term in a way that makes no sense!

I have never seen that behaviour, and wouldn't want it either.
Unless you explicitly change them they should stay put.