Modules & VBA :: How To Run A Function In Subroutine

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 Replies


ADVERTISEMENT

Modules & VBA :: Running SQL From A Subroutine In A Subform?

Feb 23, 2015

I'm trying to run an sql update query (in VBA) to update values in a table, from calculated field values in a subform (using a query as a record source).

Is this possible , and if so, can I call the subroutine from the parent form, and what would be the correct syntax for the VBA sql string (i'm guessing it would be a docmd.runSQL sqlstringname )??

View 1 Replies View Related

Modules & VBA :: Options For Calling A Subroutine

Jan 14, 2015

Imagine I have two subrountines:

Code:
Private Sub DoSomething1()
[Do Something]
End Sub

Private Sub DoSomething2()
[Do Something]
End Sub

And this Declaration:

Code:
Dim str As String
str = 1

Is it possible to refer to/call one of these subroutines using something like this:

Code:
("DoSomething" & str)

View 1 Replies View Related

Modules & VBA :: Exit A Subroutine From Within Control Loop

Jan 30, 2015

Sometimes I find the 'need' to exit a subroutine from within a control 'loop' e.g. if.. then..else with... while... et al.

I suspect this is 'bad form' but is there any 'cost' in doing this?

There presumably will be if objects have been set and are not reset to Nothing. In this case it presumably is not a problem resetting to Nothing outside a With.. - what about inside a with..??

View 10 Replies View Related

Modules & VBA :: Creating A Function That Counts Records And Use That Function In A Query

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

Calling A Subroutine In A Report

Oct 25, 2004

I want to call a selfdefined subroutine "update()" in several reports. But whenever I try this, Access gives me an Error 2465. The Report is selected dynamically by the user and should adapt itself to other settings made by the user. Can anybody help me how to do this? Thanks.

View 4 Replies View Related

Enter A Subroutine On Form Initiation

Dec 15, 2005

Is there anyway to have a subroutine be called when a form is brought up? meaning, as soon as the user opens the form, stuff happens without them doing anything besides opening the form.

View 1 Replies View Related

Advanced VBA Wait In Subroutine Until Form Is Closed

Jul 7, 2004

Hi all.
I have a form that opens up another form when you click on a button. The button does more than just open the form, it does other checks. I want to wait until the opened form is closed to continue with the subroutine. Is this even possible in VBA? I was wondering if there was a frmFormName.IsOpen or some kind of flag that would tell me if a form is open or not. Any help would be greatly appreciated. Thanks in advance.

Sub open
Checks things
Checks things
checks things

Opens form(frmDetails)
Wait for form to close (THING I DON'T KNOW HOW TO DO IF POSSIBLE)

Checks things
Checks things

end sub

View 2 Replies View Related

Tables :: Calling Subroutine From VBA Routine To Append Record

Jun 21, 2013

I am trying to call a subroutine from a VBA routine to append a record to an existing table. Below is the code for opening my recordsets for reading followed by the code creating my table to append to:

Public Sub UpdateLotsizes()
Dim curDatabase As DAO.Database
Dim ProdPlanDB As DAO.Database
Dim rstQryLot As DAO.Recordset
Dim rstInvLoc As DAO.Recordset
Set curDatabase = CurrentDb

[Code] ....

To call my subroutine, I simply put in the code:

ExceptionReportAddRecord

And this is the code of the subroutine:

Public Sub ExceptionReportAddRecord()
Dim rstExcRpt As Recordset
Set rstExcRpt = curDatabase.OpenRecordset("tblExceptionReport")
With rstExcRpt
.AddNew
.Fields("item_no").Value = txtItemNumber

[Code] .....

I am receiving "Run-time error 434: Object required" at the line I marked with ...

View 4 Replies View Related

Modules & VBA :: Inserting Function Within A Sub

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

Modules & VBA :: Multiplication With Str Function

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

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 8 Replies View Related

Modules & VBA :: Dir Function Empty For Certain Drives

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

Modules & VBA :: How To Write A Function That Does Query

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

Modules & VBA :: Possible To Initialize A Function Like Do For Variables?

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

Modules & VBA :: Function Not Available In Expression Error

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

Modules & VBA :: Argument In Custom Function

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

Modules & VBA :: Runtime Error 52 When Using Dir Function

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

Modules & VBA :: Function To Define Colors

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

Modules & VBA :: Function To Keep Numeric Value 4 Digits

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

Modules & VBA :: How To Run A Function From SQL Query In Access

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

Modules & VBA :: Function Not Returning Argument

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

Modules & VBA :: Select Case Function

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

Modules & VBA :: SQL Table Valued Function

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

Modules & VBA :: DCount Function Counting 0s As 1s

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

Modules & VBA :: Run A Function On Button Click

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







Copyrights 2005-15 www.BigResource.com, All rights reserved