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: 21
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:

More topics

 

Custom function inside SQL query (Access VBA)

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

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

Preview
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

Preview
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?

Preview
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.
 

Retreive individual components of a user defined type in a query

Preview
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.