Using VBA To Set Criteria In A Query And Refresh A Corresponding Subform
Dec 20, 2007
I have a Main Form, "frmBuildingSearch", which has a subform "frmBuildingSearchSub". frmBuildingSearch has a couple of input text boxes & 1 command button: txtCity, txtSite, txtBuilding, cmdSearch.
I want the command button to refresh my subform (which is run from a query, "qryBuildingSearch") based upon the text that is in the text boxes.
If there is any value in txtCity, then I would like to set the criteria of my City field in the query to:
Like "*" & [Forms]![frmBuildingInfo]![txtCity] & "*"
The same would go for any value in txtSite and txtBuilding.
I would like to use VBA to do this.
View Replies
ADVERTISEMENT
Sep 16, 2014
I have a form, a couple of comboboxes and text boxes on it. When these are filled out, the SQL of a query is changed using these parameters.
There are three subforms on the form, all pivot charts, all based on the query being changed.
The goal would be to update all three according to the user-given parameters.
Right now the subforms only update if I close and open the form, which is probably not the best solution, since it's too slow.
I've also tried to requery and refresh them, with no result.
Then I tried to overwrite the recordsource of the subforms with the same text that was originally there. This got them to refresh their data, but then all of the charts disappeared and had to be built again, so this is a no go too.
View 7 Replies
View Related
Nov 29, 2006
I have a frmCustomer as a main form with a subformOrders as a sub form.
I now want to add another subform that is based on a query I build. I only want that subform to refresh (update totals) when data is entered in the subformOrders fields. I am trying to use the TimeInterval, I created a macro that just Requery. But soon as I click in the subFormOrders field, It starts to do the refresh.
Any Idea's
View 2 Replies
View Related
Jun 4, 2014
I am using a DLookup to default a value in my TextBox. This text box has an event attached to it that updates a table when a different value is entered.
I am having difficulty in getting the default value to reappear when I change my criteria.
For instance Manufacturer is Ford
Colour is default Black
I Change the colour to red
now I change Manufacturer to Audi
I need Default colour to go back to Black
The Lookup Table says Black its just that the default value only seems to run once, first time in and never again unless i exit the database and reopen it.
I have tried adding
Form.Repaint
form.Refresh
Form.Requery
to my afterupdate query
View 3 Replies
View Related
Apr 13, 2006
I have a form with one subform that lists trucking loads. The form is set to run modal. There is one button on the form to add new loads which when clicked opens a popup form to enter new load. When you click on the Save button on the popup form it saves load to table and closes taking you back to the Form/subform with list of trucking loads. I have tried various refresh tips to get the new load to show, but I have to close the Form/subform and re-open for the new load to show up. Is there a way I can get the Form/subform to refresh after I click the save button on add load popup form so it shows this new load without the need to close/re-open the Form/subform?
View 1 Replies
View Related
Apr 14, 2006
In my database, I have a form that has 2 subforms on it. Both subforms are set to run off a query that is driven by a text field on the main form. I want to have a button on the form that when pressed, it will refresh the subforms based on the value in the textbox. I can't find a good way to go about this, any ideas?
View 1 Replies
View Related
Apr 7, 2005
Refreshing a sub form.
I have a form in which a 'read-only' sub form is placed. The sub form is based on a query. I have a button on the main form which opens another form with check boxes which allows updates the to table.
I want the readonly subform to refresh/requery after the close event of the update form.
What is the event I need to use? I've tried activate, gotfcous on the main form. I am tearing my hair out.
View 5 Replies
View Related
Oct 6, 2005
Hi All,
I have one main form in which i have 2 subforms on different tabs.All the forms are unbound. Basically i am trying to assign a order to multiple persons.SO the main form has the order information.1st subform shows the already assigned persons and the second one is for assigning new persons.
The problem i am having is that whenever i assign a new person using the second subform i want to refresh the first subform but i am failing in it.
i used forms!firstsubform!refresh on the second subform..but it says object doesnt support the property or method.Then i create a refresh button on the first subform and on its click event i m doing me.refresh but again its failing.
The first subform is not bound and it has datasource as a querry like select a,b,c from order where orderid=txtorderid.
Any help on this pl.thanks
View 7 Replies
View Related
Jun 14, 2006
hi guys, here is the situation.
I have 3 forms:
1- editpopsubform where the info comes from a select statement. There is one field in the list which is the status and it changes the backround colour depending on the status value.
2- editPoplist , which has the form 1 as a subform
3- editpop, a form which allows the user to edit a record selected in the poplist form.
If i modify the status in the form number 3 (editpop), when i go back to form 2 (editpoplist) , the status's value changed but the colour doesnt.
any idea?
thx, max.
View 1 Replies
View Related
Oct 9, 2006
I have tried in vain using refresh programming to overcome this,
I have a main form that records site and date information. Within this there is a subform that records a number of fields that are standard, but differ according to site location, so that it appears like;
Offsite1 Onsite1 Offsite2 Onsite2
a a a a
b b b b
c c c c
So what I want is for the main form to record that site and date info, with a subform recording the above listed info. One new record within the corresponding table will be created for each grouping (i.e. Offsite1).
To do this effectively, I want to go to the next record once I have filled in the information for Offsite1 for example, this will record the site, date and Offsite1 information in the table. When moving to the next record however, I want the site and date information in the main table to remain or be copied across. The subform will clear as it is a new record and so will the main form normally.
Can anyone help?
View 6 Replies
View Related
Jul 5, 2005
Hello, After searching for many days I have given up and so here I am. The following code is the event associated with a button New Record on a subform.
Dim txtDepth As Integer
Me.txtDepth = Depth_To
Me.Refresh
DoCmd.GoToRecord , , acNewRec
Me!Depth_From = Me.txtDepth
Me!Depth_To = 0
Me!Depth_To.SetFocus
Exit_NewRecord_Click:
As you can see the record in Depth_To is copied to the variable txtDepth, the subform is refreshed and the value txtDepth is copied to Depth_From.
I have added a subsubform to the subform that allows users to enter information about an interval only when required, otherwise it reamins hidden. What I would like is once the user has finished entering data into the subsubform, a button New Record (on the subsubform) allows the user to go to the subform, hides the subsubform and then refreshes the subform as above. I have tried copying the code (above) to the subsubform button and using SetFocus to switch to the subform, but it appears to be not as simple as that.
In advance thank you.
View 3 Replies
View Related
Nov 7, 2005
right I have searched and searched the forum but obviously this isnt as simple as I thought it would be!
I have a main form called "frmAllStudents" based on a query called "qryAllStudentsMainScreen". The unique field in all forms is Admission Number.
I have on this form a data-entry subform called "TeachingAssessment" where a member of staff puts in teaching Assessment.
I have a second continuous subform called "previousTeachingAssessment" which lists all the previous teaching assessment.
The problem I have is that unless i select another student or open and close then the previous teaching assessment doesnt update. How do I requery to get this to show on the continuous form?
View 3 Replies
View Related
Jul 19, 2006
Hello All,
I have a bit of a problem. I have a main form with two subforms. The subforms are linked to the main form my two fields. The main form and subforms are each based upon a different query.
I have written VBA code that filters the SQL statements of the subforms based upon the value of the the REV field on the main form. When the user changes to different record on the main form, the subforms are supposed to change accordingly.
The problem is that the subforms on the main form will not refresh each time the user chooses a new record on the main form. If I change to a new record on the main form and then open the subform outside of the main form, the correct information is shown. The problem however is that the subform shown on the main form does not automatically refresh. I have tried numerous strategies, but to no avail. If I am on a record in the main form, and I exit the main form, and then re-open the main form, the subform data on the main form will reflect that of the record I was on prior to previously exiting the main form.
Here is some of the code that I have tried:
Me![qryRevText_ sf].Form.Requery
Me![qryAdminRevText_sf].Form.Requery
Me.Refresh
or
Me.[qryRevText subform].Form.Refresh
Me.[qryAdminRevText subform].Form.Refresh
or
DoCmd.Close acForm, "qryRevText subform"
DoCmd.Close acForm, "qryAdminRevText subform"
Please help me if you can. I would appreciate it greatly.
Thank you in advance.
Akagami
View 14 Replies
View Related
Sep 12, 2006
hi all,got a tricky problem...hope to get help from u all...appreciate!!!i almost complete this but run into a "refresh" problem..I have a mainform with a textfield where users can input a number to search for an item; if found, a list in the 1st subform will appear in datasheet view. When i select a item in the 1st subform, if the item has a child, the 2nd subform will show the item's child/children. I try to do this by passing a parameter from a MasterPno-Link field(linked to Partlist field in the 1st subform) on the mainform to a query that the 2nd subform is based on. The problem now is when i select a record on 1st subform, the 2nd subform doesnt refresh to show the corresponding number although it always shows the first item corresponding with the 1st item in the 1stsubform.I tried to use this code on AfterUpdate event of MasterPno_Link. No error but no update..Private Sub MasterPno_Link_Change()Dim ctlList As Control ' Return Control object pointing to list box. Set ctlList = Forms!submain1 ' Requery source of data for list box. ctlList.RequeryEnd SubI also tried this Form!submain1.Requery but didnt workwhat am i doing wrong? pls enlighten me! thanks alot. Hope i make myself clear:)
View 5 Replies
View Related
Sep 27, 2013
I have created a form and a subform from a table.Initially I created a table with 11 rows. After completion of my project I added an extra of 480 rows to the main table. But the newly added rows are not coming in my subform.
Main : Main table (It consists of around 491 rows)
MForm : Main Form (It shows of only 11 records which I was 1st created)
How to refresh my subform inorder to display all the rows.
View 13 Replies
View Related
Sep 4, 2005
Hi,
Is there a simple way to add optional criteria in a query for a subform? Basically, what I'm trying to do is have a number of comboboxes in the parent form that indicate what records are shown in the subform (these records could then be added and/or deleted). I would like to be able to have a combobox such as "Month" that would filter the data shown in the subform to that month. If no month is selected, then the subform would show all months.
Any help would be much appreciated.
View 2 Replies
View Related
Jul 19, 2007
Hi,
Trying to run an append query for a specific record on a subform but won't recognise the subform control.
The Master form is fmSickEdit do I have to reference the Master file in the SQL?
The SQL for the query is:
INSERT INTO tblDisLetter ( SickID, DateAdvisedDisciplinary )
SELECT tblSick.SickID, Date() AS [Date]
FROM tblSick
WHERE (((tblSick.SickID)=[Forms]![fmsubSickListEditVersion]![SickID]));
Thanks
View 2 Replies
View Related
Nov 14, 2012
I have the following Form and Subform. Form name is "100-Select Form". Subform name is "103-Report Dates". Within the subform I have two fields I'm using, "Start Date" and "End Date'.
In my query I have a date field. I want the query to read from the Subform (if I open the subform directly my criteria listed below works, but when the main form is open, my query doesn't seem to be able to read from the subform).
Here is the criteria I have in the date field of my query: >=[Forms]![103-Report Dates]![Start Date] And <=[Forms]![103-Report Dates]![End Date]
I have a feeling that I somehow should be pointing my criteria first to '100-Select Form', and then to '103-Report Dates' within that form, but I'm just not sure how to write this and nothing I'm trying is working.
View 2 Replies
View Related
Feb 10, 2005
Hello,
I have a subform that is based on a SQL statement that sums by Dollar Amount and groups by Account. I have it set up so the user can double-click on an account, which opens an unbound form. I then have the form execute a SQL INSERT INTO statement, which works beautifully.
The form opens as a pop-up, and I have it requery the subform when it closes. However, when the pop-up closes, I go back to the first record on the sub-form. I need it to "remember" the record I was on and take me back to that one. What is the standard procedure for doing this?
Thanks in advance!
Eric
View 2 Replies
View Related
Jan 10, 2006
Ok heres the situation, I have one form(frmBikes) that i use to filter the results in a subform(frmSubBikes). From the main form i have a button which opens another form(frmSell) which allows me to enter the sold price and when "cmdSell" is pressed many delete and append queries are run on the data stored in (frmSubBikes). When this button is pressd the deleted record has #deleted in each field where as it should be gone and i get this error message.
(The expression you entered refers to an object that is closed or does not exist)
What do i need to change? my minds been off this project for ages now ive got a brain block so help would be appreciated.
this is the code for "cmdSell"
If MsgBox("You are about to complte selling transaction: " & r & ". " & Chr(13) & " Is that correct ? ", vbQuestion + vbYesNo, " User Accounts") = vbYes Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.SetWarnings False
DoCmd.OpenQuery "appSoldBikes", acNormal, acEdit
DoCmd.OpenQuery "UpdSoldBikes", acNormal, acEdit
DoCmd.OpenQuery "DelHires", acNormal, acEdit
DoCmd.OpenQuery "DelRepairs", acNormal, acEdit
DoCmd.OpenQuery "DelSoldBikes", acNormal, acEdit
DoCmd.Close
Me![frmSubBikes].Form.Filter = Searchstr
Me![frmSubBikes].Form.FilterOn = True
End If
Exit_cmdSave_Click:
Exit Sub
Hope you can help,
Thanks Sci
View 4 Replies
View Related
Apr 15, 2014
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
View 2 Replies
View Related
Aug 15, 2014
I looking for a simple trick to refresh the data in a subform in vba code?
View 3 Replies
View Related
Nov 23, 2012
I have a form called frm_Incident_Single. On this form I have a subform/subreport called tbl_Incident_Apps_subform. I have a command button on frm_Incident_Single that opens another form called frm_Incident_Apps where I choose multiple selections. When I save or close the form I would like to update the subform - tbl_Incident_Apps_subform.
I've tried to requery/refresh the subform in the save procedures in frm_Incident_Apps but when I save it, I get a syntax error. Here is the line of code:
Forms!frm_Incident_Single!tbl_Incident_Apps_subfor m.Form!Requery
I've tried many variations and can't get it to work. How I can refresh the subform?
View 3 Replies
View Related
Sep 12, 2012
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.
View 11 Replies
View Related
Feb 6, 2005
I have a Form> [Management]
I have a SubForm> [Management_History]
There is data in the [Year] field and the [Season] field.
I placed a button on the subform to create a report based on the data of the current "Sub Record" .
Basically, I want to use the data in the subform to create a small "Lookup" report.
OK:
I have the Management Form
I have the Management History SubForm
I Placed a Button Called PRICE LIST
The button kicks off a Macro that previews my PRICE LIST REPORT
A Query is the RECORD SOURCE for my PRICE LIST REPORT.
In the QUERY, I have two fields that I specify criteria "hopefully" based on the sub form record I am viewing.
I does not work on the SubForm. However if I Open the SUBFORM directly, the report pops up correctly.
What syntax in my QUERY is required to GET the data right frm the current SUB-FORM??
I tried the following:
Like [Forms]![Management]![Management_History].[Year]
Like [Forms]![Management]![Management_History].[Season]
View 2 Replies
View Related
Jun 3, 2013
I'm trying to build a query to use as a filter in the DoCmd.OpenForm function.
I have done this several times before, referencing values from forms e.g.
Point To
Field: LeadID
Table: Lead
Criteria: [Forms]![GridDisplay1]![LeadID]
This filter is used in the procedure:
Code : DoCmd.OpenForm "LeadDetails", acNormal, Point_To", , , acDialog, """"
And it works great, I click a button next to the record I want to select (records displayed on continuous form) and it opens the Lead Details form on that particular record.
However now I am trying to accomplish the same thing, except instead of a continuous form I need to draw from a continuous sub-form. The form "BrokerMgmt" contains a sub-form named "BrokerSearch." The RecordSource for the sub-form is set once the user enters data into a few textboxes on the main form and clicks the search button:
Code:
Private Sub brkSearch_Click()
Dim argCount As Integer
On Error GoTo Err_Handler
If IsNull(brkFirstName.Value) And IsNull(brkLastName.Value) And IsNull(brkCompany.Value) Then
MsgBox "You Need To Select Some Values", vbCritical, "Lead Tracking"
[Code] ....
The sub-form then displays basic info such as Name, Company, State, and the "BrokerID" which is the primary key of the "Broker" table. This "BrokerID" is displayed in "Textbox 20" so I set a button next to each result to on_click perform the procedure:
Code:
DoCmd.OpenForm "BrokerDetails", acNormal, "Point_To_Broker2", , , acDialog, """"
"Point_To_Broker2" is set up in the same style as "Point_To":
Field: BrokerID
Table: Broker
Criteria: [Forms]![BrokerSearch]![Text20]
However instead of the BrokerDetails form opening to the appropriate record I get a msg box asking for a parameter. Why is this? Even if I enter the correct BrokerID as the parameter it still gives me an error saying you can't go to the specified record.
View 11 Replies
View Related
