continuous form problem

[maxmangion]

I have a continuous form and on one of the fields i have the following code so that everytime a change is happened it will automatically re-sort the records in descending order:

Private Sub ToClean_AfterUpdate()
Dim rs As String
rs = Me.TotalDuplicates
DoCmd.RunCommand acCmdSaveRecord
Me.TotalDuplicates.SetFocus
DoCmd.RunCommand acCmdSortDescending
Me.TotalDuplicates.SetFocus
DoCmd.FindRecord rs
Me.TotalDuplicates.SetFocus
End Sub

This code works fine!

I also have another piece of code which when the up and down arrow keys are pressed the focus will move to the next/previous record rather than the next/previous field.

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo Err_Form_KeyDown
Select Case KeyCode
Case vbKeyDown
DoCmd.GoToRecord , , acNext
KeyCode = 0

Case vbKeyUp
DoCmd.GoToRecord , , acPrevious
KeyCode = 0

Case vbKeyReturn
DoCmd.GoToRecord , , acNext
KeyCode = 0

Case Else

End Select
Exit Sub
Err_Form_KeyDown:
Select Case KeyCode

Case vbKeyDown
MsgBox "There are no more records to display", vbExclamation + vbOKOnly, "Warning ..."

Case vbKeyUp
MsgBox "You are already at the First Record", vbExclamation + vbOKOnly, "Warning ..."

Case vbKeyReturn
MsgBox "There are no more records to display", vbExclamation + vbOKOnly, "Warning ..."

Case Else

End Select
End Sub

This code also works fine!

Now the problem i am getting is the following: when i update a record and click the down arrow key, as soon as the sort occurs instead of moving to the next available record, i am getting the message which i placed in the on error of the vbdownkey i.e.

MsgBox "There are no more records to display", vbExclamation + vbOKOnly, "Warning ..."

Therefore the two pieces of code independently they work fine but when i place them on the same form i get that message.

Any ideas pls ?

 

[maxmangion]

any suggestions pls ?

 

[Qamar]

try putting a me.requery after the sort.

HTH

 

[maxmangion]

i have already tried your suggestion but still i got the same problem. Any further ideas are greatly appreciated.

 

[Qamar]

how about me.refresh instead of me.requery

 

[dt01pqt]

Maxmangion you haven't discriminated which error causes what event so you need to find out if the error that is occurring after update has anything to do with record navigation.

 

[maxmangion]

i tried placing some message boxes to understand which event is being triggered and here they are: as soon as i try to amend the value i get the msgbox onKeydown ... i type the value and press the down arrow key and i get another onkeydown message box followed with an afterupdate message box. Then i get the msgbox that i am at the last record (which i placed on the on error of the vbkeydown)

 

[maxmangion]

how about me.refresh instead of me.requery

i've tried that too, but still to no avail!

 

[dt01pqt]

i tried placing some message boxes to understand which event is being triggered and here they are: as soon as i try to amend the value i get the msgbox onKeydown ... i type the value and press the down arrow key and i get another onkeydown message box followed with an afterupdate message box. Then i get the msgbox that i am at the last record (which i placed on the on error of the vbkeydown)

Maxmangion after Err_Form_KeyDown: delete all that case statement crap and put :

MsgBox Err.Description

Then tell me what happens.

 

[maxmangion]

err.number #2105
err.description "You can't go to the specified record"

 

[dt01pqt]

I suspect DoCmd.FindRecord rs may have something to do with it. Can you send me a trimmed and zipped db.

 

[maxmangion]

can i send it to you by email, because when i zipped it the db is 284kb ?

 

[dt01pqt]

Send it to dt01pqt_pt@yahoo.com give me five minutes becuase I have to clear some space.

 

[maxmangion]

ok thank you.

 

[dt01pqt]

Done you can send it now.

 

[maxmangion]

i've sent it now!

Thanks

 

[dt01pqt]

Holey moley I'm not even going to ask.
Try cancelling the key before the DoCmd.GoToRecord it makes more sense. I also changed Enter to save the record rather than just go next.

Like:

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo Err_Form_KeyDown

Select Case KeyCode
Case vbKeyDown
KeyCode = 0
DoCmd.GoToRecord , , acNext


Case vbKeyUp
KeyCode = 0
DoCmd.GoToRecord , , acPrevious


Case vbKeyReturn
KeyCode = 0
DoCmd.GoToRecord , , acSave


Case Else

End Select
Exit Sub
Err_Form_KeyDown:
Select Case KeyCode

Case vbKeyDown
MsgBox "There are no more records to display", vbExclamation + vbOKOnly, "Warning ..."

Case vbKeyUp
MsgBox "You are already at the First Record", vbExclamation + vbOKOnly, "Warning ..."

Case vbKeyReturn
MsgBox "There are no more records to display", vbExclamation + vbOKOnly, "Warning ..."

Case Else

End Select
End Sub

However I don't think your error messages are not working quite like you want them to most of the time I get nothing. Tell me the behaviour to duplicate each error.

Oh and next time a more condensed db with one form and the related table(s) would be better.

 

[maxmangion]

i placed the keycode as you suggested, and it is working fine now. the only change which i've noticed is that if i am at the first record and i press the up arrow, i am not getting the msgbox saying that i am at the first record. However, this is not important since i've managed to achieve the behaviour which i want.

is it possible to explain me the difference between putting the keycode = 0 before the docmd... or after (because apparantely that was my mistake).

Oh and next time a more condensed db with one form and the related table(s) would be better.

Do you mean that the structure of my db is incorrect ?

Thank you very much for your tip, i have been struggling a couple of days with this and i was really stuck with it!

Thanks

 

[dt01pqt]

When you have KeyPreview activated on a form you can trap these in the KeyDown, KeyPress and KeyUp events. By setting KeyCode = 0 you are cancelling what ever the key is supposed to do. By placing what you want it to do after cancelling what it wants to do prevents a mess of both. Get it.

As for the db next time don't send me the whole thing just an example form and example table(s). I'm traumatised enough by stamps as it is.

 

[maxmangion]

oh thx for the explanation, it's more clear to me now

However, i am more relieved by this

As for the db next time don't send me the whole thing just an example form and example table(s). I'm traumatised enough by stamps as it is.

because with condensed db with one form and related tables, i thought that my design was incorrect!

Thanks again!