Couple of quick and probably stupid Access Form related VBA queries

[MrChameleon]

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]

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]

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.

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]

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]

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]

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]

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

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

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

 

[MrChameleon]

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]

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]

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]

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