Would You Make Common Variables Public?

Oct 9, 2007

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?

View Replies


ADVERTISEMENT

Passing Public Variables

Nov 20, 2007

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.

View 5 Replies View Related

Public Variables, OpenArgs And Compatability Issues

Aug 10, 2005

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!

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

Modules & VBA :: Public Function With Variables Referencing Table Date Fields

May 31, 2014

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

View 5 Replies View Related

Modules & VBA :: Passing Variables To Make Table Query

Sep 29, 2014

Access 2010 vba - I'm trying to pass a start date and end date to a date field in a make table query, and use the 'between' operator on that date field.

So I have a criteria on the date field like this "Between [dtStart] and [dtEnd]" and if I run the query manually it asks for 2 values and then works fine.

Here's the code I'm trying to run:-

Set qdef = db.QueryDefs("qryTest")
qdef.Parameters("dtStart") = StartDate1
qdef.Parameters("dtEnd") = EndDate1
Set rs1 = qdef.OpenRecordset(dbOpenDynaset, dbSeeChanges)

and I get the error "3219 Invalid Operation" on the last line.

View 2 Replies View Related

Common Data

Sep 5, 2005

I have 2 tables called Table1 ( has field called Mydata) and table2 (has Field called YourData).

Mydata has followind data 1, 2, 3 and yourData has 2,3,4

2 and 3 are comman data. how can I write query or type of function I can use to bring comman data.

I have used Union table that brings all record.

View 2 Replies View Related

Public Variable

Sep 16, 2005

Why can't I set a controls default value to a public variable?

View 2 Replies View Related

Cannot Invoke Common Dialog

Nov 27, 2005

I am trying to add a common dialog to my form from the toolbox and get the following error 'you dont have the license required to use this ActiveX control. Is there any way I can sort this out? I am using Access 2002. I am getting desperate and any help would be much appreciated

View 3 Replies View Related

Common Access Errors

Sep 27, 2006

Hi,

I did a search on common access errors and other errors that you would want to trap and got the following results:


Cannot update. Database or object is read-only
Operation must use an updateable query
General error Unable to open registry key
Could not find file
Could not use '(unknown)'; file already in use
Table 'tblTable' is exclusively locked by user 'Admin' on machine 'MyMachine'
Too few parameters. Expected 1
Either BOF or EOF is True, or the current record has been deleted
Item cannot be found in the collection corresponding to the requested name or ordinal
The search key was not found in any record


If you know any extra errors, can you please add them to this list?

Regards,
B

View 6 Replies View Related

How Do You Find The Most Common Value In A Field

Mar 27, 2008

I am OK with a number field as I can put it in Excel and use =Mode().

But that wont do text such as lastname, suburb etc. I can do suburb by doing postcode but no such equivalent from some others I want to do

View 4 Replies View Related

Common Reg. Exp. For Validation Rules

Oct 12, 2004

Hey there,

Is there a listing anywhere online for common regular expressions for validations rules? In other words, is there something that shows how to construct validation rules for things like phone numbers, addresses, postal/zip codes, serial numbers, math equations, etc. Two in particular that I'm looking for are phone numbers and postal/zip codes. Every time I try to wrap my head around the formula for a phone number (with area codes, an optional 1 in the begining, making sure no leading zeroes, making brackets around area codes optional, etc.) I only get a headache :mad: . I'll get it eventually, but a quick reference in the mean time and for future use would be very helpful.


Thanks so much,

Gibby :)

View 2 Replies View Related

Public Function For Vbcrlf

Apr 5, 2008

I'm new to VB and am learing slowly with all your great help in this forum.

I'm wondering if its possible to write a Public Function for vbCRLF. I have a field in multiple forms that are all named 'Note'. If I have the following code:

Public Function PubNoteLF()
On Error GoTo PubNoteLF_Err

My!Note = My!Note & vbCrLf

PubNoteLF_Exit
Exit Function

PubNoteLF_Err
MsgBox Error$
Resume PubNoteLF_Exit

End Function

