Modules & VBA :: Set A Public Variable In Another Database To True

Apr 14, 2015

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.

View Replies


ADVERTISEMENT

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 :: Unable To Get The Public Variable To Work

Mar 27, 2015

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?

View 2 Replies View Related

Modules & VBA :: Public Boolean Variable Not Retaining Value

Oct 9, 2014

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?

View 9 Replies View Related

Modules & VBA :: Public Type (variable) For Function To Return More Than One Value

Sep 23, 2014

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

View 5 Replies View Related

Modules & VBA :: Public Variable Passed Back To Private Sub

Sep 26, 2014

I've got a private sub that runs when the form loads. I want it to get the id of the record which has opened this new form.

The public sub populates the variable but it's Empty going back into my private sub. I'm sure its a simple school boy error but it has me stumped!

I want it public as I need to keep that id as new records are added in the form.

Private Sub Form_Load()
FRStudentFundingRequestID
'fundingrequest = FundinRequestID
Me.txtFKFReq = FundingRequest
End Sub
__________________________________
Public Sub FRStudentFundingRequestID()
FundingRequest = [Forms]![frmFundingRequest]![txtpk]
End Sub

View 3 Replies View Related

Modules & VBA :: Declare Public Variable And Assign A Default Value

Nov 7, 2013

I want to define a public variable and i am using the following code but it gives me Compile error Invalid outside procedure.

Code:
option explicit
public ABC1 AS VARIABLE
ABC1="FALSE"

View 7 Replies View Related

Modules & VBA :: Using Public Function To Feed A Variable String To Query

Feb 26, 2014

I am using a public function to feed a variable string to a query. So far I have got:

Code:
Public Function ClientStreetModule(firstLVar As Variant, streetVar As Variant, newFL As Variant) As String
Dim cslStr1 As String, newStreet As String
newStreet = Right(streetVar, Len(streetVar) - Len(newFL))

[code]....

However, I only need to use newStreet as the true part of iif, in which instance all are longer. At least I think this is the problem. I realise I might need to use NZ but am not sure how. Why it is evaluating and giving errors for all records and not just when the iif criteria is true as I want it to?

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

Forms :: Display A Public Variable On A Form

Apr 3, 2013

I am working on a database where I want some specific information displayed on a form, preferably on the form header. I want to display this on all of my forms. In a standard module, I have a code segment that runs a query returning values, and I assign those values to my public variables. I run this code segment triggered by "on activate" of each form.

The variables contain an Event Date, and an Event Name. I want this information to appear on each form. How do I get this data onto my form as part of the form header, or if cannot put on header section, then in the detail section?

View 2 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 :: Open One Database Form Another And Pass Variable To It

Feb 19, 2014

I have a button on my main db, that opens a second db using hte following code:

----------------------
'Dealing with external objects, use inline error trapping
On Error Resume Next
Dim appAccess As Object

Dim db As Database
Dim strAppPathName As String
Dim strAppName As String
Dim strTimesheetPathName As String

[Code] ....

This code works great to open the other db, and handles wheter the other db is already open or not, but I cant seem to pass the variable to the other db using the startup switch /cmd.

I suspect if I used the shell method it would pass the cmd , but I havnt found any way to test if the db is already open with the shell method.

How can I pass a variable to the other db when opening it using VBA?

View 10 Replies View Related

Modules & VBA :: Open Another Database And Send Variable Back

Apr 24, 2015

I have code that from DB1 opens DB2 and runs a Function in DB2.

The DB2 function produces a Boolean result that i am trying to get back to DB1 without success. Below is the script.

Sub Test22()
Dim AC As Object
Set AC = CreateObject("Access.Application")
rc = "K:ARSHRAutomation_ProjectsMikeFEDB2.accd b"
AC.OpenCurrentDatabase (rc)
AC.Visible = True
AC.Run "SendVariable"
End Sub

Because i am using AC.Run "SendVariable" i cant find a way to get the value of SendVariable!!

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

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 :: Reading Control Value From Public Function?

Oct 4, 2013

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.

View 4 Replies View Related

Modules & VBA :: Case Select Public Function

Jun 4, 2014

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

View 11 Replies View Related

Modules & VBA :: Public Function To Assign A Macro To All Buttons With A Certain Name

