Modules & VBA :: Setting Filter On A Form With Variables

Aug 26, 2014

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:

Code:
Dim sFilterValue As String
Me.FilterOn = False
sBiller = Me.txtbiller.Value
sLastSource = Me.txtbiller.ControlSource
sLastSource = "[" & sLastSource & "]"
sFilterValue = sLastSource & "=" & sBiller
Me.Filter = sFilterValue
Me.FilterOn = True

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?

View Replies


ADVERTISEMENT

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

View 13 Replies View Related

Modules & VBA :: Setting Subreport Filters From Parent Filter

Apr 24, 2015

I have a report which is opened using a DoCmd.OpenReport. There's a criteria string which filters the main report - this works fine.

There's now a requirement to place a summary subform at the beginning of the report, in the report header. I need that summary to use the same criteria string as the main report.

For the main report's OnLoad I put : Me!Expenditure_By_Type_Subreport.Report.Filter = Me.Filter

But I get the error message:

Error 2101. The setting you entered isn't valid for this property.

I tried it the other way round as well - in the OnOpen of the subreport I tried : Me.Filter = Me.Parent.Filter

And it gives the same error.

When I just a manual Filter change such as : Me.Filter = "Project_ID Is Not Null"

View 3 Replies View Related

Modules & VBA :: Setting Filter Property Via On Open Event Of A Report

Dec 15, 2014

I have a report, on a control tab, on a main form.

On the form are two buttons: one to show all items, and one to filter them based on a boolean field called showitem.

The buttons work with the code below.

What I want to do but cannot seem to figure out is to have the report default to no filter.

The bound query has no criteria.

I'm trying to set the filter property via the on open or on load event and even if I isolate the report, cannot seem to reach it.

Code:
Private Sub b_hide_items_Click()
Me.Profile_Timeline_wNotes_subreport.Report.Filter = "timeline.showItem <> 1"
Me.Profile_Timeline_wNotes_subreport.Report.FilterOn = True
Me.Profile_Timeline_wNotes_subreport.Requery
End Sub

Private Sub b_show_all_Click()
Me.Profile_Timeline_wNotes_subreport.Report.Filter = "timeline.showItem = 0"
Me.Profile_Timeline_wNotes_subreport.Report.FilterOn = False
Me.Profile_Timeline_wNotes_subreport.Requery
End Sub

View 14 Replies View Related

Modules & VBA :: Import Spreadsheet With Form Variables

Jul 18, 2013

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

Fields:
PromoID, DiscountID, PromoCode, AddedBy, DateAdded, UsedBy, DateUsed

Spreadsheet
PromoCode

Form:
DiscountID, AddedBy (environ("username") query), Date ( DATE() )

Note, the PromoID is a autonumber.

View 2 Replies View Related

Modules & VBA :: Export From MS Access To PDF With Set Variables From Opened Form

Jul 9, 2015

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.

What I am hoping for is something like this.

10/07/15_Foundry_Furnace_LPG_Leak
(Closed date)_(Effected Area)_(Asset)_(Tittle)

Is this possible?

View 14 Replies View Related

Modules & VBA :: Run Stored Procedure Providing Variables From Form

Jan 15, 2015

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)

View 4 Replies View Related

Modules & VBA :: Test If Form Field Fits Within Range Variables

Mar 4, 2014

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.

As follows:

tblEnglishPlacement

readMin readMax writeMin writeMax englishPlacement
0 99 0 5 Eng1
55 69 6 6 Eng2
70 99 6 6 Eng3
0 69 7 99 Eng4
70 99 7 99 Eng5

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.

View 2 Replies View Related

Modules & VBA :: Error 2450 When Setting Form?

May 19, 2015

I am getting an error msg when i get to the line "Set frm = Forms(vFormname).Form". Error 2450, cannot find the referenced form...

'Code:

Dim frm as form
' Check If saved dynamic field exists
SqlStr = "Select ReportListID, DynFieldName, DynCalc, FormName, UserName From DynFields Where UserName = '" & GBL_Username & "' AND ReportListID = " & projectid
Set rs = db.OpenRecordset(SqlStr, dbOpenDynaset, dbSeeChanges)
If rs.EOF = True And rs.BOF = True Then
GoTo Exit_DynamicFieldCreator

[Code] .....

View 1 Replies View Related

Modules & VBA :: Setting Control Source For Entire Form?

Oct 21, 2013

Is there a way to set the control source for an entire form through VBA? I have a database that was set up just as an archive file, not intending to have any forms or reports in it. It was simply to house tables containing data from previous years. My main database just copies the table over to the archive file and adds the year to the table name. I have just been told that the users would like a form and a series of reports set up in this database.

I would like to set the main form up so that before it will allow them to do anything, they have to enter the year of the data they are looking for. After the year has been entered, I would like to adjust the control source of the form to reflect the appropriate table. In other words, when a user opens the archived database, he or she will see a field for the year an nothing else. Once they enter the year they are looking for, the form will populate with data from the corresponding table (the table names look like this - tblPM_Completed_2013). Is this possible?

View 6 Replies View Related

Modules & VBA :: Setting Focus To Non Default Instance Of A Form

Mar 21, 2014

How to set focus to a non Default instance of a form.

Environment
A2007 ADP Project
Document Window Option - Tabbed Documents
MS SQL 2012 Express DB
Windows 7 64 Bit

I using Allen Browne's method to open more that one version of form, storing each form object in a collection declared in a module. No problem there.

Now I'm trying to add a command button on an form to set focus to one of these non-default instances already open.

The form I am trying to set focus to has a the following related properties

Default View: Split Form
Pop-up: No
Modal: No

The form that has the command button on it is of the same type.

Here is the code I've tried:

'Code on Calling Form
Private Sub cmdProjectList_Click()
Dim FunctionResult As Boolean

If AppForms.GoToForm("ProjectList") = False Then
AppForms.Load_ProjectList

[Code] .....