When I run the code I get Compile Error: 'Sub or function not defined.'

Can someone tell me what I'm doing wrong? Its problably something real basic, but I'm stumped. Thanks.

View 7 Replies View Related

Startup Module For Common Code

Oct 22, 2007

Is there a way, similar to Excel's "personal.xls", to put all my reuseable Access code into one central module?
Thanks in advance.

View 7 Replies View Related

Two Tables With No Primary Key But A Common Field

Jun 15, 2006

Hi guys,

I am having problem with a query.

I have to tables. One named wire and the other named Input.
As my title says there is not unique ID for the tables to relate.. the only thing to relate is one ID where is not unique for all the rows

In the Wire Table I have 3 rows using the same ID, lets say 123.
In the Input Table I have 1 row using the same ID as above, i.e. 123

I need to select a couple of fields from Wire Table and 1 field from Input Table
The only relationship I have is the ID 123.... since the ID in Wire Table is repeated in 3 rows.... I would like to use the same value from the Input Table to be the same for the 3 rows in the other table.. Below is an example:

Wire Table:

ID Group
123 A
123 B
123 C

Input Table
ID Description
123 Analog


My wanted result would be

Group Description
A Analog
B Analog
C Analog


I tried using the Left join or Join function, but somehow the resulting table has many duplicates of the rows...such as:

Group Description
A Analog
A Analog
A Analog
A Analog
A Analog
B Analog
B Analog
B Analog
B Analog
...

Any Idea on whats going on and how can i solve this problem????

Thanks in advance. Your Help is much appreciated

View 1 Replies View Related

Trying To Use A Common File DIalog To Update OLE

Dec 1, 2006

I have my form where each records image displays as you scroll through the records. I want to add a control similar to the file dialog control, where users can browse to an image file on their machine, and then preview it and Update it.

Can anyone walk me thru how to do this?

View 1 Replies View Related

Common Dialog Box In MS Access Form

Jul 15, 2005

Is there anyway to put common dialog box in MS Access Form. There is an option from ActiveX controls icon, but gives an error message, saying I do not have licence to use it ?

View 8 Replies View Related

Can Open Several Forms With One Common Button??

Jan 23, 2008

Hi all,
I am new to microsoft access. I've got one question that can't solve.
I am making database with several items. Under each item, there got different field lists to fill in. So I made several forms for each of them.
In main form, I put one combo box to choose the item and one common button. So how can I write the code to open the several different forms,one at a time, by clicking one button according to the value that is chosen from combo box??
thank in advance.

View 7 Replies View Related

Modules & VBA :: Greatest Common Divisor

Sep 16, 2014

How to find Greatest common divisor in MS Access?Have a 2 columns in my form: 1) amount; 2) GCD

E.G

amount | GCD
1200
500
400
100

Should be after runing code

amount | GCD
1200 | 12
500 | 5
400 | 4
100 | 1

I was looking form some function but I found only for Ms Excel :/

View 11 Replies View Related

Database Onto The Internet For Public Viewing

May 1, 2005

Hi all,
I have done a multi user database for a private company (and thanks to all those that helped me out) but now I need to do another database to put onto the Internet so the general public can view it and order the products. Obviously some of the database needs to be private (so I can update records) but some needs to be viewed over the Internet. I would like the general public to be able to view the products over a web page, not download a program to view the database.

I have made the database already so I can pull up a web page (from within access) and order the products - all I need to know is how to do this over the Internet?

I don't expect anyone to hold my hand through this, I just ask if anyone has a link to a walkthrough so I can do this?

Regards, James

PS Have another question -
I have 17, 500 records and I wish to add a new Field, how can I change the values of the Field without going thorugh them one by one? e.g If the Record has 'Rarity = U' then I want the 'price' (which is the new field) to equal 1.

View 8 Replies View Related

Calling A Public Function From A SubReport

Oct 2, 2004

