I 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...
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'm trying to retrieve the value of a record in a table. Why doesn't this work:
tabName = "Plan Data"
Dim db As DAO.Database, rst As Recordset
Dim strQryWhole As String
Dim dbStatus As Integer, rcnt As Long
[code]....
I 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 RelatedHow 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?
why the below VBA code is not retrieving the last record in a table.
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Table_Check_Number_List")
[code]....
I have an Access Database that I will be using on a desktop. I have a table in this database that mirrors the structure of a table on a remote server in a SQL database. I have successfully created a vba function within the Access database that uses a server-side php subroutine to select records (I usually won't know how many) from the SQL database and return them to the access database. The code I use in the access vba subroutine to access the php subroutine is:
Code:
With CreateObject("Shell.Application").Windows
Set ieWindow = CreateObject("InternetExplorer.Application")
ieWindow.Visible = False
apiShowWindow ieWindow.hwnd, SW_MAXIMIZE
ieWindow.Navigate "Web address for server-side php file"
End With
The last command in my php subroutine is "return $retrievedData." $retrievedData is a multidimensional array containing data from 42 fields in multiple records (again, I usually won't know how many). I've checked the data in php so I know it has been stored correctly.how do I access the returned data within my access vba subroutine?
I should add that my overall reason for doing it this way is that I want to maintain my server-side database as an untouched master. Users can only add data to it. My client-side database is used to update the input table and further process the data. The subroutines described above are intended to retrieve "new" records only (i.e., records posted since the last access database update) from the server-side database and transfer them to the access database for further processing.
Continuing with production of my database I've come across another wall that I'm trying to pass. My aim is when the user press the "Quit" button it will export everything to a file which is stored on Google Drive as google drive is installed on the laptops that will be using this database.
However the problem is Google drive is stored in the computer user files i.e C:UsersstudentGoogle Drive - is there a way to retrieve the name of the user that is logged into windows?
Code:
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal IpBuffer As String, nSize As Long) As Long
Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
Function ThisUserName() As String
Dim LngBufLen As Long
Dim strUser As String
[code]....
However cant get this to work - I think that is probably because i'm declaring this code in the wrong place - I've tried declaring the private functions in a class module and the functions in a module - however no success - How do you set this code up? Or is there a new way to do this?
I wrote this module
Option Compare Database
Option Explicit
Public Function OdometerInput(varodometer As Variant) As Long
Dim varKilometres As Variant
varKilometres = varodometer * 1.609344
OdometerInput = CLng(varKilometres)
End Function
It works fine in the immediate window (although I haven't just fathomed what to do with null values and such) But my question which I am sure will be 'easy when you know' is how do I pass the variable to it from a text box on a form and retrieve the data in another text box on a form.
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?
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]....
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?
Front end Access 2010, back-end SQL-server 2008 R2.
Normally I retrieve a certain value by Dlookup("myvalue", "mytable",...)
or
strSQL = "SELECT myvalue FROM mytable...;"
Set rs = CurrentDb.OpenRecordset(strSQL, 4)
But is there any faster way to retrieve a single value from an SQL-server table, beside doing doing the select by a stored procedure running through a pass through query, then open a recordset
Set rs = CurrentDb.OpenRecordset("mypassthroughquery")
just to retrieve ONE value?
I could not find something like DLookup("...) for an SQL-Server or in T-SQL.
Vbscript/vb macro code for retrieving the last modified date of a file uploaded in a URL.
I am able to get the file but wanted to get the last modified date of it.
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] ......
Hopefully someone can help me this (and hopefully the solution isn't staring me in the face)
I am trying to export data from a cell in a worksheet to a field in an Access database that I am building. The database has two tables, tblCompanies and tblQuotes. It works through a macro accessed by a command button, which should export the total of a quote into the field Quote_Price in the tblQuotes table, which the users will access through a form. The problem is that if I export the data from Excel, I get an error message saying, "You cannot add or change a record because a related record is required in table 'tblCompanies'. The code looks like this:
Sub NewQuote()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:Documents and SettingsDJEdwardsMy DocumentsTest Foldermarketing.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "tblQuotes", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 55 ' the start row in the worksheet
Do While Len(Range("I" & r).Formula) > 0
With rs
.AddNew
.Fields("Quote_Price") = Range("I" & r).Value
.Update ' stores the new record
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
I can import the cell contents from Access with no problems, but I would like the user to be able to do it from Excel to simplify things.
I'd be grateful for any help!
Cheers
Dave, England
I have quite an extensive form linked to a table. When I add new columns to the table I seem to have a problem getting the form to read the data.I have just added a numerical column to the table and added a text box on the form that is bound to it. When I try to pull up the value using VBA it is blank, even though an entry is visible on the form. I have set the text box to general number and the entry shows in the actual table. When typing in the entry into VBA it capitalises where required so it must be registering the table entry. However the value it pulls remains blank.
View 2 Replies View RelatedIf I set a variable inside of a subroutine, it is set to nothing upon the end of the sub. Can I set a variable outside a sub and set its value, so that you can use it within subs?
View 14 Replies View RelatedHow can I define a variable which can be used by another Sub and of course the value stored in it?For instance:
Private SUB A ()
DIM A1 as String
A1 ="ABC"
END SUB
PRIVATE SUB B()
PRINT A1
END SUB
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]....