I have some code in a form that calls for a public function and passes on some values. the function makes some calculations and assigns a value to the variable "percent" in the function itself.
How can I get the value of this variable back in the form so I can use it?
Form:
Code:
Private Sub Form_Open(Cancel As Integer)
Dim Table As String, TotalFields As Single
Table = "tblAdmission"
[Code]....
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!!
I use a macro that attaches a document automatically to an e-mail address ready to send this report is governed by a query of which the criteria is " loss order number " this is in a pdf format of which I need if possible
The problem I have is the same pdf file keeps attaching itself to the e-mail no matter which loss order number I select. The reason I think is because the report is called NCR so it is not changing from the previous one. What I think can solve it but not sure and don't know how to do is get the " loss order number " within the report name.
If I just send the report to a file direct it does work as I am prompted for a file name
Can the loss order number get into the file name within a macro/vba
Code:
Name="EMailDatabaseObject
Name="ObjectType">Report
Name="ObjectName">SEND NCR
Name="OutputFormat">PDF Format (*.pdf
Name="To">=[E-mail Address] & IIf(Nz([E-mail Address
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
I defined a public variable and then for being able to filter a query results, assigned it to a public function. The problem is that function doesn't get the variable's value.
View 14 Replies View RelatedI 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]....
I am amending some Code I found online for an audit table, I need to store additional information in the table that is associated with some forms but not others.
I have researched about putting optional variables in, but I read this only works with the type VARIANT.
Is there a way to make a string optional as my fields contain text?
here is my code so far:
Sub AuditChanges(IDField As String, UserAction As String, Optional UserID As String, Optional DeviceID As String, Optional SimID As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
[Code]...
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.
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?
I have a form with fields that contain the address/postcode etc of the person in question, and I also have a template letter that needs to have said persons address/postcode etc at the sending stage after a button is clicked.
View 9 Replies View RelatedError 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 hit a roadblock trying to send data to excel. I have created a table of Arrays where each record has an ArrayID, a record ID, an X unit and a Y unit. I want to be able to pick an ArrayID from a form and send the corresponding X and Y values to excel as part of a Linest calculation.
View 3 Replies View RelatedI have a form which load data from variables in record set where i can make insert update and delete i need to load sub form when the form load the problem is that all text boxes in main box load from variable which are in rerecord set and how can navigate using navigation buttons in main form.
View 2 Replies View RelatedI am new to access programming.Is it possible to store a variable in one form and then retrieve it to auto fill a field on another form .
I know hot to Dim and store on the same form but trying to retrieve from another form i cant do .
I presume the second form would be something like this OnLoad Variable=forms"customers"customersFK...
How to declarate variable to working with in a form, subform and subform2
Form-->subform--->subform2
I tried to
Public ...
Private...
Dim....
And always variable working only in Form. I don't want to declarate variable in a separate Module.
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 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?
Can i make an html of login form, whose data is saved in access as back end. I am curious to know. but of course simultaneous saving is considered yes in here...
View 1 Replies View RelatedI 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?
An instance of a form can be opened with:
Dim frm As Form
Set frm = New Form_formname
How can this be done using a variable as the formname?
I manage an Access application that in many instances uses data selected from a combo on a form for variable criteria. In this instance it is in the form of:
[Forms]![Main Navigation]![Print Menu]![SchoolYear]
I recently wrote a routing that exports to Excel based on a record set derived from a query. In testing I hard-coded the criteria (School Year) in the query. Once everything worked I sustituted the variable above. Now, in the VBA, no records are put into the recordset, when I run the queries directly from Access they work correctly, drawing the results for the school year selected on the form referenced.
'Create The Recordset
If Me.Frame11 = 1 Then
strQueryName = "ExcelHS"
GroupTitle = "High School"
Else
strQueryName = "ExcelMS"
GroupTitle = "Middle School"
End If
Set objRst = Application.CurrentDb.OpenRecordset(strQueryName)
I wanna create a button in form which can allow me to import my data base file with some tables , i would select just the variable that i need in the table not all of them .
View 2 Replies View RelatedI some code that is reading the first 32 lines of a text file and paste the results into 1 text box on individual lines. I would like each line to go into an individual text box.
I have 32 text boxes named txt_1, txt_2, txt_3
I have a variable named ThisLine holding the line of the text file.
I have a variable named textBoxName holding the name of the control.
Need to putting these together to write the line to a text box .
Code:
Sub ReadFile()
Me.txt_newData = ""
Me.txt_OrigData = ""
'again, we need this strange thing to exist so that ...
Dim fso As New FileSystemObject
[Code] .....
Is it possible (and how...) to declare a module-specific form variable (or any variable for that matter) at the top of said module, so it doesn't need to be set at the start of each subsequent procedure?
I have a module of code specific to one form with a number of procedures, each one of which requires me to Dim / Set the form variable. It would be much neater if I could do it once at the start.
Code:
Option Compare Database
Option Explicit
Public Sub Populate(lngParameter As Long)Dim frm As Form
[Code] ......
I want to make a loop like this:
Dim var1 As Variant, var2 As Variant, var3 As Variant, var4 As Variant
DIm var5 As Variant, var6 As Variant, var7 As Variant, var8 As Variant
Dim var9 As Variant, var10 As Variant
Dim i1 As Long
i1 = 1
Do Until i1 > 10
var & i1 ??? = "0" & i1 & "." & txt1 & "." & txt2
i1 = i1 + 1
Loop
How to make concatenate var + i1 to make loop function?