I haev a report with 15 sub reports. I have to use Visual Basic to format the sub reports. I know how to do the formatting (see below) but what I would like to do is have one function (Public) that I can pass the subReport name to and have the formatting happen. This would be mush easier to maintain than copying the code below into on_print events of all 15 sub reports.

Has anyone done this and have an example??

Thanks

--------------------------------------------------------------------------------------
Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
Dim widthOfBox, startLeftSide
startLeftSide = 0.017
widthOfBox = 0.21
Me.ScaleMode = 1
Me.ForeColor = 0
'Repeat the following line of code for each vertical line
' 1*1440 represents 1 inch

' Me.Line (0 * 1440, 0)-(0 * 1440, 14400) 'Draws line at Left Margin
Me.Line ((startLeftSide * 1440) + widthOfBox * 0 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 0 * 1440, 14400) 'Draws line at Left Margin
Me.Line ((startLeftSide * 1440) + widthOfBox * 1 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 1 * 1440, 14400) ' Draws next line
Me.Line ((startLeftSide * 1440) + widthOfBox * 2 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 2 * 1440, 14400) ' Draws next line
Me.Line ((startLeftSide * 1440) + widthOfBox * 3 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 3 * 1440, 14400) ' Draws next line
Me.Line ((startLeftSide * 1440) + widthOfBox * 4 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 4 * 1440, 14400) ' Draws next line
'the 14400 is an arbitrary number to increase the line to the max of a
'section.
End Sub

View 1 Replies View Related

Queries :: VBA Public Function As Parameter?

Feb 13, 2015

I have a query that I'm working on through Access 2010's design view. I'd like to add a criteria to the query where it only shows results with the employee name column matching a global variable I created that stores the name of the currently logged in employee.

Here's my vba code that declares the global variable and the public function i'm trying to pass as criteria in the query:

Global gbl_loginName As String
Public Function returnName() As String
If IsNull(gbl_loginName) Then
returnName = "test" ' dummy account created for development only
Else
returnName = gbl_loginName
End If
End Function

and here's the SQL code from Access's design view:

SELECT [Entry of Hours].WC, [Entry of Hours].[Employee Name],
[Entry of Hours].[Set Up Time], [Entry of Hours].[Run time],
[Entry of Hours].[Traveler Number], [Entry of Hours].[Entry Date],
[Entry of Hours].[Quantity Finished], [Entry of Hours].Notes, [Entry of Hours].WPS,

[Code] ....

when I try running the query, however, I get this error:

'returnName' is not a recognized built-in function name

Is there a problem with using public functions in Access' design view?

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

Matching (find All In Common) Two Tables Of Titles

Aug 12, 2005

Hi,

I have two tables of titles (DVDs and CDs). Each table has a price associated with each title in an adjacent column. I would like to match the titles between them and compare prices. Any help would be greatly appreciated. Thanks. EDS

View 2 Replies View Related

Common Data In Two Tables Of Different Size, How Do I Append From One To The Other..?

Jul 23, 2006

Hello,

Firstly I would like to state that i have very little knowledge of Access databases (my job is not computer related) but I am trying my best to get a few issues sorted...
So here it goes: I have 2 tables, one large table with about 3-4000 rows(horizontal lines), and a smaller table with only about 1000 rows.

The rows in the big table contain Products (name, dates, adresses, phone numbers etc) and every product has a 'Codename' in a column-line (vertical)
of the big table. (there are about 3000-4000 products)
In the small table there exists a similar column-line (vertical) that contains 'Codenames' of the Products listed in the big table, but there are only ~ 1000 'Codenames' not the full 4000 ! The reason for this is that in the small table there exists another column-line(vertical) that for every code listed (in the small table) contains a certain specific "description".

My task is to get all the 1000 "descriptions" from the small table inserted into the Big table accordingly to their proper 'Codename'. This means createing a new column in the big table and: if the codename exists in small-table insert the description in the new column-cell, if the codename doesnt exist in small-table leave new-cell empty...

How do i do this ? :) Im guessing some coding is envolved.. and i have no clue whatsoever....
Thank you,
George P.

View 2 Replies View Related







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