Global Variables

Feb 13, 2006

I have an access form called forma. It has a command button. When I click a command button, FormB opens up and FormA closes. The FormB also has a command button. When I click the command button of Form B, it closes and opens FormC. Like this there are total 30 forms. What I need is. I want to count the no. of clicks of the command button in total. Actually each button is a correct option of A multiple choice question. I tried to declare a public Variable called total in the declareation of the first form and tried to use it directly in the succeding forms. It does not capture the values. How can I increase the value of the varialbe declared in the first form even after the form closes and capture till end. Actually I also should be able to send the value in a table. Please Help Me.!

View Replies


ADVERTISEMENT

Global Variables As Criteria

Dec 5, 2005

Anyone know how to reference a global variable as criteria for a query?

I am currently using invisible text boxes on an intermediary form as criteria for my query and it works fine, but this solution seems inellegant and I'm looking for something a little smoother. I just need the correct syntax to reference a variable in the criteria section of a query.

Thanks!

View 1 Replies View Related

Global Variables Lose Values

Jun 7, 2005

i have a form (frm1) and subform (sfrm1) that when a room number is double-clicked in the subform another form (frm2) and subform (sfrm2) loads. frm1 has building info and frm2 is used for inspection info. if a room has never been inspected then when frm2 loads the field [room] will be empty. however, after the first inspection the room number is obviously entered for frm2.

so i have created a global variable that holds the value of [lab_room] from frm1 and is supposed to use this value on frm2 if the room has never been inspected - ie, [room] is null. however, the value of intRoom (global variable) is lost after is leaves frm1 and goes to frm2. below is my code:



modGlobal (module with global variables declared)
Option Compare Database

Global intVar As Variant
Global intRoom As String

Private Declare Function CoCreateGuid Lib "OLE32.DLL" (pGuid) As Long

Private Type GUID 'Memory structure used by CoCreateGuid
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(7) As Byte
End Type

Private Const S_OK = 0 'Return value from CoCreateGuid

frm1 subform double click event
Private Sub Form_DblClick(Cancel As Integer)
On Error GoTo Err_DblClick

Dim stDocName As String
Dim stLinkCriteria As String
Dim stMsg As String

'if the user double clicks on an area that has no lab_id associated with it
'prevents a null value for lab_id
If IsNull(Me!lab_id) Then
stMsg = "There is no lab associated with this room."
intRes = MsgBox(stMsg, vbOKOnly + vbExclamation, "Error - No Lab Selected")
GoTo Exit_DblClick
End If

'opens lab forms associated with this lab_id
intVar = Me!lab_id
intRoom = Me!lab_room
stDocName = "ehs_lab_safety_surveys"
stLinkCriteria = "[lab_id]=" & "'" & intVar & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
[Forms]![ehs_lab_safety_surveys].Form.RecordsetClone.Find ("lab_id = '" & intVar & "'")

DoCmd.Close acForm, "copy_lab_search_by_bldg"

Exit_DblClick:
Exit Sub

Err_DblClick:
MsgBox Err.Description
Resume Exit_DblClick
End Sub

frm2
Private Sub Form_Load()

Dim strNewguid As String

'checks for null values of lab_id, room, id and safety_survey_date. if they are
'null or empty a value is inserted.

If IsNull(Me!lab_id) Or (Me!lab_id = "") Then
Me!lab_id = intVar
End If

If IsNull(Me!room) Then
Me!room.Value = intRoom
End If

If IsNull(Me!safety_survey_date) Then
Me!safety_survey_date = Date
End If

If IsNull(Me!id) Or (Me!id = "") Then
Set x = CreateObject("Scriptlet.TypeLib")
strNewguid = Left(x.GUID, 38)
Me!id.Value = strNewguid
End If

DoCmd.Maximize

End Sub



can anyone see why the value is being lost from frm1 to frm2? i'm ripping my hair out trying to figure this one out....

View 12 Replies View Related

Queries :: Global Variables To Be Used In SQL String

Sep 12, 2014

I have a number of global variables set when a user logs into my application which i want to use in an SQL string to record these in a table however im not sure how i go about it i know simply inserting the defined names in the SQL wont work.

Is there a way to define these in global functions instead?

View 1 Replies View Related

Modules & VBA :: Open Recordsets As Public Or Global Variables

Apr 22, 2015

Is there any way of opening recordsets as public or global in a form? E.g. if i do something like

Set rs = db.OpenRecordset("TblCustomers", dbOpenSnapshot, dbReadOnly)

the variable rs should be available in all the private sub i have on that form, and i should be able to access records without opening database/ record sets for individual subs.

View 6 Replies View Related

Modules & VBA :: Define Global / Public Variables From A Table?

