Hi,
I have a form which I can't seem to filter by form. When I click on "Filter by Form", the only combo option I get is "Is Null" or "Is Not Null", it does not give the full list in the table to choose. Can anyone tell me what's wrong with my form? The form also has a sub-form....does it matter?
I also tried tesing filtering the table with query but it also doesn't work.
My query,
SELECT DOCUMENT.TITLE
FROM DOCUMENT
WHERE (((DOCUMENT.TITLE) ALike "*work order*"));
Also, with Filter by form can I use wildcards such as entering in the search field of the form as "= "*work order*"
I am attempting to use a form field as the source for a query filter criteria. Everything works fine if I simply use an "if equal" filter condition. As soon as I try a "like" condition, nothing works.
I created a test table with just one column (fld1). The table contains three records with the following values: BRDODS, BRD, TLAODS.
The following hard coded query returns two records, as it should.
SELECT Table1.fld1 FROM Table1 WHERE (((Table1.fld1) Like 'BRD*'));
I also created a test form (Form1) with just one text field (Text0). My intent is to soft code a criteria value via the form field instead of hard coding the query, as above. When I populate the form field with BRDODS, the following soft coded query returns one record, as it should.
SELECT Table1.fld1 FROM Table1 WHERE (((Table1.fld1)=[Forms]![Form1]![Text0]));
When I enter LIKE "BRD*" in the form field, no records are returned. I should get two records, just like the hard coded query above.
I've tried all variations of the LIKE statement in the form field, but nothing works.
I've got a single form ("Lead Data") that has Cascading Combo boxes that work perfectly, entering data into "tblLeadData":cboMatterTypeIDcboMatterIDcboAttyIDcboPlglID Attorney & Paralegal are the people assigned to the Matter. My problem is in finding a way to allow a specific Attorney or Paralegal to filter for only his or her records. I made a query of tblLeadData that works perfectly as a query, but when I use it as a filter in an "on click" macro event, it doesn't work. I suspect it's because of the cascading combos, because I've successfully used this kind of query based macro filter in the past.
Okay, more on how it is set up. The same people are always assigned to a specific matter, so when you pull down the Atty & Plgl combo boxes, there's only one person. So it isn't a true Parent/Child relationship, but it's working. And there were two advantages of this set up over an autopopulate set up (which I considered): 1) When I change something in the reference tables (refAtty and refPlgl), it also changes in tblLeadData & 2) in case there's an exception to the usual assignment pattern, we can just leave Atty & Plgl blank and put the correct assignment in a text box called "AssignmentNotes."
So my query of tblLeadData that works, qryLeadDataAssign, uses the following fields:
The Join Properties in the query between tblLeadData and refAtty is set to "2: Include ALL records from 'tblLeadData' and only those records from refAtty" where the joined fields are equal." And the same for Plgl.
When I run the query, it asks me a single time, "Who?", I put in the name and it pulls up all instances of the name from any of the 3 fields. It acts as a "contains" filter, not an "equals" one.
As for my cascading combos, here are the settingsMatterTypeIDRow Source:
SELECT refMatterType.MatterTypeID, refMatterType.MatterType, refMatterType.[MatterType] FROM refMatterType ORDER BY refMatterType.[MatterType]; On Change Event:Me.cboMatter.Requery MatterIDRow Source: SELECT tblMatter.MatterID, tblMatter.Matter FROM tblMatter WHERE (((tblMatter.MatterTypeID)=[Forms].[LeadData].[cboMattertype])) GROUP BY tblMatter.MatterID, tblMatter.Matter, tblMatter.Matter ORDER BY tblMatter.Matter; On Change Event:Me.cboAtty.Requery
[Code] ....
I put a button on the form and put an embedded macro as an "On Click" event. The macro is an "ApplyFilter" and the filter name is qryLeadDataAssign. When I click on the button, I am asked to enter
data 3 times:Enter Parameter Value: Atty Enter Parameter Value: Plgl Enter Parameter Value: Who?
Clearly, the expression in the query doesn't function in the button. And the result, no matter what I put in, is that all of the records are still there, although the filtered button is activated.
I tried putting the expression from the query into the macro builder window, but I for sure don't know what I'm doing there and haven't been able to make it work.
I have a database and a form that is based off of a simple table. The table has NO look ups and all of the formatting is text. The form is continuous and simply displays these fields. (Kind of like a company roster with name and department). I have two combo boxes at the top of this form in the header with the intent of filtering the form records by department and employee type (lets say A or B).
I have tried every way I know how to get these records to filter and they will not filter correctly. Utilizing methods I have used in multiple other databases, I set the two comboboxes to cascade based on a query in the recordsource. The vba I'm using in the after event of each combo is ....
Me.Filter = "[Brand] = '" & Me.cbobrand & "'" Me.FilterOn - True 'Brand is the "department" and the other identical code is for Personel_Type
This filters the records but independently. So, cbobox1 filters the records to show all Brand 1. When cbobox2 selection is made it shows all the Personel_Type of the selection however the first filter is already disregarded. (i.e. When cbobox2 selection is made, it contains both brands instead of the one I've just selected and filtered in cbobox1. )
Outside of making two queries to account for each possible filter, I have tried setting a filter on the filterON, I have tried a SQL based VBA code, I have tried making two forms and attempting to set the filter on open but either way, nothing keeps the first filter selected (or the FilterON, or both SQL filters, etc). The ONLY way I have found to get it to filter both is with the filter function in Access, which is not useful as my users will not have access to the menu bars.
I have this EXACT same setup in another database and it works fine with 3 cbo boxes with an after even to set a filter for the recordsource for all three.
I want to add a button (FindNew) to my form (frmDenial) that, when clicked, will filter for records where the field (MBRLast) has "xx" in it, or is Null. I have spent the last 5.5 hours searching through the forums and playing around with sample codes and still cannot make it work. Please help me with this, I'm sure it's very simple. Thank you so much, everyone in the forum is so helpful!
Now here's the odd part. When I initially put this filter in, it works at first. However, once I save the datasheet form and reopen it, the filter no long works, even though the filter property still remains.
I'd be grateful if someone could offer some help with this to a frustrated Access novice : )
I have a query that filters records according to the value of an unbound text box in my form (basically search on last name)
I want to introduce wildcards into the filtering process but cannot get it to work.
Here's what I've done:
In the criteria field I had [Forms]![MAIN]![Text440] refering to the unbound text box on the form already mentioned.
This worked fine, but the following: Like "*[Forms]![MAIN]![Text440]*" does not.
Nor does various combinations of brackets such as: Like "*([Forms]![MAIN]![Text440])*"
In fact it now doesn't return anything, even if given a positive match. I have trawled help and the forums but to no avail. I have also tried the % character instead of *
The thing I've been trying to do is make it so that my form filters my records, and I'm trying to make a between function for it. My form is shown below in the attachment.
What I need to do is make it so that my form filters my records Between the two year boxes AND between the two Length boxes. But I need it so that if nothing is in the boxes, it shows all records, and if something is in only the Year boxes, it only filters the years and not the lengths.
Code: Field: Length Criteria: Between [Forms]![SearchForm]![Length1] And [Forms]![SearchForm]![Length2]
Then in a separate column I had
Code: Field: [Forms]![SearchForm]![Length1] Or: Is Null
This works fine if it's only for Length, but if I try to do the same for the MovieYear boxes, it screws up and just shows me no records...?
Is it possible to do a criteria like the "Between" to pull data like a work order number?
Ex WO#: WO5551212
I would like to be prompted to enter a Work Order Number when I run a query, then have the query display all of the data for that particular work order number.
We are creating a simple database to maintain driver license information for faculty, staff, and students who use cars from the university’s motor pool.
To do this, I have created two tables: tblDRIVER and tblLICENSE.
The fields in tblDRIVER are: pkfDriverIndex strLastName strFirstName strInitial strAddress1 strAddress2 strCity strState strZIP datBirthDate
The fields in tblLICENSE are: pkfUpdateIndex fkfDriverIndex datDateUpdated strState strLicNumber datExpirationDate ynViolations ynActive
Information about the driver is stored in tblDRIVER and the driver’s license information is stored in tblLICENSE. Periodically, we run a report that identifies drivers whose licenses are due to expire within a certain number of days. All this works fine.
My problem is that I am trying to create a lookup form that will load from a data entry form that will permit the Motor Pool Clerk to look up a driver by name and review the licensing information (which appears as a subform).
All this sort of works- I am using a combo box (based on a query) to look up a driver’s last name (which it does) and to populate all the driver’s information on the look-up form (including license information in a subform). Currently, the combo box locates the driver (including the unique index, last, and first names), and populates the form with first and last name but the rest of the information is not displayed on the look up form. Worse still, sometimes one driver’s last name matches up with the first name of the next driver listed in the table! This seems to happen only if a look up is attempted more than once. What gives.
How can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.
When I click the filter button I get a blank message box titled 'Microsoft Access" and an OK button, when closed the filter works perfectly.I have checked this procedure in other forms and it works without showing the blank message box.The only difference with this form is that its control source is a union query.
* This code works to search frmMember for criteria found in tblMember:
Private Sub cmdSearch_Click() If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then MsgBox "You must select a field to search." ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then MsgBox "You must enter a search string."
[Code] ....
* But this next code which I have copied and Pasted to frmEventInput, then renamed the frm and tbl, retrieves the same criteria as the above (tblMember) instead of the tblEventInfo...
Private Sub cmdSearch_Click() If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then MsgBox "You must select a field to search." ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then MsgBox "You must enter a search string."
i have a DB which tracks order numbers, the main table is a SQL table linked in, and there are lots of local tables as well, one of which is called userDetails and contains a field useremail
i want to open a form and only show records from trackermaindata that contain my useremail -
SELECT dbo_TrackerMainData.userEmail FROM dbo_TrackerMainData, userDetails WHERE dbo_TrackerMainData.userEmail=userDetails.useremai l;
when i double click this query in access it works and it shows me only my records, but when i try and run this query from a button (using a macro) in my form, it asks me to Enter Parameter Value userDetails.useremail
i've tried lots and lots of things so far and nothing works. i have lots of other queries which do work, but they don't reference another table - they either contain a static value or take their data from an entry box.
can anyone give any reason why this should not work? it seems straight forward enough!!
I've got a form with several date fields (in Medium date format), with each field bound to fields in a table, where also the dates are in medium format.
I also have the form set up where I can enter dates manually, or I can double-click and pick dates from a calendar form, or I can click on an icon and pick dates from the calendar form. Each date goes through the same bunch of processes (i.e., queries and code).
My problem: Some dates work, and some dates don't. On some date fields, I can key in a date, exit the form, re-open the form, and my dates show up (since they were processed into my table.) However, some other date fields do not get processed! I enter dates into these fields, exit the form, re-open the form, and there are no dates!!
I have checked all fields in my table..... they are all date fields, of medium date format. Same for my form.
Why would some dates work just fine while others don't work? If anyone has some suggestions I could try, I would be grateful. What might I look at in my application?
I have a form that worked when I left work Tuesday. It is supposed to look at all the information in my "courses" table in descending order by year, ascending order by date, in edit mode. There is also a look-up list that allows me to select the date of the course and jump to that record.
I came in today and it doesn't work! The form LOOKS like it's in add mode--completely empty. the data is still in the table. I checked all the properties and they all look right. The query still looks like it did when I left. It just quit! When I use the look-up list the dates appear in the list, but nothing happens when I select it.
I know I'm not giving you much to go on, but any ideas?
I,m trying to get a hyperlink working on a form.I've used some code I copied from another access user I found online to browse for the required file. I can't open it though in form view. The link to the file is below.Also is there a way to display a hyperlink to the file as a symbol or e.g. ... (some full stops) to reduce the form box size and hide the complete file path.URL...
With the imense help of some of the people in this forum, I have scraped together a nice little database. There is one function, however, which is not working.
I have a query that should run from a form I have created. The form has multiple criteria and all seem to be working except for the date fields... "First Order" and "Last Order". The way I have it set up is that I have a table of dates. The form has a list box of the dates (first of the month, for 4 years). The source data in the form reads: SELECT [TDateSelect].[StartDates] FROM TDateSelect ORDER BY [TDateSelect].[StartDates]; (for each of the 2 "minimum" dates) and SELECT [TDateSelect].[EndDates] FROM TDateSelect ORDER BY [TDateSelect].[EndDates]; (for each of the 2 "max" dates) The user is allowed to select a date range for the first order and the last order. (4 dates altogether).
The form looks fine.
In the query, I have set the criteria for the last date as: Between [Forms]![FSetOtherCriteria].[MinLast] And [Forms]![FSetOtherCriteria].[MaxLast]
When I run the query (even with a wide date range) I get no results. When I remove the dates from the query, or enter the limits manually, the query works like a dream.
I have a DB: This DB was sent to me via eMail (dropbox). It consist in a BE and 3 FEs. In each of the FEs I have a started form with some code activated by the Open event.
The issue: Access do nothing when I open the FEs. Note please that the DB is designed by me and it work very well on the user"s computers.
I use A2007 on Win7
The DB is in a trusted folder. Compact and Repair is not useful.
I have developed a project in Access 2003 and it works absolutely fine. There is a switchboard form too and that works fine as well. But now in our company we are moving all the projects developed in 2003 to 2010. And so I have noticed the switchboard form doesn't work in Access 2010 the way it works in 2003. The Switchboard form doesn't display all the options in 2010.
TRANSFORM Sum([rpt LEAD TYPE SUCCESS RATE2].rec_cnt) AS SumOfrec_cnt SELECT [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date FROM [rpt LEAD TYPE SUCCESS RATE2] GROUP BY [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date PIVOT [rpt LEAD TYPE SUCCESS RATE2].Results;
I get valid results.
When I run the form it prompts me 3 times for each start date and end date. Then I get the following error :The Microsoft Access database engine does not recognize '' as a valid field name or expression.
hello all, I am very new to access (my background is in VBA Excel, but I am not using VBA for access yet). I was wondering if some of the more advanced people can give me pointers on how to accomplish (or work my way) to make the kind of form I am trying to do. What I am trying to do is the following: I have a vending company with couple of products such as espresso, latte, black coffee, etc.. (all of the products are sold at the same price for each customer, so I might sell the products for company A for .75, but for company B for .55 etc) and I am selling these products to many clients company A, B, C, D, etc. As A start I created a table with my products, then I created a table for my clients (this one includes name, address, contact person, and machine number ). I would like to make a readout input form where it would get the customer name, machine number, each of the product's total for the readout period (this is usually every week), and the date of the readout. Now I would like the form to add a record in a table for the client machine number that was selected and maybe also in another table for the overall machine ( I am not sure how queries work, and weather I would need that or I can use queries for it). My ultimate goal is to be able to do the following with the data: - check (and perhaps graph) the overall statistics for a each of the products for all clients - do the same for a certain client - do the same for a certain machine at a client - check the overall sales of all products to all clients for each month of the year - do the same for a particular client or for an individual machine - do the same but for each year
I have done all of this in a VBA Excel based program but I was hoping to change the program to be access based instead of excel based, since this would make the data more centralized and easier to maintain, backup, check, and update.
So if anyone can post any hints or suggestion about how I should build my tables, the usability of quarries, and especially the construction of the forms I would very much thank you for that, since these are my first days getting to know access and I am absolutely confused with it, all the language you are supposed to put into text boxes, combo boxes etc....
Also if anyone knows of any good links to some online tutorials would be great, since most for the tutorials I found on a search was for constructing a for or a query using the wizard, but with no real discussion of how to bound the data to certain fields etc.
I have a database with a Supply Receipt Form. The first field in the form is a combobox for the user to select a material/supply. I'd like for the form to open to a new record every time. When I use:
DoCmd.GoToRecord , , acNewRec
in the Form On Open property, I lose the ability to select the material/supply. Same thing happens when I set the Data Entry property to Yes.
I have a data base in which there is a table where each record describe properties of one fishing trip. The first column in the table is a number (unique but not autonumber format) and I already made 10,000 records with only this index number . Now me and others in my lab are entering data to this table for each of the records.
The data entry is done using form which show one record at a time (lets call it "sail_info_F").
I made another form which has only two elements:
1. text box in which I enter a number 2. button which opens the data entry form ("sail_info_F") showing the record with the same index number as the number I enter on the text box.
(the code for this button is "DoCmd.OpenForm "sail_info_F", , , "[RECORD_NUMBER]=" & Me.TEXT_BOX_NAME"
So far so good and it does open the form in the wanted record. BUT when the form opens I can't use the buttons I have placed in this forms which move between records. Those were made with the wizard, and are working OK when I open the form directly without using the second form (The one which opens "sail_info_F" on a specific record).
I am trying to get a wildcard search to work with a form.
I have a query, in which the criteria is:
Like "*" & [Enter a word] & "*"
That works fine. I enter a word, and I get the few records in which the word appears.But if I try to replace [Enter a word] with a word entered on a control on a form, it doesn't work - I get all the records. This is my code:
