Focus & Navigation Problem !

I have a continuous form in which I don't want to allow more than 255
rows.

There is a field called sub_tran_no which is of the type "BYTE" & hence
the restriction.

My Problem:
-----------
When I press the Dn-Arr & the no of transactions is >=254, the focus
moves to the next
field on the right. Why does this happen ?

My Requirement:
--------------
When the field sub_tran_no achieves a value >=254, I would like to
pop-up a msgbox warning
the user that a max of 255 rows are permissable & retain focus on the
SAME field before
the Dn-Arr was pressed.

I'm sure it's something trivial but I just seem to miss it.

Can someone please correct my code & help out.




Here is my code in the CURRENT event:



Private Sub Form_Current()
Dim msg, msg1, msg2, msg3 As String

If Me.Sub_Tran_No >= 254 Then
msg1 = "MAXIMUM TRANSACTIONS ALLOWED ARE 255 !" & Chr(13) &
Chr(13)
msg2 = "Please Delete Some Rows !"
MsgBox msg1 & msg2, vbOKOnly + vbCritical, "Error !"
Dim ctlCurrentControl As Control
Set ctlCurrentControl = Screen.PreviousControl
SendKeys "^{PGUP}", True
ctlCurrentControl.SetFocus
Exit Sub
End If

If Me.Recordset.RecordCount = 0 Then
MsgBox "Empty Table !"
Me.Sub_Tran_No.DefaultValue = 1
Else
Me.Sub_Tran_No.DefaultValue = DMax("sub_tran_no", "JVTable") +
1
End If

End Sub



Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 40 And Shift = 0 Then 'Dn-Arr Pressed
SendKeys "^{PGDN}", True
Exit Sub
End If
End Sub




Thx & Best Rgds,
Prakash.

re: Focus & Navigation Problem !

What if your user presses PgDn instead of arrow down?

The best way to restrict the user to see only 254 records on the form is to
have the query (the forms recordsource) to only return 254 records.



"Prakash" <simran@omantel.net.om> wrote in message
news:1120674659.376542.298970@g49g2000cwa.googlegr oups.com...
I have a continuous form in which I don't want to allow more than 255
rows.

There is a field called sub_tran_no which is of the type "BYTE" & hence
the restriction.

My Problem:
-----------
When I press the Dn-Arr & the no of transactions is >=254, the focus
moves to the next
field on the right. Why does this happen ?

My Requirement:
--------------
When the field sub_tran_no achieves a value >=254, I would like to
pop-up a msgbox warning
the user that a max of 255 rows are permissable & retain focus on the
SAME field before
the Dn-Arr was pressed.

I'm sure it's something trivial but I just seem to miss it.

Can someone please correct my code & help out.




Here is my code in the CURRENT event:



Private Sub Form_Current()
Dim msg, msg1, msg2, msg3 As String

If Me.Sub_Tran_No >= 254 Then
msg1 = "MAXIMUM TRANSACTIONS ALLOWED ARE 255 !" & Chr(13) &
Chr(13)
msg2 = "Please Delete Some Rows !"
MsgBox msg1 & msg2, vbOKOnly + vbCritical, "Error !"
Dim ctlCurrentControl As Control
Set ctlCurrentControl = Screen.PreviousControl
SendKeys "^{PGUP}", True
ctlCurrentControl.SetFocus
Exit Sub
End If

If Me.Recordset.RecordCount = 0 Then
MsgBox "Empty Table !"
Me.Sub_Tran_No.DefaultValue = 1
Else
Me.Sub_Tran_No.DefaultValue = DMax("sub_tran_no", "JVTable") +
1
End If

End Sub



Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 40 And Shift = 0 Then 'Dn-Arr Pressed
SendKeys "^{PGDN}", True
Exit Sub
End If
End Sub




Thx & Best Rgds,
Prakash.

re: Focus & Navigation Problem !

Thx for replying Barry !

When the user presses PgDn ... since I've put my code in the "Current"
Event, it behaves as expected & the focus also does not shift.

But the Dn-Arr is still troubling me.

I'd greatly appreciate if you or someone else could help me solve this.
It's really bugging.

Thx & Best Rgds,
Prakash.

re: Focus & Navigation Problem !

You can use the Current event to see whether the user navigates to
another record. If that is a new record, the NewRecord property of the
form will be True. You can check for the amount of records using
DCount(). In the >254 case, you can pop up your warning, and issue

docmd.gotorecord,,acprevious

I can't really see how down-arrow would take you to the next record; do
you have the form in datasheet view?

If you manage to get code running at the moment the focus moves to the
next field, you can execute

screen.previouscontrol.setfocus

to return to the previous control. Maybe that does help.

Prakash wrote:
[color=blue]
> I have a continuous form in which I don't want to allow more than 255
> rows.
>
> There is a field called sub_tran_no which is of the type "BYTE" & hence
> the restriction.
>
> My Problem:
> -----------
> When I press the Dn-Arr & the no of transactions is >=254, the focus
> moves to the next
> field on the right. Why does this happen ?
>
> My Requirement:
> --------------
> When the field sub_tran_no achieves a value >=254, I would like to
> pop-up a msgbox warning
> the user that a max of 255 rows are permissable & retain focus on the
> SAME field before
> the Dn-Arr was pressed.
>
> I'm sure it's something trivial but I just seem to miss it.
>
> Can someone please correct my code & help out.
>
>
>
>
> Here is my code in the CURRENT event:
>
>
>
> Private Sub Form_Current()
> Dim msg, msg1, msg2, msg3 As String
>
> If Me.Sub_Tran_No >= 254 Then
> msg1 = "MAXIMUM TRANSACTIONS ALLOWED ARE 255 !" & Chr(13) &
> Chr(13)
> msg2 = "Please Delete Some Rows !"
> MsgBox msg1 & msg2, vbOKOnly + vbCritical, "Error !"
> Dim ctlCurrentControl As Control
> Set ctlCurrentControl = Screen.PreviousControl
> SendKeys "^{PGUP}", True
> ctlCurrentControl.SetFocus
> Exit Sub
> End If
>
> If Me.Recordset.RecordCount = 0 Then
> MsgBox "Empty Table !"
> Me.Sub_Tran_No.DefaultValue = 1
> Else
> Me.Sub_Tran_No.DefaultValue = DMax("sub_tran_no", "JVTable") +
> 1
> End If
>
> End Sub
>
>
>
> Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
> If KeyCode = 40 And Shift = 0 Then 'Dn-Arr Pressed
> SendKeys "^{PGDN}", True
> Exit Sub
> End If
> End Sub
>
>
>
>
> Thx & Best Rgds,
> Prakash.
>[/color]

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

re: Focus & Navigation Problem !

Bas ... thx a ton for replying & WITH the solution.

I had just done something very similar to what you described & it sems
to be 99% ok.

Pressing Dn-Arr takes me to the next record because of the foll sub :

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 40 And Shift = 0 Then 'Dn-Arr Pressed
SendKeys "^{PGDN}", True
Exit Sub
End If
End Sub




Here's my final code:

Private Sub Form_Current()
Dim msg, msg1, msg2, msg3 As String

If Me.NewRecord = True And Me.Sub_Tran_No >= 254 Then
'MsgBox "New Record !"
msg1 = "MAXIMUM ROWS/TRANSACTIONS ALLOWED ARE 255 !" & Chr(13)
& Chr(13)
msg2 = "Please Delete Some Rows !"
MsgBox msg1 & msg2, vbOKOnly + vbCritical, "Error !"
SendKeys "^{PGUP}", True
Screen.PreviousControl.SetFocus
End If

If Me.Recordset.RecordCount = 0 Then
MsgBox "Empty Table !"
Me.Sub_Tran_No.DefaultValue = 1
Else
Me.Sub_Tran_No.DefaultValue = DMax("sub_tran_no", "JVTable") +
1
End If

End Sub


Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 40 And Shift = 0 Then 'Dn-Arr
If Me.Sub_Tran_No >= 254 Then
Exit Sub
End If
SendKeys "^{PGDN}", True
Exit Sub
End If
End Sub





A couple of questions:
-------------------------------
1) In the >254 case I used: SendKeys "^{PGUP}", True
whereas you've used: docmd.gotorecord,,acprevious

Which is better & why ??

2) Let's say I've already entered 254 rows of data. I position myself
on the 250th row & start pressing Dn-Arr. The cursor behaves & goes
down correctly. However, when I reach the last row, the cursor starts
moving RIGHT into the next right field until it reaches the right-most
field & pops up my err-msg. This seems to be the only glitch now. Rest
all seems ok. If I click on a new record using the mouse or even press
PgDn, the keyboard navigation behaves as expected.

ANy ideas ?

Thx again !!

P.S. Cd u pls air-check my code to see if I've coded it correctly. I'm
v.v. outa touch with programming.

Best Rgds,
Prakash.

re: Focus & Navigation Problem !

> Pressing Dn-Arr takes me to the next record because of the foll sub :

Ah. Okay.
[color=blue]
> Dim msg, msg1, msg2, msg3 As String[/color]

While we're at it: understand that in VBA, other than in VB, this
declares msg, msg1 and msg2 as *variant* (the default datatype). For
that reason I always Dim one variable per line.
[color=blue]
> If Me.NewRecord = True And Me.Sub_Tran_No >= 254 Then[/color]

NewRecord = True is redundant, although perfectly legitimate, easily
understandable and correct. I usually do "NewRecord And sub_tran_no>=254"

Can you be sure this test is water tight? I mean, if numbering starts at
-200, you have way over 255 records at number 254.
[color=blue]
> MsgBox msg1 & msg2, vbOKOnly + vbCritical, "Error !"[/color]

I don't think that is an error. It is a violation of a constraint you
created. "Correction required" seems to me to be a better voicing.
[color=blue]
> A couple of questions:
> -------------------------------
> 1) In the >254 case I used: SendKeys "^{PGUP}", True
> whereas you've used: docmd.gotorecord,,acprevious
>
> Which is better & why ??[/color]

docmd.gotorecord documents itself better; and the use of Sendkeys is a
frowned upon, sometimes SendKeys behaves a litte buggy (especially in
changing the NumLock state). But in general, if a statement/command is
available for the action you want, it's best to use it.
[color=blue]
> 2) Let's say I've already entered 254 rows of data. I position myself
> on the 250th row & start pressing Dn-Arr. The cursor behaves & goes
> down correctly. However, when I reach the last row, the cursor starts
> moving RIGHT into the next right field until it reaches the right-most
> field & pops up my err-msg. This seems to be the only glitch now. Rest
> all seems ok. If I click on a new record using the mouse or even press
> PgDn, the keyboard navigation behaves as expected.[/color]

Does it go away if from the KeyDown event (keydown? You should use KeyUp
to provide the user with a way out. But that is quite a different issue)
you don't do SendKeys ctrl-pgdn, but docmd.gotorecord,,acnext?
[color=blue]
> P.S. Cd u pls air-check my code to see if I've coded it correctly. I'm
> v.v. outa touch with programming.[/color]

You're not sending SMS to me! :-) what is v.v. here?

I already did that, before I read the question. Your style seems to
evoke it <evil grin> don't take offense :-) You have my comments.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea