Modules & VBA :: Command To Execute When Form Is Closed Or Quit
Feb 5, 2015
There is a form where whenever the form is closed, the below code needs to execute:
If IsNull(Me.CostPerPiece1.Value) = True And IsNull(Me.CostPerPiece2.Value) = True And IsNull(Me.CostPerPiece3.Value) = True And IsNull(Me.CostPerPiece4.Value) = True And IsNull(Me.CostPerPiece5.Value) = True Then
Me.AllowAdditions = False
DoCmd.SetWarnings (0)
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings (-1)
Else
Call AppendQuoteCharges
End If
The If Then just looks at if certain fields are all null and if so, it deletes the current record. If at least one field is not null, the AppendQuoteCharges routine is called.
The form has 4 or 5 navigation buttons that close this form and send you to a different one. I've added the above code to each of those buttons before it runs the DoCmd.Close Form. I also have a Exit button that runs a DoCmd.Quit. I developed this months back but I'm pretty sure I added the above code under each button's click event rather than a Form On Close event because Form On Close does not execute after a DoCmd.Quit command
In rare cases, the form is being closed without the routine being ran. I think if a user clicks the Close button in the top right of Access (the X), it might be running a DoCmd.Quit which is doesn't run this code.
How can I be sure that whenever the form is closed or exited, the code is ran? Is there a way to tie this code to the user clicking the X in the top right?
I'm developing in Access 2007 and created a runtime version.
When the user clicks the X to close the runtime application, the main form's Load event fires. Any clues as to why this might be? Some of the code is based on other forms that do close, so of course errors start flying. Note, this is only in the runtime version; the accdb file works just fine.
My current workaround is to put the offending code into the forms On Current event, which I'm able to do in this context.
Is it wrong to think that a forms On_Load event shouldn't be firing when the application is closing?
I got the error message cannot execute command after we upgraded from Access 97 to Access 2000. There are multiple users connected to one database on Windows 2000. Some people are able to get access, but there is a good chunk of people that get the error message " Cannot execute Command".
I have a main form called frmAF54. In this main form I have a subform named frmPassdown. The subform record source is from a table called tblCards. This table contains many different cards. I have a combobox called lstOpCard in the main form as well. The combobox lists all of the different cards in "tblCards". I have a button called "buttonFilter".
A user opens the form and clicks (or selects) the different cards in the combo box "lstOpCard" that they want to filter for. Once their selections are made, they can click the "buttonFilter" button and the subform "frmPassdown" will be filtered based off of their selections.
The filter is applied using the IN() function as shown below: Me.AF54PassDown.Form.Filter = "[OpCardID] IN ( " & sTemp & " )" --sTemp is a string containing the keyIDs of the cards selected.
My problem is that people will be opening and closing the form throughout the day / week. When the form closes, it loses the applied filter. How can I have my filter on the subform be saved after the form is closed and the filter is still applied when the form is opened later?
My database is used by a few users on a shared drive in "read-only" mode so they can't change any data.I set a scheduled task to copy an updated version of the database (which I edit) every morning.
The problem is that when the users forget to close the database file I can't overwrite it.For this purpose I wrote a small code that quits the application at 00:00 using Application.Quit.The code WORKS when I test it on my computer, but every morning when I try to open the file I see that it is opened by another user since I also open it as "read-only" - meaning the code didn't work...getting the code to work properly OR get a better solution to be able to overwrite the file even though it's opened by other users.
Evening All! (Old enough to remember Dixon of Dock Green (stupid enough to mention it!))
I'm working on a small database for a small team of support workers with a client base of 60 clients at anyone time, although the turnover is quite substantial.
I have done some searches on archiving records but am concerned that once archived a record would be difficult to re-integrate in to the live database, particularly if there have been changes to it?
I have considered that I may be just as well slapping a big label over every record that has a date in the [CloseDate] field and if a closed case is re-opened, the closed date is removed and the label disappears.
I'm still open to being convinced that the archiving is the way to go, but in the mean time I hope someone will be able to help with the expression needed in the form on_current procedure to make the label visible. If it was just a tick box I'd be flying by now, but I can't get my head around getting a populated [CloseDate] field to initiate the CloseRecordLabel.
Populating the table in db3 with a subset of records from db2.
I've gotten as far as opening the db2 and copying the table structure. Can't figure out how to run the query using execute rather than docmd.openquery.
Code: Dim appAccess As Access.Application Set appAccess = New Access.Application appAccess .OpenCurrentDatabase "DbPathString" 'copy the table structure to dbQn, overwrites any previous with same name
I keep on getting this error. The weird part is that I have exactly the same stored procedure but insted of FinancialYear it has a calendar year. So instead of of having something like '2013', it has 'FY2012 - 2013'. The calendar year SP works perfectly, but this does not.
Option Compare Database Dim objConn As ADODB.Connection 'Use this code to run the SP and extract all the records Public Sub LoadVRData() Dim rsData As ADODB.Recordset
I'm trying to do something with Excel and Access. From Excel 2007, I need to open an Access database exclusive, import from Access to Excel a table, do some work within Excel, and then start a macro within the Access database. Briefly, here’s what I have
1) To open the database exclusively:
Set connDB = New ADODB.Connection With connDB .Provider = "Microsoft.ACE.OLEDB.12.0" .Mode = adModeShareExclusive .Open strdbpath 'path to database End With
2) To import from Access:
strSQL = "SELECT * FROM [" & tn & "];" 'tn is Access table If connDB.State = adStateOpen Then Set objRS = New ADODB.Recordset objRS.Open strSQL, connDB, adOpenForwardOnly On Error Resume Next objRS.MoveFirst If Err.Number = 0 Then On Error GoTo 0 fieldCnt = objRS.Fields.Count For fieldNum = 0 To fieldCnt - 1 ws.Cells(1, fieldNum + 1).Value = objRS.Fie(fieldNum).Name Next fieldNum ws.Cells(2, 1).CopyFromRecordset objRS End If End If
3) To start a macro:
DoCmd.RunMacro "Daily Import"
Everything seems to work until the ‘DoCmd’ statement where I get a “You can’t carry out this action at the present time” error message.
I am getting the following error message in strsql:You tried to execute a query thats does not include the specified expression 'ScanDate=20140730 and SCanhour=8' as part of an aggregate function in the following strsql.
Code: Call func1("Z", 8) Public Sub func1(b As String, a As Integer) strsql = "SELECT Count(BatchNo) AS CountOfBatchNo, Sum(Envelopes) AS SumOfEnvelopes, Sum(Cases) AS SumOfCases, Sum(Pages) AS SumOfPages, ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') AS QueueNo " _ & "FROM jabberwocky " _ & "group By ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') " _ & "HAVING ScanDate=" & J & " and Scanhour=" & a & "" End SUb
I'm using shellexecute in my form whenever a picture is clicked the respective program/ application will open to show the picture. Because I want to see the picture more clearly by zooming it in or out. But the problem is I don't know how to disable the delete button and prev./next button, because I want the user use the program only to zoom it in.
Now i want to write VBA code to check sheet name "Summary" in that workbook and check if any of column D value matches with Access table tblmain field "Reference" values. If yes then change the status field of tblmain to "Yes".
I have a table which lists all documents that refer to a certain entity. the table contains the file names and paths. I would like the user to be able to search for text inside these documents.Can I use Windows Search for that by using code? Is there any other way?
I'm trying to create a function to update and amend records in a table.
The update part works and updates existing records with new data but I'm getting an error with the insert part.
Run time error 3078 The Microsoft Office Access database engine cannot find the input table or query 'FALSE'. Make sure it exists and that its name is spelled correctly.
Nothing called 'FALSE' so not sure what that means?
outputFileName is a variable I've created for file path / name etc...When I run this command, I get a popup box asking what format I'd like to export / save the form as. I'd like to disable this and just have it save as a PDF.
I have a series of forms that become current in a certain order. For example a menu form comes up. This is followed by a search form where the user gives search parameters. Then appears the results list. If the user wants a detail form comes after that, etc. Now when the user closes the last form I want the form before that one to show up and when he closes that one the one before that and so on. That is I am going to use the close button to go back to the previous step. How can I do this.
I am stumped as to how to get a form to update/refresh. Heres what I have! My main form:[Daily Log From] is opened, with a subform:[DailyDSQry subform] populated by a Query. This form has a command button that opens another form: [NewDSLog] in "acAdd","acDialog". What I need is: when the [NewDSLog] form closes, the [DailyDSQry Subform] needs to requery/update so that it displays the new entry.
I have tried the following in a command "CLOSE" button in form [NewDSLog]: docmd.Runcommand AcCmdRecordSave Me!Forms![Daily Log]![DailyDSQry subform].Refesh Me!Forms![DailyDSQry subform].[Query].!Requery doCmd.close
but no LUCK.....Keep getting errors Any Ideas Thanks all, I do appreciate the help Enviva
I have a DB with 20 forms. All of the forms are closed. I opened a new form and added a button. I would like to do the following when the button is clicked:
Check the "Tag" property of each form and count whichever is not null.
I have a form which is associated with a table. When I put some values in the fields which are connected to the database and try to close the form, it gives me an error that "Can't insert record the primary key might be duplicated"...! and then it closes.
Does the form store a record in the table while closing??
If so is there a way to prevent it?? Please reply..
I would like to have the Switchboard I created open when the database is selected. I used Tools/Startup to set this up. It works to a point. The database window opens and THEN the Switchboard opens (on top of the database window). There are some who I wish not to see the database navigation window). Also, is it possible to OPEN the switchboard again when a Form is closed?
I have a query that retrieves one of its criteria from a form. I have referenced correctly the value on the form from the query, and it works, but what I wanted to do is a bit more complicated: when the form is closed, I want to launch the query with a "default value". I tried to do it in 2 different ways:
a) Defining an "IIf" at the query criteria: I would need a function that checks if the form from which I retrieve the values is open. b) Defining public variables with a default value, which would be changed from the form: I don't know where/when to initialize the value of the variable.
Query gets criteria from form when it's open. If form is closed, query uses default value.
Hi, I have code for a command button that allows me to save the form information to 7 different tables primary key (the same primary key), owever when i close the form it resaves the information and i get a duplicate error msg. Is there anyway to get around this? Can I change what happens when the form is closed. i tried to apply this code to Form_Close() but to no avail.
