Modules & VBA :: Public Function To Assign A Macro To All Buttons With A Certain Name
Jan 23, 2014
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.
I have a form called departments . one that form i have 5 buttons. I want this form only to open when some logs onto the database . The buttons represent each department ...
1. sales 2 purchasing etc etc
Is it possible to assign a password to those buttons so each department has security to their own form ...
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
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 .
I have the following function that I found online. Unfortunately I can't remember where I got it since I've had it for a little while. Today, when I tried to actually put it to use it didn't work.
I'm calling it from a form as follows: CompactDB (tblHotword)
tblHotword is just a random table from the back end. My understanding of the function was that it would use that table to connect and get the file name of the back end.
Whenever I run it, Access pops up a window that says "Object required" and nothing else. It doesn't look like a standard error message popup. When I click 'OK', Access continues with the rest of the code as if nothing went wrong. The function doesn't run though.
Code: Public Function CompactDB(TableName As String) As Boolean On Error GoTo Err_CompactDB Dim stFileName DoCmd.Hourglass True stFileName = db.TableDefs(TableName).Connect
I have multiple reports that use similar IIF statements as the controlsource for four textboxes. Naturally, I don't want to have to update twelve controlsources if any of the calculations change, so I thought I'd make this a public function. However, I don't know how to pass along multiple textboxes as variables. Here's what I have so far:
Code: Option Compare Database Public Function txtColor(ByRef textbox As Control) Dim str1, str2, str3, str4 As String 'The IIf statement is simplified for this example. It's not important. str1 = "=IIf(IsNull([Inquiry start date]),'W',IIf([txtInquiry]<1 And IsNull([Inquiry end date]),'R'))"
[Code] ....
And this is in the report (where ??? is what I'm asking about)
Code: Private Sub Report_Load() Call txtColor(???) End Sub
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?
I have a table with only two fields and one record: BegDate and EndDate (beginning and end date of the reporting period respectively). I also made a function with variables that look up those values for use as a date parameter in a query.
Here is the code:
Option Compare Database Option Explicit Public Function getCurrentRepDates() As Date Dim dtBegDate As Date dtBegDate = DLookup("BegDate", "tblCurrentRepDates")
[Code] ....
I am getting a syntax error for the line marked red. How can I use "Between" function in VBA code? Access 2010
I am attempting to make a macro to pull information based on a clicked button to provide information to another form. While my example below will be rudimentary, I am attempting to create so that it can be applied to 450 buttons, hence the need for it to be a macro and not simply code per button.
For example if I have 3 buttons, captioned: red, green and blue. If I click on the blue button, it will open another form with a text-box that will say 'blue'. Likewise, if I click the red button the text-box will be changed to 'red'.
In the code below the section that I need to change is: Command1.Caption. Using this data I can pull from the one button to create the value, but I need it to be based on an OnClick or something in order to pull the value from the right button.
Question 2: I am trying to use a query to change button colour on the basis of values stored in a table.What I want to do is change the button background after comparing the button name to the same variable in a table, and determining another variable.
IE: Button1, Button2. In table: Button1, Val = Y, and Button 2, Val = Z
Pseudo Code: Array: From Button1 to Button2 Query for Button1 against table If Val = Y, Button1.background = red Else if Val = Z, Button1.background = blue Else Button1.background = black End if
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'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 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 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 have created a Public Function that would get a new Production Instructions number based off the [PI Number] of Tbl_Production_Instruction table.
I have a form that people will put in all information but the PI Number, then when ready they will click a button to update the PI Number. I place an unbound txtbox that will be hidden, with the control source to =NewPINum(), but when I tested the see if the unbound txtbox was populated with the new PI Number it was blank.can't figure out what I am doing wrong.
Code: Public Function NewPINum() As String Dim vNum As String Dim strYYMM As String Dim getnextPI As String strYYMM = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" If strYYMM = Left(DMax("[PI Number]", "Tbl_Production_Instruction"), 6) Then vNum = Right(DMax("[PI Number]", "Tbl_Production_Instruction"), 3) vNum = vNum + 1 getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")Else vNum = "001" getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000") End If End Function
Just learning Access so may have missed something really basic!
I am trying to add some shortcut keys to existing toolbar functions such as FileGet External DataImport (&Import). My issue is that when I try to use the shortcut key I get a message saying:
"A macro can call itself a maximum of 20 times. Your macro contains a RunMacro action that calls the same macro more than 20 times. Use a condition to stop the macro after it has been run 20 times, or call another macro with the RunMacro action."
I have added the shortcut key "CTRL+I" to the button by going ViewToolbarsCustomise then right clicking on the button and under PropertiesShortcut Text adding "CTRL+I".
I have added the Macro Group "Autokeys", and within Autokeys added "^I" to the column named Macro Name, "RunMacro" under the column called Action, and "AutoKeys.^I" under Macro Name (at the bottom).
Both the Repeat Count and Repeat Expression fields at the bottom have been left blank.
I am trying to write a more complex macro that will start another macro at a preset time, however I am getting stopped at the first hurdle - getting a macro to run another macro.
Here is the code i am using at the moment, all I want to do currently is click the first button, then get the second macro to execute. But no luck, getting error 2157 "cannot find the procedure"
Code: Select distinct [tbl_DTP/CTP].id_zlecenia, [tbl_DTP/CTP].Folia_na_lakier_UV, [tbl_DTP/CTP].Wykrojnik, [tbl_DTP/CTP].Makieta, [tbl_DTP/CTP].Matryca_do_tloczenia, [tbl_DTP/CTP].Matryca_do_zlocenia, [tbl_DTP/CTP].kalka, tblGoraZleceniaRoboczeLaczenie.NumerZlecenia from [tbl_DTP/CTP] inner join tblGoraZleceniaRoboczeLaczenie on [tbl_DTP/CTP].id_zlecenia = tblGoraZleceniaRoboczeLaczenie.NumerZlecenia where tblGoraZleceniaRoboczeLaczenie.NumerZlecenia = r1;
Where "r1" is a public variable as string.The variable has value e.g. "10/145" But query can't get this value and all the time ask about value from "r1" :/
I'm creating a simple database to track some data that needs to be cleared out every 4 months or so.
I have all of the data stored in a single table, and I'd like to create a button that would do the following: Step 1: copy the current data into a backup table for disaster recovery Step 2: delete all of the current data in the table...but leave the table structure in place for new data Step 3: compact and repair the database
Is it possible to open another database, set a Public boolean variable in a module?
in db1: Open database by Access.Application, OpenCurrentDatabase, setting obj to db2.CurrentProject, loop through main objects
in db2: Public ByPassCloseVar As Boolean is in a module ModLinkTblReview
I have a process that opens up a database runs through all of its objects to get their properties and values. There is a form that is set up on Unload to close the tool if the variable is set to False. When I open the form to get the properties and their values, then close the form, it closes the database. I would like to set the variable to true in order for the database to stay open.
Thought something like this would work: db2.Modules!ModLinkTblReview.ByPassCloseVar = True
Indicates that method or data member does not exist.
I have a module that has a global variable defined as shown below
Module: initGlobals -----------------------
Code: Option Compare Database Option Explicit Public strGlobalUserId As String Sub initvar() strGlobalUserId = "Myuserid" End Sub
This is invoked in a login form("frmLogin") and it displays the above value before I replace it with the one that is accepted in the form.
Here is the code from the login form
Code: strGlobalUserId = Me.cboEmployee.Value
When the password is successful, I close the login form and open a form called "frmMainForm". In the form_open event of the "frmMainForm", I am trying to display the user id using strGlobalUserId but it just has spaces. What am I doing wrong?
I have a backup subroutine which automatically triggers when the front-end is closed down (it just takes the back-end, makes a copy and compacts it).It's driven off a hidden form which I use to track who is connected to the BE at any given time. This form is opened as part of the AutoExec when the FE is opened and writes some basic user info to a table. When the form is closed, it updates the table and fires the backup process before quitting Access.
Part of that user tracking process checks to see if the same user is already connected - either elsewhere (i.e. on a different machine) or on the same machine (i.e. opening a second instance of the same FE) - which is undesirable (and, frankly, unlikely, but not impossible) A brief prompt appears to explain that they can only be connected once, at which point the application is quit (to enforce the rule) This also works fine.
I would add a public boolean variable, set it to True by default, then switch it to False if the same user is already logged on before quitting Access.Trouble is, the variable doesn't seem to be holding its value? Here is the Load event for the tracking form :
Code: Private Sub Form_Load()
On Error GoTo ErrorHandler
Dim dbs As Database Dim rst As Recordset Dim strSQL As String Call InterfaceInitialise blnPerformBackup = True
[code]...
For some reason, blnPerformBackup is False every time, even though I set it to True at the very start of the Load event (and it is a Public variable, defined in a separate module where I store all my Public constants and variables)
why it is not retaining its value from the Load event? I've checked and it does get set to True - and when I debug, it remains True - but at runtime it reverts back to False by the time it reaches the decision whether to backup or not?
