Customizing Access Parameter Queries

Apr 7, 2006

Hi
I wonder if anyone could re-orientate me and put me in the correct direction regarding the above subject. Without wittling on too much what I,ve been basically trying to do is to get a query that I've made, derive its criteria from a form that has 3 combo boxes on it allowing a user to select varying options. I have been using a forum thread which described the process in more detail and whilst I,ve followed it to the letter the end result that I get
an annoying message "The expression On Click event that you entered as the event property setting produced the following error: A problem occured while MS Access was communicating with the OLE server or Active X control. It goes onto say that the expression may not result in the name of a macro, the name of a user defined function, or [Event Procedure].There may have been an error evaluating the function, event or macro". The thread took me to www.fontstuff.com/access/acctut08.htm and it is on this site that I have been trying in vain to come up with a matching result. Am I going mad or is there someting on their tutorial that is mis-leading me! I am desperately trying to adminster some 'self help' and to use the forum threads but I am still at a loss as to what to do next. Could some kind person please help...

Carol:confused:

View Replies


ADVERTISEMENT

Queries :: Customizing Access Parameter Query - No Data

Apr 22, 2015

I don't want my user to type in the parameter value for a query in case of miss spelling. Therefore, I'm using a dialog box form with a combo box field. The row source of the combo field is a table with one field for the list. I've added VB code (Event Procedure) to a buttons on the dialog box which says to run a query after click. I've created the query for the info I need displayed and am using the forms combo field as the criteria.

Private Sub cmdOK_Click()
DoCmd.OpenQuery "qryRequestsbyBranch", acViewNormal, acEdit
DoCmd.Close acForm, "frmDepartmentList"
End Sub

The query runs except I'm not getting any data.

View 14 Replies View Related

Customizing An Access Application

Dec 6, 2007

Hello Forum members

Am an ardent access user and have a small problem that am Requesting for help.

I have designed an application software using access and I would like to peddle it to a few prospective clients. I want to package it as a trial software to run for thirty days and after that if the clients is not satisfied,I want it to disable but store the data. How is this possible.

All help is appreciated

View 1 Replies View Related

General :: Removing Customizing Access Toolbar Button

Jan 23, 2014

I am currently using the code

Code:
<customUI
xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="True">

[Code]...

To remove the file button from access. however the more commands button in the quick access toolbar renders this method pointless unless i can remove the more commands button. How to remove the "customise quick access" button or failing that how to remove the more commands button?

View 1 Replies View Related

Queries :: Access Multiple Queries Run With Date Parameter

Jun 19, 2015

I have 5 queries that I am running. The first query has the date range parameters set in the field area that I need to run and each additional create table query is based off the results of the previous query.

1. Which is better to use to run all of the queries in one simple step? A macro or a form? I am exporting the final table to excel so that I can make some additional adjustments off of it.

2. How would I setup the date range parameters for the first query if I were to use a macro without going into the query itself and updating the date field? I tried setting up a macro to run the queries by using the OpenQuery action for each of the 5 queries, but I cannot figure out how to do the date range.

View 11 Replies View Related

Is There A Way To Include Wildcards In MS Access Parameter Queries?

May 4, 2008

I have a table with a string field for a street. It might have data such as:

Bleeker Street
or Bleeker St.

I want to create a query that will enable the user to search for a string by entering a parameter. Is there a way to construct a query using wildcards for a string so that if the user types in "Bleeker" both the above values will be returned?

Thanks.

View 2 Replies View Related

Customizing A Toolbar

Jun 28, 2006

Hi,
I was trying to customize a toolbar by adding the "size to shortest", "size to narrowest", etc., stuff from the format menu. The first item, "size to tallest", I just clicked and dragged from the format menu onto my toolbar, and that worked fine....except that it disappeared from the format menu. Then, I tried to drag "size to tallest" back to the format menu; Now it has disappeared totally from both my toolbar AND my format menu. How do I get it back???:confused:

BTW, I found out if I ctl-drag items from the format menu to my toolbar, they stay on the format menu but also appear on my toolbar. Wish I had known that before ....

View 1 Replies View Related

Customizing Ribbion Issue

Jan 10, 2008

I have the following code that does seems to work right:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="true">
<tabs>
<tab id="tabMain" label="CRSTHA Manager">
</tab>
</tabs>
</ribbon>
</customUI>

But when I try to add a button with the following code it doesn't work:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="true">
<tabs>
<tab id="tabMain" label="CRSTHA Manager">
<group id="grpHome" label="Home"/> <button id="cmdHome" label="Home" size="large" onAction="onOpenFormEdit" tag="frmMain"/>
</tab>
</tabs>
</ribbon>
</customUI>

Any ideas?

View 3 Replies View Related

Customizing Currency Fields

Feb 5, 2007

Hi,

I am creating a database of private equity funds. I want to have a currency field for an input entitled fund size. However, I need to be able to display whether the size of the fund is in dollars or euros. How can customize a number/currency field to allow me to input the correct currency symbol?

Thanks

View 3 Replies View Related

Customizing Date Format

Sep 19, 2005

Hi
I was hoping someone could give me some advice on formatting dates in a text box upon a form.
The date forms part of a serial number that consists of a user selected prefix, from a seperate list box and a 4 digit date in the format of year and week, ie "yyww".
I was intending to use the current system date as the source.

Thanks
Henson

View 2 Replies View Related

General :: Customizing A Database Template

Feb 28, 2014

I am having trouble customizing a database template, i have changed the fields that i needed and got rid of one that i did not but for some reason it will not work.

View 4 Replies View Related

Customizing Multiple Item Forms

Apr 15, 2014

I am trying to create a form for a query so that I can filter on a chosen record and have the data presented in a more reader friendly format: the idea being that I have the common data show once in the form header and the rest show in the detail section (kind of like a form with subform type)

First I tried using the form wizard to create a Tabular layout form which I could have easily customised, but I am unable to use this because I get an error message telling me the wizard cannot create my form due to me choosing too many fields.

In my next attempt, I elected to use the Multiple Item form option, which seems to work OK but presents the following problems:

1) It has not included all of the data fields in my query... though it looks like I can add these manually
2) I am unable to reorganise the text boxes and labels the way I would like, as they all seem to be locked together Is there a setting somewhere that I can change to stop this from happening?

View 3 Replies View Related

Customizing Field Display Depending On User

Aug 30, 2006

Hi,

I am looking for a way to customize fields to be hidden or not depending on user in forms or reports.

Any idea ?

I wanted to explore the customisation of each query but there must be an easier way to save personal parameters somehow so that the user would go through the forms and hide the non necessary fields once and we would then save somewhere these settings for each user.

Has anybody already experienced such a request ?

View 5 Replies View Related

Queries :: Crosstab Queries - Export Command Ask To Enter Date Parameter Twice

Feb 24, 2015

I have a crosstab queries which uses the date query parameters. However, when I go to my Export command (code is below), it ask me to enter the date parameters (start date and end date) twice. What do I have to do so that the system will ask me to enter once only?

Code:
On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "qryEXPORT"

[Code] .....

View 9 Replies View Related

Forms :: Customizing Input Mask Violation Error Message?

Jan 5, 2014

I am trying to change the standard input mask violation error message to a personalised one. I have found this code:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const INPUTMASK_VIOLATION = 2279
If DataErr = INPUTMASK_VIOLATION Then
MsgBox "There was an input mask violation in the field no!!"
Response = acDataErrContinue
End If
End Sub

However, i would like to change the message for a number of different text boxes. and i don't know how to isolate each one, and give each one a different message?

View 5 Replies View Related

Queries :: Sub-queries With Text Box Based Parameter

Sep 18, 2014

I'm trying to perform a DELETE query at run-time. The criteria for deleting records is somewhat complex due to various one-to-many dependancies between the tables, overlapping of date fields etc., so in order to restrict the DELETE to only those records which qualify, I've set up a load of smaller queries to produce the list of eligible records.There is only one parameter required - a 'CutOff' date which is arbitrarily chosen by the user and is available in a textbox control on an open form when the code is triggered.

