Filtering Look Up Using Parameters On Main Form
Feb 28, 2012
I am creating a booking database for a Bus Hire company. There is also an Invoice element to it, which will Invoice customers for a range of bookings that they've made.You will see in the screen image, the relationships between the four tables:
-tblInvoice
-tblInvoiceDetails
-tblCustomer
-tblBooking
IN tblBooking (see screen dump) the customer is allocated a booking number and so on. However, at the end of the month, the Customer will be invoiced for specific bookings. I would like to, firstly, select the customer in the Invoice form, then in the InvoiceDetails subform, select THE BOOKINGS THEY HAVE PREIVOUSLY Made using a look up. (see screen dump called frmInvoice.Gif)
I have applied a criteria to the data source for the look up. (see image datasource...GIF) with forms![frmInvoice]![CustomerID] in the hope that the query will search for BookingIDs where the currently selected CustomerID (in Form Invoice) finds a corresponding match in tblBooking.
View Replies
ADVERTISEMENT
Jul 6, 2005
Below is the working version of the code works for single report filtering. say a report called "rptduedate_census2" and i can filter out using form instead of hard coding query of that report (say lastname=form!frmsample!txtlastname). I dont want to hard code query and there are reasons for that.
I am using a Main report called "rptDueDates_Dept' and there are about 10 different subreports (see print screen). The main report is not bound to any query. It has many subereport. Each subreport is bound to some query and i need to be able to filter them before data appear on the main report. Am i making any sense, LOL.
How can I implement my working code below to do this. PLEASE PLEASE PLEASE HELP. I AM NEW TO ALL THIS. I have attached a print screen of the main report, just to show how i put subreports in their.
===== MY WORKING CODE ======
Private Sub cmdPrv_Click()
Dim strFilter As String
strFilter = "[Mail_Census_Date] BETWEEN #" & txtStartDate & " # AND # " & txtEndDate & " # "
' Apply the filter and switch it on
If SysCmd(acSysCmdGetObjectState, acReport, "Report") <> acObjStateOpen Then
DoCmd.OpenReport "rptduedate_census2", acViewPreview
With Reports![rptduedate_census2]
.Filter = strFilter
.FilterOn = True
End With
DoCmd.Restore
Exit Sub
End If
End Sub
View 1 Replies
View Related
Jan 22, 2014
Is it possible to use Filter option in my form without using Query Wizard? - So that the user would be able to edit and change the other field on the same form
View 2 Replies
View Related
May 6, 2013
I have faced with a problem while i was filtering my main form.....my problem is when i filter the main or parent form the related sub-form also gets filtered how to manage the main form so that when i filter it the sub-form should not be filtered so that i can get the related record to my filtered one in the sub-form... as for the time being when i filter the main form the related record in the sub-form is not shown and when i press unfiltered on the sub-form its data gets appeared....
View 11 Replies
View Related
Sep 17, 2006
hey there
i have a main PatientForm that contains patient information from multiple tables:
[GENERAL] values
Subform - [RefMD] values (referring doctor)
Subform - [Drugs] values (prescribed meds)
Subform - [Diagnosis] values (medical diagnosis)
Subform - [Encounters] values (visits to the doctor)
what i have is a form (image attached) and i need to be able to select any combination of parameters (including state, zipcode from GENERAL, i.e. referring doctor last name from RefMD, Drugname from Drugs, Diagnosisname from Diagnosis, and VisitType from Encounters) and filter PatientForm where all the selected parameters are true..
does that make sense?
all tables are linked using the field HistNum
how do i code this? i am pretty lost right now
View 2 Replies
View Related
Apr 27, 2013
I have a Suppliers database which contains a form that will allow me to place orders with Suppliers.The Main form has a combo box that allows me to select the supplier. The combo box is called SupplierID with the following:
Row source: SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers ORDER BY Suppliers.CompanyName;
The subform is called Stock Subform witha combo box called ProductID with the following:
Row source: SELECT DISTINCT Products.ProductID, Products.ProductName, Suppliers.CompanyName, Products.Discontinued FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID=Products.SupplierID WHERE (((Products.Discontinued)=0)) ORDER BY Products.ProductName;
Event Procedure - AfterUpdate: Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate
Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID
[code]..
The Link fields are done on the Purchase Order ID (PONoID).What I want to achieve is to select the supplier from the combo box (SupplierID) on the main form and then the combo box (ProductID) on the subform to filter to only show products directly supplied by the Supplier selected on the Main Form.
View 11 Replies
View Related
Aug 8, 2014
I have a database (Access 2007) that handles a large number of consultants. Each consultant has a number of skills, has worked in a number of countries, and speak several languages. As contracts come in, I wish to generate a report based on the skill set, language and country/countries that best suits the contract; thereby identifying a group of most suitable consultants. I have designed a report with three sub reports for skills, languages and countries. Each sub report is linked to the main report through Link Master/Child fields using the ConsultantID. That's all fine; I get a report showing all consultants listing all their qualities.
Now, I want to be able to filter the sub reports on the three aspects: Skills; Countries; and Languages.
In the past, for other applications, and with the unfiltered form in preview I have used a popup form with unbound Combo Boxes to generate a string to use in the reports filter. I think I got that technique from Allen Browne, and it has really served me well.
Now, however, I can't use this technique since I can't set the filter for the sub reports. I thought that I might be able to use the Link Master Field with text boxes on the main report, but haven't managed to figure that out.
View 14 Replies
View Related
Jun 24, 2015
I have a database with a Main Menu Form, containing a Button that loads my main data entry form. When the Button is Clicked portions of the data entry form that is loading shows through the Main Form Background (e.g. portions of the navigation bars, and portions of the boarder on the form that is loading.)
View 5 Replies
View Related
Dec 1, 2005
I have a subform which makes a change to a field on the main form. When focus is returned to the main form, the BeforeUpdate and AfterUpdate events fire. Why? I thought from the form's perspective, the subform is just another control.
BTW, I get the same behavior if I modify the field from within the Exit event of the subform control.
In either case, the main form's Dirty event is NOT triggered.
View 2 Replies
View Related
Feb 16, 2005
I've searched the forums for quite a while now and haven't exactly found the answer I'm looking for, so I appologize if it's already been covered elsewhere.
I have a tough situation to explain, but I'll do my best:
I have a query that has several fields that are calculated by adding or multiplying other fields in the same query - a few of which are parameters. I have made the parameters the "right way" by going to the query menu and selecting Parameters... and filling in the neccessary Parameters and Data Types instead of creating a new field in the query and putting my parameter info in that way.
So, long story short. I want to have a form where the user can enter in the parameters, select a value for a field from a drop down box, hit a "go" button and a report will be generated listing info from that query.
How do I go about entering parameters through a form and how do I put in a drop down box that will list all of the values available for a field in the query?
Thanks in advance,
Eric
View 5 Replies
View Related
Sep 11, 2006
I have created a form with multi-select list boxes, behind which is the following code to transform the users' selections into query parameters.
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("Test")
If Me!lstAB.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstAB.ItemsSelected
strCriteria = strCriteria & "Centres.[Area Board] = " & Chr(34) _
& Me!lstAB.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "Centres.[Area Board] Like '*'"
End If
strSQL = "SELECT * FROM Centres " & _
"Where " & strCriteria & ";"
qdf.SQL = strSQL
DoCmd.OpenQuery "Test"
Set db = Nothing
Set qdf = Nothing
End Sub
I would like to allow the user to set, on the same form, which field they want the resultant data to be sorted by, preferably by having a tick box alongside each list box, which sets that field as the sort field. The form will obviously need to allow only one of these tick boxes to be selected.
Can anyone advise me on how to modify the above code to make this possible?
Thanks,
Gary
View 1 Replies
View Related
Dec 22, 2006
Hi all,
I have a continuous form that is bound to a SQL Server view.
For each record in my form I have a button, which when pressed opens up a second form. The second form is bound to a stored procedure that takes a parameter. The parameter value that I want to pass to this second form is the value of one of the fields in the first form.
I did the following in the click event of the button on my first form:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "SecondForm"
stLinkCriteria = "[Field1]=" & "'" & Me![Field1] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
However, when I run this it keeps prompting me to specify the value of Field1 so this value is obviously not getting through. Do you have any idea why this might be happening?
Thanks in advance
Kabir
View 4 Replies
View Related
Jul 15, 2013
i have a form with a sub form and combobox, when i select item i want that the sub form will be update with new values according to parameters from the combox.
the data of the sub form is from query with criteria
Code:
[Forms]![Examination]![Client_ID]
and the combobox (Client_ID) "After Update" event set to macro- requery (the sub form)
so every time that item selected in the parent form combobox the sub form items /data will change.
*i attached a screenshot
View 3 Replies
View Related
Mar 23, 2006
Good Afternoon,
I am trying to create a form where a user will enter in a value into a text field. Afterwards, when the user clicks "Enter", a query will run and will LOOK FOR THE VALUE THAT WAS ENTERED INTO THE TEXT FIELD. i.e.
User enters their address into the field and clicks the enter button.
Afterwards, a query will run like
select * from customers where address = @address <== the value the user entered into the text field. This is where the mystery lies. How do you pass values?????
Thanks,
Nervous Jervous
View 6 Replies
View Related
Aug 19, 2007
Hi There,
I am trying to get a query to pull dates from a form to use as limiting parameters for a report. One of my forms has the user enter a range of dates into two text boxes (a from-date in one box and a to-date in another) as the range of dates they want to pull information from a table via the query. These dates are also used in the Title header of the report that is generated from the query information.
How can I get the query to use these dates in the text boxes, rather than having to use the "between / and" command on the criteria line of the query design?
If I just have parameter windows pop up and have the user enter the dates there, I can't use those dates for the report, or can I? Any help would be appreciated. Maybe I haven't explained this properly, if so, I'll try again if I've just confused you.
View 3 Replies
View Related
Aug 18, 2015
I have a sample database (attached) where I have a form called frmVoteHeadsOne. The voteheads that I have are swimming (Swim) and transport (Trans), which both appear for data in a table. On the form, from the combo list, I would wish that if I select swimming, for example, only Mark Tyler's name is returned since he has paid 2000; if I select Transport from the same list, only John clement's name is returned.
My purpose for doing this is because in the actual database, I have 21 different voteheads which I would like to get different results from based on what the user selects from the combo list.
View 8 Replies
View Related
Mar 15, 2007
I have created a query that will pull the price of a property into the cost field combo box on a booking order subform depending on the values of the start date and property number enter onto the same form.
The query gets the price from the property price table matching on the property no i've selected on the booking order subform and also the start date i've entered on the booking order subform which needs to be between the start and end date fields in the property price table.
Fields in the property price are property price no, property no, start date, end date and price.
Also the booking order subform is a subform on a booking form
This is the query i have created:
SELECT [Property Price].Price
FROM [Property Price]
WHERE (((forms![booking order subform]![start date]) Between [property price].[start date] And [property price].[end date]) And (([Property Price].[Property No])=forms![booking order subform]![property no]));
The problem is when i run the query from the cost combo box the query isn't picking up the fields on the booking order subform instead its bring up a seperate meesage box from parameters start date and property number to be enter.
Can someone give me advise how to make the query use the values in the fields on the booking order subform i enter before running the query. cheers
View 2 Replies
View Related
Nov 1, 2005
Hey all I am having a problem with my form filter. I was wondering if you can just direct a filter from form 1 to form 3 without referencing form 2. I am thinking not cause I am having a problem getting the filter to work. Here is the code I have in my load form.
framfiltval = "CONSTR_LOCATION = " + "'" + Form_PID.ADDRESS3.Value + "'"
Form.Filter = framfiltval
Form.FilterOn = True
Thank for the help in advance
View 2 Replies
View Related
Feb 19, 2008
Hi all,
I'm creating a database for agents to use at work. Because they will all be using it at the same time, I need to create a combo box on the form that will filter the contents to only show those with their name in them. Is this possible?
Thanks!
View 6 Replies
View Related
May 2, 2005
I have a subform on my main form. Note that there is no relationship between the recordset of the main form and the recordset of the subform.
I need to be able to change the filter on the subform dynamically during run-time. I know this sounds a little odd because in theory I should be able to drive the subform off the main form using the LinkChildFields / LinkMasterFields.
Anyway, some things that I've tried:
Forms![MainForm]![SubForm].Filter = Blah
Forms![SubForm].Filter = Blah
Any help would be appreciated!
Thanks
View 2 Replies
View Related
Jul 1, 2005
Anyone has any idea about how to filter a form depending on values of a subform?
For example:
There is a main form that displays the students of a school one at a time and a subform that displays the courses that each one has selected. A button on the main form opens an InputBox so that the user can enter a course to see the students that have selected it. So I want after the user presses enter to have the main form filtered to show only those students.
View 2 Replies
View Related
Aug 15, 2006
I would like to have a user enter a start date and an end date into two
textboxes on a form. The two dates will be used to query a table. I
would then like to print a report that was created from that query.
Here is the query created as a stored procedure:
SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [@StartDate] And [@EndDate])
ORDER BY [Transactions].[Date];
What would be the best way to pass txtStartDate to @StartDate and
txtEndDate to @EndDate in the VBA code of the form? How would I open or
print the report created from that query filtered on that date range?
Any suggestions? Am I going about it wrong? Should I have created the
report from the above query, or should I do it another way? Can anyone
direct me to some code that does all of the above or something
similiar?
Thanks.
View 14 Replies
View Related
Oct 18, 2013
I have a query and a form, and what I want to be able to do is have the user type in within the form the parameters for the query.
The part of the query that will hold the parameters is based on an amount (formatted as Currency), but I want the user to be able to enter >10 , =<100 or >100000 and get the correct results.
I have already set up the query and the form with unbound cells which are then referenced in the query I've tried just one cell where the user would enter >100000 or tried two cells where one cell would be for >,< etc and one cell for the value (which is formatted as currency), but that didn't work either.
The idea is that you enter the parameter and value then click on a button that runs a macro to export the query based or the user parameters, but everytime I try it I get a box appearing saying Property not Found.
View 1 Replies
View Related
Dec 4, 2012
I'm trying to create an archiving system, where i use a simple Append Query followed by a Delete Query.
A typical criteria for the Append Query is less than Date()-30...so any records older than 30 days can be appended to an archive table. This works fine when i enter it in the Query Design criteria row.
But, I would like to make this user-defined. I have set up an unbound form as shown in the first attachment...and made a global variable entitled 'ArchiveDays'. I am hoping to use the variable to act as the criteria for the append criteria. (Please note that in the screendump...they can select an option button if they just want to stick to 1 month old. I also show you my assignment operations there).
My question is... how do i get the variable 'ArchiveDays' value to be the criteria for my append query....
View 14 Replies
View Related
Sep 11, 2014
The input form of the database have one common table containing data for all parameters for row source. As all data was in one table had one ID. My problem is I am trying to run reports from a form after selecting dates and parameters.
For selecting parameters I created dynamic combo box. For dynamic combo box I needed ID and description of the parameters. So I created separate tables for each parameter with ID and Description column. Which ran me into the problem that when I run report based on main input form which has different row source table and my report form has different row source tables and reports doesn't run when I base my queries on the report form.
View 2 Replies
View Related
Jan 25, 2005
I've search around the forum and haven't come up with the answer yet.
So here goes...
I have a main form (GRCSearch) that is tied to a table (StoredSearches). When users enter data in this form it saves it to the table. It is a single record form. The data entered by the user is used to search a big second unbound table via some queries.
The search data entered is then recorded to the table (StoredSearches) as a way for the user to track searches and to recall them if they want to run the same search again.
I've created a pop-up form that displays a continuous record layout of the StoredSearches table and a command button at the left of each record.
I want the user to be able to click the command button to the left of each record in the pop-up and that in turn set the focus of the main form to that record. It recalls the search data, repopulating the search fields of the main form. And in the onclick event I'll also have it close the pop-up window.
I however cannot find any references to setting the focus from one form to another form this way. I've found plenty that changes the focus of a form from an embedded subform.
Any ideas?
Thanks!!
Scott Doyle
View 1 Replies
View Related
