Modules & VBA :: Run Function From Another Database
Aug 7, 2014
I have 2 database files.
database A has a button (buttonRun) that when clicked, it opens database B and runs the autoexec macro.
In database A, there's a label control (lblRun) next to the buttonRun that has its back style property set to transparent.
What I'm trying to accomplish is this:
After the procedure in database B completes, it exits the system. I want
lblRun's back style property to be set to normal.
I already have a function that does this and the function works fine from within the database A.
Code:
Dim frm As Form
Dim color As Variant
Set frm = Form_frmReportingDashboard
frm!lbl_cmslite.BackStyle = 1
I need to trigger that function from database B before it exits.
View Replies
ADVERTISEMENT
Dec 11, 2013
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies
View Related
Mar 1, 2006
Any help would be really appreciated...
I have a database in the database I have products customers orders etc.
In Products table I have productid price etc. I would like to create another table and this table should consist of all product parts for example.
ProductID x uses
metalnumber23
stone45
plasticnumber43
so all metalnumber23, stone45, plasticnumber43 will be in Parts (new table that i will create) when i am entering a productid to Product table for that productid i should be able to choose which parts from parts table that it uses.
so in the order table if i customer ordered productID x I should be able to see all the parts that ProductID needs in the parts Parts table. There will be maybe 2000 parts but ProductID x will use only 3 of them. And if I create a query lets say order from z date to y dates I should be able to see parts needed for those products ordered.
hope you guys understand and give me a head start i would really appreciete
View 2 Replies
View Related
Aug 29, 2005
Hey, its been a while since I've posted here, wow.
I had a database that contained samples on different date functions
(first of the month, last of the month, first weekday, etc...)
I can not find it, I have tried searching. I am pretty sure I got it from here.
Does anyone know where this is?
View 3 Replies
View Related
Aug 13, 2013
I've been trying to create a search function in my database and a " TmprryQryFrLnkFldsCrtn0123456789 " Query has appeared that I cant alter or change.
View 6 Replies
View Related
Aug 24, 2006
Hi,
I am running an autocompact module in Access97 which will created a 'compacted version' of my database. I then want to rename this compacted version to the name of the original database I compacted. I cannot however find a way of doing this in code.
Basically the idea is to compact every 45 minutes or so, but what my autocompact is doing is creating a compacted version under a different name, and so compacting a copy of the database, I then want to change this compacted database name to the original name and re compact every 45 mins
Can anyone help?
View 1 Replies
View Related
Jun 15, 2014
I am trying to create a statistics function on a database. The idea is that the user will enter a start and end date and either search for all records during that date range or select a client from a combo box and only view records for that client during the selected dates.
After doing quite a bit of searching, it seems that I should be using a wildcard in the criteria however I cannot get this to work. The code I have been playing with is:
Code:
=Nz(Forms![Statistics]![ClientCombo] ,"*")
I have changed the "*" to a specific client number and if the combo box is left blank, results are shown for that client only and if a client is selected from the combo box then the selected client is shown. The only thing I cannot get it to do is show all entries if the combo box is left blank.
View 2 Replies
View Related
Aug 19, 2004
I am using a Access database with a frontend/backend design. I want to keep a table in the BE updated with the current list of users inside of the frontend. To do this I thought that I would just have the front end update a time field saying that that user is still inside of the FE. My question is two-fold - firstly, how can I run a function every minute or so to update this table.
Secondly - is there a better way to do this?
Thanks in advance for any help you can offer.
View 3 Replies
View Related
Apr 23, 2013
I have a database, i need to automate mail merge function from access DB to insert selected records into the merge fields, i have tried everything i could and i have searched through out the web nothing really works for me.
View 1 Replies
View Related
Feb 25, 2015
I am new at VBA in access and I am working on an application I didn't develop but I am maintaining.
Code:
Private Sub Command122_Click()
Dim formName As String
formName = getFormName(tblName)
DoCmd.OpenForm (formName)
DoEvents
Forms(formName).FilterOn = False
[Code] ....
I am trying to to write afunction that translates the deptid into a table 'id (myreference)
View 1 Replies
View Related
May 6, 2015
I got the following problem.
This code doesn't calculate the exact value:
Code:
Sub TEST()
Dim strSQL As String
Dim b As Double
DoCmd.SetWarnings False
b = DLookup("MC", "COMMON_DISTRIBUTION", "YEAR = 2015")
[Code] ....
My variable b is 0.230.
The TNS is multiplied with 0.228.
It seems a problem with the Str(b).
View 5 Replies
View Related
May 17, 2014
I have a database which needs to be updated everyday. So I decided to make a subroutine to perform the update steps just by clicking on a button.
The problem is that I can run all the steps except one! At the end of the steps, there is a function/module that must be run to calculate and complete the job.
How I can run a function in a vba macro. This is my code:
Private Sub cmdUpdateDatabase_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry1AddNewStockSymbols"
DoCmd.OpenQuery "qry2AddDailyPrice"
DoCmd.OpenQuery "qry3UpdateStockDailyPrice"
DoCmd.OpenQuery "qry4AddOverallStockValue"
DoCmd.OpenQuery "qry5AllSymbolsActiveDaysTractions"
DoCmd.OpenQuery "qry6SortToCalculateDailyRemainedStock"
DoCmd.SetWarnings True
End Sub
Module name is: CalculateDailyRemainedStock
View 3 Replies
View Related
Sep 17, 2014
I have a module for importing several excel files from a folder in Access. The user is prompted to select the folder which contains the Excel files.The issue:The code works fine if I select a folder from my company network drive (strFile variable returns with correct file name).The code does not work if I copy and paste the same folder to my desktop and select this location (strFile variable returns empty)
Code:
Option Compare Database
Private Sub btn_ImportData_Click()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim intWorkbookCounter As Integer
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
[code]....
View 7 Replies
View Related
Jul 22, 2013
I have a query string in the following code. I want to put the entire code into a function so I can use it somewhere else as well.
Code:
Private Sub Form_Load()
'To enable the AllowAddition property once the form is opened
Dim dbs As DAO.Database
[Code]......
View 5 Replies
View Related
Jul 15, 2015
Sometimes we need a one-line function to just get the database path or things like that we cannot do on a query or on a Macro Object.
Like:
Function GetDatabasePath() = currentproject.path
possible?
View 5 Replies
View Related
Sep 10, 2013
I have got a problem on one of our computers.
We use Microsoft Access databases to enter test data and eventually generate reports.
On 1 computers i get the following error when opening the report:
"Function is not available in expressions in query expression 'Left(.....)"
I figured this is a reference problem so the next thing i did was to pinpoint what reference was causing this error.
It turns out it is the Microsoft DAO 3.6 Object Library. Simply removing the reference and adding it again fixes it and allows you to open reports just fine.
The problem is though, this message comes back every now and then. Which is getting annoying and the person who is making the reports is about to throw his computer out of the window.
View 2 Replies
View Related
Jun 6, 2014
I'm having a problem with argument in custom function..My table is like this:
myValues
123.5
32.7
65.8
11.1
What I want to achieve is to multiply each value with the average of all values, so that at the end I get (avg(myValues) = 233.1):
myValues2
7196.963
1905.593
3834.495
646.8525
For this I would like to have a function (this is simplified example of bigger problem, that is why I need function, otherwise simple SQL would suffice as mentioned below also), so that I could do something like this:
'SELECT TestFn([myValues]) as myValues2 From MyTable' or
'SELECT TestFn("[myValues]") as myValues2 From MyTable'
and I should get the table above.
My code is like this
Code:
Public Function TestFn (FieldName) as String
Dim myAvg as Double
myAvg = Davg(FieldName, "myTable")
TestFn = FieldName * myAvg
End Function
I understand the problem is that Davg needs string, but if I change to string then the multiplication does not work. The 'FieldName' parameter does not have a Type specified because that is where the problem is if I give string Avg works ,but calculation does not. If I give Number, avg does not work.
P.S. "as String" at the end of function is not a mistake, I need number as a string
P.P.S I did not inculde NZ either as in my example it can never be Null.
View 8 Replies
View Related
Jul 2, 2013
I'm trying to delete a file in a network location. Sometimes the file is there, sometimes it's not, so I'm using the following code:
Code:
strPath = "etwork driveMy Folder"
strFilename = "MyFile.doc"
If Not Dir(strPath & strFilename) = "" Then Kill strPath & strFilename
Occasionally, I get a runtime 52 error when the file doesn't exist. I don't understand why I'm getting this when, if the file doesn't exist, the Dir function should return "".I searched the forums for Runtime 52, and didn't find anything pertaining to this.
View 11 Replies
View Related
May 1, 2014
I have one color scheme I want to use all through the database I am developing. The next examples have just one color defined, to make it simpler (a dark blue, that I would call B1)
Code:
private sub setlabel()
Dim B1
B1 = RGB (0,52,105)
me.label1.forecolor = B1
end sub
... however this means I have to repeat the color definition every sub, so I thought would be neater to define a function to set my color codes (I have 20 colors).
Code:
Function SetColor()
Dim B1
B1 = RGB (0,52,105)
End function
My objective, is when I'm working in forms, Iwould (ideally) call this function "setcolor" and just write my code for the blue. I tried the examples below:
Code:
Private sub setlabel()
SetColor()
me.label1.forecolor = B1
end sub
[code]...
Again, this is probably some definition of arguments or dimensions that I am not aware oh. How to predefine the colors in a function to give them a "short" code which I can call in any sub in the database?
View 4 Replies
View Related
Jul 14, 2015
I have the below function that works correctly. However, I would like the "Last_Nbr_Assigned" value to start at 0000 and increment by 1. So the very first number assigned would be 0001, then 0002, then 0003, and so on.
This number is automatically assigned so I haven't had luck with the validation properties. And if manually enter 0001 for the "Last_Nbr_Assigned", it will automatically cut off the 3 zeros, leaving me with 2 instead of the desired 0002.
Code:
Function NewRF() As String
Dim startRange_Var As Long, lastNum_Var As Long
On Error GoTo Err_Execute
'Retrieve last number assigned for RF
groupname_Var = Nz(DLookup("Group_Name", "Codes"), 0)
[Code] ....
View 7 Replies
View Related
Nov 26, 2013
I started using Access recently and I have an inquiry about VBA codes and sql queries. I have function which has the following signature: RAG(Stability as Integer, DS_SYNC_RATE as Integer, Profile as String) as String.
I would like to call this function in Insert sql query in order to calculate and add a new column to my table. I tried this basic query:
Insert INTO my_table VALUES (RAG(my_table.stability,my_table.ds_sync_rate, my_table.profile))
but it doesn't seem to work...
View 8 Replies
View Related
Jan 13, 2015
I have just added a function to a database to strip out any commas or quotation marks from a passed string.
However, it is returning a black string even though when I step through the function it creates the out string correctly and the variable "OutString" is populated when the Exit Function command is executed.
Calling code.
InString = Btxt
OutString = ""
Call Strip_String(InString, OutString)
SBtxt = OutString
[Code] .....
View 4 Replies
View Related
Aug 17, 2014
My problem now is about evaluating the result of that query in order to calculate quarterly and annual performances. The query returns correctly:
1) NULL - when I don't have values in the given month
2) 0 - when one of the components of the performance formula is 0
3) value (positive or negative) which can include any positive or negative value
In order to calculate the quarterly values I need the performance of the last month in the quarter and the first month in the quarter. My monthly values are stored in columns so say for Quarter 1, I would need something like this:
Code:
[mar]/[jan]-1
As you might have thought, the problems come when I have NULL or 0 values. Let me give you a couple of examples.
[jan] is NULL
[feb] is a value
[mar] is a value
Than my formula would have been
Code:
[mar]/[feb]-1
Second case:
[jan] is 0
[feb] is 0
[mar] is 0
In this case I would like the formula return '-' because I want to be able to spot the case in which the quarterly performance is 0 because the initial values were 0 from the case in which the performance was actually 0 (i.e., say [jan] = 101 and [mar] = 101 then performance is 0 which is different from having [jan] and [mar] = 0 thus performance '-').You can combine the three states (NULL, 0, value) with the three months and find many combinations (I have found 27 relevant ones).
I initially thought to use SWITCH in SQL to evaluate the 27 combinations but I found out that SWITCH would evaluate non more than 15 conditions. So I guess the solution should be use a function in VBA which does what I was about to do with SWITCH in SQL.
I have built a test function which evaluates the numerator of my formula using only two cases (CASE 1: all variables have a value <>0, and CASE 2: the first month is null, the second is 0 and the third a value). Here the code:
Code:
Function evaluate_s(Var1, Var2, Var3) As Double
Select Case Var1
Case Is <> 0
[code]....
My main question here is what is wrong in the syntax of my function. Why is not correctly evaluating at least the two cases in my function? I always get 0 for all records.
View 8 Replies
View Related
Jul 5, 2013
i'm trying to connect to a table valued function on sql (2005) but keep getting the following error: argument are of the wrong type, are out of acceptable range, or in conflict with one another.here's the code
Code:
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
cnn.ConnectionString = "DSN=mydsn;UID=myuser;PWD=mypw;"
cnn.Open
[code]...
View 4 Replies
View Related
Sep 24, 2013
In my database when an item is currently being loaned the item's loaned field will have a 1 to indicate this. When the item is returned the value is 0. Only so many items are available and when I use the DCount function it is counting the 0s as 1s.
View 1 Replies
View Related
Sep 24, 2013
I try to run a function off of a button click.
The code is
Private Sub Command_Click()
Run fuctionname()
End Sub
The code is ran, but then I get a msg box : Run-time error '2517':
Microsoft Office Access can't find the procedure '.'
View 5 Replies
View Related