Sep 4, 2013

I would like to declare Global or Public variables from a table so they can be added to or edited easily. I had the following function to do this with Alpha Five but at the moment my lack of knowledge of Access VBA is making this task difficult.

Here is the function that I use in Alpha:

FUNCTION udVars AS A (udTabName AS C )
dim codeStr as c
t=table.open_session(udTabName,file_ro_shared)
t.fetch_first()
while .not. t.fetch_eof()

[Code] ....

It opens a table reads in the records and then makes them into a string the string would look something like this:

"Public EuroRate as Single = 0.885"

I would then need to use this string to declare the variables but not sure what command to use - I was looking at the Eval function last night but couldn't get this to work...

The table would hold strings for all the above so you would have:

My_Var (variable name)
My_Typ (type of variable e.g. Single)
My_Val (value e.g. 0.885)

The table would hold as many variables as required, some would be dates, some paths for making directories and some would be numbers.

View 6 Replies View Related

Global Var

Apr 15, 2008

Quick question that I suddenly have gotten stuck on: How do I use a global var in the query builder grid as a parameter? It keeps putting quotes around it - ?!

View 14 Replies View Related

Global Rename

Jul 20, 2005

Is there a way to rename all my queries at teh sam time.

View 2 Replies View Related

Global Name Query

Mar 10, 2008

I need to creat a global Name Query. I have 4 Tables. Each table has a Unique Name and a name Field, Such as:

Table: Parents
Field: Parents name

Table: Students
Field: Student Names

I need to Create a Query That just has one Field Call Name.

And i need it to pull all the names from the name field in all 4 tables.

PLease help.

View 1 Replies View Related

Global Variable Use

Sep 23, 2005

I have 3 different forms, each belonging to 3 different tables. The user is required to enter data on form 1 then form 2 & then form 3. The records on all the 3 forms has to be attached to the same primary key.
The problem is that the user has to enter the same primary key for each form.
I want to be able to carry the primary key entered on form 1 to form 2 and form 3, so that the user does not have to type it 3 times. How can i accomplish this ? Can i call the last primary key entered on table 1 and automatically pop it up on form 2 & 3 every time form 2 & 3 load?
Thanks

View 2 Replies View Related

Global Change For End Users

Jun 20, 2006

All my users have a shortcut on there computer to the database.

What I like to do is create a new shortcut.

But I don't want to go to each users desk and change the old one to to new one.

There should be someway that you can do a global change.

Anybody have an idea how this can be done?

View 3 Replies View Related

Global Search And Replace

Dec 13, 2006

Hi,
My company has renamed a product. I want to find all the records in all the tables in my database that might have the old name, and replace with the new name. What's the easiest way to do this?

I don't really want to have to manually open each table and do a find.

I can write code to go through all the tabledefs and do a find on each one, but the only way I know to do that is by telling it the field name, and field names where the product name might exist vary.

I suppose I can tell it something like
for each TD in tabledefs
for each F in td.fields
{search and replace}
next f
next t

Is this really the best way? I have a program called SpeedFerret, which I thought would do this, but it apparently only searches the table names, not the actual records in the table.

View 4 Replies View Related

Referencing A Global Variable

May 18, 2005

I am trying to use a global variable in a dlookup but get an error when i try and run the form

Here is the code:

VUserID is my Global Variable.

Private Sub Form_Open(Cancel As Integer)

Dim UsersName As String

Me.WelcomeBox = UsersName

UsersName = DLookup("[Name]", "tbl Password", "[username] = vUserID")

End Sub

Any Ideas

View 1 Replies View Related

Global Variable Question

Oct 2, 2006

Got a problem trying to reference a global variable in the control source of a text box.

If i do a DLookup when the form opens and have the text box value = the result it works fine ie

Private Sub Form_Open(Cancel As Integer)
Text2.Value = DLookup("UserName", "Users", "[UserID]= " & MyUserID)
End Sub

Where MyUserID is the global variable.

if i put
= DLookup("UserName", "Users", "[UserID]= " & MyUserID)
in the control source of Text2 i get #Name? as a result and if i input
= DLookup("UserName", "Users", "[UserID]= 1")
i get the correct result.

As a test i tried to have the control source = my global variable, of another text box control source with the same result.

It looks like i can only reference my global variable within the VB and not in the forms control sources. Is this correct or can you referece the Global variable in the control source.

Shot

View 6 Replies View Related

Using A Global Variable In Outside Query?

Dec 15, 2004

i am having trouble with my where clause.. ca someone take a peek please..

Note: gID is defined globally as integer and tblCheckReq.ID is defined as an auto-number (in table). Thanks in advance!

