Modules & VBA :: Set Value To Variable Outside Sub?

Aug 26, 2014

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 Replies


ADVERTISEMENT

Modules & VBA :: Error 91 - Object Variable Or With Block Variable Not Set

Jul 8, 2013

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?

View 14 Replies View Related

Modules & VBA :: Sorting / Object Variable Or With Block Variable Not Set

Oct 3, 2014

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

View 3 Replies View Related

Modules & VBA :: Update Contents Of Variable But Not The Variable Itself?

Aug 20, 2014

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?

View 7 Replies View Related

Modules & VBA :: How To Define A Variable In One Sub Which Can Be Used By Another SUb

May 12, 2014

How 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

View 5 Replies View Related

Modules & VBA :: When To Use Global Variable

Apr 8, 2014

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]....

View 4 Replies View Related

Modules & VBA :: Object Variable Not Set

Nov 6, 2013

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]....

View 2 Replies View Related

Modules & VBA :: Report Name As Variable

Nov 6, 2013

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.

View 3 Replies View Related

Modules & VBA :: SQL Query To Set In Variable?

Sep 19, 2013

I have a form in which am gathering information from the user to populate a table(Customer Master List) and at the same time (btn_Copy_Click Event)transferring the info to another form(Case) populating another table(Customer_Call). It is working fairly well, My issue is that before transferring the info I need to run a check(SQL Query) to make sure the customer or actually the Well ID don't exist in the Customer_Call table. I am trying to do this in pieces .....

1) capturing the well_Id in a variable(WellID) in the first form and using that to build the sql string and query the Customer_Call table.

2)once that works place it in a if / else clause to copy or not with appropriate messages

With that I am stuck in step 1

It works up until Set rst = CurrentDb.OpenRecordset(strSQL) the i get Run-time error '3061' Too few parameters. Expected 1.

Code:
Dim WellID As String
Dim strModel As String
Dim strSQL As String
Dim rst As DAO.Recordset
WellID = Forms!f_Customer_Lookup.Well_ID
MsgBox WellID ' testing to see if it picks up the correct box in form
strSQL = "SELECT Customer_Call.[Cus_Well_ID] " & _
"FROM Customer_Call " & _
"WHERE Customer_Call.[Cus_Well_ID] = WellID;"
Set rst = CurrentDb.OpenRecordset(strSQL)
strModel = rst!Cus_Well_ID
rst.Close
MsgBox rst ' Testing to see if the strSQL captured the data
Set rst = Nothing
End Sub

View 8 Replies View Related

Modules & VBA :: Send Report With Variable Name

Jan 8, 2015

I use a macro that attaches a document automatically to an e-mail address ready to send this report is governed by a query of which the criteria is " loss order number " this is in a pdf format of which I need if possible

The problem I have is the same pdf file keeps attaching itself to the e-mail no matter which loss order number I select. The reason I think is because the report is called NCR so it is not changing from the previous one. What I think can solve it but not sure and don't know how to do is get the " loss order number " within the report name.

If I just send the report to a file direct it does work as I am prompted for a file name

Can the loss order number get into the file name within a macro/vba