Here is the main DELETE query :

Code:
DELETE DISTINCTROW [tblComments].*
FROM [tblComments]
INNER JOIN [qryCommentsToPurge]
ON [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID]
WHERE [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID]

I've put qryCommentsToPurge and all the cascading sub-queries below - have highlighted in red where the parameter value is required (it is used in the queries to identify the records which must be retained, i.e. not deleted)Each of the individual sub-queries works fine and correctly (they return the correct population of records that they were designed to)If I run the sub-queries manually, I am prompted to provide the CutOff date, as you would expect - and the recordset returned in each case is correct for that date.

If, however, I run the DELETE at run-time (with the form containing that textbox open and containing a valid date value) I get a 3061 error :Suggesting that the queries can't pull the parameter value from the textbox on the form. But I've stuck a watch in the debugger on [Forms]![frmArchive]![txtCutOffDate] and I'm getting the correct date value? The form is open and available at the time.So why can't the query see it? I use these kinds of control references elsewhere in other queries and they work fine. I don't understand why it's a problem here?

Code:
SELECT [tblComments].*
FROM [tblComments]
INNER JOIN [qryIssuesToPurge]
ON [tblComments].[IssueID] = [qryIssuesToPurge].[IssueID]
ORDER BY [tblComments].[CommentID];

[code]...

View 1 Replies View Related

Parameter Queries Vs Parametrized Queries

Nov 5, 2004

I have been asked to set up a parameterized query using 4 fields (ID Number, Surname, Christian Name and Treating Dr/s). When I search Microsoft On-line under parameterized queries it gives a tutorial which basically set out a simple query with "prompt" parameters in the Criteria field. When I search under the Access Help it gives me a totally different explanation - ie. setting up a cross-tab query first and then defining parameters. Is this a loose use of terminology or is there indeed a difference. Help please!

View 1 Replies View Related

Parameter Queries

Jun 29, 2007

Is there a way to label a parameter in a query?

I need to enter a start date for a query - the parameter - and the end date is always six months later.

Currently, I use;

Between [Enter Start date:] And [Enter End date:], but this is not idiot-proofed.

How can I make the second parameter be a calculation based upon the first; i.e., [First Param] + 180 days?

Thanx for any help!

View 2 Replies View Related

Parameter Queries

Nov 15, 2007

Hi there.

I am looking to rationalise some of the queries I have in my access application.

Let's say I have two forms that query the same table based on a value they have in a combo box.

Currently I would have a separate query for each form and set the criteria to the name of the control on the respective form.

My question is whether I can use just 1 parameter query and use this for both forms, passing the necessary criteria - similar to how you would with an SQL Server stored procedure.

I have done this in the past programatically but wondered whether it is possible to do this within the GUI?

One solution is to not have any criteria in my query and then define an SQL statement in the rowsource of my form/control that queries my original query i.e.:
"SELECT FirstName, LastName from Qry_Customers WHERE QryCustomers.CustomerID = ????"

This works but it not as easy to maintain as having separate queries (same reason why I prefer to use stored procedures rather than definining my SQL statements within my code)

Also are there any negative performance impacts in defining the rowsource in this way (opposed to just setting to an existing Table/Query)?

Thanks,

View 1 Replies View Related

Parameter Queries

Nov 22, 2004

I have a query field with the criteria: [enter state]. I would like to be able to enter multiple states (ie: IL or WA or AZ) but the query only functions when one individual state code is entered. Any ideas how to fix this? Thank you.

View 1 Replies View Related

Need Help On Parameter Queries

Dec 18, 2006

I'm new to this forum and am looking for help in the following:
I'm trying to create a parameter query (using Access 2003) and would like to display just the data that are not null. I tried to insert "and Is Not Null" after the [parameter name] under "Criteria", but it still list null data. Any help will be greatly appreciated (please note that I am a basic Access user and do not do visual basics or anything other than the quick command buttons or the wizard.

Many thanks in advance

View 3 Replies View Related

Date Parameter Queries

Jan 9, 2006

Hi everybody,

