How do I capture Form Variables before editing them

[Cyd44]

I am using a form to show a list of Appointment records. I want to choose a record from the list and edit the fields. However. I wish to capture the record fields before editing as I need this information to subsequently delete the old Outlook appointment.

I have tried capturing this info using the before edit Event and have declared results as Public variables in a Module.

However, when I try to recall the Public variables they are showing empty.

Is this because the before edit Sub is within the same class module? or am I doing something stupid.

[ADezii]

Let's assume that you wish to Capture the First Name, MI, and Last Name Values on a Form prior to Editing.

1. Declare your Variables as Public in a Standard Code Module
   

   Public gstrFirstName As String
   Public gstrMI As String
   Public gstrLastName As String
    

2. In the Form's Current() Event, assign Values to those Variables (First and Last are required, MI is not):
   

   Private Sub Form_Current()
   'Must Initialize the Public Variables, avoid carryovers
   gstrFirstName = ""
   gstrMI = ""
   gstrLastName = ""
    
     With Me
       gstrFirstName = ![First]
       gstrMI = Nz(![MI])
       gstrLastName = ![Last]
     End With
   End Sub

3. The Values in these Variables will persist until you Navigate to another Record, and will be accessible for the lifetime of your Application. Should you Edit any one or more of these Fields, the Variables will still retain their Original Values.

[NeoPa]

This would be so much easier if you showed the code you're using and explained what happened when it failed. I think I can guess, but I may be wrong - and I don't see why we have to keep guessing when a bit more care and preparation would make a full question.

I suspect you public variables have been declared in a class or object (Form or Report) module. Such modules can declare public variables, but when they are accessed from outside of that same module, they need to be referenced explicitly. Assuming the variable is declared as public in the module for a form named "frmMain", and the variable itself is named strName, then the full reference to them would be Form_frmMain.strName. As you know from an earlier thread, this cannot be referenced from SQL. No public variables can.

PS. For a variable to be referenced anywhere in your code without the module specifier, it needs to be declared in a Standard Module.

[Cyd44]

Hi Adezii

Thanks for this. I have placed your code but I am using 2 date fields and not sure how to intitialise these and am getter an error on the oldDate field.

My Code is

Dim oldLocation As String
Dim oldStart As Date
Dim oldDate As Date
Dim oldName As String
 
oldLocation = ""
oldStart = #12:00:00 AM#
oldDate = #00/00/0000#
oldName = ""
 
  With Me
    oldLocation = ![BookLocation]
    oldStart = Nz(![BookTime])
    oldDate = ![BookStartDate]
    oldName = ![BookName]
  End With 

The oldStart is Date/Time (medium time) and oldDate in Date/Time (short date) These coorespond to the tble fields for BookTime & BookStartDate

I have declared the oldXXXX variables in a module as follows:-

Public oldLocation As String
Public oldDate As Date
Public oldStart As Date
Public oldName As String
 

When I run I get a Syntax error on the oldDate?

[Cyd44]

Hi NeoPa

I seem to be always falling foul of you. I can only assure you I am trying but as a novice I find it difficult to distinguish between being verbose and being brief. I was simply asking a question about capturing a form variable before editing it and did not give any further details in case I was being stupid. ADezii appears to have understood completely.

In relation to Module I am using, I was using Standard Code Module. I am hopefull that once I have resolved the Syntax error on initialising the variables (as per ADezii's code) my pain will go away.
Your comments, advice & assistance are always greatly appreciated but I do not always know how to put my question properly due to my lack of knowledge on Access and VBA syntax etc.

[ADezii]

1. Date Variables can represent Dates in the Range of 1/1/100 to 12/31/999. There is no such Date as:
   

   00/00/0000

2. When dealing with Variables, especially Public Variables, their names are very important in identifying their Scope, Type, and Context.
   

   gdteOldDate

3. When looking at the above Variable in Code, it is easily recognized as Public/Global(g), a Date Value(dte), representing an Old Date.
4. You have 2 Options to eliminate the Syntax Error:
   1. Set the Value to 0 as indicated below:
      

      oldDate = 0

      will display as:
      

      12:00:00 AM

   2. Declare oldDate as a Variant, and Reset to Null:
      

      Public gvarOldDate As Variant

      gvarOldDate = Null

      will return Null

[NeoPa]

It seems your ability to express yourself isn't so bad Cyd. That post #5 was well said. I should appreciate that preparing such questions properly isn't so easy for most people, but I'm confident you'll get there with increasing experience. The improvement already is noticeable. I will try to bear that all in mind for future questions.

Anyway, for this question, that I'm still largely unclear about (notwithstanding ADezii's apparent comprehension), I would just comment that Date variables, unlike form controls and most fields, cannot contain Null values (which would typically be used to indicate a non-value). ADezii has already explained that the range of values doesn't allow all 0s, so if you must reflect that then you may be better advised to use a Variant variable to store your date value.

[Cyd44]

Thanks to Adezii I have changed the variable names to reflect that they are golbal and have add a prefix as either gdte or gstr.

Initilaly whilst I got no syntax error I was still not getting the correct results. I then realised why! I had set on entry event for each field to validate the owner. This was conflicting with the on Current() event for the Form (I think). I have since taken the en entrty() events out and the sub works OK.

Many thanks to Adezii I will set as best answer. Many thanks to NeoPa for the advice and gudance,

[ADezii]

One last thought is that you must be very careful with Public/Global Variables due to their retention (Scope). This is why they must be Re-Initialized in the Current() Event for every Record or their Value(s) will persist to the Next/Previous Record.

[Cyd44]

Thanks ADezii

I will look at this and see. If we are initialising at the start of each loop, will it not set the golbals to null if we choose another record ......or not?

[Cyd44]

ADezii

I have tested the Global Variables and made a number of changes without closing and re-entering the form. It changed the globals to reflect the pre-edit details of the records chosen OK.

Think I am happy the variables are re-initialised each time.

Thanks for the advice anyway. Cheers mate

[ADezii]

They have to be re-initialized for each Record. Suppose you are on the 1st Record, and 3 Public variables for First, MI, and Last get initialized to:

William 
C
Clanton

if you now Navigate to Record 2, and the Name is:

Thomas
 
Jefferson

When you retrieve the Values of the Variables they will read:

Thomas
C
Jefferson

because the Variable set to MI still maintains its prior Value. Makes sense?

[Cyd44]

I think I understand and it does appear logical that where a catured variable is Null it would retain the old value. Thankfully however, within my database, I will not have Null values in any of the variables that I wish to capture as these are the key fields to the string.

Think we can close this ADezii, dont you. I have marked your last as best answer and am happy with the help unless you feel that there may be some flaw in the logic.

Many thanks yet again