Code:
Name="EMailDatabaseObject
Name="ObjectType">Report
Name="ObjectName">SEND NCR
Name="OutputFormat">PDF Format (*.pdf
Name="To">=[E-mail Address] & IIf(Nz([E-mail Address

View 2 Replies View Related

Modules & VBA :: Setting Variable To Null When It Has A Value?

Jan 21, 2015

I have the following simple variable assignment...

Dim PolRef As String
Let PolRef = Me.Policy_Ref_Num

But when I run my code I get "Invalid Use of Null" and PolRef is set to ""

This field is populated however, so I'm confused to why I cant set my variable to it.

If I don't use a variable and simply have let XXX = me.Me.Policy_Ref_Num then it still wont return the value from the field.

View 5 Replies View Related

Modules & VBA :: How To Get Query Output Into Variable

Mar 15, 2015

I want to get the output of a vba query (only one solution possible) in to a variable but the variable stays empty.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT info FROM evaluationtable WHERE evaluation= " & evaluationchoice & " ")
var = rst(0).Value
rst.Close

some explination: evaluation and info are fields of evaluationtable evaluationchoice is a field in an accessform where I can choice a value from the evaluation field

the table is build as this (only two fields) evaluation - info

View 5 Replies View Related

Modules & VBA :: Str Variable In DLookup Criteria

Aug 18, 2013

Why does this work when the text box is used and not when the variable is used directly?

Code:

Private Sub Command61_Click()
Dim strDBName As String
strDBName = getDBName()
Me.Text59 = strDBName
Me.Text62 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = text59")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = " & strDBName)
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = strDBName")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName]" = strDBName)
End Sub

Text62 returns the correct value
Text64 failes on everyone of the examples

View 7 Replies View Related

Modules & VBA :: How To Construct String For Variable

Sep 10, 2013

I am trying to set the following variable that will be used to create a PDF file in an already existing folder and name the file WorkorderIDddmmyyyy-hhnnss.pdf

The following variable setting creates the filenameddmmyyyy-hhnnssWorkorderID.pdf.

mFilename = "C:RPR AccessPDF Reports Emailed" & Format(Now(), "ddmmyyyy-hhnnss") & WorkorderID & ".pdf"

DoCmd.OutputTo acOutputReport, mReportName, acFormatPDF, mFilename, True

I just can't figure out how to construct it correctly.

Also, is there a way to dynamically create a folder if the folder doesn't already exist? I currently hard code the folder name but would really like to create the folder name based upon some other variable.

View 8 Replies View Related

Modules & VBA :: Load Sub Form From Variable?

Dec 29, 2013

I have a form which load data from variables in record set where i can make insert update and delete i need to load sub form when the form load the problem is that all text boxes in main box load from variable which are in rerecord set and how can navigate using navigation buttons in main form.

View 2 Replies View Related

Modules & VBA :: SQL Code Return Value And Set To Variable?

Sep 4, 2013

Code:
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim SQLstr As String

[Code]....

I created this about 1 hour ago but my laptop crashed and didnt save. So, I open a record set and rs is now loaded with the record I want,

how do I assign the value of "Status" as a vba variable. when I try StatusInt = rs I get the "Type Mismatch" error...

View 4 Replies View Related

Modules & VBA :: Mixing Variable Types

Jul 26, 2013

Dim MooringLines As String
Dim MooringLinesPrice As Double
MooringLines = DLookup("TotalComponent", "ComponentT", "[TotalComponent] = '" & Me.P_MooringLinesCmb & "'")
MooringLinesPrice = DLookup("EuroPerMetre", "ComponentT", "[TotalComponent] = '" & Me.P_MooringLinesCmb & "'")
Me.P_MooringLinesTxt = (MooringLines + " - " + (MooringLinesPrice * Me.P_LengthMLTxt.Value))

In the first variable I'm trying to get a string and combine it with a double in the second. Is it that I can't mix a string and a double in the one variable and if so how do I convert the double to a string?

View 4 Replies View Related

Modules & VBA :: How To Get Portion Of String Into Variable

Jun 19, 2015

How to I get a portion of a string into a variable?

I want to capture the table name of a recordsource.

The problem is sometimes the recordsource is "Select * from mytable where ID = 6" or "Select * from mytable" or "mytable"

I want to put "mytable" in a variable for later use.

View 11 Replies View Related

Modules & VBA :: Matching Field Value To A Variable?

Jul 24, 2014

I'm working on a module which determines which the previous and next task for the part is. To do this the module finds the first record where the previous field says 'none' and puts the following into variables; the ID for the part (WoTasksFID), The routing number (TaskNumber), the job that is being completed at this stage (Shop), and the task order ID (TaskOrderID). I then want it to find the first record which has the same ID for the part and has a TaskNumber that is one smaller (This is where the error in my code occurs). I record the same variables as before (this time with a prefix "Prev") and use the information to fill the previous and next fields for each record set.

Like I said, the error occurs when I am trying to match the WoTasksFID - instead of finding a field with the same WoTasksFID, it seems to choose the first field in the table no matter the value. Here's the function:

Code:
Function PreviousTask()
Dim TaskOrder As Recordset
Dim WoTasksFID As Integer 'For Current Record
Dim TaskNumber As Integer 'For Current Record
Dim Shop As String 'For Current Record

[code].....

What am I doing wrong?

View 6 Replies View Related

Modules & VBA :: Variable Value Not Getting Stored In Function

Sep 8, 2014

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

Modules & VBA :: Variable Retrieving From Another Form

Jan 23, 2014

I am new to access programming.Is it possible to store a variable in one form and then retrieve it to auto fill a field on another form .

I know hot to Dim and store on the same form but trying to retrieve from another form i cant do .

I presume the second form would be something like this OnLoad Variable=forms"customers"customersFK...

View 2 Replies View Related

Modules & VBA :: Variable Declaration Within A Form

Jul 24, 2014

How to declarate variable to working with in a form, subform and subform2

Form-->subform--->subform2

I tried to

Public ...
Private...
Dim....

And always variable working only in Form. I don't want to declarate variable in a separate Module.

View 8 Replies View Related

Modules & VBA :: How To Use Public Variable In Query

Oct 10, 2013

I have a query

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" :/

View 3 Replies View Related

Modules & VBA :: Passing Variable From One Database To Another

Jan 6, 2015

I need to pass a variable from one Access Database to another

The scenario is I have a item number in one database that I need to use to open a form in another database,

I can open the database using vba, currently launching a BAT file that copies the latest version database to a local drive and then opens it,

Is there an easy way to pass the variable after this has completed?

View 1 Replies View Related

Modules & VBA :: Build Loop Into A Variable

Jul 19, 2014

I am relatively new to VBA and have this loop (see below) was wondering if it was possible to build it into a variable, so I could call upon it within an IF statement.

Set ctl = Me.listSeeAllAssets
For Each varItem In ctl.ItemsSelected
rs1.AddNew
rs1!AssetNo = ctl.ItemData(varItem)
rs1!DateOfService = Me.DateOfService

[Code] .....

View 7 Replies View Related

Modules & VBA :: Using Access Recordset As SQL Variable?

Jan 16, 2014

I am trying to create a process which selects a customer's site identifier and uses that to query an Oracle database through an ADODB connection. Which is all well and good until that customer has more than one site.

Here is a simplified version of what I have created:

Code:

'Get site details from current database on basis of company selected on form

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim RsSites As New ADODB.Recordset
RsSites.ActiveConnection = cnn

[code]....

As I said, this works perfectly when the customer only has one site, but I have failed to find a way to turn a recordset with multiple values into a SQL variable that can be used in the IN clause.

I have attempted to convert the recordset to a string using GetString, but could not find a way to correctly seperate out the records.

View 5 Replies View Related







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