SubForm Filter Based On A ComboBox
Aug 18, 2006
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.
Thanks
Steve
View Replies
ADVERTISEMENT
Apr 22, 2005
I have 2 comboboxes, the first one is called "activity", whereby I have 3 options to choose from, and the second is called "level".
When I click an "activity", for example Drawings, I want the "level" combobox to list a unique set of options for that category. and if I click on a different "activity" for example Planning, I want the "level" combobox to show a completely different set of options.
If anyone can give me any help on how to do this then I would be very grateful!!
Thanks
Angela :eek:
View 2 Replies
View Related
Mar 21, 2013
I have a table which has a "Start" and "End" fields, and i made a query based on this fields which continues to look up the next points until the last end points. I wanted to filter the last end points on the query table that was related on the combobox value.
View 3 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
Jun 30, 2015
I have a database that is used to generate quotations. I have a form that I want to use to Re-Quote something. By Re-Quote, I mean I want the employee to be able to go in and search for the Part desired for Re-Quoting and create a new quotation with all of the information from that part and have the ability to change any of the information as needed. The Form is unbound, and has tab control with three tabs. I would like to search by three different categories "QuotationNumber", "Customer", Or "PartNumber". How do I have the combobox filter the list box below based on what is being typed in the combobox?
View 6 Replies
View Related
Jan 9, 2006
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.
View 6 Replies
View Related
Aug 5, 2015
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!
View 3 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
Aug 25, 2005
Hello.
I am trying to make an invoice sheet in Access 2000.
My program consists a Order Sheet (frmOrder2), Delivery (frmDelivery) Sheet and a Invoice Sheet (frmInvoice).
All three are linked by DoNo.
The DoNo is made by frmOrder2.
I have a form that is called frmInvoice and a subform called subInvoice.
What I basically want to do is, to get the subform (subInvoice) to show records depending on the selection in the combobox DoNo in the main form.
subInvoice should show the Items, UnitPrice, Amount, TotalPrice, Remarks. These Items should show the records of frmOrder2.
I am kind of confused and totally lost, because I have no clue how to start!
And I am quite a beginner so, bear with me! Thank you!
View 4 Replies
View Related
Dec 13, 2005
Hello everyone!
I think I am asking the impossible, but can't think why it can't be done!
Here's the dilema:
I am working with an unbound form to search a Mobile Register.
On the form I have an unbound combo box which looks up User details from a table (LKP_User).
When I select a user, I want the subform below to filter all the details for that user and show me which mobiles they have.
PROBLEM:
Combo Box has 3 fields, User_ID, User, Dept_ID. User_ID is bound column but not displayed in combo (only User) is displayed.
I need to link the User_ID with the User_ID on the subform, but don't know how to tell it to look at column 1 of the combo box.
Can anyone point me in the right direction? Do I need some fancy code to get this to work?
Cheers in anticipation
NKA
View 6 Replies
View Related
Feb 28, 2005
I need to open my form with a filter attached that is based on criteria already existing in the subform. I've tried putting the following code in the OnOpen event of my main form :
Code:Dim stDocName As StringDim stLinkCriteria As StringstDocName = "Campaign Form"stLinkCriteria = "[Campaign - Last Contact Status subform].Form![Communication Response] in ("2","4","10","11")"DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormPropertySettings, acWindowNormal
Basically I want to open my main form (Campaign Form) and only show records where the "Communication Response" is 2,4,10 or 11(a variable on my subform "Campaign - Last Contact Status subform"). Which in itself is a problem because I can't seem to code all of these variables- it will only accept one.
When I run the above code I am getting asked to enter a parameter value for "Campaign - Last Contact Status". I'm not too sure where this is picking this up from or how to fix it. I've even tried to run this based on the underlying query of "Campaign - Last Contact Status subform" with the same results.
Can someone help me? All I want to do is open the form based on the criteria I specify which is contained in a field in the subform. It doesn't sound that hard but it seems to be getting the better of me!
View 4 Replies
View Related
May 2, 2005
Hi,
I've posted this same question a few months ago, started something else and forgotten about the problem, now I really need to solve this! (I had no luck with the original post - code still not working).
I want to apply a filter to my main form based on criteria showing on a subform. This is the code I've got (applied to the OnOpen event of the main form):
Code:Private Sub Form_Open(Cancel as Integer)Dim stDocName As StringDim stLinkCriteria As String stDocName = "Campaign Form"stLinkCriteria = "[Forms]![Campaign - Last Contact Status subform]![Communication Response]=1" DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormPropertySettings, acWindowNormalEnd Sub
The main form is called Campaign Form
The subform is called Campaign - Last Contact Status subform
The field I want to filter on is Communication Response
The criteria I want to filter is : I only want to show records from the Campaign Form (main form) where the status of the Communication Response on the Campaign - Last Contact Status subform (subform) is equal to 1.
I've tried a few variations of the above code but the results are always the same - prompting to enter a paramater value. Now I'm just fishing and hoping I'll get it right.
FYI. the subform only contains one record for each record on the main form. It works a bit like this :
Customer||Date||Communication Response(1=open, 2=closed)
One||20/4||1
Two||21/4||2
Three||21/4||1
One||23/4||2
The corresponding record on the subform for customer One will show the communication response of 2 as this is the most recent date. Therefore you now have the following :
Customer||Communication Response
One||2
Two||2
Three||1
Now I want to only show those records that have a Communication Response of 1. Remember this field appears on a subform. This is the part I am having trouble coding.
I know I've probably just explained the most irrelavent details but if anyone can help me it would be greatly appreciated!!
View 1 Replies
View Related
May 23, 2013
I have researched but couldn't find any working filter criteria for subform criteria.i have used the code, searching the web but still no luck.I have attached the picture and sample northwind database (accdb). I just want to filter customers in Orders navigation using text criteria.
View 9 Replies
View Related
Jun 12, 2012
I have a Master form which includes tabs within the tab I have subforms. I am looking for a way to filter my master form based on data found within my sub form.
I have a check box within one of the sub-forms called "softwareInstall" if yes the box would be checked. I want to filter all customers within my main form with a check box in my main form to turn on and show all customer who has this box check within the subform SoftwareInstall=True.
View 9 Replies
View Related
Jul 26, 2015
So I have a search form that I created that has also a subform (a continous form, that has a query as its source) where my records are listed. I started on making a Between dates filter where i would be able to pick dates and get the results of records that are in that range.
I put two text boxes in which I set format to Short Date so that I get the button to select the date from a calendar for each text box. Then I made a button that is only for this and added a Clickon code so that it filters my subform over the recordsource of the form. The problem is is that i get an error and it doesn't filter.
Code:
Private Sub btnDatumUpis_Click()
Dim SQL As String
SQL = "SELECT qrySearchV.VID, qrySearchV.MarkVoz, qrySearchV.ModelVoz, " _
& "qrySearchV.TipMot, qrySearchV.Regist, qrySearchV.VlaVoz, " _
& "qrySearchV.KorVoz, qrySearchV.KatV, qrySearchV.DatumUVoz, " _
[Code] ....
View 5 Replies
View Related
Jul 31, 2014
I have a Main form, and a subform which lists client details. On the Main form I have an unbound field. I want to be able to type a word into this unbound field and have it display all company names that have this word in them. ie. I type "Ltd" into the unbound field and it displays all companies with "Ltd" in the title.
I have created a query that does exactly this (Like ("*" & [Enter Word] & "*")), it displays a dialog box and I type in "Ltd" and it displays all relevant companies.
I have tried everything I know to make this work when I use the unbound field on the Main form, but I've had no luck.
View 3 Replies
View Related
Oct 23, 2013
I have a multi select list box which I am trying to use to open another form and apply a filter based on the user's multiple selections.
List box - lstCatergories
Main Form - frmSuppliersSummaryCategories
Subform on Main Form - frmSuppliersSummaryCategoriesSubForm
The Subform contains the text box CategoryID
Here's my code which nearly works...
Code:
Private Sub cmdFilterSuppliers_Click()
On Error GoTo Err_cmdFilterSuppliers_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
[Code] .....
My problem is that the control "CategoryID" is on the subform and I'm having massive problems trying to reference it. This is the part of my code that won't work:
Code:
DoCmd.OpenForm "frmSuppliersSummaryCategories", acNormal, , "CategoryID IN(" & strWhere & ")"
However; if I open the SubForm directly it works perfectly. I.e.
Code:
DoCmd.OpenForm "frmSuppliersSummaryCategoriesSubform", acFormDS, , "CategoryID IN(" & strWhere & ")"
To summarise... How do I reference the control "CategoryID" on the subform "frmSuppliersSummaryCategoriesSubform"?
View 4 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
Jan 25, 2006
I've tried searching the forums and haven't found quite what i'm looking for. I would like to be able to change the source for a combobox based on another combobox. The simplest way i can summarize that is i want to be able to choose A or B, depending on my choice i want another combobox to display all the values that A or B can have.
Thanks
Jim
I'm not sure I worded that very well. :(
View 1 Replies
View Related
Sep 1, 2005
I need to be able to use a combo box on a form to filter the records shown. The fields on the table are as follows:
ID No, Ref No, Start Date, Comp date, Price etc.
I have set the form up to run off a query that shows all records with a certain ID No. Each ID No has a number of reference numbers. I would like to use a combo box on the form so the user can select a Ref No and the form would be filtered to show only the details of that specific ref no.
What is the easiest way to do this?
Any help would be greatly appreciated.
View 4 Replies
View Related
Jan 11, 2006
I'm building a filter using comboboxes and I'm having trouble with two of them. One is to filter by records that are completed or by records that are not completed. The other combobox is to filter by records that have comments or by records that have no comments.
Filter7
Row Source Type: Value List
Row Source: Completed;[tblMainData.Status]='Complete';Not Completed;[tblMainData.Status]<>'Complete'
Column Count: 2
Bound Column: 1
Filter8
Row Source Type: Value List
Row Source: Comments;[tblMainData.Comments] Is Not Null;No Comments;[tblMainData.Comments] Is Null
Column Count: 2
Bound Column: 1
The VBA Code is:
==================
If Me(Filter7) <> "" Then
strWhere = strWhere & " " & Me.Filter7.Column(1) & " " & " And "
Debug.Print strWhere
End If
If Me(Filter8) <> "" Then
strWhere = strWhere & Me.Filter8.Column(1) & " And "
Debug.Print strWhere
End If
==================
I'm getting an error on building the strWhere variable. Can I use filter criteria statements in a combobox column?
Thanks,
PC
View 1 Replies
View Related
Mar 2, 2005
Hi there
This im sure is a easy question but I am just trying to learn access so I am having a few problems.
I have made a form that contains 2 comboboxs and a listbox.
How can I use the 2 combobox to filter a table and to place the information into the listbox.
Is it possible to use either 1 of the combo boxes or both to get the filtered result.
Any suggestions would be great!!
TIA!
View 2 Replies
View Related
Feb 19, 2006
Hi everyone
I use Access 2003 and saw Ken Higg's sample database to filter records in a form using a combobox. I managed to replicate his settings and wondered about an extension to the principle, but I don't know if it's possible.
In the sample, a value is selected using a combobox and all matching records are filtered so it's possible to scroll through them. Is it possible to have a second (or more) combobox on the form so it is possible to filter on one or another criterion? This would mean one combobox filling the other one and I realise that the one that Ken provided is unbound.
The reason that I'd like to do this is, for instance, if there are records with Surname, 1st, 2nd and 3rd lines of the Address and Postcode, I may wish to filter records by entering the Surname or Postcode. Each of these fields would have to be displayed in a combobox. As the sample is set up at present, only one of the Surname or Postcode has to be selected via the combobox and the other is a textbox.
It may be that what I'd like to do isn't possible - or someone may have come up with some other idea. I'd prefer to have the search/filtering on the form, rather than using the buttons on the Form View toolbar.
Thanks for your patience.
Juan
View 7 Replies
View Related
Aug 17, 2006
I have three comboboxes on a form – Fabric, Degree, Color.
The row source type for each is Table/Query. The row source is a Select Distinct query. I’m using a query so the list will populate itself. The choices the user makes in the comboboxes are used to filter called forms.
Would there be a way to include the word “ALL” in a drop down list in addition to the data generated by my query. If the user chooses “ALL”, the called page will not be filtered on that particular choice. I just created a record with “ALL” in those particular fields. That seems to work, but I think there might be another way.
Thanks
View 1 Replies
View Related
Jan 14, 2015
I am using Access 2010, Windows 7
I have an unbound form with a combo box and a bound subform on it.
I am using the combo box to get a value to filter (and display) records in the subform.
My code is:
Private Sub cboSessions_AfterUpdate()
On Error GoTo cboSessions_AfterUpdate_Err
Me.SSubform.Visible = True 'subform was initially invisible
Me.SSubform.Form.FilterOn = True
Me.SSubform.Form.Filter = "[LinkID]=" & Me.cboSessions
Me.SSubform.Form.Requery
cboSessions_AfterUpdate_Exit:
Exit Sub
cboSessions_AfterUpdate_Err:
MsgBox Err.Description, vbCritical, "PROGRAM ERROR"
Resume cboSessions_AfterUpdate_Exit
End Sub
The subform is initially invisible to stop it showing all records.
This works fine AFTER the first selection(!) - the first Update of the Combo Box just doesn't filter the subform??
I have tried putting code in the main form's On Load Event - allocating a value to the Combo Box and running cboSessions_AfterUpdate but that does not work either.
View 1 Replies
View Related
May 11, 2015
I created a form that has about 8 different filter options that can be used to filter a query. I used the filter by form table to set it all up and I can get each of them to work individually as well as 2 work together... but as soon as I added the information for the 3rd, I stopped getting results... in fact nothing happens. Even with the code for all 3 and selecting just 1 option, nothing happens any more. I have
cbopersonnel that is supposed to look up values in 4 different personnel columns
cboshift that looks up all shift work
cboworkdef that looks up what the job was (just a title)
Having just 2 in the filter by form works great, but adding 3 screws everything up. And I'm not talking about selecting all three (I know that would limit the output more) but I mean, with all 3 setup and selecting shift as "nights"... nothing gets filtered anymore. I have cbopersonnel on "look for" tab and everything else is on the next "or" tab. I tried to set it up on individual tabs but access combined it. I also tried adding cboshift to the "or" tab with everything else and access sent it back to "look for". All of the cbo references are under their respective search area... so in the table, the shift column has the cboshift lookup value. Is it just because I'm not using quotes around the cbo output? That doesn't make sense to me though. Anyway below is the lookup value for the cboshift.
[Forms]![Log Book Lookup]![cboshift]
View 2 Replies
View Related
