Forms :: Run Subform Queries From ComboBox Result
Sep 10, 2014
I am currently building a main form with a combo box control, two subforms and a hidden text box (optional). There are many different references to be made between tables, queries and forms to get to the result I am after, and I have tried many different codes from other people’s queries that seemed close to mine; however, had no luck getting my subforms to work as I’d like.
The names of my controls/forms/queries are as follows…
Main Form: frm_UReport
Subform 1: sbfrm_Query1 (refers to Query1, which refers to and calculates on tbl_C)
Subform 2: sbfrm_Query2 (refers to Query2, which refers to and calculates on tbl_S)
ComboBox: cmbo_ProductList (refers to tbl_ProductList)
TextBox: txt_ProductCode (refers to cmbo_ProductList, column 0)
I want the two subforms to run their respective queries after a Product Code has been chosen from the ComboBox. The relationship between the tables is one-to-many, from tbl_ProductList to each of tbl_S and tbl_C; with ProductCode as the primary key in tbl_ProductList.
Currently, I can choose a Product Code from the combo box, and it populates the text box successfully, but nothing happens in the subforms.
The codes I have worked with so far are as follows…
sbfrm_Query1 (Record Source):
SELECT Query1.ProductCode, Query1.PurchaseDate, Query1.ExpiryDate…
FROM Query1 WHERE (((Query1.ProductCode)=[Forms]![frm_UReport]![txt_ProductCode]));
sbfrm_Query2 (Record Source):
SELECT [Query2].[ProductCode], [Query2].[ProductDescription], [Query2].[PurchaseDate], [Query2].[AverageCost]…
FROM Query2;
[Code] ....
I have started working on integrating sbfrm_Query1 into the main form first, which is why the code looks different between the two subforms. Once I have it working, I will translate the same format to sbform_Query2.
Due to all the different levels of references that need to be made, I am having trouble identifying which (or if all) of the codes are incorrect.
View Replies
ADVERTISEMENT
Jun 25, 2014
I have saved query object named qrySearchBill. I wan to call this query through vba and display the result in a subform named subQrySearchBills in datasheet view. Here's how I want it to work:
When the main form loads, I want all unfiltered records to be displayed in the subform initially. The user may then decide to filter based on date range, so he enters startdate and enddate parameter values in their respective textboxes in the main form. Then click search button to run the saved query based on the date range parameter taken from the textboxes.
I have this code so far:
SQL of the saved query object:
Code:
PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.Date, Sum(tblInvoice.[TotalPrice]) AS Amount
FROM tblCrdCustomer INNER JOIN tblInvoice ON tblCrdCustomer.IDNo = tblInvoice.NameID
WHERE tblInvoice.Date Between [StartDate] And [EndDate]
GROUP BY tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.Date;
vba code to call the query and its parameter:
Private Sub btnSearchBill_Click()
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set qdf = CurrentDb.QueryDefs("qrySearchBills")
[Code] ...
This code works fine except that when the main form loads, a prompt window appears to ask for the value of dateStart and dateEnd. I don't want it to prompt because it's suppose to get these values from the main form's textboxes (txtStartDate and txtEndDate respectively), plus it should initially display all the unfiltered records.
View 3 Replies
View Related
Nov 29, 2014
Database that will show both his side income information as well as his regular income and expenses. I have constructed a tables for the categories, his rec/payables which has information for both his personal & business, the other table he wants to filter only information regarding the business. I set up a form with a subform (business). I need the combo box in the main form to auto populate the matching fields in the business subform, everything but the client name and id. I have tried creating a query for criteria business, it works in query mode but not when I try to use it on the form.
The main form:
IncSrc IncAmt ExpSrc ExpAmt ActionDate
The subform
IncSrc IncAmt ExpSrc ExpAmt ActionDate ClientName ClientID
View 2 Replies
View Related
Dec 5, 2013
On my main form I have a subform which holds two table values. [Roles] & [TrainLevel] example of this looks like this:
Plant Manager - T1
Assistant - T2
and so on for about 12 rows with different roles, each with different training levels.
This subform is then based on a query: [qryRoleEventWithRoles] which also looks at all the other selections, selected on the main form.
eg. User selects a training course on the main form, this then changes the subform and shows what level of training each role requires training in, T1, T2 etc..
All this has been working fine for months, almost a year. Each selection on the subform is based on using two combo boxes [Role] & [TrainingLevel] these are both setup on the subform and using the autonumber field, RolesID and TraininglevelID from two separate tables.
Like I say, this has been working fine. Now, all of a sudden, they've stopped working and telling me I can't change the valve because there bound to an autonumber value. or something like that.
View 3 Replies
View Related
May 3, 2015
So I have a Form (frmPositions) with a subform attached (fsubPosSkill).They are linked through Position ID frmPositions Record Source is qryPositionNeeds
Code:
SELECT tblPositionNeeds.PositionNeedsID, tblPositionNeeds.PositionID, tblPositions.Position, tblPositionNeeds.NeedsID, tblNeeds.NeedStatus
FROM tblPositions INNER JOIN (tblNeeds INNER JOIN tblPositionNeeds ON tblNeeds.NeedID = tblPositionNeeds.NeedsID) ON tblPositions.PositionID = tblPositionNeeds.PositionID;
It has two texts boxes on it [PositionID] and [NeedID]
fsubPosSkill Record Source is qryPositionSkills
Code:
SELECT tblPositionSkills.PositionSkillID, tblPositionSkills.PositionID, tblPositionSkills.SkillID, tblSkills.SkillName, tblPositionSkills.SkillValueID, tblSkillValue.SkillValue
FROM tblPositionSkills, tblSkills, tblSkillValue;
It has two combo boxes, cmbo1 and cmbo2.
cmbo1: control source is SkillID and Rpw Source is qrySkill
cmbo2: control source is SkillValueID and Row Source is qrySkillValue
when I open frmPositions, the subform shows only the combobox label in the header, there is no combobox that allows me to select Skill or Skill Value.
View 1 Replies
View Related
Apr 2, 2014
I have a main form that identifies a Client File: frmClientFile (Single Form)
On the main form is a subform for Cases: subfrmCases (Continuous form)
Also on the main form below the Cases subform is a tab control that contains additional subforms to view/update different aspects of a case: subfrmCaseClients (Continuous Form).
The way this operates is that the user first chooses a file using a combobox on frmClientFile.
The subfrmCases is linked to the main form (using the caseFileID) and filters correctly.
The subfrmCaseClients is then linked using some hidden text fields on the main form (using caseFileID, CaseID) and this filters correctly.
On subfrmCaseClients is a combobox (cboClientID) that I need to requery based upon the Case Row selected on subfrmCases.
I have tried numerous combinations of options to force the requery by trying to apply a macro on the following:
OnCurrent, OnSelectionChange, OnChange and AfterUpdate events to no avail.
If I hit F5 to refresh the entire page the combo box gets updated as expected.
How do I get the Requery to work programatically? Is this even possible?
View 2 Replies
View Related
Aug 17, 2015
I am trying to use a combo box to refresh a subform. I am working with Access 2007 as my front end and SQL 2014 on the back end. On my main form called frmDealers I have an unbound combobox where the user selects a dealer and then text boxes are populated with address and phone number. I have it unbound because I don't want the user to make changes to the dealership at this point. On the subform I have people that work at the dealership. That form is called sfrmDealerContacts. The subform could have multiple contacts for each dealership. The subform is bound to a query called qryDealerContacts. I have it bound to a query because I want email hyperlinks in the table too. I tried searching the forum and found this thread number 279791.
After searching I added:
Form_sfrmDealerContacts.Form.RecordSource = "SELECT * FROM dbo_DealerContact WHERE DealerID = " & Me![cboDealerName].Column(1)
But this brings up an input box so I must have something wrong. My code is:
Private Sub cboDealerName_AfterUpdate()
Me.txtDealerAddress = Me![cboDealerName].Column(2)
Me.txtDealerAddress2 = Me![cboDealerName].Column(3)
Me.txtDealerCity = Me![cboDealerName].Column(4)
Me.txtDealerState = Me![cboDealerName].Column(5)
[Code] .....
View 2 Replies
View Related
Jan 14, 2014
I do review SQL sometimes to check what's going on. Access has been a steep learning curve, but my understanding of queries and relationships improved a lot recently.Some of the finer points of forms are just giving me frustration. My form related knowledge probably lags behind my data/table/relationship/query related knowledge. Its probably simple, but as you know these things are not too intuitive at first.
I have a query which (built on another query and some tables in turn) delivers exactly the data I want. I want that data to come up in my form (or datasheet subform actually) BUT, I want to be able to filter for PERSON.ID. I want the user to be able to use a combo-box to do that filtering.
OK then, main form, with unbound combo-box at the top. I assume it should be unbound because I don't want any data edited based on selections which are supposed to cause temporary filtering only (correct me if I'm wrong). For the Row Source I've used a qryWholeName. This query creates a WholeName for each person, and includes their PERSON.ID as well.
[URL]....I cannot see the example DB they have attached in the thread. Finally, my database is likely to be accessed by several people at once. Different users will want to be selecting and filtering for different people simultaneously.
View 3 Replies
View Related
Jan 30, 2015
I have a form with a combo/dropdown on the MAIN form and a child subform.I have designated a Master /Child link of ID to FGId respectively linking the combobox with the subform.
However, when a select an item from the combobox I expect to see the subform datasheet refresh accordingly. This is not happenining , even when I force a refresh call on the subform control itself....?
View 1 Replies
View Related
Feb 14, 2014
I've got an access front-end database running on Access Runtime on a Terminal Server with a back-end on a data server both of which are hosted by a remote company and accessed via Terminal Services. All is well and the database is working fine but because this is a cloud system I need a way that I can easily delve into the tables on the back-end without copying it down locally from the cloud each time (I've set up an "administrator" option so that this would be available to limited users). I've got the front-end forms working as I'd like but I have quite a few tables and I'd like to avoid creating a separate form for every table. I've been able to populate a combobox on my form with the list of linked tables and I'd like that when I choose a particular table from this list that the whole table is then visible within a subform below in case I need to manually intervene with the data for any reason.
View 1 Replies
View Related
Jun 25, 2015
I am setting up a subform in continuous with combobox, but when I enter the data on the first combobox, it duplicated to all the following records. Is there a way to stop that?
View 2 Replies
View Related
Aug 18, 2015
I made a subform based of a query that I put in a main form. The subform contains a date field, where a full date is inserted (example 24.12.2015.). What I am doing is making a form where the subform would be filtered by a combobox by months. This may be wrong (correct me if it is), but for values in the combobox I placed number from 1 to 12 (as months). This combobox would then only show records from that month. I used a Change function in VBA on the combobox but I keep getting an error.
This is the VBA on the combobox where I change the record source of the subform. I probably made the WHERE part wrong but dont know how to fix it.
Combobox name is cboMonth.
Field where the full date is, is DateOut.
Subform is called subCustomer.
Code:
Private Sub cboMonth_Change()
Dim SQL As String
SQL = "SELECT qryCustomers.SID, qryCustomers.KID, qryCustomers.MMT, " _
& "qryCustomers.Owner, qryCustomers.User, qryCustomers.Policy, " _
& "qryCustomers.DateOut " _
& "FROM qryCustomers " _
& "WHERE Month([DateOut]) LIKE '*" & Me.cboMonth.Text & "*'"
Me.subCustomer.Form.RecordSource = SQL
Me.subCustomer.Form.Requery
End Sub
View 4 Replies
View Related
Mar 5, 2014
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
View 1 Replies
View Related
Dec 15, 2005
If you have a form that is based on a select query that has selected no records, the form will display as totally blank.
One way to avoid this is to ensure that the query is always able to select at lest one record; but Is there a way a message can be displayed if no records are selected?
View 3 Replies
View Related
Jul 17, 2013
I have a mainform in access with a subform (continous form mode). The subform's source is a query that has a criteria parameter that gets the values from a combobox in mainform.(list of projects) Combobox name= cmbProject
It works fine for some records (old records), so when I migrate new data into the table the comboxbox display the new data but when I select the record to be dispayed on the subform, the record or data is not in the subform, so the record is in blank.
View 1 Replies
View Related
Feb 25, 2014
I have a continuous subform of 'static data' whose record source is a SELECT query across multiple linked tables.
Most of the fields are locked and purely there for information purposes but I need to be able to allow end users to change one particular field in each record if they need to - choosing a value from a predetermined list (i.e. one of the tables)
Usually, when I need to do this, I add a command button to the subform and use that to open a separate pop-up form specific to that record, from which the user can make whatever changes they like and then update the record. So in this scenario, a simple unbound combobox linked to that table, on that separate form, would work quite easily.
But I would prefer if users could make their changes directly from the subform without (yet another) pop-up form required each time, to make managing these records more fluid and less time-consuming.
So my thought was to use a bound textbox in the continuous subform (to take the existing value for each record), hide it and overlap it with a visible unbound combobox whose row source is the table of available options and whose default value is driven by the hidden bound textbox. And then to use the AfterUpdate event of the combobox to run the UPDATE query on the record in question.
However, while the combobox is getting the correct default value and the correct list items, I can't make a selection from it. Now I am aware there are 'issues' with using a combobox in a continuous subform but I was hoping I could circumvent them by not binding it.
View 14 Replies
View Related
Apr 12, 2006
Hi All, I hope someone can help with this, I have 2 tables, 1 main table that holds all my part data ( ie part num, description, etc) and a second table with vendor info. On my Form I have all my fields that display the record. I placed a combo box on my form that I need the user to be able to select a vendor, which is working but I need the form to show the vendors part number in a field. The main table has a manufactures part number, and I have 3 fields that have my 3 vendors part numbers for that part in it. I have the combo box so that it shows the vendor name but how do I get it to look at a certain field for the vendor part number. Im still really new to Access and am clueless any help would be greatly appreciated. I know I have not explained this every well so I am attaching a sample of the DB so you can see that I have Thanks again
Thanks everyone..
View 4 Replies
View Related
Jun 17, 2015
I have a form, frmProjects, with a continuous subform, sfrPeopleOnThisProject. They have a master-child relationship. The form shows one Project record at a time, while the subform shows all the People records for that project. This works as expected - the correct records show up in the subform as you move through the form records.
To make it easier to glance through the list of subform records (as they can sometimes be quite long), I've added a navigation combobox to the header of the subform. If I understand correctly, as the combobox is within the subform, it should obey the master-child relationship: it should only show the subform records associated with the main form's current record. It does not. Instead, the combo box collects *all* of the records from the table that the subform is based on. So, choose the record for Project 1 in the main form. Up pops the 2 people on Project 1, but the combo box lists both the 2 people on Project 1 and the 3 people on Project 2.
The row source for the subform combo box is:
SELECT tblPersonOnProject.PersonOnProjectID, tblPerson.Fullname FROM tblPersonOnProject INNER JOIN tblPerson ON tblPersonOnProject.PersonFK=tblPerson.PersonID ORDER BY Fullname;
I can make the combo box perform as required by adding "WHERE Project=Forms!frmProject!ProjectID" to the row source and placing a hidden textbox on the main form for ProjectID (the autogenerated primary key) to sit. Have I misunderstood the point of the master-child relationship in the subform? This hidden textbox thing can't be correct.
View 2 Replies
View Related
Jun 6, 2013
There is a command button on my form that calls a filtered query. I'm filtering the query based on a combo box within the form using
[Forms]![frmAssignDwgs]![Combo15]
It works beautifully until I put the form as a subform in the Navigation Form I'm using. When I execute it from within the Navigation form, it errors out looking for [Forms]![frmAssignDwgs]![Combo15].
Is there another syntax I can use so this will still work from within the Navigation Form? Or another way to approach this functionality?
frmNavigation is the main navigation form
frmAssignDwgToPkgs is the subform that holds Combo15
I found another thread (which I can't link to since I'm new) and tried various arrangements of the following to no avail.
[Forms]![frmNavigation]![NavigationSubform] .[Forms]![frmAssignDwgToPkgs]![Combo15]
View 3 Replies
View Related
Oct 12, 2013
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies
View Related
Apr 1, 2013
I have a suppliers table and a products table. Two forms, Stock form with combobox to list Suppliers and a sub form with combobox that lists Products.I want to select a supplier from the Stock form and then the combobox in the subform to only list products directly sold by the Supplier.Have dabbled in SQL as follows:
SELECT Products.ProductID, Products.ProductName
FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID
WHERE ((Stocksubform.SupplierID=Stock.SupplierID))
ORDER BY Products.ProductName;
View 2 Replies
View Related
Sep 4, 2013
Can the result of a combo-box be used in text in an AfterUpdate event?
Example, the combo-box (Result) can be negative or positive. If it is negative a textbox is populated with the test is (combo-box here)?
View 3 Replies
View Related
Jan 10, 2015
The forms in the code are subforms on a TabControl on a main form.
I have this code which needs to add together only the areas [Area] of records with a FloorNumber field value of 1 or 2 or 3 or 4.
At the moment the code works for one entry of 1 in the form frmRoomDetails. I'm guessing i need For Next or something like that but i don't know and also unsure of how to code it.
Private Sub FloorNumber_AfterUpdate()
If [Forms]![frmSiteDetails]![frmRoomDetails].[Form]![FloorNumber] = 1 Then
[Forms]![frmSiteDetails]![frmFloorsDetails].[Form]![Text8].Value = [Forms]![frmSiteDetails]![frmRoomDetails].[Form]![Area]
End If
End Sub
View 1 Replies
View Related
Sep 16, 2013
I would like to select a record from my combobox dropdown list and have that record populate in my subform. Currently, I am only able to select the 1st record at the top of the dropdown list to appear in my subform. But I would like to select any record from the dropdown list and have it populate my subform.
View 8 Replies
View Related
Apr 26, 2015
I am trying to use a combobox called Manufacturer to select which table the combobox called Model gets it's rowsource from using the code below.
Code:
Private Sub Manufacturer_AfterUpdate()
If (Me.Manufacturer.Value = "Siemens") Then
Me.Model.RowSourceType = "Table/Query"
Me.Model.Recordset = "SeimensTable"
Me.Model.RowSource = "SELECT Model FROM SeimensTable"
Else
If (Me.Manufacturer.Value = "Samsung") Then
Me.Model.RowSourceType = "Table/Query"
Me.Model.Recordset = "SamsungTable"
Me.Model.RowSource = "SELECT Model FROM SamsungTable"
End If
End If
End Sub
But when I run the form and select Manufacturer. Combobox Model remains empty. tell me what I'm doing wrong?
View 5 Replies
View Related
Mar 28, 2005
This should be fairly simple, but it is driving me nuts trying to get it to work right. I feel like I'm chasing my tail.
Anyway, I have a query with three fields: "PersonnelID", "QualificationID", and "QualificationStatus". This is a query off of my large table for tracking qualifications; however, I am using a form with three combo-boxes for the same information. The first two combo-boxes (personnelID and QualificationID) are used to filter the query. This gives the result of one and ONLY one row in the query. A unique personnelID number, a qualificationID number and the qualification status of the record in question. I WANT to be able to automatically retreive the last field (qualificationStatus) from the query and display in the form (and then save in my new table - I know, shouldn't save calculations and such).
I have it working somewhat. I can select the first two combo-box fields and then when I click the third combobox for qualificationstatus, I only have one item in the dropdown menu and then I select it. So far, so good, but this only works for the first record that I am working on. If I do another record without closing the form first, the combobox selection for qualificationstatus still shows the previous entry. However, if I switch the form to design mode and then back to form mode, the combobox shows the correct value. I just can't seem to get the form to refresh prior to selecting the third combobox. I have tried "DoCmd.RepaintObject" after update on the second box and on focus for the the third box, but it still doesn't work unless I switch the views back and forth.
Any suggestions would be ever so gratefully appreciated...
Brian :eek:
View 1 Replies
View Related
