Topics Search

Using DLookup inside a user-defined function which is inside a query

Using DLookup inside a user-defined function which is inside a query
Views: 23
Access gurus,
I am not sure if using DLookup inside a user-defined function and then
using that function inside a query is forbidden. When I did that, the
following error is encountered;

"Cannot open a form whose underlying query contains a user-defined
function that attempts to set or get the form's RecordsetClone
property"

I never use RecordsetClone property in my code. Does anyone have any
Sponsored Links:

Similar posts...

 

Custom function inside SQL query (Access VBA)

Custom function inside SQL query (Access VBA) Icon
I'm trying to use a custom (user-defined) function inside an SQL query. When the function is in a normal module, the code works fine, but when the function is in a Class Module, Note: I have made sure the Function is Public in the Class Module. The Routine that creates and executes the SQL command is in the Class Module.

Is it forbidden to call a function residing in a Class Module from an SQL query, or am I missing something?
 

OpenFunction Macro Action

OpenFunction Macro Action Icon
In an Access project, you can use the OpenFunction action to open a user-defined function in Datasheet view, inline function Design view, SQL Text Editor view (for a scalar or table user-defined function), or Print Preview. This action runs the user-defined function when opened in Datasheet view. You can also select the data entry mode for the user-defined function and restrict the records that the user-defined function displays.
 

Substracting from a value returned from function

Substracting from a value returned from function Icon
I was wondering if you know any way to substract 1 to the instr returned value inside the left function, I'm doing something like left ([Field],instr([field]," ")), except theinstr value needs to have something like this left ([Field],instr([field]," ")-1) but access won't let me do it, I'm wondering if I can do this inside the whole function becausethey are inside a query,
 

Retreive individual components of a user defined type in a query

Retreive individual components of a user defined type in a query Icon
Access 2007, win 7 32
I have a user defined function that creates a user defined type from a number of tables. I am trying to figure out how to access individual parts of the returned type in a query for producing a report.
 

Using DLookup in a subform

Using DLookup in a subform Icon
I have a form called frmQuote and a embedded form inside it called frmQuoteProd. I want to select a product from the drop-down menu and have a dlookup enter the product name into a new text box.

Every way I do a DLookup I am getting an error. I have attach my test database that is in Access 07.
 

How to put a prompt message inside a text box

How to put a prompt message inside a text box Icon
I am trying to add a instructional message inside a text box like you see in Outlook and explorer help boxes. I have included pictures below of what I am trying to do. Essentially, I want a short statement describing what goes in the text box and once the user clicks in the box, the text disappears. Right now I am using the default value function
 

Group and Count on Same Report

Group and Count on Same Report Icon
I have a query which joins to tables that is being used to generate a report. The query is returning the following fields:

Route #
Meter Type
Status
Size Description

It is Grouping on Route and giving me a count of meters by size for each route.

I now have a need to add a Location field to the query. I need to Count the number of meters by route that have "INSIDE" in the text field and still be able to count based on meter size.

I know I need to add the Location field to the query but what other changes do I need to make to the report in order to sum the meter count by route correctly.

Example:

Route 10 has 50 meters total.
10 are 1" that are not inside
20 are 1" that are inside
10 are 2" that are not inside
 

Nest a query inside another query or in an expression by using a subquery

Nest a query inside another query or in an expression by using a subquery Icon
Sometimes you may want to use the results of a query as a field in another query, or as a criterion for a query field. For example, suppose that you want to see the interval between orders for each of your products. To create a query that shows this interval, you need to compare each order date to other order dates for that product. Comparing these order dates also requires a query. You can nest this query inside of your main query by using a subquery.
 

How to add a button for every record inside subform?

How to add a button for every record inside subform? Icon
I want to have a form with a subform and then have inside the subform a button for each record in the subform, if so how?

or is there any other way to accomplish this, the reason is I need the user to be able to select a particular record inside the subform.
 

Can I look up a value using DLookUp() from an input/paramater query?

Can I look up a value using DLookUp() from an input/paramater query? Icon
Everything is okay until I put a parameter in my query and all my textboxes bound to a DLookUp() function are displaying #Error