Jan 23, 2014

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 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 :: Public Function - Giving Control Name As Argument

Oct 22, 2013

I am designing a Public Function F()

I want this function to populate value from a any field selected from any table to any text box in any form ...... Lets say in a Database named TestDB we have a Table named tblTest , a Form named frmTest and in this form( frmTest ) we have one Bound Combo Box named cmbTest and one unbound TextBox named txtTest

We assume that the table tblTest has three fields : TestID , FName and LName .

We also assume that there are already some records in the Table tblTest .

If the function F() is already programmed it should take as arguments as it follows :

F(FormName as??? ,ControlName as ???, TableName as ???, FieldName as ???, ID_Field_Name_of_the_Table as ???, Combo_box_selected_ID as ???)

In result the function should (probably) DLookup (FieldName , TableName , ID_Field_Name_of_the_Table = Combo_box_selected_ID ) and then store the value in a variable ( probably Variant ???) , lets say called varSetValue.

The problem is IF this is the correct way to handle that issue , than what should i do next.... I really don't know how to pass a Control's name to a function from the Event module of the ComboBox -

I mean - how can I obtain the value so it is usable for the function .... What data type should be the function arguments so I can use them to set a value for a control in any form .

In example : If the Dlookup() is somehow successfull then I want to assign the varSetValue to the txtTest what should I do : FormName.ControlName.Value = varSetValue >??????

I have read a lot materials but couldn't find a good answer for that , and aswell what data type should be the arguments that the function accepts and how do I obtain Controls and Forms names so I could use them in the function as described above .

View 4 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 :: 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 :: Calling Public Function To Backup / Compact Back End?

Dec 23, 2013

I have the following function that I found online. Unfortunately I can't remember where I got it since I've had it for a little while. Today, when I tried to actually put it to use it didn't work.

I'm calling it from a form as follows: CompactDB (tblHotword)

tblHotword is just a random table from the back end. My understanding of the function was that it would use that table to connect and get the file name of the back end.

Whenever I run it, Access pops up a window that says "Object required" and nothing else. It doesn't look like a standard error message popup. When I click 'OK', Access continues with the rest of the code as if nothing went wrong. The function doesn't run though.

Code:
Public Function CompactDB(TableName As String) As Boolean
On Error GoTo Err_CompactDB
Dim stFileName
DoCmd.Hourglass True
stFileName = db.TableDefs(TableName).Connect

[code]....

View 8 Replies View Related

Modules & VBA :: How To Change Controlsource Of Multiple Textboxes With Public Function

Jun 11, 2013

I have multiple reports that use similar IIF statements as the controlsource for four textboxes. Naturally, I don't want to have to update twelve controlsources if any of the calculations change, so I thought I'd make this a public function. However, I don't know how to pass along multiple textboxes as variables. Here's what I have so far:

Code:
Option Compare Database
Public Function txtColor(ByRef textbox As Control)
Dim str1, str2, str3, str4 As String
'The IIf statement is simplified for this example. It's not important.
str1 = "=IIf(IsNull([Inquiry start date]),'W',IIf([txtInquiry]<1 And IsNull([Inquiry end date]),'R'))"

[Code] ....

And this is in the report (where ??? is what I'm asking about)

Code:
Private Sub Report_Load()
Call txtColor(???)
End Sub

The error I get is "Runtime 424 Object Required"

View 7 Replies View Related

Tick Checkbox If Database Is True

Dec 3, 2006

Hi,
in my web page, i would like to user to see a ticked checkbox should the database = True however, i cannot seems to be getting it. Would anybody be able to help me out?

strSQL = "SELECT DeptHeadA FROM Employee WHERE EmpId = '" & strEmpId & "'"
nRecDHA = GetRecordset(strSQL, arrDHA)

if arrDHA(0,i) = True then
arrDHA(0,i) = "Checked"
else
arrDHA(0,i) = ""
end if

<tr>
<TD valign=top width="27%"><font size="2"><b>Department Head Alternate (1st) :</b></font></TD>
<td width="72%">
<input type="checkbox" name="DeptHeadA" value="<%=arrDHA(0,i)%>"></td>
</tr>

View 2 Replies View Related







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