Couple of quick and probably stupid Access Form related VBA queries (1 Viewer)

MrChameleon · Sep 25, 2017

Hi. Two quick queries...

First, I can't understand why the following line of code doesn't work?

DoCmd.Close acForm, "frmProgressWindow", acSaveNo

Second, I have a form with a Listview on it and several buttons which call other forms which change dtata sets that could influence the data that's displayed in the listview on the first form. Originally I was populating my listview in the form_activate event, but that doesn't cause a refresh of the listview when the form receives rrent works, but it also refreshes several times when the form is open causing the selection in the listview to jump about.

Simple query is, which event is the best to be triggered just when a form gets the focus back from another form closing? I've scoured MSDN and this site but can't get clarity.

sneuberg · Sep 26, 2017

MrChameleon said:
Hi. Two quick queries...

First, I can't understand why the following line of code doesn't work?

DoCmd.Close acForm, "frmProgressWindow", acSaveNo

If you mean by "doesn't work" that the data in the form gets saved anyway then that's because the acSaveNo affects whether or not the form itself is saved, e.g, any change in the size of control if executed when the form is in design view wouldn't be saved.

MarkK · Sep 26, 2017

In response to your two queries...
1) "Doesn't work" is not enough information when discussing the failure of code. "Doesn't work" is your conclusion from evidence, but to troubleshoot we need the evidence, not the conclusion. How do you know it fails? Is there an error message? Does the form simply fail to close? Does it save when you specified acSaveNo? There are endless evidences that would satisfy the conclusion "doesn't work."

2) Do you mean listbox or listview? Listbox is a control provided by Access. Listview is an ActiveX control provided by MSComCtlLib. Also, any refresh of either a listbox or listview will need to be programmed specifically, and will not happen automatically in this context.

What I would do if FormA opens FormB, and if activity on FormB alters the presentation of FormA, is get FormB to explicitly update FormA before it closes.

Code:

private sub Form_Close()
[COLOR="Green"]   'this FormB closing[/COLOR]
   const FN as string = "FormA"
   if currentproject.allforms(FN).isloaded then
      forms(FN).Requery [COLOR="green"]'you can requery, or ...[/COLOR]
      forms(FN).RunCustomPresentationUpdate[COLOR="green"] 'run a custom method[/COLOR]
   end if
end sub

See how that handles the Close event of FormB, and explicitly updates the presentation of FormA?
hth
Mark

MrChameleon · Sep 26, 2017

MarkK said:
In response to your two queries...
1) "Doesn't work" is not enough information when discussing the failure of code. "Doesn't work" is your conclusion from evidence, but to troubleshoot we need the evidence, not the conclusion. How do you know it fails? Is there an error message? Does the form simply fail to close? Does it save when you specified acSaveNo? There are endless evidences that would satisfy the conclusion "doesn't work."

Apologies for not being clear enough and thanks for the replies. So the form closure thing; if I specify the name of the form in quotes as per my example, it just doesn't do anything - the form stays open, no error is triggered. If I leave the form name out, the current item closes. Usually that is the form I want to close, but not always (the behaviour can be unpredictable) so I want to be specific. I guess there might just be something wrong with my syntax but don't know as it seems correct from what I've read.

The second item is the ComCtl listview. The problem with refreshing it from another form is the population is a whole routine (called PopulateWI) and I'm not sure how to call a routine embedded in one form from another. If you can do that, presume you need to also make it Public rather than Private to the form?

sneuberg · Sep 26, 2017

MrChameleon said:
Usually that is the form I want to close, but not always (the behaviour can be unpredictable) so I want to be specific. I guess there might just be something wrong with my syntax but don't know as it seems correct from what I've read.

If the name isn't specified I believe it closes the active form. Your syntax is correct the only reason I can see why that wouldn't work is frmProgressWindow being misspelled or not being open.

The second item is the ComCtl listview. The problem with refreshing it from another form is the population is a whole routine (called PopulateWI) and I'm not sure how to call a routine embedded in one form from another. If you can do that, presume you need to also make it Public rather than Private to the form?

This thread discusses that but I suggest you read through it to Galaxiom's input on running the class procedure vs the objects procedure.

MrChameleon · Sep 28, 2017

Thanks again for the reply Steve.

So, I have two computers, both running Access 2010. I have created a blank database on both, created two forms, form1 and form2. Form1 has a cmd button that opens form2, form2 a cmd button that closes itself, using "docmd.close acform,"Form2". It doesn't work in either case. Googling "Docmd.close not working" produces reams of results so there is obviously an inherent flakiness to Docmd.close (seems to also happen with reports). So far though no thread anywhere seems to have an answer. Crazy. Does anyone know of another way (maybe an API call) to close a specific form please?

isladogs · Sep 28, 2017

No - Access isn't flaky at least in this respect - must be something about your setup.

You DEFINITELY do NOT need an API to close a form

So is your DoCmd.Close code done using VBA or a macro? Hopefully VBA!
Either way, open the form in Design view, click the close button and look at the property sheet.

If you are using VBA, it should say '[Event Procedure] for the On Click event
For a macro it should say [EmbeddedMacro]

If it does say one of these, click on it & check it opens the relevant code/macro
If there's nothing there, its become detached somehow.
If so use the dropdown box in the property sheet & select the right one.

As an example I have this on the form I'm currently using

Code:

Private Sub cmdClose_Click()

On Error GoTo Err_Handler
    
    DoCmd.Close acForm, Me.name
    DoCmd.OpenForm "frmMain", , , , , acWindowNormal
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
    MsgBox "Error " & Err.Number & " in cmdClose_Click procedure : " & Err.Description, vbOKOnly + vbCritical
    Resume Exit_Handler
End Sub

In this case, I'm specifying that the active form should be closed as there is another form called frmMain hiding in the background.
Closing the form opens that one 'normally'

For a straightforward form close I'd just use

Code:

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

MrChameleon · Sep 28, 2017

So I guess I'm going to have to resort to forcing the form I want with SetFocus and then just the generic Docmd.Close, which works. It's as soon as I insert the name of the form that Access just completely ignores the line of code. As I say, 2 completely different machines, 2 different vanilla but patched installs of Access 2010-same result, and thousands of other people having the same issue.

I don't often resort to API calls but just wondered if getting the hwnd to the form and then using the Win API to close it might be a more reliable thing to do - I imagine that's what Docmd.close does anyhow. :banghead:

sneuberg · Sep 28, 2017

I've created and attached a database with two forms, Form1 and Form2 with open and close buttons. All of these work on my system (Access 2013) and should work on yours. Please download this and try it and let us know if it works on your systems.

Minty · Sep 28, 2017

Set focus won't work on a form, unless it has no controls on it. Either select a control on the form to have focus then close or use
DoCmd.SelectObject acForm, "FrmYourName"

I've been checking dozens of forms and I frequently use the form name, without any issue. Weird

isladogs · Sep 28, 2017

Agree with Minty. Same for me.
Just out of interest did you check what I wrote in my last post?
I apologise if it all seemed rather obvious but what you're describing doesn't make sense.
Also where's your evidence for

thousands of other people having the same issue

Couple of quick and probably stupid Access Form related VBA queries (1 Viewer)

MrChameleon

New member

sneuberg

AWF VIP

MarkK

bit cruncher

MrChameleon

New member

sneuberg

AWF VIP

MrChameleon

New member

isladogs

MVP / VIP

MrChameleon

New member

sneuberg

AWF VIP

Attachments

Minty

AWF VIP

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread