which event fires WHENEVER textbox content is changed 3

[jamaarneen]

Hi,

I haven a textbox to fill in and/or change a date.
The fillin or change, can occur in several ways: manually; automatically by the Click Event; through the Plus/Minus or Up/Downbuttons.

I want to put some code that should validate the value, and undo (back to OldValue) if necessary.

So which event will fire EVERY time the value will be changed?, so I can put there my code once
(the Change event will fire only if it's changed manually)

 

[TheAceMan1]

How are ya jamaarneen . . .

Best place would be the controls [blue]Before Update[/blue] event. Although it won't trigger until you attempt to set focus to another control, it won't allow you to enter faulty data (via your validation). If validation fails and can use the cancel arguement and the Undo method to rollback and display the prior text ... keeping the focus on the same control, same record:

[blue]Private Sub DateFieldName_BeforeUpdate(Cancel As Integer)
   
   If Not IsDate(Me!DateFieldName) Then
      Me!DateFieldName.Undo
      Cancel = True
   End If

End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[danvlas]

Change made manually: BeforeUpdate or Exit event for the control. Cancel the event if necessary. Ironically, Change event does not meet the specs in your case

Change made programatically: the program remembers the initial value, calculates the final value and makes a decision.


Daniel Vlas
Systems Consultant

 

[jamaarneen]

Thanks for your reply

AceMan1:
I really don't know why, but the BeforeUpdate event os NOT triggered when i go to the next control or to next record. :-(

Daniel:
It's OK to put my code in several events. I'll stick to this if I won't get any better options
I was just wondering, if there isn't a possibility to catch ALL changes in ONE event.

 

[ProgramError]

have you tried the dirty event?
You will have to reset the focus to the control

Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[MichaelRed]

from my experience, there is no single event which is 'always' triggered, and to the best of my understanding there is NO event triggered when a control value is altered via code, although I would look into the form DIRTY event is triggered, you could at least then check further into which control(s) ...

as for including your V&V in a number of events - do not do it this way, at least not directly, as you create difficulties if you decide to change the V&V criteria. Instead, generate one instance of the V&V procedure NOT attached to any control / event and call that from the various controls and events of interest.



MichaelRed

 

[TheAceMan1]

jamaarneen . . .

So Sorry! What I gave you only works for manual entry. Before proceeding I'd like to go over the triggers (Manual, Click Event, Plus/Minus, and UP/Down) you intend to use.

Note: [blue]there's no need to validate unless some data in the field of a record changes![/blue]

With the note above in mind, are you telling me you want to validate using navigational controls [blue]Plus, Minus, Up, Down[/blue] ... meaning your changing data with these navigational movements? Forget these, they work great for what they were meant ... navigational control.

So .... throwing out the above navigational movements, were left with your intent of the [blue]Click[/blue] event. Unless your updating the control by say ... assigning the current date, whats the point? Its still a navigational movement. It would be better to use the [blue]On Dbl Click[/blue] event here, as it at least shows intent.

Bottom line is, manual entry should be the final trigger as it does so properly (puts the record in edit mode!).\

The code for the forms [blue]Before Update[/blue]
event would look like:

[blue]   Dim ctlName As String, DL As String
   
   ctlName = "YourCtlName"
   DL = vbNewLine & vbNewLine
   
   If Not IsDate(Me(ctlName)) Then
      MsgBox "'" & ctlName & "' Is Not a Date!" & DL & _
             "Go back and make proper correction!" & _
             vbCritical + vbOKOnly, _
             "Not a Date Error"
      Me(ctlName).SetFocus
      Cancel = true
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[jamaarneen]

Hi all of ya, here I am...

MichaelRed: thanks for your respond and advice. although I'm not sure what's the meaning if V&V, but i did understood your suggestion.

AceMan1: Thanks for your respond
and here are My Thoughts...

it's about an order of a certain service that the client is requesting for one or more days - sequential or non-sequential.

so... at the first step the user is clicking in an empty the date-textbox (first empty record on the (sub)form). the user should pick a date or by clicking an calender, or manually. here is only ONE control: to make sure the date isn't prior to today (if the user will try to enter an alphabet, the textbox will resist, bc it's format-property is set to date)

