I have used Allen Browne's code (see http://allenbrowne.com/ser-16.html, bottom of page) as a model for my user-defined type and public function in an events management database.
Using the function in forms/VBA works fine.
But my DB crashes whenever I try to use it in a query.
My modules code:
= = =
Option Compare Database
Public Type EvCheks
EvType As String
EvAttCat As String
EvUnitAss As String
EvCheksAll As String
End Type
= =
Public Function getEvCheks(EV, EvUnit) as EvCheks
'Event Type: Event or DL
If (DLookup("evtype", "tblevents", "[evid] = " & EV)) = "Event" Then
getEvCheks.EvType = "Y"
Else: getEvCheks.EvType = "N"
End If
'Event Attendance Category: INDB= in database or LIST
If (DLookup("evattcat", "tblevents", "[evid] = " & EV)) = "INDB" Then
getEvCheks.EvAttCat = "Y"
Else: getEvCheks.EvAttCat = "N"
End If
'Event Assessing Organisation
Dim AOROName As String
Dim AOName As String
AOROName = DLookup("evunitassessable", "tblevunits", "[evunitid] = " & EvUnit)
AOName = Mid([AOROName], 1, InStr([AOROName], "/") - 1)
Select Case AOName
Case "NA"
getEvCheks.EvUnitAss = "N"
Case "ABCD"
getEvCheks.EvUnitAss = "Y"
Case Else
getEvCheks.EvUnitAss = "X"
End Select
getEvCheks.EvCheksAll = getEvCheks.EvType & getEvCheks.EvAttCat & getEvCheks.EvUnitAss
End Function
====
On a form, I have a button with on-click event code that defines the EV and EVU variables and displays a message box
MsgBox getEvCheks(EV, EVU).EvCheksAll
This works fine.
=====
In a query I have this SQL code:
SELECT tblEvUnits.EvId, tblEvUnits.EvUnitID, getevcheks([evid],[evunitid]) AS EventDetails
FROM tblEvUnits;
This causes the db to crash.
I cannot get the "back-end" definition of the function into the query, as Access rejects it because of the dot, i.e. ".EvCheksAll".
I suspect there is some formatting quirk for using a public function that includes a user-defined type, in a query.
I have quite a few user defined / custom functions which I would like to be available in any new database that I create without have to copy the VBA codes in a module each time. Basically, some sort of add-in that loads on MS Access startup may be (but I am not too sure - never created one). So something that will start with the MS Access Application and not just a particular database. I know excel addins can be copied to the XLStartup folder to achieve something like this in excel, but not sure about Access.
I have created user defined function for lastdayofweek, lastdayofmonth, and firstdayofmonth. When I apply this function to an expression in my query, it returns a julian date. How do I apply the format for the calendar date?
Maybe I am not calling my function correctly. Here's my functions below as well as my expression..
Function LastDayThisMonth() LastDayThisMonth = DateSerial(Year(Date), Month(Date) + 1, 0) End Function
Function FirstDayThisMonth() FirstDayThisMonth = DateSerial(Year(Date), Month(Date), 1) End Function
I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().
The field on which this function is to enter as a criteria is another calculated date function called Due.
When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.
I copied some VBA from one database to another. I didn't change anything and I am able to run it fine in the first database. But in the DB I pasted it to, I am received a Compile Error message with the XlApp As Excel.Application area highlighted.
Function OpenAutoCount() Dim xlApp As Excel.Application Dim xlWB As Excel.Workbook
This is giving me the error 'User defined type not defined'. I know I have to set something in a list somewhere but have forgotten how to do that. Where to go, and what to set?
I'm trying to get a public function to work within the design view of a query. The function is defined as:
Public Function Percentile_(fldName As String, _ tblName As String, p As Double, _ Optional strWHERE As String = "") _ As Double... ( I can put the whole code if u need it)
Any thoughts about how I can make the Percentile function calculate values according to a GroupBy statement and not for whole data set?
I created a Public Function called fNetWorkDays in my access database, however, when I try to use it in an expression I get "Undefined function 'fNetWorkDays' in expression". The Public Function is in a standard Module in my vba project. Why I cannot call it in my queries?
I am using a public function to feed a variable string to a query. So far I have got:
Code: Public Function ClientStreetModule(firstLVar As Variant, streetVar As Variant, newFL As Variant) As String Dim cslStr1 As String, newStreet As String newStreet = Right(streetVar, Len(streetVar) - Len(newFL))
[code]....
However, I only need to use newStreet as the true part of iif, in which instance all are longer. At least I think this is the problem. I realise I might need to use NZ but am not sure how. Why it is evaluating and giving errors for all records and not just when the iif criteria is true as I want it to?
On Compiling my assecc database VB code I get the following error message "User defined type not defined". I understand it is beecause I have not declared the Variable Type, but have no idea to exactly which part of the code the error is referring to.
How do I find out WHICH User defined type is not defined, especially when I have not got any (or do not want to use any) user defined types?
Thank you in advanced programming wizards. Kind regards, Adam.
I have a combo box that the user can select a value and the form will display that record on the form. This all works fine, but if the user dosn't enter anything in the combo box, it does not work. Is there a way to have it work if the combo box is left empty? Thanks in advance - John
Is there any way of creating a user defined query/ report based on fields in a table? Basically we want a report to be generated based on whatever fields the user enters. So, say we have 30 different fields in a table, the user gets to click on a button that gives them the option to create a report that includes: field1, field2, field3 and where field10 = Blue. Is this possible to do?
I want to run an activity query (i.e. objective is to check which data lines have changed) within a user defined date range. Please advise what is the most efficient way to achieve this.
Is it possible to run such a report without having a date column i.e. does Access have a hidden line modification date that can be used in a search?
I am making a classic sales over time crosstab query.
Rows: Customers Columns: Sale months
Sales date is defined by the ETD of the order.
However, with the simple Format([ETD],"yyyy-mm") I get regular months, but I need to adjust the months to be between the 21st and 20th rather than 1st to 31st(30th).
August would be 7/21/2013 to 8/20/2013 September 8/21/2013 to 9/20/2013
I'm new to VB and am learing slowly with all your great help in this forum.
I'm wondering if its possible to write a Public Function for vbCRLF. I have a field in multiple forms that are all named 'Note'. If I have the following code:
Public Function PubNoteLF() On Error GoTo PubNoteLF_Err
My!Note = My!Note & vbCrLf
PubNoteLF_Exit Exit Function
PubNoteLF_Err MsgBox Error$ Resume PubNoteLF_Exit
End Function
When I run the code I get Compile Error: 'Sub or function not defined.'
Can someone tell me what I'm doing wrong? Its problably something real basic, but I'm stumped. Thanks.
I haev a report with 15 sub reports. I have to use Visual Basic to format the sub reports. I know how to do the formatting (see below) but what I would like to do is have one function (Public) that I can pass the subReport name to and have the formatting happen. This would be mush easier to maintain than copying the code below into on_print events of all 15 sub reports.
Has anyone done this and have an example??
Thanks
-------------------------------------------------------------------------------------- Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer) Dim widthOfBox, startLeftSide startLeftSide = 0.017 widthOfBox = 0.21 Me.ScaleMode = 1 Me.ForeColor = 0 'Repeat the following line of code for each vertical line ' 1*1440 represents 1 inch
' Me.Line (0 * 1440, 0)-(0 * 1440, 14400) 'Draws line at Left Margin Me.Line ((startLeftSide * 1440) + widthOfBox * 0 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 0 * 1440, 14400) 'Draws line at Left Margin Me.Line ((startLeftSide * 1440) + widthOfBox * 1 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 1 * 1440, 14400) ' Draws next line Me.Line ((startLeftSide * 1440) + widthOfBox * 2 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 2 * 1440, 14400) ' Draws next line Me.Line ((startLeftSide * 1440) + widthOfBox * 3 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 3 * 1440, 14400) ' Draws next line Me.Line ((startLeftSide * 1440) + widthOfBox * 4 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 4 * 1440, 14400) ' Draws next line 'the 14400 is an arbitrary number to increase the line to the max of a 'section. End Sub
I have a query that I'm working on through Access 2010's design view. I'd like to add a criteria to the query where it only shows results with the employee name column matching a global variable I created that stores the name of the currently logged in employee.
Here's my vba code that declares the global variable and the public function i'm trying to pass as criteria in the query:
Global gbl_loginName As String Public Function returnName() As String If IsNull(gbl_loginName) Then returnName = "test" ' dummy account created for development only Else returnName = gbl_loginName End If End Function
and here's the SQL code from Access's design view:
SELECT [Entry of Hours].WC, [Entry of Hours].[Employee Name], [Entry of Hours].[Set Up Time], [Entry of Hours].[Run time], [Entry of Hours].[Traveler Number], [Entry of Hours].[Entry Date], [Entry of Hours].[Quantity Finished], [Entry of Hours].Notes, [Entry of Hours].WPS,
[Code] ....
when I try running the query, however, I get this error:
'returnName' is not a recognized built-in function name
Is there a problem with using public functions in Access' design view?
I am trying to read the value of a control on an open form from a Public Function in a Module with no luck. I suspect the issue is syntax.
Code:
Public Function MyFunction(varFormName As String, varControlName As String) If Forms(varFormName).Controls(varControlName) = True Then ' = 0 even when control is -1 Else End If End Function
The variables pass through fine but the If statement equals zero regardless of the state of the form's control. I tried several variations to no avail.
If Forms(varFormName).Form.Controls(varContactControl ) = True Then If Forms(varFormName)!Form.Controls(varContactControl ) = True Then Etc.
I have an issue with this case select below. The DelayStart is time so lets say I put in the debug window
?DatePart("h", #04:00pm#)
The result would be 16 which is correct BUT the issue is my second shift starts at 04:01pm and the result is still 16 untill 5pm. How do I fix this so I get the correct shifts? Im guesing use something other than DatePart but what?
Code: Public Function getShiftForRecord(DelayStart As Variant) On Error Resume Next Select Case DatePart("h", DelayStart) Case 6 To 16 'Daylight 6:00am - 4:00pm
I have built an access application that contains a set of buttons along the top of every form that serve as navigation.* These buttons each perform the same function on every form they are on. (menu opens the main menu, etc) I have database macros to assign each button the same function but I still have to go through each form and manually assign them. I was wondering if it was possible to define a public function that on db open will look for all buttons with a certain name and assign them the macro. (so all buttons called cmdmainmenu will have the OpenMainMenu macro assigned and so on).Before you go there, I have already tried the navigation form and set all forms as subforms.
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 want this function to populate value from a any field selected from any table to any text box in any form ...... Lets say in a Database named TestDB we have a Table named tblTest , a Form named frmTest and in this form( frmTest ) we have one Bound Combo Box named cmbTest and one unbound TextBox named txtTest
We assume that the table tblTest has three fields : TestID , FName and LName .
We also assume that there are already some records in the Table tblTest .
If the function F() is already programmed it should take as arguments as it follows :
F(FormName as??? ,ControlName as ???, TableName as ???, FieldName as ???, ID_Field_Name_of_the_Table as ???, Combo_box_selected_ID as ???)
In result the function should (probably) DLookup (FieldName , TableName , ID_Field_Name_of_the_Table = Combo_box_selected_ID ) and then store the value in a variable ( probably Variant ???) , lets say called varSetValue.
The problem is IF this is the correct way to handle that issue , than what should i do next.... I really don't know how to pass a Control's name to a function from the Event module of the ComboBox -
I mean - how can I obtain the value so it is usable for the function .... What data type should be the function arguments so I can use them to set a value for a control in any form .
In example : If the Dlookup() is somehow successfull then I want to assign the varSetValue to the txtTest what should I do : FormName.ControlName.Value = varSetValue >??????
I have read a lot materials but couldn't find a good answer for that , and aswell what data type should be the arguments that the function accepts and how do I obtain Controls and Forms names so I could use them in the function as described above .