SELECT tblCheckReq.UserName, tblUsers.UserDept, tblCheckReq.PaymentMethod, tblCheckReq.PaymentDueDate, TRIM(tblCheckReq.Company & " " & tblCheckReq.TaxType & " " & tblCheckReq.PaymentType) AS DISTDETAIL, tblCheckReq.TaxYear, tblStateTaxDepts.To, tblStateTaxDepts.Street, Trim(tblStateTaxDepts.City & ", " & tblStateTaxDepts.State & " " & tblStateTaxDepts.Zip) AS FULLADDR, tblCheckReqAmtsToAccts.GLAccountNo, tblCheckReqAmtsToAccts.Amount, tblCheckReq.ID, tblCheckReq.Approvedby, tblUsers.SalaryCode, tblCheckReq.Date, tblStateTaxDepts.Dept, tblStateTaxDepts.To, tblCheckReq.TaxYear, tblUsers_1.SalaryCode, tblCheckReq.Company, tblCheckReq.TaxType, tblCheckReq.PaymentType
FROM (((tblCheckReq INNER JOIN tblCheckReqAmtsToAccts ON tblCheckReq.ID=tblCheckReqAmtsToAccts.ID) INNER JOIN tblUsers ON tblCheckReq.UserName=tblUsers.UserName) INNER JOIN tblStateTaxDepts ON (tblCheckReq.TaxType=tblStateTaxDepts.TaxType) AND (tblCheckReq.State=tblStateTaxDepts.StateAbrv)) INNER JOIN tblUsers AS tblUsers_1 ON tblCheckReq.Approvedby=tblUsers_1.UserName
WHERE (((tblCheckReq.ID)='" & gID & "'));

View 4 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

Slow Query Using A Global Variable

Aug 9, 2005

Hello,

I have the following query that I set up as a test, and it runs fine:

SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON STATUSHISTORIE.PROBLEM_ID = PROBLEM_DE.PROBLEMNR
WHERE (((STATUSHISTORIE.STATUSDATUM)<#1/1/2005#) AND ((PROBLEM_DE.DATENBEREICH)='SPMO') AND (((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))='K29') AND ((PROBLEM_DE.ERLSTAND)<>"WEIF"))
ORDER BY STATUSHISTORIE.PROBLEM_ID,
STATUSHISTORIE.STATUSDATUM;

I then set up two global variables ( a String and a Date) and respective functions to return them – ReturnE( ) and ReturnKW( ). Now my query looks like this, but takes ages to run:

SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON [STATUSHISTORIE].[PROBLEM_ID]=[ PROBLEM_DE].[PROBLEMNR]
WHERE (((STATUSHISTORIE.STATUSDATUM)<ReturnKW( ) ) AND ((PROBLEM_DE.DATENBEREICH)='SPMO') AND (((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))=ReturnE( ) ) AND ((PROBLEM_DE.ERLSTAND)<>"WEIF"))
ORDER BY [STATUSHISTORIE].[PROBLEM_ID], [STATUSHISTORIE].[STATUSDATUM];

My two public functions that return the global variables look like this:

Public gstrE As String 'global variable: contains E used for query
Public gdatKW As Date

Public Function ReturnE ()
ReturnE = gstrE
End Function

Public Function ReturnKW ()
ReturnKW = gdatKW
End Function


The tables are actually Views set up from an ODBC Data source. Can anyone please tell me why these global variables are causing the traffic jam? :)

Thanks in advance
J

View 6 Replies View Related

Refering To Global Variable In Query

Mar 15, 2006

Hi All,

Can anybody tell me whether it is possible to use a global variable defined in a vb module as part of the criterea in a query, if not whether there is a work around.

I suppose I could temporarily write the data to a table and use it from there, I thought there would be less messing around this way though.

Cheers

View 2 Replies View Related

Global Field For Calculation In A Form

Aug 22, 2005

OK, I figured out how to make a calculation. My big problem now is to have a global field, which would be "beginning balance" and "ending balance". I have 5 employees that share the budget. The budget starts at $1280.00. One employee may buy somthing one day and then another day while another employee may also buy something. I want to be able to put in the amount the employee spent and have it deduct from the balance. But then when I go to another employee and subtract what they spend I want that to subtract from the balance. I want the balance to be visible at all times no matter what employee I am on. Hope this makes sense. I have an attached file that shows how I did it for one employee. Cannot figure how to be able to do it for each employee and just the balance changes. Also I built a subform to be able to show different dates. But the problem again is having it subtract from one global balance. Thank you.

View 3 Replies View Related

Global Variable Time Settings

Feb 3, 2005

i noticed that if access is open for a long time
the globals return null

can i set the time of a global variable?

View 1 Replies View Related

Module Variable Global Or Scoped?

Feb 6, 2008

I have a variable declared in a module as such

Code:Dim tracking_customer As Double

When i use one form to set the variable like

Code:Private Sub Command83_Click()'Set the global variable to this customer number'So it can be called in "ON OPEN" event in URL formtracking_customer = Me![Customer Number]'gets expected number in following lineMsgBox ("Trackign is " & tracking_customer) DoCmd.OpenForm "Url Tracking", acNormalEnd Sub

Now when I open that next form I try grabbing the newly set module variable but I always get the number 0. So I am assuming it loses the number once I change forms for some reason

Code:Dim trackID As DoublePrivate Sub Form_Open(Cancel As Integer)trackID = tracking_customer'Following line always produces 0MsgBox ("hey this is tracking customer number " & trackID)End Sub

Can someone explain this, and let me know what I am doing wrong please.

View 4 Replies View Related

Forms :: Global Variable Not Available When Needed

Oct 21, 2013

I have this declaration in a module called Global Code
Option Compare Database
Public currentCustomerId As Long
Option Explicit

There is one report that simply prints a the firstname of current customer and the amount of each of his invoices.
The current displayed customer's ID is always copied into the variable currentCustomerId.
Below is the SELECT code of the data source of the report.
But whenever I open this report it asks for currentCustomerId.
Is my decalartion not global enough?

SELECT tblCustomers.FirstName, tblInvoice.Amount, FROM tblCustomers INNER JOIN tblInvoices ON tblCustomers.CustomerID=tblInvoices.CustomerID WHERE tblInvoices.CustomerID=currentCustomerId;

Note: I chose to save the current customer's ID in a global variable because, for some reason, when I select the report the Customer form loses the current customer record and goes to the end of the table as if creating a new customer record. This would not be an issue as long as I can retrieve the value in a variable.

View 4 Replies View Related

Modules & VBA :: How To See Value From Form As Global Variable

Apr 16, 2015

I want to use several values entered in form controls as variables within multiple subs triggered by further form edits. I do not want to define the variables in each sub as this will bloat my code, but I am not being successful in declaring my variables outside of an individual sub, and it is the 'Form!' reference that is throwing it I think.

Here are my variables:

Code:
Dim limit0txt, limit1txt, limit2txt, limit3txt As Integer
limit0txt = Forms!F_Samples_CF_FF_FLot!SF_EnviroCountLimitscntrl.Form!Limit0
limit1txt = Forms!F_Samples_CF_FF_FLot!SF_EnviroCountLimitscntrl.Form!Limit1
limit2txt = Forms!F_Samples_CF_FF_FLot!SF_EnviroCountLimitscntrl.Form!Limit2
limit3txt = Forms!F_Samples_CF_FF_FLot!SF_EnviroCountLimitscntrl.Form!Limit3

If I put these at the top of a module I get an "invalid outside procedure" error message. If I put these in their own module as 'Public' I get the same error. how within a forms code module I can make these variables available to as many events as I like?

View 6 Replies View Related

Setting A Couple Of Global Values?

Oct 5, 2015

Right so I have a database which is split. I have two values which are universal and only change occasionally so I don't want them in a table against every record in the database. I have currently set them up as globals in a module I want to set it up so I can draw the values in from the backend database rather than having to edit every single copy of the front end each time.

View 7 Replies View Related

Loading Button Caption To Global Variable

Nov 13, 2013

I have a form with up to 100 buttons generated based on a number of conditions. I am trying to pass the caption of a button to a sub so that I can open the correct forms. When a button is clicked how can I read it's caption. Me.caption is reading the form caption.

View 7 Replies View Related

How To Use IIf With Three Variables

Aug 15, 2006

Hi, here is a query which returns rolling sum for past5days.The resulting table has threee columns,namely,

MyDateFiled ,MyValue,Past5DaySum


SELECT tblData.MyDateField, tblData.MyValue,
(SELECT SUM(a.MyValue)
FROM tblData As a
WHERE DateDiff("d",a.MyDateField,tblData.MyDateField)
Between 0 and 4)
AS Past5DaySum
FROM tblData;

Someone help me in modifying the above query ,so that ,a Type(1,2,3) can be assigned to each Past5DaySum
based on the following criteria.Is,it possible to use IIF function for three variables ?,so as tp get a new table(Mydate,MyValue,PastDaySum,Type)

Type,LeanPeriod,GoodPeriod
1, A<5 , A<20
2, 5<=A<=10, 20<=A<=40
3, A>10, A>40


LeanPeriod=Jun to Nov
GoodPeriod=Dec to May

View 1 Replies View Related







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