I currently have a form with a combobox that I want to use to control a subform that I have inserted. Both the parent form and the subform are pulling data from a different table. From the combobox, I want the user to be able to choose a persons name, and then the subform will display each project that the person has worked on based on the name that has been chosen. The reason that I have a subform is so that the user is able to click through projects that pertain to a certain employee. I have the master fields and child fields linked on the employees name as there are not to many employees and the names are all unique.
I have a combobox with a value list as the control source. I have a user who clicks the drop-down, but there are no options. Every other user is able to view these options, and this user has no problem with any other combobox with a value list.
OK. I have searched and searched and every thread dances around similar situations but none seem to address this particular one. I am trying to make a control on a sub form visible/not visible depending on the condition of a control on a main form. The catch is that the control I'm trying to change the state of is on a subform located in a tab control. I've tried a thousand combinations and none seem to work. How do I reference the control on the subform in a tab control?
Main Form: frmMemberMain Tab Control: TabCtl12 Tab Control Page: 2 Subform: frmChildren Control on subform: txtRelationship
Can someone please help before my brain explodes!! Thanks! :eek:
Hello guru's, I've searched the complete forum for the answer but I just can't find it. I've got a main form with 2 comboboxes on it. The first one filters the second one. What I need now is the 2nd combobox to filter the SubForm. I've tried to add a subform with the wizard but then it shows the complete table instead of only the ones selected in the second combobox.
I've got 3 tables: 1 for classes, 1 for students and 1 for the attendance. The 1st combobox selects the class, the second one selects the student. The 3rd table is used to enter absences (date, time, teacher etc) and it should appear in the subform when the student is selected.
Can anyone please give me a little advice? Thanks heaps in advanced! Sebastian.
I have a form called NewRequisitions that had a combobox in which the users select the Supplier (name, address, shipping details). I created a button to open a subform called NewSupplier so that they can add a new supplier to the Supplier table as needed. My code works okay for the first "NewSupplier" entry - the table and combobox are both updated and the new supplier is available for selection in the dropdown list. However, when there is a 2nd record that needs a NewSupplier added the combobox list doesn't get refreshed. The record is added into the table okay but you can only view it if the NewRequisitions (main) form is closed and then reopened.
Here is the code in my subform:
Private Sub Form_Close()
If CurrentProject.AllForms(NewRequisitions).IsLoaded Then
'save the record if there have been any changes If Me.Dirty Then Me.Dirty = False
'requery combobox on other form Forms!NewRequisitions!ComboSupplier.ReQuery
'yield to other events - and put the new list into use DoEvents
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.
I have this form where there's mention of CompanyID
In a subform, I have the information about all the contacts of this Company. In order to see full detail information for a particular contact (of that particular Company) I have a Combobox with a query.
At least: that's the whole idea. Unfortunately it doesn't work in Access: SELECT Contact.ContactID, Contact.Name FROM Contact WHERE ((Contact.CompanyID)=(Me!frmCompany![ContactID]));
Can any help me? I need to do more of the sorts of queries... and I can't figure it out how to use values from forms and parent-forms.
Hi well baisically the title explains the dilemma... I have a combobox in which the user selects an item and this updates the subform depending on the selection. However when i make a selection i get the following error:
Rumtime error '2101':
The setting you entered isnt valid for this property.
I understand what its telling me, however i cant seemed to find within the code where i have gone wrong, so heres the code:
Private Sub Combo4_AfterUpdate() UpdateSubform
End Sub
Private Sub UpdateSubform() Dim strSQL As String Dim varWhere As Variant
varWhere = Null
strSQL = "SELECT * FROM Sub_qry_CostCentre"
If (Me.Combo4 > 0) Then varWhere = " WHERE (CostCentre = " & Me.Combo4 & " )" ' MsgBox (strSQL & varWhere)
I am running into a problem with just one form in my database. I was getting the error 'Can't build a link between unbound forms'. After searching here and elsewhere the solution is/might be to type the linked fields into the child and master proprty fields. However when I do that I still can't get this form to work. Would anybody please take a look at the file attached - I trimmed it down to two tables with a few records and a form/subform.
I want to make a selection in the combo box and have the filtered results displayed on the form below - simple and I have done the same thing in the database elsewhere but I just can't see why it won't work here.
I have a subform for a hotel list, I have a combobox on the subform that show the list of the hotels, what I need is to create some fields on the subform wich shows me all the details of the hotel as address, email, facilities once I have selected a hotel from the combo list. How can I achieve this! Thanks Marco
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]
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?
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:
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.
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....?
I have a main form with an unbound combo box. I also have a subform showing records in datasheet view.on the after_update event of the combobox I want to filter the subform. Here is my code (note frmqrysubmittals is the subform and the recordsource for the subform is initially the RS1 portion of code):Dim RS1 As StringDim RS2 As StringDim RS3 As StringRS1 = "SELECT tblSubmittals.SubmittalID, tblSubmittals.Format, tblSubmittals.[Specification Section], tblContractorInfo.Contractor, tblSubmittals.[Project Number], tblSubmittals.Description FROM tblContractorInfo INNER JOIN tblSubmittals ON tblContractorInfo.ContractorID = tblSubmittals.ContractorID"RS2 = " WHERE (((tblContractorInfo.Contractor)=" & Chr$(34) & Me.cboContractor.Value & Chr$(34) & "));"rs3 = RS1 & RS2Me.frmqrySubmittals.Form.RecordSource = rs3me.frmqrySubmittals.form.requeryWhen I update the cbo box the subform doesn't display any records. When I go to design view and look at the record source it displays the RS1 portion (the original RS), but all of the terms are placed in brackets []. Note, the subform and contorl are both named frmqrySubmittals.I've been at this for days. What am I doing wrong?Thank you everyone for your help. This group has been a great resource over the past few months as I stuble through learning Access.
I have an orders subform displaying the line items in an order.
I have two combo boxes in the subform that allow the user to select 'Product catagory' and 'Product'. To enter a product the user must first selct a product catagory which filters the products available for selection. If the exact product is not listed then on option to 'Add new item' must be offered
The problem: At the moment the combo boxes show the same product catagory on every line of the order, even though each line is different and the second combo box is empty
How do I: 1. make the combo box only display the entry for the line it is on
2. force it to filter the available entries for the second combo box
3. offer an option in the second combo to 'add new item' (if the users selects this then Access should open the products entry form and pass the vale of 'Product catagory' from that line as a parameter to preset the form.
other info: The data for the subform is generated by a query on 3 seperate tables all linked by relationships. (tblProductCatagory, tblProducts, tblOrders)
I am currently having trouble filtering my subform by a different column than the bound column set in properties.
the comobobox shows the ID for the last email sent, with the combobox drop down showing the name and date of email when dropped down. I have tried 2 things and neither work...
1) Calling the filter on the combo-box column itself:
Code:
Dim myDate as string myDate = [Forms]![BenSearchForm]![BenSearchSub]![LastEmail].[Column(5)] DateFilt = " AND" & myDate & " BETWEEN " & "Nz([forms]![BenSearchForm].[Date3],#1/1/1900#) AND Nz([forms]![BenSearchForm].[Date4],#31/12/2100#)"
I have used similar code on another form, but I can't get the myDate variable to get to value of the 6th column in the dropdown (Date Of Email). an Easier way to look at this would be:
Code: DateFilt = " AND [Forms]![BenSearchForm]![BenSearchSub]![LastEmail].[Column(5)]" & " BETWEEN " & "Nz([forms]![BenSearchForm].[Date3],#1/1/1900#) AND Nz([forms]![BenSearchForm].[Date4],#31/12/2100#)"
2) Inside the subform, I have set up a seperate field that reads the result of the dropdown box column(5) and shows it. For example the Email with ID 22 has a date of 4/8/15, so any record with last sent email being 22 has a record that says 4/8/15. I am trying to use this value to filter between, but am unable of passing the value to my filter.
The textbox is called "Email Date" and the Control source is "=[Forms]![BenSearchForm]![BenSearchSub]![LastEmail].[Column](5)"
It shows the correct data, but does not allow me to filter by this field, when the apply filter button is pressed it asks me for the parameter value of the LastEmail Field.
Code: DateFilt = " AND" & " [EmailDate] BETWEEN " & "Nz([forms]![BenSearchForm].[Date3],#1/1/1900#) AND Nz([forms]![BenSearchForm].[Date4],#31/12/2100#)"
I use a number of set variables for my filters, so my filter ends up looking like
Code: .Filter = IDFilt + EmailFilt+ DateFilt
where all but IDFilt begin with AND. I use this on a number of other forms so I am sure this is not the issue!
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.
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?
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
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.
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.
