Modules & VBA :: Refresh / Requery Form From CmdButton
Jan 23, 2014
I have a main form that the user will enter data. Once they hit a submit button, it saves the record and I was hoping it would also refresh the form in which is populated by a query. The form that is linked to a query will be displayed on a monitor 24/7. It will display pending orders. So I was hoping as new orders are placed, the query would display them without needing to be closed and then reopened.
Here is the method I'm using but it's not working.
I have a form called Add New Delegation, i have combo box of Institution names on the main form and a subform for Agreements discussed. with a combo box called agreements. I successfully cascaded the combo boxes so that the agreements discussed on the subform are filtered by their respective institutions on the main form. However i am unable to refresh the list each time a new institution is selected as the Macro will not allow me to requery a control on the mainform from the subform..
I am sorry if this has been asked already a million times already but I could not find any relavant information in the time I had.
I have a requery / refresh problem.
I have a Customers form showing all customers, I also have a NewCustomer form for adding new customers.
On the Customer form, on activate I have me.refresh
On the NewCustomer form I have in the onclose,
If IsLoaded("Customers") Then Forms!Customers.Requery
These work well however if Customers is open and I enter a new record in the New Customer Form, then view the Customer form again the new record is not shown.
I can however click back on the NewCustomer from then straight back to Customers and Hey Presto it appears
Any clues as to what I need to do to update the form?
I am looking for a way to ensure a sub form is refreshed or requeried.
I have forms as follows:
1.form for Purchases 2.subform for all items purchased 3.subform which has the total purchase price ie the total of all items in the subform 2 above
A save is forced in 1 before data in 2 is entered. Once 2 is exited a save if forced. However 3 is not calculated unless a manual refresh is performed.
I am sure there must be a way around this. Any suggestions would be helpful.
There are 2 levels of forms, the main parent form is called INSPECTION. Then there are two subforms: TESTRESULTS and addTESTRESULTS.
the form TESTRESULTS is linked to the TESTRESULTS query "QTESTRESULTS2". This query looks at two hidden fields on INSPECTION called FILE and REPORT NO. The query finds all fields inside of TESTRESULTS that match the two criteria. This works fine. The form TESTRESULTS is linked to the QTESTRESULTS2 query as direct data source link. This works fine.
a temporary table has been setup to append test results to TESTRESULT table. This works fine. What doesn't work fine is updating the subform TESTRESULTS when needed. I want it so that the welder enters his information, clicks save, the info gets appended to TESTRESULTS table and instantly updates on the form for them to verify.
[inside subroutine for saving a record] all efforts at accessing this form have failed. I've tried !form!subform!requery, it has failed, I've tried making dummy fields inside the form and accessing them and that has failed, I've tried .setfocus on the form and that has failed, each time the compiler tells me 'unknown field', when in fact I'm trying to hit the form itself.
Its worth mentioning that I've set events [on this form for any kind of update event including mouse click] to Me.requery for this form when you click on it, it updates, but I need to automate this somehow, and the form just seems untouchable via VBA.
I set up several combo boxes to display Tables from msysobjects. The record source is using a WHERE clause to display certain tables both Linked and Local. Occasionally, when clicking the dropdown arrow, the box only displays local tables. This situation seems not to matter whether tables have been newly linked or unlinked. despite having both a me.requery and a me.refresh in the code and/or even clicking the Refresh All button on the ribbon menu, the form does not seem to update the combo box to display both linked and local tables.
In further testing, it seems that it is not a form issue because I took the SQL statement I used as the record source and created a new query with the sql string. The query behaved the same as the combo box, only listing out local tables.
My current work around is to close the database and reopen, then all is well. Hopefully there is another way to get the linked tables listed without this drastic step.
Don't think it's necessary, but here is the sql statement. Type 6 is linked.
Code: SELECT msysobjects.name FROM msysobjects WHERE msysobjects.[flags] = 0 AND msysobjects.[type] in (1, 6) AND Right([name],7)<>'_SOURCE' AND Right([name],4)<>'_OUT' AND msysobjects.name not in ('tblImport','tblImportFormats','tblUniversal') OR msysobjects.database=gblprojectname() ORDER BY msysobjects.name, Right([name],7);
I have been using AYS2000 database example from Rogers access library. I have been in the process of modifying to meet my needs. Although I have run into a problem that was prevalent in the original database example. I have attached the original database for your reference.
Problem: At the bottom of the database beside the "New Response" button there are 2 combo boxes that allow you to select the survey and response in which you want to view in the subform. Although when selecting the response the subform does not always update the record in the subform. It would appear that the problem occurs only after a response has been previously selected. I have tried adding additional code to refresh and requery the subform already without any success.
I have a subform in a tab within a mainform. Within the tab there is a button to open a form to delete/add data to the subform. So upon closing this pop up form I want to update the subform to show what has been deleted or added, I have tried the various things to requery the subform but none of it seems to work, here is what I have tried;
I have an close button on a form that is supposed to Refresh the subform of a different form and then close the form with the close button.
frmOperations - no refresh / requery subfrmPlants - Needs to be refreshed / requeried
frmPlants - Needs to be closed
Currently the button effectively closes frmPlants, but it's failing to refresh the data on my subform to display any changes that have been made on frmPlants. I've tried with refresh and requery, neither seem to work.
Code:
Private Sub lblClose_Click() If CurrentProject.AllForms("frmOperations").IsLoaded Then Forms!frmOperations!subfrmPlants.Form.Refresh End If
DoCmd.Close acForm, "frmPlants", acSavePrompt End Sub
I have a DCount code that traps duplicate records in the database and gives a message box. (the dcount is based on 2 criteria that are a compound index) I would like to have the message box ask if the user wants to go to the duplicate record. How would I write the code that when the user clicks yes, the form requeries and opens to the record that matches the criteria? Is there a way to add criteria to a requery?
I'm working with a form someone else created and am adding a delete button. I've managed to add the button and get some code working which will delete a record from a table. The problem I'm having is that after the delete operation occurs all fields in the form, and the corresponding items below the form, show #Deleted in all fields. How can I force a refresh using code?
Public Sub cmdRequery_Click() Dim vFlag As String vFlag = Me![EncounterNbr] Me.Requery
With Me.Recordset .FindFirst "[EncounterNbr] = '" & vFlag & "'" Me.Bookmark = .Bookmark End With
The user starts on a continous form and opens a record, makes some changes, and then when that form closes it triggers this public sub.
The code does what I want it to do in that it returns to the last encounter number that was selected (now on the continous form again) and it requeries and shows anything that was changed about the record on the form that was closed before requery.......
But it always resorts and moves it and I want it to stay in the same spot unless the user sorts. Is there any way to make that happen?
I have a form with two subforms. One subform is a datasheet that pulls data from a temp table. During the other subforms Form_Current, the temp table is emptied (DoCmd.RunSQL "Delete from tblAddRefs"), reloaded with data relevent to the current record, and requeried (Forms("frmEDFP").Controls("tblAddRefs subform").Requery). This works like I want it to...
With the exception of the initial load of the main form. The subform based on a table shows (#Deleted) instead of data. if I step through the code, I can see that the temp table is emptied and repopulated by the time I try to requery the subform. This is still happening during the Form_Current of the other subform, which is successfully running on main form load.
If I requery (Forms("frmEDFP").Controls("tblAddRefs subform").Requery) using a button on the main form once it is loaded, the #deleted data is correctly is replaced by regular data, so it's like the requery in the Form_Current event isn't doing anything during load.
The Header and footer are not linked. The Footer simply displays all the existing bookings for said ouse/Room/Date combination. (Date being all dates between the StartDate and EndDate) BUT (surprise, surprise)users don't look at this to check if a booking already exists.
Also - It seems pointless to have the users enter the same data (House, Room, UserID) in 6 times (one for each day that they want to book the room.
So I am trying to automate the process.
Therefore, I append each 'new' record (that the users adds in this session) into a temporary table. (House / Room / UserID / StartDate)....note, no end date because I need a separate record for each day.
I have a query that is supposed to check the EXISTING bookings with the temp table. My code (paraphrased here for simplicty) says
do while StartDate < EndDate - if Qry_CheckForClash returns 0 records then '(uses a dlookup) - append from TEMP to BOOKINGS. - - else - compile an alert message to the user (appending the Room No and Date each iteration) - In Qry_CheckForClash, increase the StartDate by +1 - end if loop
My problem is that after the last step (increase StartDate by +1) the call to Qry_CheckForClash still returns the initial StartDate. But when I check the TEMP table, the StartDate has changed.
So how do I REFRESH or REQUERY a query that is not associated to a form or report?
1. I have a two-tier, tabbed Navigation form (Menu-Main).
2. One of the second-tier tabs opens a form (VendorMain) which displays a Datasheet based on [Query.Vendor_Query]
3. VendorMain contains a Command Button (EditButton), which calls a subform (VendorForm) used for editing.
4. Upon saving the edits, VendorForm's OnClose event is supposed to Requery the datasheet on VendorMain, so the edits are reflected when the user returns.
I have tried over a dozen different permutations of the following, but nothing seems to work:
Essentially I have a form_A with several tab's and then one list control box in each of those tabs. At present if you double click on any item within the control boxes your taken to another form_B with info about that item and when you close that from down again if refreshes form_A.
Is it possible to only refresh the specific list control box that is active instead of refreshing the whole form ?
I have a form which contains two sub forms. In the first subform I have 4 fields of Cut#, Size, Quantity and Style. Once the cut# is selected, a vba code runs and fill in the rest of fields. In second sub form I have a field of Fabric# (Combo Box) which should be restricted to the Style value on the first sub form. I have a query which contains the Fabric# and Corresponding style and I try to write a vba code to requery once Style value changes.
I want a command button to "Requery" a combobox. the combobox uses a query to determine the records listed (it lists incomplete records). after completing the record, i'd like to hit a command button that will "requery" the combobox so that the recently completed record is no longer listed.
I have a form, containing a button that when clicked, it runs a query and displays the results in Pivot Table view. The query is set to display only results for the manager name selected on the form. So, when someone opens this database, they first select their name from a drop down box and then click the button to run the query. The query displays average call rate for equipment that person is responsible for. The only problem I am having is that if someone goes back to the main form, selects a different name, and clicks the button again, the query will not refresh. The query won't even refresh when I click the Refresh All button in the Ribbon. In order for the query to update, I have to first click the button to refresh the pivot table and then click the button to refresh the query. Is there a way to do these two steps through visual basic, so that the user doesn't have to do those two extra steps?
Right now, I am using the Docmd.OpenQuery code on my button.
I have a form frm_GlobalSettings with a combobox cmbDescription that finds a record based on the value selected. The row source type for cmbDescription is Table/query, and the row source is a select statement on the form's underlying table.
I want the user to be able to use cmbDescription to add a new record to the form's underlying table. I currently have
Code:
Private Sub cmbDescription_NotInList(NewData As String, Response As Integer) Dim strTmp As String 'Get confirmation that this is not just a spelling error. 10 On Error GoTo cmbDescription_NotInList_Error 20 strTmp = "Add '" & NewData & "' as a new global setting?"
[code]....
but when a new value is entered this errors on line 70 with "runtime errror 2118 - you must save the current field before you run the requery action".I've tried various ways around this but can't get it to work.
I want to call commandbutton1,2,3 after every 10 seconds automatically but following code doesn't do that.
Code: Private Sub UserForm_Initialize() 'Update the Barcodes printed today Call CommandButton1_Click 'Update batches to be scanned / batches scanned today Call CommandButton3_Click 'Update files batched and counted today Call CommandButton2_Click Application.OnTime Now + TimeValue("00:00:00"), "GoToSub" End Sub
I have a typical Access 2007 database with a main form that has combo boxes that are populated by other tables. To edit or add to the combo boxes I have separate forms. When I make the changes I want the combo boxes to be refreshed. I would imagine I can do it with a FormName.Refresh command. However, I am curious if there is a command to refresh all forms and reports in case I have more than one form open with combo boxes that link to the changed data.
I have a listbox on a form with several fields, 3 of which are currency fields.When I filter the listbox using afterupdate from a combobox on the same form, the listbox loses the currency format on the fields.
