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

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:

Related Topics

Custom function inside SQL query (Access VBA)
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?


Nest dlookup inside a dlookup
I am looking up the factory name for a field but the database stores the number - so I need to use a dlookup inside a dlookup. This is what I tried:

DLookup("Name", "WAVE3_ADD_REC", DLookup("[FACTORY]", "WAVE3_INVN_DL", "[Ref2] = '" & Me.cobLot1 &amp


OpenFunction Macro Action
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
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,


Cannot retrieve data via Excel if query got user-defined function
I would like to retrieve data from Access using Excel VBA through ADO. This was successfully done in the past until a user-defined function was added to one of the fields in the Access query. On Access, the query can run successfully. On Excel, an error "Microsoft Access Database Engine -> Undefined function in expression" is returned.

Has someone encountered similar problem before? Any workaround? I think using user-defined function in queries should be quite common because it is so useful.


How to put a prompt message inside a text box
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


SQL inside DLookup()
Forgive me but I thought this would be something simple. I am trying to execute a piece of SQL inside a DLookup(). Is that possible? I keep getting a run-time error when I try to execute.


mySQL = "SELECT * FROM tblSKUMin_Max WHERE [SKU] = '" & vSKU & "';"
vMin = DLookup("SKUMin", mySQL, "[SKUClass] = '" & vStoreClass & "'")
vMax = DLookup("SKUMax", mySQL, "[SKUClass] = '" & vStoreClass & "'")
Form_SfrmEmail.txtMedMin.Value = vMin
Form_SfrmEmail.txtMedMax.Value = vMax

Error States:
"The Microsoft Acces database engine cannot find the input table or query 'SELECT * FROM tblSKUMin_Max WHERE [SKU] = '(SKU NUMBER)';'. Make sure it exists and that its name is spelled correctly."


Using DLookup in a subform
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 add a button for every record inside subform?
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.


Group and Count on Same Report
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


Retreive individual components of a user defined type in a query
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.


two value in forms
friend I have in this database inside parents forms with two fields call value1 and value2
when I enter value into child forms inside field call value2 it must all they time smaller then value1
in case the value inside value2 is bigger I need Massage box appear The value is big and not permit to change value.


User defined keyboard shortcuts
Is there a way to define keyboard shortcuts in Access 2010?

For example, I'd rather use the Alt-~ than Control-F6 to toggle between windows inside a database.


Email contents of Form as txt INSIDE email ?
I would like to accomplish the following using Access commands or through VBA:

* When a new record is added via a form, the user would click on a command button and the contents of the form for the new record would be emailed to a predetermined listing of recipientswith the data from the form being included inside the body of the email as TXT and not as an attachment.
* The data that is being sent should be only for the new record and not the records for the entire table.

I can send the form contents as several different types of attachments using the SendObject command inside a macro but have not found a way to simply paste the data inside an Outlook emailrather than using an attachment.

Basically, I would like the same results as if the user had submitted the data in an HTML document with the MAILTO command and enclosure type as plain text.

I am zipping and attaching a sample db in case it helps. It has one table, I am hoping to replace the attachment with text directly in the email for the newly added record.


Calculation in access forms
I have in form call main inside contain filed call sum_in and subform call out inside sub form filed name out sum.
Now my question I want put valve inside out sum filed it must minus it from
sum_in in main form and put remain inside filed call rem


SQL Subqueries
You've seen the Russian dolls where you've got one doll, inside another, inside another, etc.? A SQL subquery is very much like this; it's tucked inside other commands, or even other SQL subqueries.


Use vba to draw inside Object Frame
Does anyone know whether I can use vba in Access to draw inside an Object Frame, which is placed on a form? As a start I would like to draw a solid rectangle at a specific location inside the Object Frame


Passing recordsets to user defined functions
If I pass a recordset to a user defined function, will the recordset being passed point to the same record by default? so If I'm on record 10, when I pass the recordset will the passed recordset be pointing to record 10?

Second question: If I pass a recordset to a user defined function. how do I initialize it in the function


Nest a query inside another query or in an expression by using a subquery
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 (subquery: An SQL SELECT statement that is inside another select or action query.).
You can write a subquery in an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) or in a Structured Query Language (SQL) statement in SQL view (SQL view: A window that displays the SQL statement for the current query or that is used to create an SQL-specific query (union, pass-through, or data definition). When you create a query in Design view, Access constructs the SQL equivalent in SQL view.).


Dlookup Problem
I have a query with 3 Column DLookup Function, the query is 'make-table' Type, and when I run it, all Dlookup columns will be null