continuous form problem

[cassius]

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_KeyDownSelect
Case KeyCode
Case vbKeyDownDoCmd.GoToRecord , , acNext
KeyCode = 0

Case vbKeyUpDoCmd.GoToRecord , , acPrevious
KeyCode = 0

Case vbKeyReturnDoCmd.GoToRecord , , acNext
KeyCode = 0
Case Else
End Select
Exit SubErr_Form_KeyDown:
Select Case KeyCode
Case vbKeyDownMsgBox "There are no more records to display", vbExclamation + vbOKOnly, "Warning ..."
Case vbKeyUpMsgBox "You are already at the First Record", vbExclamation + vbOKOnly, "Warning ..."

Case vbKeyReturnMsgBox "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 ?

[Pat Hartman]

I don't know what is causing the conflict. I would put messageboxes in both events so that I could see when they run. Perhaps the keydown is being fired multiple times.

Sometimes using messageboxes or stepping through code will force something to work. When that happens, the problem was timing. When running normally, some piece of code isn't finishing before another piece starts. Durn fast computers!

[cassius]

thx for your reply. Kindly could you explain me further where would you add the message boxes which you suggested ?

Thanks !

[Pat Hartman]

At the beginning of each event procedure:

msgbox "afterupdate",vbokonly
....
...

msgbox "keydown",vbokonly

[cassius]

i'll see. i will try it out and get back on it.

Thanks!

[cassius]

when i click on the ToClean field as soon as i start typing the new value i get the msgbox Keydown, then as soon as i finish and i click the down or up arrow key i get another keydown message, as soon as i click ok i get the afterupdate message and after i am getting the msgbox "There are no more records to display" which i set in the on error of the keycode event !

any suggestions woould be greatly appreciated as i am totally stuck with this.

[Pat Hartman]

I'm off to do something else so I won't be back until tomorrow or late tonight.

I think you need to check the BOF and EOF properties of the recordsetclone before moving the recordset. If you search for recordsetclone, you should find sample code that shows how to define the recordset and manipulate it. I thinK it is:

Dim rs As DAO.Recordset
Set rs = me.recordsetclone

put the appropriate If statement into your case statement so you know if you're at the end of file or beginning of file and either ignore the requested move or pop a message.

If rs.EOF then .....

If rs.BOF then ...

[cassius]

i've tried your idea of using the rs.eof and set rs to recordsetclone but although the sorting worked fine, i still got the msgbox that i am at the last record (when in fact i am not).

i've tried to attach a sample of the db but unfortunately when zipped it is 284kb!

Thanks for your support, i really appreciated it :)

[Pat Hartman]

If you post the modified code, we'll look at it.

[TonyJollans]

Hi cassius,

I haven't tested this but the order of events should be ..

You press Down Arrow
Keydown event runs
GoTo Next Record Command first forces update of record (and the ToClean field)
ToClean Field Afterupdate event runs
In here you sort the Recordset
Now the GoTo Next Record tries to complete

My GUESS is that the sort is making you lose position in the recordset so that the GoTo Next can't complete properly and so your error trap kicks in.

Even if you make it work, I can't help thinking the movement will be counterintuitive if the sort causes the record you update and leave to move to somewhere else.

[sgrant]

The orginal issue is that when you add a new record to a table you are automatically at the end of the table, so when you goto the next record - it will save the new record, but there is not another record to goto which gives you your error message - which is logically correct.

I would suggest that in the goto next function when you get the no more records error you can state that (or not) and just set the form to the last record in the table.

Another point I see is that a recordset.refresh should resort your data if you are sorting on the table keys. -that may simplify your code a bit.