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.
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.
I have a table with only two fields and one record: BegDate and EndDate (beginning and end date of the reporting period respectively). I also made a function with variables that look up those values for use as a date parameter in a query.
Here is the code:
Option Compare Database
Option Explicit
Public Function getCurrentRepDates() As Date
Dim dtBegDate As Date
dtBegDate = DLookup("BegDate", "tblCurrentRepDates")
[Code] ....
I am getting a syntax error for the line marked red. How can I use "Between" function in VBA code? Access 2010
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 2 Replies View RelatedAnyone 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!
I'm trying to use a RecordSet to pull data out of an Access DB. I need to compare a field in the DB to a text box in my form. This is what I have so far.
Dim Hardware As Database
Dim Records As Recordset
Dim strSQL As String
Set Hardware = OpenDatabase("c:hardware.mdb")
Serial.SetFocus
strSQL = "SELECT * FROM Computers WHERE Computers.Serial = " & Serial.Text
Set Records = Hardware.OpenRecordset(strSQL)
When I try to run this I get a data type mismatch. Some explanation.
Computers is a table, Computers.Serial is a field in that table. The "Serial" in Serial.Text is a field in my form. You may ask why I'm using the "&" operator instead of just putting it all in the string. The reason is that I cannot figure out how to get VB to treat "Serial.text" as a variable and not a string literal. When I try to put it in single quotes (') I get an error saying the OpenRecordSet call requires an argument, and I'm not providing one, meaning I'm quoting it wrong somehow. Any ideas?
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....
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?
I'm into one of the subforms that will be using the public variable from the main form and am not having success passing the data from the main form over. on the subform, i created a textbox (txt_currentyear) w/ this in the control source field: =[WrkYear] & " Golf Outing"
WrkYear was the defined in a module as:
**********
Option Compare Database
Option Explicit
Public WrkYear As String
**********
I have created a listbox w/ a few years in it (current_year_listbox). this has =[WrkYear]=Me.Current_Year_listbox.Value in the after update field. i loaded the main form, selected the year, went into the subform to see if it passed the data along w/ no luck.
do i need to call or reference that module in every form or report before i can use the data from it? right now, i just get an empty field on the subform.
It just occurred to me that I use certain variables in several of my routines. Maybe you've done the same... something like:
Dim db as DAO.Database
Dim rst as DAO.recordset
Dim qdf as DAO.QueryDef
Now, if it's re-used in many routines, wouldn't it make more sense to make it a public variable, and set them to whatever you need it for a certain routine? Would it be more efficient than having to re-initializing and discarding it for every routine that used it? What problem would one expect from making them public?
Also, since it's very common to set db = CurrentDb, would it also make sense to set it publicly, and for cases where you aren't using CurrentDb, use a private variable just for that one?
Hi all,
I seem to have encountered a strange problem at work.
I declared a public variable for user department. The user selects his department from a Log On form and if the password is correct, he opens another form with the department variable passed on in openargs.
All of this works fine in access2k but upon testing with 2003, the openargs somehow do not seem to be being passed on. Maybe I am being dense somewhere but it is kinda puzzling for me.
I am not using access's security for users and groups because I am trying to reduce the complexity of the whole project (I wont be around to maintain it next time).
Any help rendered is greatly appreciated!
Hi all,
Anyone know how to open 2 recordsets eg recordsets of 2 tables at same time, and also how to update 2 different fields at once,eg in one line, at the moment I can only open a single recordset and I have 2 docmd.runsql "update etc" commands
Dim TableName As String
Dim dbname As String
dbname = "c:studentswithdisabilities"
TableName = "InputStudent"
Dim CustDB As Database, CustTable As Recordset
Set CustDB = DBEngine.Workspaces(0).OpenDatabase(dbname)
Set CustTable = CustDB.OpenRecordset(TableName, dbOpenTable)
DoCmd.RunSQL "UPDATE InputStudent SET InputStudent.photoBalance = InputStudent!photoBalance-[Forms]![photocopy]![txttop] WHERE [InputStudent]![TNumber]=[Forms]![photocopy]![ComboTnum];"
DoCmd.RunSQL "UPDATE PhotocopyTable SET PhotocopyTable.RemainingBalance = [PhotocopyTable]![StartingBalance]-[PhotocopyTable]![TopupAmount];"
When I run this code using the (Add Record) click event on a button, it performs the first update but it doesnt perform the second until I add the next data record,
Any help much appreciated
I have a form based on an underlying query in a .mdb database. I want to be able to navigate backwards and forwards in the form using .movenext and .moveprevious etc.
Which sort of recordset should I use? I've seen discussions about ADO and DAO, and have come across the term "forward-only", which I'd not been aware of before.
Having made the choice, what is the correct syntax for opening the appropriate recordset, and also for .movenext and .moveprevious etc?
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 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?
When I try to compile the code below I get the error message 'Sub or Function not defined'. I'm using Access 2007.
Code:
For x = 0 To 20
If Nz(rec1(x), "") <> Nz(rec2(x), "") Then
DoCmd.SetWarnings False
strSQL = "INSERT INTO tblResults ([Version], CUSTOMER_ID, CUSTOMER_NAME, CUST_LONG_NAME,
[Code] ....
What I'm trying to do is compare two recorsdsets and if it finds a difference, record the result line from both recordsets in a new table. This worked fine until I tried to go beyond more than 21 records from recordset 2 to publish to the Results table.
The problem arises in the 2nd INSERT INTO... statement. I get the error and it highlights the rec2(20) value. I can enter anything after it (it doesn't have to be a recordset value) but the error still occurs and it wont compile. If I close the statement at Rec2(20) it works fine.
I have 3 tables; tblProjects, tblTesting, and tblEmployees.
The issue I'm having is that I've created a form which collects testing information from testers that test a particular project. Each project can have multiple testers. tblProject and tblTesters have a one to many relationship and the form (frmTesting) consists of a main form for project info and a subfrom for testing info.
For each project tested there are 4 possible outcomes. 1st not all testers have tested to project and that case project status is "InTesting", 2nd all testers pass testing in that case project staus is "Waiting Final Approval", 3rd all testers fail testing in that case project status is "Maintenance" and last some testers pass and some fail in that case project status is "IPR".
Here is my code for a button I've placed on the sub form.
Private Sub BtnTest_Click()
Dim db As DAO.Database
Dim rsSQL As DAO.Recordset
Dim intMax As Integer
Dim strSQL As String
Dim csSQL As String
Set db = CurrentDb()
[Code] ....
My select statement for my recordset isn't working and I've tested it by putting a specific project number in the statement and I still only pulling one recordset.
I have parameter form that ask for state and begining date. on click it runs a query and creates a report by state that has multiple pages.. I want to create a separate pdf file for is page (which would be by delegate_name).
This is the coding I have so far and am gettting the error '424' object required. This is the coding I currently have. I haven't worked with access in over 9 yrs so am a little rusty with it.
StateAbb = Me.State
PeriodStartDate = Format(Me.Period_start_date, "MM-DD-YYYY")
Dim strSQL As String
Dim db As Database
Dim rs As Recordset
Dim strRptName As String
[Code] ....
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" :/
Is it possible to open another database, set a Public boolean variable in a module?
in db1: Open database by Access.Application, OpenCurrentDatabase, setting obj to db2.CurrentProject, loop through main objects
in db2: Public ByPassCloseVar As Boolean is in a module ModLinkTblReview
I have a process that opens up a database runs through all of its objects to get their properties and values. There is a form that is set up on Unload to close the tool if the variable is set to False. When I open the form to get the properties and their values, then close the form, it closes the database. I would like to set the variable to true in order for the database to stay open.
Thought something like this would work: db2.Modules!ModLinkTblReview.ByPassCloseVar = True
Indicates that method or data member does not exist.
I am trying to read the value of a control on an open form from a Public Function in a Module with no luck. I suspect the issue is syntax.
Code:
Public Function MyFunction(varFormName As String, varControlName As String)
If Forms(varFormName).Controls(varControlName) = True Then ' = 0 even when control is -1
Else
End If
End Function
The variables pass through fine but the If statement equals zero regardless of the state of the form's control. I tried several variations to no avail.
If Forms(varFormName).Form.Controls(varContactControl ) = True Then
If Forms(varFormName)!Form.Controls(varContactControl ) = True Then
Etc.
I have an issue with this case select below. The DelayStart is time so lets say I put in the debug window
?DatePart("h", #04:00pm#)
The result would be 16 which is correct BUT the issue is my second shift starts at 04:01pm and the result is still 16 untill 5pm. How do I fix this so I get the correct shifts? Im guesing use something other than DatePart but what?
'Daylight 6:00am - 4:00pm
'Afternoon 4:00pm - 2:00am
'Midnight 2:00am - 6:00am
Code:
Public Function getShiftForRecord(DelayStart As Variant)
On Error Resume Next
Select Case DatePart("h", DelayStart)
Case 6 To 16 'Daylight 6:00am - 4:00pm
[Code] ....
I have a module that has a global variable defined as shown below
Module: initGlobals
-----------------------
Code:
Option Compare Database
Option Explicit
Public strGlobalUserId As String
Sub initvar()
strGlobalUserId = "Myuserid"
End Sub
This is invoked in a login form("frmLogin") and it displays the above value before I replace it with the one that is accepted in the form.
Here is the code from the login form
Code:
strGlobalUserId = Me.cboEmployee.Value
When the password is successful, I close the login form and open a form called "frmMainForm". In the form_open event of the "frmMainForm", I am trying to display the user id using strGlobalUserId but it just has spaces. What am I doing wrong?
I have a backup subroutine which automatically triggers when the front-end is closed down (it just takes the back-end, makes a copy and compacts it).It's driven off a hidden form which I use to track who is connected to the BE at any given time. This form is opened as part of the AutoExec when the FE is opened and writes some basic user info to a table. When the form is closed, it updates the table and fires the backup process before quitting Access.
Part of that user tracking process checks to see if the same user is already connected - either elsewhere (i.e. on a different machine) or on the same machine (i.e. opening a second instance of the same FE) - which is undesirable (and, frankly, unlikely, but not impossible) A brief prompt appears to explain that they can only be connected once, at which point the application is quit (to enforce the rule) This also works fine.
I would add a public boolean variable, set it to True by default, then switch it to False if the same user is already logged on before quitting Access.Trouble is, the variable doesn't seem to be holding its value? Here is the Load event for the tracking form :
Code:
Private Sub Form_Load()
On Error GoTo ErrorHandler
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
Call InterfaceInitialise
blnPerformBackup = True
[code]...
For some reason, blnPerformBackup is False every time, even though I set it to True at the very start of the Load event (and it is a Public variable, defined in a separate module where I store all my Public constants and variables)
why it is not retaining its value from the Load event? I've checked and it does get set to True - and when I debug, it remains True - but at runtime it reverts back to False by the time it reaches the decision whether to backup or not?
I have built an access application that contains a set of buttons along the top of every form that serve as navigation.* These buttons each perform the same function on every form they are on. (menu opens the main menu, etc) I have database macros to assign each button the same function but I still have to go through each form and manually assign them. I was wondering if it was possible to define a public function that on db open will look for all buttons with a certain name and assign them the macro. (so all buttons called cmdmainmenu will have the OpenMainMenu macro assigned and so on).Before you go there, I have already tried the navigation form and set all forms as subforms.
View 5 Replies View RelatedQuestion 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.