Senin, 24 April 2017

Re: [MS_AccessPros] Closing a Form with VBA

 

John, for whatever reason, I ran into problems. It does make sense not to close what is controlling a process before issuing other commands that also need to happen.

~crystal

On 4/24/2017 1:25 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
Crystal-

Just for grins and giggles, I created a form with a single command button and wrote this code:

Option Compare Database

Private Sub Command0_Click()
    ' Close me
    DoCmd.Close acForm, Me.Name
    Debug.Print "Close Started"
    ' Do a message
    MsgBox "Close Started"
    ' Open another form
    DoCmd.OpenForm "Customer Details"
    Debug.Print "Other form opened"
    ' Done
    Debug.Print "Done"
End Sub

Private Sub Form_Close()
    Debug.Print "Form Close"
End Sub

Private Sub Form_Load()
    Debug.Print "Form Load"
End Sub

Private Sub Form_Open(Cancel As Integer)
    Debug.Print "Form Open"
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Debug.Print "Form Unload"
End Sub

Here's what happened in the Debug window:

Form Open
Form Load
Form Unload
Form Close
Close Started
Other form opened
Done

Note that the Unload and Close events happen as soon as the DoCmd is issued (even before the Debug just after the command), and the rest of the code executes just fine.

(Using Access 2010)

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Apr 24, 2017, at 7:10 PM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



ps

>> DoCmd.Close acForm ... Statements following that WILL execute <<

in practice, I have not always found this to be the case when the code is behind the form that is being closed ... and it does make sense so I put it last ... maybe it is not a problem anymore but I wouldn't know ~

~crystal


On 4/24/2017 11:39 AM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] wrote:

hi David,

here is a link for DLookup : 

https://msdn.microsoft.com/en-us/library/office/ff834404(v=office.15).aspx

while DLookup is less code than opening a recordset, it is slower if you have a lot of records.  If the table is small, or the field used in criteria is indexed, there will not be a noticeable difference.  If you assign DLookup to a variable (that is not variant), be sure to wrap with NZ in case it is not found. Instead, you can test for : if isnull(DLookup( ...

respectfully,
crystal 
 
~ have an awesome day ~

On 4/24/2017 10:39 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
What is the Row Source of the combo box?  If it is a query, than what is the SQL?

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Apr 24, 2017, at 5:34 PM, david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,
Can you give me an example of the DLookup code you mention?  Would I need to be using a named query as the row source for the combo, in order to be able to use the DLookup function before the form with the combo is even opened?  

I like the idea of doing this before the form is called and then calling the appropriate form rather than calling one form, closing it and opening the correct form.


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Crystal-

It is not true that any statements after a close of the form where the code is running will not run.  Issuing a DoCmd.Close acForm, Me.Name simply triggers the closing process.  Statements following that WILL execute.

I do agree that David should check for the possibility of an empty combo box before opening this form.  He could simply do a DLookup rather than open a recordset.  He could also do that in the Open event of the form and cancel the open (but then he would need an error trap in the code that opened the form).


John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Apr 24, 2017, at 4:39 PM, crystal 8 strive4peace2008@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



Hi David,

if you are trying to close the form you are behind:
rather than specifying the form name, you can use Me.Name -- in my opinion, this makes it more clear what is happening in the code
... and, if this is the case, realize that NO statements will be executed after form close -- so, in code, open the next one and then close the one you are behind.

>> txtLocationID <<
again, for clarity, and to take advantage of intellisense, use Me.txtLocationID

>> want to close the form, Undo <<
perhaps Undo first? If the control has gotten a value, you can't do anything till control AfterUpdate has happened, or value is undone.  Is code setting values? 

>> associated specification can be changed by 2clicking on the Sample Group and thus bringing up the form in question <<
better would be to see if there are records before opening, and decide which form to open.

'~~~~~~~~~~
   On Error GoTo Proc_Err
   
   Dim sSQL As String

   Dim db As DAO.Database _
      , rs As DAO.Recordset

   fim sFormName as string

   sSQL = "SELECT MyField  " _
      & " FROM MyTable_
      & " WHERE MyConditions" _
      & " ;"

   Set db = CurrentDb
   Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
   
   With rs
      If Not .EOF Then
         sFormName = "MyForm1"
      else
         sFormName = "MyForm2"
      End If
      .Close
   End With
   set rs = nothing
   set db = nothing

   docmd.openform sFormName '... more parameters if you need them

Proc_Exit:
   On Error Resume Next
   'release object variables if applicable-- ie:
   If Not rs Is Nothing Then
      rs.Close
      Set rs = Nothing
   End If
   Set db = Nothing
   Exit Function 'or Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   MyProcedureName "

   Resume Proc_Exit
   Resume
'~~~~~~~~~~

>> simple three field form <<
terminology correction: a form has controls, not fields.  A control can be bound to (contain) a field.

on naming:
be sure you are not using Group as a name, it is a reserved word.
allenbrowne.com/AppIssueBadWord.html

>> GotFocus <<

I use this event to change RowSource, issue command for ActiveControl.DropDown, and other things -- but never to close a form.

respectfully,
crystal 

http://www.MsAccessGurus.com
connect to me, let's build it together 
 
~ have an awesome day ~


On 4/24/2017 8:21 AM, david.pratt@... [MS_Access_Professionals] wrote:
There is a table of sample Groups and a table of Specifications.  The form is used to change the specification that is associated with the Sample Group.  The combo can be empty if no specifications were ever created for the Sample Group.  In that case I want to redirect the User to a form where the specifications are created.

The first two fields are disabled because the values for them are provided by the calling form.  The calling form is an unbound form where I have a list box of the sample groups and a related text box where I display the associated specification.  The associated specification can be changed by 2clicking on the Sample Group and thus bringing up the form in question.

I was hoping there was a quick, easy answer (always the hope) for why Access would not allow me to close the form during this Event (GotFocus) processing but it does allow me to close a form with the Click event of a command button.

After describing this, perhaps my logic is wrong.  Perhaps I should somehow check for existing specifications at the 2Click event of the calling form? I currently do not have a "named query" that is used for the combo box.  A SQL statement is used for the row source of the combo box.

If it is a complicated answer, just hold off until I do some more experimenting.  I don't want to waste your time.


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

David-

I don't think I've ever used GotFocus.  

What are you trying to do with this form?  Why are the other two controls disabled?  Why would the combo box have zero rows?  How is the form opened?  What other code is there in the form?

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Apr 24, 2017, at 00:12, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have a simple three field form. the first two fields are disabled and the third is a combo. I want to test the combo to see if there are any rows in it. If there are no rows, I want to close the form, Undo, and open a different form where rows are added which the combo box can then use.

Private Sub cboSampleSpecID_GotFocus()
 If Me.cboSampleSpecID.ListCount = 0 Then
MsgBox "No specifications have been created for this Location/Application Group." & vbNewLine &        vbNewLine & _
"You will be returned to the Sample Specifications form to create new specifications.", vbOKCancel, gstrAppTitle
DoCmd.Close acForm, "frmSampleGroupSpecification", acSaveNo
DoCmd.OpenForm "frmConfigureSampleSpecDetails", acNormal, , , acFormEdit, acWindowNormal, txtLocationID
End If
End Sub

I get an error message saying I can't do this while processing a form or report event. The DoCmd.Close statement is then highlighted.

I don't know why I can't close the form in this event when I can close it using the same DoCmd as the click event for a command button.
















__._,_.___

Posted by: crystal 8 <strive4peace2008@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (11)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Tidak ada komentar:

Posting Komentar