I defined a public variable and then for being able to filter a query results, assigned it to a public function. The problem is that function doesn't get the variable's value.
View RepliesI am amending some Code I found online for an audit table, I need to store additional information in the table that is associated with some forms but not others.
I have researched about putting optional variables in, but I read this only works with the type VARIANT.
Is there a way to make a string optional as my fields contain text?
here is my code so far:
Sub AuditChanges(IDField As String, UserAction As String, Optional UserID As String, Optional DeviceID As String, Optional SimID As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
[Code]...
I have some code in a form that calls for a public function and passes on some values. the function makes some calculations and assigns a value to the variable "percent" in the function itself.
How can I get the value of this variable back in the form so I can use it?
Form:
Code:
Private Sub Form_Open(Cancel As Integer)
Dim Table As String, TotalFields As Single
Table = "tblAdmission"
[Code]....
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.
See the proper way to use it submitted below.
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 used Transfer text cmd to export query to text in MS access. This works fine. path= "D:/test/"DoCmd.TransferText acExportFixed,"Query1",path,False But i need to change the path dyanmically as my wish when i run this query. Like i may save my txt in desktop or C: or D:. I dont want to hard code path as above.
Is there any way to achieve this? If i get the dyanmic location stored in path variable i can achieve it. But i dont know how to achieve this.
Actually i was confused
Docmd.OutputTo acOutputQuery,query1
Above query prompts me for selecting location. But transferText doesn't? why?
As this application is stored on server. If i give a static path, its exporting in Server D:/test/ But i need this to be stored in my local. This can be done only if it prompts box for user to select the location.
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer
LR = 5
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set wbRef = xlApp.Workbooks.Add
With wbRef
wbRef.Activate
.Worksheets("Sheet1").Activate
With ActiveSheet
.Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes
End With
end With
I look at a lot of files to see when they were last updated. I wanted to write a generic procedure to manage that so ..
Code:
Public fDate As Variant
Public vField As String
Public vFile As String
'GTSdata
vField = "txt_gts_data"
[Code] ....
What I hoped Me.vField would do is update the date field [txt_gts_data] on my form with the date the file was last saved.
i.e. me. txt_gts_data = fDate
What actually happens is the variable vfield gets updated from "txt_gts_data" to 19/08/2014 then later code falls over because the fieldname is lost .
Me.[vField] corrects itself to me.vField (and does not work)
Me!vfield falls over (cannot find the field vField, not surprising J)
How do I say update the contents of the variable, not the variable itself?
I want to make a loop like this:
Dim var1 As Variant, var2 As Variant, var3 As Variant, var4 As Variant
DIm var5 As Variant, var6 As Variant, var7 As Variant, var8 As Variant
Dim var9 As Variant, var10 As Variant
Dim i1 As Long
i1 = 1
Do Until i1 > 10
var & i1 ??? = "0" & i1 & "." & txt1 & "." & txt2
i1 = i1 + 1
Loop
How to make concatenate var + i1 to make loop function?
I have the following in Form and it works OK
Call RunForm("frmProviderLookup", "", "Edit", "", "", "Normal", "Normal")
I want to build a combo box with several different RunForm variations as follows:
RunForm("frmProviderLookup", "", "Edit", "", "", "Normal", "Normal")
RunForm("frmCustomerLookup", "", "Edit", "", "", "Normal", "Normal")
RunForm("frmVendorLookup", "", "Edit", "", "", "Normal", "Normal")
etc, there are many of these
When I select one the variations from the combo box the RunForm statement is stored in strAction varaible..How do I code the Call strAction line? (I want only one call statement).In other words how to call a Function from a variable...The start of my module Function works OK and looks like this:
Public Function RunForm(FormName As String, _
Optional WhereCondition As String = "", _
Optional xMode As String = "", _
Optional filterName As String = "", _
Optional Args As String = "", _
Optional WindowMode As String = "", _
Optional View As String = "")
' Parameters (all optional except parameter 1)
' 1 - Form Name
' 2 - Where condition
' 3 - Mode [Add, Edit, View(Read Only), ""(PropertySettings), DS(Datasheet Edit)]
' 4 - Filter Name
' 5 - Opening Arguments
' 6 - Window Mode
' 7 - Form View
I'm having an issue getting a return value from a stored procedure that I'm calling from VBA. This is what I have at the moment:
Code:
Dim strDate As String
Dim strWOStatus As String
Dim CurrentConnection As ADODB.Connection
Dim adoCMD As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim ParamReturn As ADODB.Parameter
[Code] .....
The problem I am having is this error:
Error: 424
Description: Object Required
The line of code it errors on is:
Code:
Set .Parameters("@PartsUSedMTD").Value = ParamReturn
And the value of ParamReturn is always Null after it hits the line before it.
So it seems like it's not really creating the parameter variable SQL Server needs to run
How would I update all SQL stored within all forms to change its syntax?I am returning all the row-sources for all the comboboxes on all forms in an Access db. Most work fine but some have syntax errors, for example double quotes instead of single. Is there any way of finding these stored SQL statements and updating them on mass, as I am doing it manually at the moment!
View 1 Replies View RelatedI have code for calling stored procedure with parameters,which is as follows
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim IdValueToProcess As Long
IdValueToProcess = 221177 ' test data
Debug.Print (IdValueToProcess)
Set qdf = CurrentDb.CreateQueryDef("")
[Code] ....
And my stored procedure is
Code:
ALTER PROCEDURE [dbo].[spItemDesc]
@ItemNo varchar(200) ,
AS
BEGIN
set nocount on ;
select ProductDesc1,ProductDesc2 from ProductDatabase.dbo.tblProductInfo where ProductNumber = @ItemNo
END
The error comes on debug.print(rst!ProductDesc1)
And it says "Item not found in the collection"
Following concerns about someone accidentally deleting the access database we have been using to crunch performance numbers, I have successfully moved the data on to an SQL server
While the database works as it is, several of the queries are running extremely slow. I therefore decided to see if a stored procedure could run the number crunching on the server instead of passing the data back and forth all the time.
I have taken the series of queries and converted them into a stored procedure that runs too fast for me to blink while giving the same results as before.
The problem I face is that I can trigger the stored procedure from the server management studio manually while supplying the variables needed thus providing the data I need to export to excel in a table for this purpose.
What I want to do is to have a form in access supply the chosen variables (like I could before) and run the stored procedure at the click of a button as part of a series of other queries.
I have looked at pass-through queries but apparently they do not take kindly to variables unless they are hardcoded. The other solution would be to trigger it from VBA but I have not been able to find a solution I could get to work.
How to run a stored procedure on an SQL server from access while also giving it the variables it needs?
Stored procedure name: spNearMissCalculation
Variables:
@SelectedDate (date format) (taken from a form field)
@SelectedVessel (nvarchar(max) format) (taken from a form field)
@SelectedVesselGroup (nvarchar(max) format) (taken from a form field)
I am calling a stored proc and passing some params (sql). i need to loop through the execute a few times though before I kill the connection.
I am getting an error saying there are too many params on the second run through. What i need is to clear the param list once it's executed once.
Is this possible or to i need to keep killing the conn then re-connecting to the db?
I have two different database files. One is 2010 ".accdb" format where I have created a form and the inputs to the form is getting saved as records to an access.mdb file in a shared path.Now if the users want to edit the existing record I should allow them to search their previously submitted record with a unique ID number.
I know it is possible when we have both the form and table in the same db. But I want to know whether it is possible to search with a unique ID and pull the data from different db in a shared path using a command button?
I am trying to call a stored procedure from access ,but it is giving me this runtime error :
Code:
2147217900
Syntax error or access voilation
I am doing this first time so i dont know about how to pass parameters (IN and OUT)..
My code is
Code:
Public Sub createDataToAnalyze()
Dim objConnection As New ADODB.Connection
Dim objCom As ADODB.Command
[Code].....
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 RelatedI would like to know if this is possible using VBA ( or other method)
I want to determine the back color o a cell based on a color value stored in another cell in other word
backcolor of cell A = the value of cell B
So if I change the numbers of cell B (which is color codes) , backcolor of cells A change accordingly and it will have the color specified in cell B.
I'm trying to search a for string within a subform to find information stored on the mainform to which the particular subform belongs.
The problem is that the subform is generated from a query which uses a number from the main form to generate.
So the subform record is only generated when the correct mainform record associated with it is loaded.
Now to solve my problem I've made a new query that brings up ALL the results that could be generated by the main form and from that I can search to find my search term I'm after and read off the ID number to tie it back to the mainform.
But all of this is done manually, I want a way to do all this using VBA in a way that the user can't edit any records as they are doing it.
If I set a variable inside of a subroutine, it is set to nothing upon the end of the sub. Can I set a variable outside a sub and set its value, so that you can use it within subs?
View 14 Replies View RelatedHow can I define a variable which can be used by another Sub and of course the value stored in it?For instance:
Private SUB A ()
DIM A1 as String
A1 ="ABC"
END SUB
PRIVATE SUB B()
PRINT A1
END SUB
I have an application where several different procedures are run repeatedly every xx seconds to get live updates from a server (different procedures depending which form the user currently has open.) Within each procedure, a separate single procedure is called only if a stored boolean setting is set as true. The setting itself is only changed very infrequently, such as on application startup, but needs to be initially set (i.e. it cannot just have a default of False if it is not set.)Because of this I am storing the setting in a single record table and have a function 'UsingWPilot' to return the boolean value. However, given the frequency that the setting needs to be accessed, and the fact that speed is crucial in the running of the app, I am considering adding a global string variable and modifying the function to look at the variable (possible values 'N' and 'Y') and only going to the table if the variable has not been set.
I have heard various things about how you should never if possible use global variables. My question is: is it faster to use a global variable than to open the table record each time?
option 1 :
Code:
Public Function UsingWPilot() As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("rbl_Settings_System")
Option 2:
Code:
Public Function UsingWPilot() As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
If Len(g_WPilot) > 0 Then
UsingWPilot = (g_WPilot = "Y")
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Settings_System")
[code]....
I am just in the middle of writting a little bit of code that will go through each record in a query and export it into a word table. It's in the early stages but all was working yesterday. I've come to it this morning and ran it and now I get an error message;Run Time error 91: Object Variable or With Block Variable not set.
Code:
Private Sub Command15_Click()
Dim MyDb As DAO.Database
Dim rsLogin As DAO.Recordset
Dim ObjHead As String
Set MyDb = CurrentDb()
Set rsLogin = MyDb.OpenRecordset("query here")
[code]....
I have some code that will allow a user to pass to it the name of a report. The code will do various things to that report before presenting it for Preview, opening in Word etc.
At one point in the code I would like to refer to some control or property of the report. If I was to include this directly for one report, I might type:
Reports!Annual_Budget_Report.Recordsource = xyz
But instead of a specifc report, it would be the report as a variable (srReport_Name). However, if I type:
Reports!stReport_Name.Recordsource
The code complains, rightly, that there isn't a report called stReport_Name. The stReport_Name variable is currently set as String. I *think* the answer might lie in the variable type, but I'm still struggling.