Using DLookup inside a user-defined function which is inside a query
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 &
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
"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:
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.
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
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.).
Paging a Recordset
Is it possible to query a table to show the first say 20 rows and then show the next 20 rows and so on.
My reason for asking is that I can create a paged recordset in ASP and query it to return a next / previous page of results, but I am currently using VBScript in an .hta wrapper. This wayI cannot simply link to new pages and so I need to find a way to use the Access engine to do the work in returning the records I need in a paged way.
If I can get a query that runs inside Access to do this then I can replicate it in VBScript code to run inside the .hta