Modules & VBA :: Way To Declare Variables In Sort Of Module That Access Uses By Default
Nov 16, 2014
Can't find any way to declare variables in the sort of module that Access uses by default.Having changed the default, I now get 'Option Compare Database' as the first line (previously nothing was there). Adding anything at all in that section, or changing it to 'Option Explicit' has the consistent effect that all the procedure names in the module are not recognised and nothing works.
Variables declared in procedures work only in the procedure even if declared as Public. Can't get a Static variable to work in more than one procedure. However many variables I declare, there are never any in the Declarations list at the top of the Code Window.
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
I've been using Access for a while now, to track some names, addresses, locations, etc. Recently, when I try to generate a report, I step through the field selection, etc, and when I get to the "sort" page, if I just keep going, it proceeds fine. If I select a field to sort on, however, I get the following message:
"The expression Before Update you entered as the event property setting produced the following error: The function you entered can't be used in this expression.
*The expression may not result in the mane of a macro, the name of a user-defined function, or [Event Procedure]. *There may have been an error evaluation the function, event, or macro."
I don't think I've changed anything to do with the program, or any of its Office brethren. Can anybody give me some advice? The report is generally useless without the ability to sort/group.
I'm still learning here, so please don't get annoyed if I don't know what I'm talking about.
I have a Module that I created that looks up a value in a table.. It is shown below.
Public Function DescLookup()
DescLookup = DLookup("[Projdescription]", "ProjDescTable", "[OrderN] = [Forms]![Production]![ProjIncList]") End Function
I want this to be the default value for a field, however I want the field to be editable and I will later have a button that calls on an update query that will update the field with whatever changes are made to the Text Box, however when I set DescLookup() as the default value of the text box, it will not let me edit the text box in the form.
I need to declare a variable (max_nog_leveren ) but that variable may not be rouded or only after 5 digits so as example 15,xxxxx
I am now using it as a "Integer" but that is not working very well..
Code: Dim iRet As Integer Dim strPrompt As String Dim strTitle As String Dim max_nog_leveren As Integer Dim strQuery As String Dim nettohoeveelheid_toevoegen As String
I need to set some 'global' variables with default values when my Access 2007 database is loaded. Depending on the user etc these values may be modifed after Access starts but defaults need to be set.
I declared the variables as 'Public' in a Module, then put a function in the same Module (to set the default values) then tried to call the function from an "AutoExec" macro, so it's the first thing that runs when Access loads.The macro throws an error - it can't 'find' the function(?)
I have been at this for almost 3 weeks now and I'm having great difficulties trying to get this right and working. First let me explain what I am doing and what I am trying to do.
Firstly, I am making a Maintenance database using MS access software, what I have are tables, forms and no queries or report existing so far. The forms and tables all work correctly.
After the form (Job request) has been completed I need to save/export into PDF so I am able to hyperlink it against its existing asset card elsewhere.
now what I have done is Created button, on event "on click" I have tried to go to macro builder
ExportWithFormatting Objet Type: Form Object Name: FrmMachineFault/GenMaint Output format: PDF Output file: Auto Start: No Template File: Encoding: Output Quality: Print
when I click this button it opens to save to and it works perfectly but I have to manually type the file name when it gets to the Save to section.
What I want it to do is define the name by the existing fields in the open form. Example Closed date, Effected area, Asset and title.
I am trying to set the default sort code to something other than the primary key. Example- Book Code is Primary Key but I want the table to be sorted by Title. I have went into properties and used the Order By property and typed 'Book.Title' however when I view the datasheet, Book Code is still the 1st column??? Is it still being sorted by Title??
I'm stuck on a step where I want to import an excel worksheet into the msaccess like we do normally. I do not have any data inside, it's just the header I will be importing. The data will be feeded by other forms based on some selections. My requirement is the "Default value" of each field should be set to 1 as we see in the property of a table in design mode.
The data would be updated later for some fields via macro or commands, but the fields were nor touched should be set to 1 (Value).
I'm wondering how other members here make decisions whether they want to place codes behind form or use a standard module instead.
I understand there is a performance penalty when you add another module (and use it), but am not sure whether one big fat module would be faster than several smaller modules with identical coding.
Furthermore, I know that some members use a hidden form to deal with startup and shutdown processing. Sometimes the processing has nothing to do with forms and would make more sense in a standard module, but since the form is already loaded, does it makes more sense to use the module behind the form than calling a function in a separate standard module to execute the needed code?
I have a small system that consists of a back end data base and 2 front ends. having 2 front ends was a historic reason but they cover 2 distinct processes in the system.
I now have the need for using the same module(s) in both front ends. Each has a copy which is fine if nothing changes but when it does I have to apply the changes made (or copy over in its entirety) from one front end to the other.
Although this is no big deal I can forsee it slipping through the net.
Can 2 front ends access the SAME module and if so how?
I have this code below that is working however the calculation are updating on my form late.
Basically, I have some calculation that are performed on a "After Update" event on some controls on my form. I wanted it to do the calculation after I update the control on the form.
The code is in a module and I just call the function after update on the control But the form is not updating when I change the value in a field. I have to change the field again for it to update.
Control
Code: Call GeraAuditCalc DoCmd.RefreshRecord
Function
Code: Function GeraAuditCalc() Dim fHrs As Double Dim Ttl As Double Dim Ttl1 As Double Dim Ttl2 As Double
I am trying to return a value in an expression (call it FundedPeriod): CurrentWeek, CurrentMonth, PreviousMonth, based on a date value in field [funded_date].
Here are the criteria I am using:
Current Week: DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())
Current Month: Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())
Based on example, I expanded upon that and came up with this:
Code: Function FundedPeriod(FD) ' returns CurrentWeek or CurrentMonth or PreviousMonth or None based on FundedDate (FD) criteria Dim ret As Boolean ret=CurrentWeek
[Code] ....
Am I in the ballpark w/ this in order to return:
CurrentWeek or CurrentMonth or PreviousMonth
so in the query expression I think I would type FundedPeriodName: FundedPeriod([funded_date])
I am trying to pass a boolean variable to a class module
Code: Set rps.ViewS = View
the code in the module that this in theory is calling reads as
Code:
Private ViewC As Boolean
Public Property Set ViewS(ByRef ViewA As Boolean) Set ViewC = ViewA End Property Public Property Get ViewS() As Boolean Set ViewS = ViewC End Property
However I am getting the error message
Quote:Definitions of property procedures for the same property are inconsistent, or property procedure has an optional parameter, a ParamArray, or an invalid Set final parameter.
This module is giving me the "undefined function" error message when I try to run my query. I don't know why, but I have checked that there are no references with "missing" and there are not. I also added the word "Public" to the function becasue that was advised by another forum user. I thought it worked perfectly the first time I ran this query, but now it is not working and I do not recall making any changes. I have called the module basFunctions:
Option Compare Database '************************************************* ********* 'Declarations section of the module '************************************************* ********* Option Explicit '================================================= ========= ' The DateAddW() function provides a workday substitute ' for DateAdd("w", number, date). This function performs ' error checking and ignores fractional Interval values. '================================================= ========= Public Function DateAddW(ByVal TheDate, ByVal Interval) Dim Weeks As Long, OddDays As Long, Temp As String
I have a back end database that a number of people are working with using an Excel add-in that imports and edits records saved in the backend. Occasionally, when a user will update a record at first it saves to the backend but then an hour or two later, the changes made disappear. We have validated that after the user saves the changes, those changes are present in the backend of the database, but for some reason those changes disappear after a period of time.
I am trying to create a customizable report that would allow the users to choose fields. I have a pretty common code that I found online and adapted it, but it fails on the first SetReportControl function.
The error reads: Run-time error 2465: Application-defined or object-defined error
Could it be something as simple as an incorrect reference? I have checked multiple times, but I am stumped.
Code: Option Compare Database Option Explicit Sub MakeReport()
[Code].....
This is a trial run, in the end I need to be able to open a report, then adjust the Report controls within 1 or more subreports inside the main report. That is a battle for another day.
we upgraded to 2003 access. Now in a code getting the msg that I need to declare a variable for:xlexcel9795 here is how it is in the code. what do I put? FileFormat:=xlexcel9795
I am creating an form in a database and whenever one of my procedure's run it creates this error message:
The expression ON Load you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives.
*The expression may not result in the name of a macro, the name of a user-defined function, or [event Procedure]. *There may have been an error evaluating the function, event, or macro.