as the first record is OK, then a new empty record is appearing. now if the user will only CLICK in the date-textbox, it will automatically be filled with the date next to above.
but, what if the client would like to skip one day? therefore I would like to offer one or more simple ways to increase the date to the next day. and that should be by clicking the +/- button, or up/down...

well i might also add a spin control...

so to catch the +/- & up/down buttons, i am working with KeyDown and KeyPress event

am I totally wrong? I admit, I don't really have experience with designing DB's, I am newbie, but that's my way of thinking

thanks in advance...
jamaarneen

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmm


april 1, 2009 is not a date?



MichaelRed

 

[jamaarneen]

Sorry, but still don't get what does V&V creteria means... :-(

 

[TheAceMan1]

jamaarneen . . .

I misunderstood some things in your explanations. Have since writted new code (should do what you want).

Whats the actual name of this infamous date textbox?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

I would not recommend using the key press event to increment/decrement a field. It can be done, but it gets tricky to do both error checking and incrementing. It is a lot easier to use a spin or some other controls next to your date field.

V&V. I assume is Validation and Verification of the data.

 

[TheAceMan1]

MajP . . .

I'll be using key up/down events! I've been waiting to get the actual field name from jamaarneen. Looks like I might as well post with a dummy name ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Every time I tried that, I get myself in a logic loop when I try to enter text and use key press events. I will be interested in your approach.

 

[TheAceMan1]

jamaarneen . . .

All date entry control as you asked are taken care of in the code provided:
[ol][li]Manual date entry. The user sets focus to the control via the [blue]tab or enter[/blue] keys.[/li]
[li]Auto date entry from the [blue]previous record[/blue] via the controls [blue]On Click[/blue] event. Note: if the recordset of the subform is empty, the first date will have to be entered manually or thru the calendar you mentioned.[/li]
[li]Increment/decrement the date value via the [blue]up/down arrow keys[/blue] as well as [blue]the keypad plus/minus keys.[/blue] These keys will also [blue]transfer the date from the previous record[/blue] (on 1st keypress) if the date field is empty ... subsequent key presses or holding down the key ([blue]repeat mode![/blue]) increments or decrements the date. Repeat is cool! [/li]
[li]Be aware: as requested the code only works when your [blue]editing a new record![/blue][/li][/ol]
Moving on to the code ...disable or remove any code you have to handle this secnario to prevent interaction. In the code that follows [blue]you![/blue] substitute the [blue]date fieldname[/blue] in [red]red[/red].

In the code module of the subform, copy/paste the following function:

[blue]Private Function SetPrevDate()
   Dim rst As DAO.Recordset
   
   If Me.NewRecord = True And Me.Recordset.RecordCount > 0 Then
      Set rst = Me.RecordsetClone
      rst.MoveLast
      Me("[red][b]FieldName[/b][/red]") = rst("[red][b]FieldName[/b][/red]")
      Set rst = Nothing
   End If

End Function[/blue]

Be careful of [blue]spelling[/blue] when substituting the fieldname!

In the [blue]On Click[/blue] event of date textbox, copy/paste the following:

[blue]   SetPrevDate[/blue]

Finally, in the [blue]OnKeyDown[/blue] event, copy/paste the following:

[blue]   If Me.NewRecord = True And Me.Recordset.RecordCount > 0 Then
      If Trim(Me("[red][b]FieldName[/b][/red]") & " ") <> "" Then
         If Shift = 0 Then
            If KeyCode = vbKeyUp Or KeyCode = vbKeyAdd Then
               Me("[red][b]FieldName[/b][/red]") = Me("[red][b]FieldName[/b][/red]") + 1
            ElseIf KeyCode = vbKeyDown Or KeyCode = vbKeySubtract Then
               Me("[red][b]FieldName[/b][/red]") = Me("[red][b]FieldName[/b][/red]") - 1
            End If
         End If
      Else
         SetPrevDate
      End If
   End If
         
   If Shift = 0 And (KeyCode = vbKeyUp Or KeyCode = vbKeyAdd Or _
                     KeyCode = vbKeyDown Or KeyCode = vbKeySubtract) Then
      KeyCode = 0
   End If[/blue]