The code compiles and executes with seemingly no problems. It finds the form loaded, then cycles though and finds the form in Forms but the SetFocus call seems to do nothing. When I run the code against a defualt instance ( one not opened using Allen Browne's method) it works fine and sets focus to it as expected.

View 11 Replies View Related

General :: Setting Filter With VBA?

Aug 19, 2014

I'm trying to set a filter with a command button. I've tried to do it like the examples I've read on numerous sites, but I can't seem to get the filter set. The code that seems most like the examples to me is:

Me.Filter = "[chkPaid] = 0"
Me.FilterOn = True

When I run this I get a box telling me to enter a parameter value for chkPaid. chkPaid is definitely the correct name for the checkbox control on the form. I've tried many variations with and without the square brackets, but as long as I have [chkPaid] inside of quotes I get the Enter Parameter box. If I leave the quotes off I get no parameter value request, but it doesn't set the filter either.

View 3 Replies View Related

Modules & VBA :: Setting Up Certificate Request Database - Attach PDF To Form

Aug 11, 2015

I have been tasked with setting up a certificate request database. I have an Access 2032 FE & SQL 2012 BE. I need to store PDF documents from all our branches.

I have been working on this for the past 3 working days and I have seen a great deal about saving the file path to the database vs saving the actual document in the DB. The documents would be located on each users computer.

I have a Form with all the information that is required by the certification department. On the form is a button (Called InsertID(ID in this case is the persons Identity Docuement)) and the button is linked to a table called dbo_CertificationSupportingDocuments. The table is referenced to the Main Certificate Request table.

The dbo_CertificationSupportingDocuments table is structured as follows:

Code:
[CertSupportingDocID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[CertificateRequestNumber] [numeric](18, 0) NOT NULL,
[RequestBlob] [varbinary](max) NULL,
[RequestFileName] [nvarchar](50) NULL,
[RequestFileExtension] [nchar](10) NULL,
[RequestFileMimeType] [nvarchar](50) NULL

When I click the button I get a Run Time error 3075 "Syntax error (Missing operator) in query operator.

The code I am using is:

Code:
Dim fd As FileDialog
Dim strFileWPath As String
Dim FileNoExt As String
Dim FileExt As String
Dim StrSQL As String
Dim db As Database

[Code] ....

I have been looking at this code for so long that I cannot see what is wrong.

View 14 Replies View Related

Setting A Filter On Dynamically Created Table?

Apr 23, 2015

I am currently working on a Database to automatize some process.

The User can import a CSV (Text) File via a DialogBox, which gets imported into a Table. After this, the content has to be filtered, setting conditions on 4 different rows. The new Table gets exported into a new CSV (It has to be CSV, since it later gets imported into SAP)

How can I set a Filter on a dynamically created Table (using VBA)?

Best case would be to save the filter into my Import spec, but Access doesn't seem to have this option (?)

View 3 Replies View Related

Setting Numbers Equal To Words To Filter Forms?

Sep 19, 2014

I'm working on a form that when opened has a pop up filter that asks you to enter "1, 2, 3, 4 or 5". These numbers have to be equal to an actual word within the form. For example, on the form we have a box called "business unit". One of the options in "business unit" is "Human Resources".

I want to set 1 = to Human Resources so that way in the filter box all a person has to type is 1, and all of HR's records will pop up. The reason for this is we are trying to eliminate typing as much as possible.

View 1 Replies View Related

Modules & VBA :: Dcount With Date Variables

Jul 11, 2015

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:

Dim WorkoutDate as Date
Dim datecount as integer

datecount = DCount("[WorkOut Date]", "tblworkoutlogs", "[workout date]= " & WorkoutDate)

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.

View 5 Replies View Related

Modules & VBA :: Passing Variables To A Sub Routine

Jan 13, 2014

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

[Code]....

View 4 Replies View Related

Modules & VBA :: Converting String To Variables?

Jan 30, 2015

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

Code:
strleftB = Chr(34) & " & me."
strRightB = " & " & Chr(34)
strTemplate = Replace(strTemplate, "[", strleftB)

I then get a string but in need to convert part of the string into variables, before I capture the correct output for my email

Code:
"<div>RFC Submission: <strong>" & me.ChangeID & ", </strong> " & me.Details & "</div>"

My question is: what is the best way to split the string into strings and variables

View 7 Replies View Related

Modules & VBA :: Possible To Initialize A Function Like Do For Variables?

Jul 15, 2015

Sometimes we need a one-line function to just get the database path or things like that we cannot do on a query or on a Macro Object.

Like:

Function GetDatabasePath() = currentproject.path

possible?

View 5 Replies View Related

Modules & VBA :: Assigning Index To Variables

Sep 11, 2014

How can I assign some type of index to the below variables (data representation) ? I'm using VBA to build a .RFT file for MS Word.

"Line # 1 data"
"Line #2 data"
"Line #3 data"

. . . .

"Line #7 data"
"Line #8 data"
"Line #9"

. . . .

"Line # 22 data"
"Line #23 data"
"Line #24 data"

View 14 Replies View Related

Modules & VBA :: How To Set Up Variables When Access Starts

Oct 9, 2013

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(?)

View 2 Replies View Related

Modules & VBA :: How To Use Variables In Update Statement

Apr 10, 2014

What is the syntax for using variables in a VBA update statement? I have the following that I want to use to update a record field.

Code:
Dim thisTbl as String
If Answer = vbYes Then
If MedicationInvNo2 <> "" Then
thisTbl = "tblMyMedData"
Else
thisTbl = "tblMedData"

[code]....

I'm getting run-time error '3144' when the database tries to run the SQL. So I'm assuming my syntax is wrong (specifically in the WHERE clause)?

View 3 Replies View Related

Modules & VBA :: Calling Variables From Other Subs

Oct 29, 2014

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.

View 14 Replies View Related

Modules & VBA :: Auto-fill On Variables

Apr 9, 2015

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.

View 1 Replies View Related

Modules & VBA :: Filter The Form Records With It?

Sep 12, 2014

vba code for filtering the records of the form made out of a table. The form contains combos that contain the lists of unique records in several colomns of the source tale. So i need to filter the records of the form using the combination of the values of that combos. But can't even apply a single filter using this code:

Me.Filter = "Business = " & list59.Value
Me.FilterOn = True

"Business" is the name of the column in the source table, and the "list59" is the name of the combo which contains the unique records of the "Business"

the code takes place as a button "apply" is pressed

View 4 Replies View Related

Modules & VBA :: List Box Form Filter?

Feb 12, 2014

I want to be able to show all records from tblRecords where, The [tblUser][PositionID] = The Value selected on the list box.The problem being I don't know how to do this with them all being on seperate tables and linked via relationships. I.e. [tblRecords] only holds the UserID, [tblUser] has the PositionID.

I also need this value to keep building towards my filter string, i.e. I can select this position ID and records from within the past 4 days.I've attached pics of the form and relationships.

Here is my current code:

Code:
Option Compare Database
Option Explicit
Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
Dim ctl As Control

[code]....

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved