VBA CODE FOR ACCESS 2002 FORM CONTROL

[kjv1611]

Usage info: Windows 2000 environment, Access 2002

I have a database with a combo box labeled cmbComments. I have some code that runs in the _AfterUpdate event of the combo box, but I would like the same code to run if the user does not enter a comment at all, and just hits tab or enter to move on. I do not, however, want this code to run if they hit shift-tab to go back to a previous record, or click on another field to edit it. Does anyone know a way to do this? I would imagine using an If Then statement or Select Case statement, but was unsure of a good method for doing so. Any advise/assistance will be greatly appreciated.

Thanks,

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[usgupta]

Try this. Paste the following code in the LostFocus event of the Combo Box cmbComments.

If IsNull(cmbComments) Then
MsgBox "No value selected."
Else
Call cmbComments_AfterUpdate
End If

Hope this shud solve ur prob.

 

[kjv1611]

Well, usgupta, that works as good, as far as when the user just tabs to go to the next account, so it fixes one part of my problem (I had some code in the AfterUpdate event that I wanted to run), but it doesn't fix it, so that if someone goes to another control (b/c don't want to go to next record just yet). For example, say the person has gone to that control, and then decides, that woops, I put the wrong value in the 3rd control back (well of course they would use the name most likely, but for these purposes, it doesn't matter what control the user would want to go to). Any ideas as to how to take care of that? Something like this:
kind of pseudocode, I guess, b/c don't know the correct context for certain:

cmbComments_LostFocus()
     Select Case LeaveComments
     Case <ENTER> - when the person hits enter
          Call cmbComments_AfterUpdate
     Case <TAB> - when the person hits tab
          Call cmbComments_AfterUpdate
     Case Else
          If other ctl of the current record is selected, Then
               Do Nothing...
          End If
     End Select
End Sub

The idea may be crazy, but I think it's what I want it to do. So, in other words, if the person just Mouse-clicks on a control that would be selected for the next record, then it would move to the next record, but if it is a control that would have to do with the current record, it wouldn't move. If I can't get something to work for that, then I'll just advise those people to just hit the "previous record" button (default) to edit anything they think they messed up.


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[BobJacksonNCI]

Hi Stephen,

Look at setting the KeyPreview property true and then using the On Key Press event for the control. You can set up a
Case Select snippet to identify the keystroke. Use a variable to check in the control's On Exit event to determine whether you want your code to run.



HTH,
Bob

 

[kjv1611]

Use a variable to check in the control's On Exit event to determine whether you want your code to run.

Bob,
With this statement, are you talking about doing that in the same code with the Select Case Statement, b/c that alone could be the ticket, I would think..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[BobJacksonNCI]

Stephen,

Yep, you're correct. I misread the KeyPreview property - having to do with the sequence of form-level versus control-level events.

Sorry about that...
And,
Good Luck!
Bob

 

[kjv1611]

Thanks, I'll give that a try and post back when I get a chance.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV