Code does not always stop on DoCmd.OpenForm

[wiklendt]

How about using DoEvents?

Ah. that would require me to learn about them and know how to use them! i've read a little bit, and tried a couple of things on my code, but to no avail.

Since i'm short on time, my workaround is to have an "apply settings" button, which triggers the action queries to run separately.

Though "DoEvents" sounds like something i will need to learn how to use - especially when running those long-running queries... have you any good DoEvents-for-dummies links to point me to?

ta.

 

[vbaInet]

There's nothing to learn about Doevents really, it simply yields execution to the processor. Have a look in the help files for a more precise explanation. Here's your code with it included:

    'open the targets form so changes can be made
    DoCmd.OpenForm "frmTargets_Display", acFormDS, , , , acDialog 'open as dialog, so the next code runs after changes
    
    [COLOR=Red][B]DoEvents[/B][/COLOR]

    'the following will delete all records in ttmpProfiles and recalculate them according to new target display settings
    DoCmd.OpenQuery "qdelProfiles", acNormal, acEdit
    DoCmd.OpenQuery "qappProfiles", acNormal, acEdit

    Me.sfrmCCC_TargetAbGroups.Requery

See if that works.

 

[wiklendt]

There's nothing to learn about Doevents really, it simply yields execution to the processor. Have a look in the help files for a more precise explanation. Here's your code with it included:

    'open the targets form so changes can be made
    DoCmd.OpenForm "frmTargets_Display", acFormDS, , , , acDialog 'open as dialog, so the next code runs after changes
    
    [COLOR=Red][B]DoEvents[/B][/COLOR]

    'the following will delete all records in ttmpProfiles and recalculate them according to new target display settings
    DoCmd.OpenQuery "qdelProfiles", acNormal, acEdit
    DoCmd.OpenQuery "qappProfiles", acNormal, acEdit

    Me.sfrmCCC_TargetAbGroups.Requery

See if that works.

thanks. no, i tried that - doesn't work. edit: that is, my code still runs when it shouldn't.

my understanding is that DoEvents doesn't 'pause' or 'wait' for anything, just makes it run outside Access (i.e., on the processor)?

here, my issue is that the code below the open form runs immediately ON openform, despite the acDialog command, which - i always thought - is supposed to make the execution of the code wait until the dialog form was closed, before continuing...?

 

[vbaInet]

Oh I see. It doesn't work with Datasheet View unfortunately.

What you can do is create a global boolean variable and set the variable to True on the CLOSE event of the form:

    'open the targets form so changes can be made
    DoCmd.OpenForm "frmTargets_Display", acFormDS, , , , acDialog 'open as dialog, so the next code runs after changes
    
[COLOR=Red][B]    Do while myVar = [COLOR=Blue]False[/COLOR]
    Loop[/B][/COLOR]

    'the following will delete all records in ttmpProfiles and recalculate them according to new target display settings
    DoCmd.OpenQuery "qdelProfiles", acNormal, acEdit
    DoCmd.OpenQuery "qappProfiles", acNormal, acEdit

    Me.sfrmCCC_TargetAbGroups.Requery

 

[wiklendt]

Oh I see. It doesn't work with Datasheet View unfortunately.

bloody microsoft!

[COLOR=Red][B]    Do while myVar = [COLOR=Blue]False[/COLOR]
    Loop[/B][/COLOR]

ta. that's a good idea.

 

[Banana]

I have to confess the idea of doing a loop to mimick a dialog form makes me a bit uncomfortable, mainly owing to the fact that VBA is single-threaded and therefore could act funny if directed to act as if there were two threads going on.

I'd want to try and embed the datasheet as a subform on a blank unbound form and see if opening the unbound blank form magically fix the problem or eschew the datasheet in favor of a continuous subform. In one recent project, I actually built a continuous form that looks alot like a datasheet just to get more flexibility and less hassles than if I had used a real datasheet.

HTH.

 

[wiklendt]

I have to confess the idea of doing a loop to mimick a dialog form makes me a bit uncomfortable, mainly owing to the fact that VBA is single-threaded and therefore could act funny if directed to act as if there were two threads going on.

I'd want to try and embed the datasheet as a subform on a blank unbound form and see if opening the unbound blank form magically fix the problem or eschew the datasheet in favor of a continuous subform. In one recent project, I actually built a continuous form that looks alot like a datasheet just to get more flexibility and less hassles than if I had used a real datasheet.

HTH.

i thought about doing that too. it would make it easier to have the two processes happen in one procedure.

also, wouldn't a loop use up a lot more resources than without it? especially if the user took a long time on the form? i mean, how often does it check for a change in variable? (i.e., how quickly does it 'loop'?)

 

[vbaInet]

Just tried the loop and yeap, vba couldn't handle it. I know other languages that would

Your only way would be to use a subform as the container of your form as Banana suggested. Set the subform's Default View as Datasheet and widen it to fill the whole detail section of the form, with no headers or footers on the main form.

Open the form as a normal form.

 

[wiklendt]

Just tried the loop and yeap, vba couldn't handle it. I know other languages that would

Your only way would be to use a subform as the container of your form as Banana suggested. Set the subform's Default View as Datasheet and widen it to fill the whole detail section of the form, with no headers or footers on the main form.

Open the form as a normal form.

ta. i think it's worth giving every (well, most!) ideas a chance. you never know when it will be that perfect solution to something else i had many moments like that when i was first learning - when the 'penny' dropped about something or another.

 

[wiklendt]

FYI, i've made the form a sub and put it on a normal-view form. also had to remember to change the view of the new form in the VBA(!) to normal and it works a treat.

thanks everyone.

 

[vbaInet]

Great!! It wouldn't have been done without Banana's suggestion.

Glad it's working for you.