Modules & VBA :: Return Rows In Query From Variant Array Return From UDF
Sep 16, 2014
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField]
"kick the ball"
"return the pass"
my query result should looks like this
[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
I display some buttons on my Form. I should do an SQL request to know how many and what the button should display. It works fine with a RecordSet. Now I want to move the code that do the stuff to a function. I read that I can't return a pointer to a RecordSet and I should used a variant with a getrows.
So I try this :
*here is my function
Function get_nom_operation(ByVal cnn As ADODB.connection) As Variant Dim requetteSQL As String Dim rst As New ADODB.Recordset
Set res = get_nom_operation(conn) Dim i As Integer 'i = 1
For i = LBound(res) To UBound(res)
Set Obj = Me.Controls.Add("forms.CommandButton.1") With Obj .Name = "monButton" & i .Object.Caption = res(0,i) .Left = 14 .Top = 25 * i .Width = 60 .Height = 20 End With
'ajout de l'objet dans la classe Set Ge = New gere_event Set Ge.CButton = Obj Collect.Add Ge i = i + 1
Next
But it doesn' work, and I don't know why...
The error doesn't show where the code is stoped, but only show the call to display this form.
someone could show me some way to find the solution please ?
I hope this is a simple query becuase my brain has drawn a blank on it.
I have a query with a parameter which a form textbox. If the text box has X value I want to use one list of parameters and if has Y I want to list all rows in the query.
I am looking to return one row from groups of the same EpisodeID whereby the row with the minimum date is selected each time. This includes returning all other fields in the row such as EventID below and ideally others as well if that will be possible.
To illustrate I include the following. What Access 2003 query would I need to return all the rows with the earliest dates? EventID will be unique in the intial table.
For each record in my database, there are observation periods which are recorded in the format dd/mm/yyyy hh:mm:ss, titles as follows
1st Obs Start 1st Obs End 2nd Obs Start 2nd Obs End 3rd Obs Start 3rd Obs End.
I have been asked to create a query that will quickly show how many obervation periods commenced in a particular month. What I am trying to do is create a column that will be named Obs Start, and another, Obs End. For each record ID, this would then show as follows:
In a query I'm trying to return a list of rows sorted by Service Type Ascending and then the last item in the list should be a row called "Add Edit Value".If I 'ORDER BY 2' then the "Add/Edit" row appears at the top which is not what i want.
My SQL: SELECT '' As ServiceTypeID, 'ADD/EDIT VALUES' As ServiceType FROM ServiceTypes UNION SELECT ServiceTypes.ServiceTypeID, ServiceTypes.ServiceType FROM ServiceTypes ORDER BY 1 DESC;
I have an Access 2010 application that talks to SQL Server. Part of the application creates tables on the SQl Server and inserts data to it (I know SQL Server has its on data inport/export wizard and bulk insert capabilities but for reasons outside of my control it needs to be done from Access). So I have a connection to the SQl Server and i have VBA that happily executs SQL statements on the SQl Server. The problem is I want to return a recordset from the result of a SQL query issued to the SQL Server. The idea being to check for the presence of tables already created, and if so, append to the already existing table, otherwise if it does not exist, create it and insert data to it.Ive tried the following code but it seems no recordset is being returned:
Set objConnection = New ADODB.Connection objConnection.Open "DRIVER={SQL Server};SERVER=10.200.3.14;trusted_connection=yes; DATABASE=" & Me.Combo54 strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '" & Me.Combo54 & "'" strSQL = "USE " & Me.Combo54 & " " & strSQL Set Rst = cmd.Execute("USE " & Me.Combo54 & " " & strSQL)
Trying to run a query where each 4 fields calling a custom function will not just re-run the same custom function over and over again for each field in a single record.
A Function has a huge amount of multiple queries and logic to perform.The Function returns a Integer, Integer, Integer, and optional Integer. Each integer requires a DLookup to lookup a String description value for each individual integer (in each of 4 fields).
The problem is, the DLookup in each column that runs against each of the integers re-run the same function.The result is that a single record, each of the 4 columns returning a single of the 4 values, the complex function is re-run 4 times.
The function is huge, part of a Business Rules Engine. Depending on the Rule-Meta data - it might launch up to a dozen queries and perform logic steps for each record. This is not the ordinary SQL Query.
Imagine if one record (for 1 field) takes 0.1 second to run. By referencing the function in 4 columns, this same function is re-run 4 times (0.4 Seconds) Against 50,000 records - this duplication of re-running the function for each column can really add up.
Possible Solutions: Researched Class Modules - There is a comment that the property Get, Let actually reduce performance. There are huge advantage of code documentation, documentation and centralization.It doesn't claim class modules reduce execution as each propery is returned. It also describes that Class Modules can't be called directly in a Query - unless each property is wrapped in a function.
Function Returns one String with delimiters: e.g 34;54;55;1 This single column goes into a Make Table (runs function one time per record) Then the D-Lookup is run against static local data. This pevented the function from being run over and over across the network linked data.
Final Solution: Eventually, the many hundred lines of VBA code for the Rules Engine will be converted into SQL Server T-SQL Functions on the server.For a Rule Engine development, Access has been great for a rapid protoype development and testing. The TSQL will be a final big step requiring re-coding. It is not currenty my option for the delivery time frame.
I want to return a value from a separate query and have that show up in a field on my main form. The field name from the query is "Six Month Date" and the query name is "DT6Monthqry". I want to return the value displayed in the"Six Month Date" from the query, matching the CustomerID in the query result to the CustomerID that is being displayed on the main form. The CustomerID is a text field.This is the code I placed in the Control Source property for the field I want to be displayed on the main form:
Maybe I've been looking at the monitor for too long but... I need to have a query return data based on a date field or return all dates greater than 1/1/2001 if left blank.
criteria: [Enter date - mm/dd] Or >#1/1/2001#
When I leave it blank it gives me all dates as it's supposed to but when I type a date it does not filter by that date, I still get everything.
I also tried [Enter date - mm/dd] Or Like "*" but it behaves the same way. I know it has got to be easy but my skull may be too thick to let me see it.
I have a database which connects to another application (by creating an object) for the purposes of reading/writing data. Part of the code involves creating an instance of this application, and then taking input from the user via an Access form.However, when I load the form, the focus stays with the other application, and the user has to 'click into' the form in order to reset the focus to the form and key the relevant details. Not the end of the world, granted, but frustrating over time and something I'd like to iron out.
I originally had this process in Excel, and used the following code to change the focus back to Excel (so that the user could key directly into the form)
Code: Dim ExcCap As String ExcCap = Application.Caption
' ...assorted code, including creating the object to 'house' the other application...
' Return focus to Excel AppActivate ExcCap
But this doesn't apply to Access as it utilises the Application.Caption / AppActivate method to identify the appropriate window to switch back to. In Access, I don't have a caption to reference.I've tried setting the focus in the Form_Load event but it doesn't bring the form 'to the fore'
I'm looking for a bit of code for a module that will return the field name from an expression (that uses an array) instead of returning the value in that field.
Example: (Minimum is a working module that finds the smallest number in the array) Field1 = 34 Field2 = 53 Field3 = 21 Field4 = 62
I have a code that finds the first, second, and third minimum value in a row across the fields. Now, I am trying to find a code to look at these values, find the field it is located, and return the field name. I tried several variations of my code to return the field name rather than the value, but have been unsuccessful to this point.
Function NthMinimum(intPosition As Integer, ParamArray FieldArray() As Variant) As Variant Dim varTempArray() As Variant, varTempValue As Variant, intArrayValues As Integer Dim I As Integer, J As Integer ReDim varTempArray(UBound(FieldArray)) intArrayValues = 0
[Code] ....
As you can see, this works to find these values while ignoring NULLS. How to return the field name?
How i can cancel the a carriage return and replace with a tab or stop it moving down my subform and make it move the the next field instead of going to a new line.
I am using the Keydown but don't know how to cancel it, it just bring an message but it stops it moving to a new record,
Code:
Private Sub Barcode_KeyDown(KeyCode As Integer, Shift As Integer) If KeyCode = vbKeyReturn Then MsgBox "rtn pressed" End If End Sub End If
I'm new to Access and VB but I managed to write parts of what I want access to do.By tweaking a code I found on the internet, I managed to write a small Subroutine that allows the user to click on one of the fields of a record in a report and then Access opens the form on that specific record.*My code goes something like this:*
Code:
Private Sub Edit_Click() * Dim strWhere As String * Dim DocName As String * DocName = "FormName" * strWhere = "[Field Name]='" & Field & "'" * DoCmd.OpenForm DocName, acNormal, , strWhere End Sub
Now I want to get the name of the Table where the record exists.So, let's say when I click on the "Field" it gets the name of the table where the record with that field exists and sets it in a variable.OR even better would be, get the name of the Form where that record exists but I guess that's a little more complicated since the record is directly linked to the table...*
what is the best/cleanest way to return the next primary key value which is in the format yyyy-###.These are case files for the current year, ### being 3 digit number incremented by one for each new case.
I could use a function that finds the last one, checks the date, increments by one and returns it, but maybe I could use a public property or a class? I'm keen to use classes more for code maintenance.With a class then: would i create a PrimaryKey variable of the class type/object, so when I need a new key I would just type PrimaryKey.NewKey, for example?
I have a query with a Start Time where the need to return a set integer in another field in my query. I am attempting to get this to work in my StripSecondsQry.
I am not getting any error messages and I am not getting any output, When I view this in the Locals window I can see that it should be returning 7, but instead I get nothing unless I change it to
Code: Function SortStart(StartTime As String) As Integer
then I get zero.
I had this working within the query, but I had to add one more time and then received a message that the expression was too complex.
Basically I want to get the ID (a number) from a specific record where the JOB NUMBER equals the string I have typed in to a field on the form, also called JOB NUMBER.
However, my problem is that it doesn't navigate to the record where the criteria matches, it just chooses the ID from the very first record of the table.
I would like to select a case depending on the output of a function.
This function tests the syntax of the reporting month.
If the syntax is fine nothing should be done further in the main sub else it should return to the Input window for the reporting month.
Somehow it doesn't work out.
Code: Public Function RepMonthCheck(rep_date As String) as Boolean If Len(rep_date) <> 6 Or Left(rep_date, 2) > 12 Or Left(rep_date, 2) < 1 Then MsgBox ("Reporting rep_date is not in the correct format = mmyyyy") Return False ElseIf Right(rep_date, 4) > 9998 Then MsgBox ("No forecast available for year 9998")
I have a date field in a form fed by an option group. Most of the options return the correct values, but I'm having a hard time with this:
If the inspection date (in another form) is in Jan Feb or March, then return the date 1 Apr of the inspection year. If not, then return 1 Apr of the following year.
I have a small table, that only contains one record (and should only ever contain one record, which simply gets edited to suit once in a while).The table is called OtherRates, and the fields are as follows:
I am trying to use DLookup to return a value from the relevant field in this table, however the field to look in is a variable.The code (below) that I have tried returns an error and highlights the DLookup line,.
Question for Documentation purpose: Should the Public Type be declared in its own module?
Or should it be declared in a standard module where non-public functions use it? It is not for a Form module use.
For a Rule Engine, a function is calling one record on 4 different SQL Views (as linked tables) that have the same field format.
For speed, the recordset should only be opened once. However, there are multiple values that must be returned to the result table multiple fields.
One way to return multiple values is an Array. That has over head too.
Another way is to create multiple public variables. Not my choice for documentation. Another is to create a string.
This is a pure code module with several non public functions / subs. What is the documentation preference? List a Public Type close to the function, or place it in the Global module?
Background: A function can only have one return value.
By creating a public Type, multiple values can be returned.
Code: Public Type Income Wages As Currency Dividends As Currency Other As Currency Total As Currency End Type
Use this structure as the return type for a function. In a real situation, the function would look up your database tables to get the values, but the return values would be assigned like this:
Code: Function GetIncome() As Income GetIncome.Wages = 950 GetIncome.Dividends = 570 GetIncome.Other = 52 GetIncome.Total = GetIncome.Wages + GetIncome.Dividends + GetIncome.Other End Function
To use the function, you could type into the Immediate Window:
GetIncome().Wages
(Note: the use of "Public" in the Type declaration gives it sufficient scope.)
Important Notice The way this function is called will work, but is wrong from the aspect it re-calls the recordset over and over.
I am trying to use a DLookup to search to find what block goes into the production of an item.
In a table called "dbo_vw_MCE_job_with_materials", each item contains a list of the parts which go into its production in a column titled "Expr1". However there are always more than one parts to this, with varying names.
What I am trying to do is to use a Dlookup to only return the first value which starts with B (as the block number is always the only part on the list which begins with B).
However I think as the item column contains the same item number in 8 columns, with different values in the Expr1 column, Dlookup only returns the top value "A-CF0057" (which is irrelevant as far as I am concered) - whereas I am hoping it will be able to pull the "B1499" value from the column as it is the only one starting with a B
Is there anyway to specify which value the dlookup function would return, or is there any way to apply some sort of permanent filter on my table?
I have been trying to find a solution to why I can't get a Dlookup with multiple criteria to return the value I need.
Essentially I am trying to use an Order Number to find the item number which is contained within another table. However the order number has multiple lines (suffixes) which alter the item number. Therefore I am trying to have the item number be populated by the correct 'combination' of Order Number and line ("suffix").
I have managed to use the Dlookup in the after update of each box of the form separately and they retrieve values in the table correctly:
Afterupdate of main order number:
Code:
Private Sub OrderNumbertxt_AfterUpdate() ItemNumbertxt = DLookup("item", "dbo_job", "[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'") End Sub
Afterupdate of suffix:
Code:
Private Sub SuffixTxt_AfterUpdate() ItemNumbertxt = DLookup("item", "dbo_job", "[suffix] = Forms![**INPUT]![SuffixTxt]") End Sub
However when I combine them as follows in the afterupdate of the Suffix box I receive a "Run-time error '13': Type mismatch"
Code:
Private Sub SuffixTxt_AfterUpdate() ItemNumbertxt = DLookup("item", "dbo_job", ("[suffix] = Forms![**INPUT]![SuffixTxt]") And ("[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'")) End Sub
I think the reason is something to do with some being numbers and some being a combination of text and numbers (based on the replies of other topics), but have been trying to modify these slightly and can't get it to work still.
Also the Order Number is a combination of letters and numbers (normally in the form of AB12345678), the suffix is just a number between 0-9 and the Item number it finds is a combination of numbers and letters.
