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? :)
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.
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
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.
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: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.
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.
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?
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.
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 - ?!
I am creating a 2 level report to confirm an order. Main report already created, runs successfully called as subform/subreport under "OrderDetails" form. Linked to master using Order.ID. There are two versions of the confirmation report that have different layouts for different program types.
The hangup comes when I try to add a "Class Dates" subreport. It lists dates of individual classes and Skip dates. I have created the subreport as "srClassDates". When I add it to the main report, it lists the records. However, when I try to link it to the Main report, an error message box appears with the "object variable or With block variable not set".
I have tried rebuilding both the main and subreports, rebuilt the query, have not found anything that changes the result.
Linker has been working successfully on other subforms. Report with groupings works fine, but I need data from 2 tables both linked to order.id.
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.!
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.
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.
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 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
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.
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?
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.
