I'm trying to monitor a few fields and record who updated them together with the old and new values, so far, I have this;
This does work ok, I was just wondering if there's a way of referring to the current field, rather than having to specify 'me.NPW.oldvalue' etc.
Code:
Private Sub NPW_AfterUpdate()
Call LogFieldUpdate(Me.AppNumber, "NPW", Me.NPW.OldValue, Me.NPW)
End Sub
[Code] ......
This is the code I'm trying to run from a Standard Module:
Code:
Public Sub RunPaxCalculations()
Dim frm As Form
Dim intNumberOfRecordsInFilter
[Code]....
I have a bunch of Forms set up in Access that are updating tables etc......
At present 100% of the code is sitting on the Form. I have some code that is repeated because of a string parsing issue. What I am thinking of doing is writing this code as a couple modules and then call them when needed.
Does that make sense to write them as modules and call them?
How do you call a module???
Thanks
I am trying to populate an Excel Shreadsheet (Template in Effect) with Data from Access. This is going ok no problem. Although I need to run an excel macro, which does some formatting to the WorkSheet after each entry in the Access Recordset. I've tried copying the VB over, but getting some errors, and frankly, I think it's easier if I just call the macro itself, rather than try to adapt it for the Access context. Though, I accept it would be a cleaner approach. Can I do this?
View 1 Replies View RelatedI have three forms - Form1,Form2 and Form3 linked to a navigation form with Navigation buttons. I have separate VBA codes under each Before update event of these forms, Form1,Form2 and Form3 to call a VBA module to track the changes performed by the users(Like an Audit Trail) in specified fields of the forms which will track them to a table.there are control tags in the form properties --> others --> Tag to track the changes in the fields which are defined in the VBA Module.this is the code to call the VBA Module:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Call AuditChanges("UniqueID", "Part No", "NEW")
Else
Call AuditChanges("UniqueID", "Part No", "EDIT")
End If
End Sub
[code]....
The code is working perfect when the forms are run separately.but when they are run from the navigation form by clicking the navigation buttons, the code is not working !!
I'm having to recode some old MS Access DBs so they will run in the following environments:
Office 2000 on WinXP
Office 2003 on WinXP
Office 2010 on WinXP
Office 2000 on Win7
Office 2003 on Win7
Office 2010 on Win7
When I wrote my code for Office 2000 on WinXP things were simple because directory paths were the same across all computers and I could hard code pathing when using a shell command to launch other files.
My new approach is to make a function call to the Windows registry to determine the default executable and path for opening a file based upon its extension (see apicFindExecutable in basWindows API module).
I'm able to use code to create a shell call and debug print it to the immediate window. If I put my cursor in the immediate window at the end of the shell call and hit [enter] the external file will open as desired. If I try to open the external file directly through code, I get a file not found error.
To recreate the error take the following steps:
(1) browse to files that are accessible from your computer
(2) click the PREPARE DATA AND OPEN MAIL MERGE DOCUMENTS command button
Shell function call is made by the fnOpenFile function located in the basOpenFile module. There has to be a trick here that I'm missing.
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?
So, what do you tend to do in such situation?
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'm trying to use select case on the 1st two digits of a field. I f it equals 1- then the case is meet but its not working. Will this work or no?
[CODE Case (Left(Me.Step42, 2) = "1-")][/CODE]
I would like to do a loop but never done one before, basically i want to copy the current record by the number of times specified in a quantity field
So if the quantity field in the record says 5 then copy that record 5 times (I have managed to create the copy and paste code but dont know how to make it do it 5 times
Code:
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
I want to put some form functions into a module.here is what I currently have in the module:
Public Sub Fun_Test()
Forms!Form_Output!Sequence.ColumnWidth = 250
End Sub
my form name is "Form_Output" but it still cant find it..
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
[Code] ....
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())
Previous Month: Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1
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])
How you automatically initialize a newly instantiated class?
So when you create a new instance it will run a procedure to set attributes.
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 have the following module in my database to allow for multiple alarms.
It all works great, except before showing the actual alarm record called, the form first pops up showing the first record in its record source.
I.e. it flashes up showing the first record in the table, then changes to the 'correct' record.
I would just like to change it so it either doesn't show until the record is loaded, or it shows blank to begin with.
Here's the module...
Code:
Option Compare Database
Option Explicit
Public clnPopUpAlarm As New Collection 'Instances of frmPopUpAlarm
[Code].....
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
[code].....
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.
View 7 Replies View RelatedI am trying to add various values based on Select Case to the value of field. The problem I face is that each time when I get different Case in select statement, the value of the field rather changing adds the value on top.
Code:
Private Sub ProductID_AfterUpdate()
Dim qflPrice As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlQry As String
Dim instID As Integer
[Code] .....
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.
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.
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.
An ideas?
I'm looking for some type of custom function that will search a specified column for any keywords listed inside another table.
I can run a query on each keyword individually, however there are 50 and it takes a long time each time I do it. I was hoping to write in a function for that column and it would just select all records that match.
These would all need to be a "like" with an " * " on each end of the word.
With SQL it would look something like:
Code:
select a.address1
from main_tbl as a
where a.address1 like '* north *'
or a.address1 like '* park *'
or a.address1 like '* south *';
I just want it to read each of the table values instead of hard coding them and the column name would be the function name so it can be used in any column I specify. I'm just not sure how to incorporate this into a custom function.
I have a perameter query that tracks everyone's time on different projects. When they click on the command button to run the query they are asked for the current start data and end date (this is loaded into the date field within the query on the criteria cell). When they click on this query I would like them to only be able to view the current month instead of any date range. The query is set up on a short date format and I have tried some diffent combinations but can't figure out exactly what to put into the criteria. Any help would be great.
Tim
Hi. First of all, this is my first post, so tell me if I'm being stupid.
I have a simple form, 'BuildingID', which contains a list box and two buttons. The list box lists a field from the records in the table 'BuildingID'. The two buttons are 'Add Building' and 'Delete Building'. The 'Add Building' button works fine, but I cant figure out how to make the other one work.
I want to be able to single click one of the records in the list box, then hit the 'Delete Building' button to delete the entire record. So I'm guessing I have to somehow make the record that I click on in the list box current when I click on it, then have a standard delete record button.
If anyone can help I would be very greatful. :)