Note: The code was [blue]fully tested[/blue] in A2k.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

MajP . . .

As you see I've used the KeyDown event. The key is the [blue]KeyCode = 0[/blue] at the bottom. Here were actually chahging the code that gets passed back to the system. Zero is the equivalent of [blue]Do Nothing![/blue] ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[jamaarneen]

Hi, sorry for my delay...
(I'm at my PC only in the afternoon, and we are probably in defferent continents, because when you sent your posts - I was already on my way to bed...)

MajP: Thank you. I must admit, I am not handling 'errors' yet... DO i must change my habit urgently?

AceMan1: thank you for your code!!! although you understood me partially, and in fact I won't use your actual code, but I have learned a lot! and therfore thanks a lot! but now... i have some questions...

Shift = 0: does that mean that none of Shift/Ctl/Alt are pressed? and what will 'Shift' be if one of them ARE pressed?
KeyCode = 0: I understand that is meant to neutralize the key. so, I would like that ALL other keys should be ignored - should i put b]KeyCode = 0:[/b] without the 'IF' you wrote.
If Trim(Me("FieldName") & " ") <> "" Then: I understand the purpose of this line, but why have added [red]& " "[/red]? the trim function will trim it anyway?
SetPrevDate: it's a nice function, but what i need is just the opposite: I have to make sure that No date is repeated. (but anyway, I have learned from you, to rather write small private functions in the form module. what makes the code much easier).
As DAO.Recordset: I am writing just 'As Recordset'. should i start adding 'DAO'? and why not 'ADO'? (I'm a little confused with that one...).
and one more... How do you format your text in your post so nicely? are there paragraph formatting?(I mean the first line hanging etc.)

(still curious about the field name? here you go: ReqDel_Date... ;-)

Ja

 

[TheAceMan1]

jamaarneen . . .

I'll answer these in order:

[blue]Shift = 0: does that mean that none of Shift/Ctl/Alt are pressed?[/blue]

Yes!

[blue] ... and what will 'Shift' be if one of them ARE pressed?[/blue]

Following is the complete table for the [blue]shift value[/blue] for all 3 keys:

[tt][blue]Alt Ctrl Shift ShiftValue!
*** **** ***** ***********
No No No 0
No No Yes 1
No Yes No 2
No Yes Yes 3
Yes No No 4
Yes No Yes 5
Yes Yes No 6
Yes Yes Yes 7[/blue][/tt]

[blue]KeyCode = 0: I understand that is meant to neutralize the key. so, I would like that ALL other keys should be ignored - should i put KeyCode = 0: without the 'IF' you wrote.[/blue]

If the inc/dec keys are to work while your in the field, then its necessary. Also the [blue]up/down arrow keys[/blue] are navigational and will force the focus to an adjacent field ...killing repeat! If you kill all other keys you won't be able to exit the control except thru the mouse. The choice is yours.

[blue]If Trim(Me("FieldName") & " ") <> "" Then: I understand the purpose of this line, but why have added & " "? the trim function will trim it anyway?[/blue]

Yes your right ... at least in theory. The Idea as presented (besides trimming), is to return a [blue]zero length string[/blue] for comparing against [blue]<> ""[/blue]. Some time ago (I believe 2006) I had a situation where trim kept returning a null (not a null string). So the comparsion failed! At 2:30 AM (dead tired and my thought processes were incoherent, I thought I'd [blue]force it to work[/blue] by concatenating the space character. Its never failed since. I've seen [blue]trim[/blue] posted all over [blue]Tek-Tips[/blue] in the same way. I just don't know if it was for the same reason.

[blue]SetPrevDate: it's a nice function, but what i need is just the opposite: [purple]I have to make sure that No date is repeated.[/purple][/blue]

I have to reserve an answer on this because in your post dated [blue]15 Feb 09 14:01[/blue] you state: [purple]it's about an order of a certain service that the client is requesting for one or more days - sequential or non-sequential[/purple]. To at least start on the next day, you could:

[blue]Change: Me("FieldName") = rst("FieldName")
To      Me("FieldName") = rst("FieldName") [purple][b]+ 1[/b][/purple][/blue]

[blue]As DAO.Recordset: I am writing just 'As Recordset'. [purple]should i start adding 'DAO'? and why not 'ADO'?[/purple] (I'm a little confused with that one...).[/blue]

If your using [blue]A2k[/blue] or higher, yes to should be using DAO.

The use of DAO requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

Were not using ADO because as you see I've addressed DAO! ADO is a different way handling objects than DAO and is geared better for networks.

[blue]and one more... How do you format your text in your post so nicely? are there paragraph formatting?(I mean the first line hanging etc.)[/blue]

What you see is how my code formatted in the modules. The window you see in Tek-Tips comes from putting between [ignore]

[/ignore] & [ignore]

[/ignore] ... a part of the TGML markup language, used in Tek-Tips. To see all of the TGML click the [blue]Process TGML[/blue] link at the bottom of any post your editing.

Its all a matter of grouping code approriately. My indent is set to 3 and any code that get nested is indented (Like the [blue]If Then Else End If[/blue] statements in [blue]On KeyDown[/blue] event.

Hope I covered it all!

[blue]Your Thoughts? . . .[/blue]

BTW: did you at least try the code to check out operations & navigation?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

One thing to add to what AceMan was saying because this is one of the biggest things that gets vba programmers and I am not sure way Microsoft designed vb/vba so sloppily.
There are a lot of Objects that have the same name, but are different, such as recordsets. They may have some similar properties, methods, and events but they will also have different properties, methods, and events. They are not interchangeable and will cause an error. Examples:
Recordsets (DAO recordset and ADODB recordsets)
Forms, Textbox, combobox, listbox ( There are actually Access controls and Microsoft common controls)


Now if you have both references loaded and you do not explicitly tell it which one it is it will set it as the first one in the list. Example, if your reference list has
this order:
ADODB object library
DAO 3.6 object library

if you simply do this
dim rs as recordset
it becomes a ado recordset because that is the first in the list. If you do
dim rs as DAO.recordset
It knows which one you want.

I sometimes have a reference to the MS Forms common controls because you can do more with them. So I always reference explicitly. So I am always in the habit of anything that has two object models to define explicitly.

Dim cntrl as access.control
dim txtBx as access.textBox
dim cmbo as MSFORM.combobox

It takes some practice and headaches to find objects that have two different object models.

 

[jamaarneen]

WOW! what a lesson! Thank you all very much!!! (well, the only thing i can pay you right here, will be aznother star...)

MaJP: thank you for your explanation. but is there any suggestion about which one is better - DAO or ADODB?

AceMan1: you are really great. I'm just curious how much time have you put into this...

- if i understand right, you suggest DAO.

- to be honest, I did not tried your code. because: i already have wrote code by myself. a bit clumsy though, but writing code by myself is the best way to teach myself.
I had it actually split up in several modules for the whole project, because I would like to use some of these inc/decr functions in other places too (and not only for dates).
I have got public functions like KeyPlusAndMinus and _SpinUp/_SpinDown. they are using private functions like Increase/Decrease.
they have options to get arguments as UpperBound/LowerBound.
My only problem was, in which event to make the Validation.
[BTW, the validation itself is another story, but I worked it out. it's based on a Dcount function, what will search if there is already an order for the same OrderID AND the same date]

- thanks about the TGML explanation. but it's not about your code formatting i have asked. it's about your text in your 3th post of 15 feb, from the 3th line until after the 10th line...

All the best
Ja