I have a report that is based on an underlying query.At the query level,I
want to define parameters of date data type so that records to be displayed
should fall between DateA and DateB. ie Between[Enter Date A:] And [Enter Date B:].
I want the DAteA and DateB to be only days and Months of a year.

Say my DateA = 01 Jan 06 and my DateB = 31 Dec. 06

But I want only the Parameter to be Between[Enter day and Month A:] And [Enter day and Month B:].


How can I write a query with the two parameter values?

Thank you

dfuas

View 1 Replies View Related

Multiply Parameter Queries!

Apr 13, 2007

i have a Query where i have Criteria for two fields as the following:

Field :Date of Incident
Crirteria: [Forms]![frmSearchStudent&Date]![cboStartDate] And [Forms]![frmSearchStudent&Date]![cboEndDate]

Field: StudentSurname
Criteria: [Forms]![frmSearchStudent&Date]![Combo0]

Occasionally, staff may just want all incidents (records) to be shown for the selected StudentSurname and so, just select the surname and leave the date fields blank.

i tried the following:
Field :Date of Incident
Crirteria: [Forms]![frmSearchStudent&Date]![cboStartDate] And [Forms]![frmSearchStudent&Date]![cboEndDate]
Or: [Forms]![frmSearchStudent&Date]![cboStartDate] Is Null And [Forms]![frmSearchStudent&Date]![cboEndDate] Is Null

Field: StudentSurname
Criteria: [Forms]![frmSearchStudent&Date]![Combo0]

then when i tested this by leaving the date fields blank and selecting a valid student surname it simply returned all incidents (records) for ALL students not the selected one.

is it possible to achieve this? if so, could someone please help!

View 6 Replies View Related

Parameter Queries Using Multiple Between....And

May 24, 2007

I have created a query so that two fields have a range in them i.e.

One Field called "Price"
There are two text boxes on the seach form called "price1" and "price2"
The query reads

Between [price1] And [price2]

A second field called "Date Ordered"
There are two text boxes on the search form called "date1" and "date2"
Field called "Date Ordered"
Between [date1] And date2]

I have used the AND operator to join the first and second range. The user will fill in the criteria from a form with text boxes. This form is unbound but as soon as they click on the Search button, it will open up the query with the parameters in as above.

However, if the user were to type in a price but not a date the query shows no results. Results are only shown if a price and a date are inputted.

The OR operator will work but it is not what we need. We need to show results if a price is entered but not a date and vice versa. The fields are coming from one table.

Please can anyone advise? The database is a library management system so the price and date are the price of any books and the date that the book was ordered.

Thank you in advance for any help or advise.

Janet

View 9 Replies View Related

Queries :: Don't Want Parameter In Query

Apr 25, 2013

I have a table that has a date column. I am assigning one group of students a test date of 4/29 and another group 4/30. I don't want access to change the column or ask for a parameter during the query, but no matter what I do, it keeps asking me for a parameter. I just want it to use the data that is already in the column.

I tried changing the column name in Excel to DOT from Date. That didn't work.I also tried changing the column to text, so that it wouldn't be a "date." That didn't work.

View 7 Replies View Related

Queries :: Parameter Value Against A If Statement

May 15, 2013

This statement returns a "date" each time the event occurs. I want to have a parameter between [Start Date] and [End Date] so I can pull events for a specified date range. I have tried this "Expr [EventDate]" Between [start date] And [end date] but it returns "null" results. I am using access 2010

EventDate: IIf([tblEventException].[EventID] Is Null,IIf(([qryEventCartesian].[PeriodTypeID] Is Null) Or ([qryEventCartesian].[PeriodFreq] Is Null) Or ([qryEventCartesian].[InstanceID] Is Null),[qryEventCartesian].[EventStart],DateAdd([qryEventCartesian].[PeriodTypeID],[qryEventCartesian].[InstanceID]*[qryEventCartesian].[PeriodFreq],[qryEventCartesian].[EventStart])),IIf([tblEventException].[IsCanned],Null,[tblEventException].[InstanceDate]))

View 2 Replies View Related







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