Modules & VBA :: Applying Filter In Form With 2 Variables
Jun 6, 2013
I am fairly new to VBA and am trying to apply a filter within a form so that agent records can be filtered based on an agent's name (so that they can only see information that refers to them).
I also want a record to be removed when it has been completed (when this habbens a date completed field will populated) however I do want to see those completed records that refer to the agent that have been completed today.
Here is my code. The 2 filters work on their own howver when I put them together I get a mismatch error.
If Me.txtRole = "Agent" Then
DateCompletedFilter = "(DATECOMPLETED Is Null) Or (DATECOMPLETED = Date())"
AgentFilter = "CASEOWNER ='" & Me.txtName & "'"
DoCmd.ApplyFilter , AgentFilter And DateCompletedFilter
Exit Sub
End If
I am trying to set a filter on a form with VBA using variables and having no success at all. The code I tried last, which seemed to be "close, but no cigar", was:
sLastSource and sBiller are global variables. When I debug this code the sFilterValue is exactly what I would plug in manually and the Me.Filter shows up as "[Field Name] = Filter Value". But it crashes on Me.Filter = sFilterValue. I have tried all sorts of combinations but nothing I've tried works. What is the proper syntax for using variables as filters?
When I'm applying a sort and filter in a form, Access is updating the Filter and Order By properties of the form, so that it is possible to re-use in conjunction with Filter on Load and Order By on Load properties. However, a consequence of this is that when the form is closed, it prompts the user whether they want to save the design of the form. I want to circumvent this as I don't want to re-use the sort and filter and I don't want to be prompted to save the design of the form.
Although I can circumvent this by closing the form using a method that doesn't prompt for saving, the additional complication here is that the form in question is in the Navigation subform of a Navigation Control. Hence when I click on a another Navigation button, it (not me) closes my current form and hence prompts me whether I want to save the design of the form (if I have been sorting and/or filtering). I can't see how to circumvent this and the prompting is resulting in unacceptable usability.
I have a pretty simple form that includes subform. Subform's table is linked to main form's table with parent/child relation. Connecting fields are main table's ID field and corresponding field in child table. Subform is in datasheet view. This is pretty basic stuff so there should not be any problems, but every time I apply a quick filter in main form it causes data in subform become invisible. There is single row in subform, but all it's fields are empty.
I am attempting to apply a filter to a query i have setup. I only want the user to be able to search for specific words within two fields (technical and behvaioural) i have setup. I know i have to use a WHERE expression but am confused as how to exactly setup the expression... any idea's???
I am using two combo boxes to filter a list box with the code below. The combo boxes work, but when the form opens, the list box is empty until it is filtered using the combo boxes.
I want the list box to return all records when no filter is applied.
Here is the code:
SELECT Q_Gender_Statistics.ParticipantID, Q_Gender_Statistics.Gender, Q_Gender_Statistics.Date, Q_Gender_Statistics.Year, Q_Gender_Statistics.[First Name], Q_Gender_Statistics.[Last Name], Q_Gender_Statistics.[Other Names], Q_Gender_Statistics.[Passport No], Q_Gender_Statistics.[Duty Station], Q_Gender_Statistics.[Contact Number] FROM Q_Gender_Statistics WHERE (((Q_Gender_Statistics.Year) = Forms!F_Gender_Statistics!cboYear) AND ((Q_Gender_Statistics.Gender) = Forms!F_Gender_Statistics!cboGender)) ORDER BY Q_Gender_Statistics.Date DESC;
While applying filter in forms, i have 6 fields in the form and i want to display the data even one of the filed is with 0.
I used the below code, but this one display the data where all the fields are 0. I tried or in place of and but still its not working.
Private Sub Form_Open(Cancel As Integer) Me.Filter = "[Placed]=0 and [receievd]=0 and [Ordered]=0 and [processed]=0 and [delivered]=0 and [closed]=0" Me.FilterOn = True End Sub
I chose the criteria 2 for the filter just as a test as I knew there are some records with that value in the master category field.
The problem is when ever i click the button to apply the filter it clears all the data as if it has not found any records with that value.
Is my syntax and method OK? Why its filtering everything out?
The only other thing to consider is that the field I am filtering on was set up using a lookup wizard linked to a table so the values stored are a foreign key (hence the value being 2 rather than something descriptive).
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.
I'm looking at importing data into a table from a spreadsheet. The spreadsheet will only contain a single column of data, while the table in Access will have a few more, some of which will would be will provided from the form the user is using to import the data, and some at a later date.
Is there a way to do this? I've found the "DoCmd.TransferSpreadsheet acImport" command, but not sure this fulfills what I need, as I can't see a way to set variable data.
Here is a brief idea of what my Table would look like, where where the data to import would be obtained from
I have been at this for almost 3 weeks now and I'm having great difficulties trying to get this right and working. First let me explain what I am doing and what I am trying to do.
Firstly, I am making a Maintenance database using MS access software, what I have are tables, forms and no queries or report existing so far. The forms and tables all work correctly.
After the form (Job request) has been completed I need to save/export into PDF so I am able to hyperlink it against its existing asset card elsewhere.
now what I have done is Created button, on event "on click" I have tried to go to macro builder
ExportWithFormatting Objet Type: Form Object Name: FrmMachineFault/GenMaint Output format: PDF Output file: Auto Start: No Template File: Encoding: Output Quality: Print
when I click this button it opens to save to and it works perfectly but I have to manually type the file name when it gets to the Save to section.
What I want it to do is define the name by the existing fields in the open form. Example Closed date, Effected area, Asset and title.
Following concerns about someone accidentally deleting the access database we have been using to crunch performance numbers, I have successfully moved the data on to an SQL server
While the database works as it is, several of the queries are running extremely slow. I therefore decided to see if a stored procedure could run the number crunching on the server instead of passing the data back and forth all the time.
I have taken the series of queries and converted them into a stored procedure that runs too fast for me to blink while giving the same results as before.
The problem I face is that I can trigger the stored procedure from the server management studio manually while supplying the variables needed thus providing the data I need to export to excel in a table for this purpose.
What I want to do is to have a form in access supply the chosen variables (like I could before) and run the stored procedure at the click of a button as part of a series of other queries.
I have looked at pass-through queries but apparently they do not take kindly to variables unless they are hardcoded. The other solution would be to trigger it from VBA but I have not been able to find a solution I could get to work.
How to run a stored procedure on an SQL server from access while also giving it the variables it needs?
Stored procedure name: spNearMissCalculation Variables: @SelectedDate (date format) (taken from a form field) @SelectedVessel (nvarchar(max) format) (taken from a form field) @SelectedVesselGroup (nvarchar(max) format) (taken from a form field)
I have an Access form that gathers students test scores on Reading & Writing exams. Depending on those scores, there are five possible classes for them to be assigned.
I have the intervals worked out, and have created a table that contains the min reading, max reading, min writing, max writing, and class they belong in.
The scores are entered into a form as scoreReading & scoreWriting (integer)
I'm trying to figure out the best way to pull and compare the values and return the proper englishPlacement text. Its set up this way because there are multiple tables to pull from, depending on the date of the exam. Each table has different ranges for each Eng1,...,Eng5.
I'm thinking perhaps to iterate through each column and evaluate T/F, then return the englishPlacement value of the correct row. I'd like to keep this stuff in the VBA so that the code to select a table based upon date doesn't break when the intervals inevitably change requiring an additional table.
I have a login screen to open a form. I would like to filter the form based on what user logs in. In my tbluser, I have a trainer name, a UserLogin, and a password. I would like the form to filter on trainer name based on userlogin.
I know the code: DoCmd.ApplyFilter , "Trainer = 'Joe Smith'" but how to I edit the code to change as the tbluser changes?
I have a simple form where a user selects a query from a drop down list. I need to have the query apply a date range that has also been selected in the form.
Would I call to the date form fields from the query as a variable somehow?? If so, could someone show me the syntax? I am extremely new to access.
Im a relative novice with access VBA, and I'm really struggling with using Dcount with date variable. All I want to do is count if a certain date appears in a table. Here is the extract from my code:
Whatever dates are in tblworkoutlogs, datecount is still = 0...I've trawled the net and tried many variations of the code but no success!if I change all the date formats to strings in the code and the tables, it works so I know i'm looking in the correct place.
I have a public sub routine which requires parameters to be passed to it when I call it from an access form. When I try to enter the code to call the sub I get a compile error. I've also tried calling it from another sub in the same module but get the same compile error - see below.
Code: Sub EmailData(Datafile As String, To_mail As String, CC_mail As String, Subject_mail As String) 'code to use variables passed in End Sub
I am trying to set up some template emails using text someone has entered in a form with a variable indicated with a key word in brackets aka. [ChangeID] or [ChangeDate]. The field on the form is formatted as Rich Text so I am getting http code. (No problems yet) In the form the template is required, I lookup the template required and I get the string. I replace the brackets with the following
I need to set some 'global' variables with default values when my Access 2007 database is loaded. Depending on the user etc these values may be modifed after Access starts but defaults need to be set.
I declared the variables as 'Public' in a Module, then put a function in the same Module (to set the default values) then tried to call the function from an "AutoExec" macro, so it's the first thing that runs when Access loads.The macro throws an error - it can't 'find' the function(?)
I have been tasked to create a multi-keyword search form, however, my form isn't working right and only the first record of the table is opened.
Code: Public Sub txtSearch_AfterUpdate() Dim strWhere As String Dim strWord As String Dim varKeywords As Variant Dim i As Integer Dim IngLen As Long
[Code] .....
These are the codes that I am using for my search form. I have a feeling that I am not calling the variable from the after update portion the right way.
I have a Change Management database with several fields, the key fields that I need to talk to work together are as follows:
[Patch] , [CVE] & [Patch Approved]
When opening a form I have I can sort it by the patch number which will then sort out all other CVE's that are not associated with that patch. What I would like to be able to do is approve one patch under the [Patch Approved] field and have it carried through the rest of the filtered CVE's that have the same [Patch] number/ field.
