Stop requery returning to first record on form. 1

[cashe]

Hi All,

I have a common problem which I can not seem to stop. after the requery the forms records return to the begining. This is annoying for the users when there are 1500+ records. I need the requery but I can not use bookmark with a requery. The following code is under the command button.

If anyone has ideas to stop this it would be greatly appreciated.

Private Sub Command24_Click()

Dim prevClaim
prevClaim = DSum("amnt", "qryOldClaim", "contractorID=" & [contractorID] & _
" AND lastDateClaimed BETWEEN #" & [weekEnding] & "# AND #" & [weekEnding] & "#+31")
If Not IsNull(prevClaim) Then
If vbNo = MsgBox("Warning - we already have a claim for £" & prevClaim & vbCrLf & _
"Do you want to continue?", vbYesNo) Then
Exit Sub
End If
End If

'Mark this week as approved and copy the values to tblExpenses
Dim sql As String
sql = "UPDATE tblWeekly SET ConfirmedP4=Int(now()) WHERE weekEnding=" & _
fmt([weekEnding]) & " AND contractorID = " & [contractorID]
DoCmd.SetWarnings False
DoCmd.RunSQL sql
sql = "UPDATE tblClaim SET ConfirmedP4=now() WHERE [day] BETWEEN " & _
fmt([weekEnding] - 6) & " AND " & fmt([weekEnding]) & _
" AND contractorID = " & [contractorID]
DoCmd.RunSQL sql
sql = "INSERT INTO tblAExpenses(CONTRACTORid,entryDate,lastDateClaimed,payRollDate,[format]," & _
"mileage,travel,[hotel],[phone],other,vat) VALUES " & _
"(" & contractorID & ", Now(), " & fmt([weekEnding]) & "," & fmt([payroll]) & ",'web'," & _
[mileage] & "," & [travel] & "," & [hotel] & "," & [phone] & "," & [other] & "," & [VAT] & ")"
DoCmd.RunSQL sql
DoCmd.SetWarnings True

Me.Requery

End Sub

 

[stephenk1973]

I had a similar problem on a continous form where it would scroll all the way back to the first record.

I resolved this by having text boxes with calculations to display modified values (Queries to update could be run in the background once the record was left). Instead of using Me.Requery I used Me.Refresh which doesn't quite complete the same level of re-interrogation and therefore the form doesn't move.

Stephen

 

[cashe]

Thanks Stephen for your reply.

I don't think this would work because the record is being removed from the form in to another table. Then te requery is run to remove the ghost record from the form.

 

[RoyVidar]

Correct, you can't use bookmark, because a requery might invalidate it.

You're saying a record is moved, would that mean that the current record is supposed to "dissapear"?

Well, the method of staying on the same record as before the requery, involves storing the PK value, and using a .FindFirst afterwards, something like this with a numeric PK:

[tt]dim lPK as long
dim rs as dao.recordset
' your code
lPK = Me!txtControlHoldingPK.Value
me.requery
set rs = me.recordsetclone
rs.findfirst "PKField = " & lPK
if not rs.nomatch then me.bookmark=rs.bookmark
set rs=nothing[/tt]

Should it be the current record you're removing, you could for instance use a docmd.gotorecord previous/next, and fetch the PK value from that record.

Roy-Vidar

 

[cashe]

HI Roy-Vidar,

Thanks for you response.

Your method partially works. It has stopped the records from returning to the first record after the requery. However the requery does not work all the time. Some times the requery fails and the record stays on the form. If the user clicks the command button again the record is removed from the form and entered in to tblExpenses but there are now 2 records in tblExpenses because the first time the requery failed.

Is there away to improve the requery so it does not fail when the user clicks the cmdbutton the first time?

My code now reads:

Dim lPK As Long
Dim rs As DAO.Recordset


'Mark this week as approved and copy the values to tblExpenses
Dim sql As String
sql = "UPDATE tblWeekly SET ConfirmedP4=Int(now()) WHERE weekEnding=" & _
fmt([weekEnding]) & " AND contractorID = " & [contractorID]
DoCmd.SetWarnings False
DoCmd.RunSQL sql
sql = "UPDATE tblClaim SET ConfirmedP4=now() WHERE [day] BETWEEN " & _
fmt([weekEnding] - 6) & " AND " & fmt([weekEnding]) & _
" AND contractorID = " & [contractorID]
DoCmd.RunSQL sql
sql = "INSERT INTO tblAExpenses(CONTRACTORid,entryDate,lastDateClaimed,payRollDate,[format]," & _
"mileage,travel,[hotel],[phone],other,vat) VALUES " & _
"(" & contractorID & ", Now(), " & fmt([weekEnding]) & "," & fmt([payroll]) & ",'web'," & _
[mileage] & "," & [travel] & "," & [hotel] & "," & [phone] & "," & [other] & "," & [VAT] & ")"
DoCmd.RunSQL sql
DoCmd.SetWarnings True

lPK = Me!contractorID.Value
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "contractorID = " & lPK
If Not rs.nomatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing

Thanks in advance

Cashe

 

[RoyVidar]

Hi again!

Unless someone else pops in here and spots something obvious that I've missed, I'm stumped. Going to do a little thoughts and guessing though, but emphasize it's only thoughts and guessing

I don't think it's the requery failing, I think it must be some other issues, perhaps timing?

You didn't have any case of "failed" requery before at all? You don't delete the record here, does the query the form is based on filter out this record? If this is the case, it seems you're storing the same data in more than one table, would it be enough just changing a status or something on the record? Prolonging the "moral" talk, you could perhaps try using some compound/combined index on the tblAExpenses, preventing a second update/append;-)

Try fetching the PK of the current record at the start of the routine, in stead of just before the requery, and see if that helps

If it is a timing issue, then it implies the queries are still running at the time of requery.

One simple suggestion, add another requery below the first one (perhaps two requeries deos the trick?)

You might try some of the other methods of running action queries, here's a method by Allen Browne Archive: Move Records to Another Table using DAO and transaction.

Something very dirty that has worked in a similar setting, is to clear the forms recordsource and reset it again, but it's perhaps not bulletproof either (or make a little wait routine, which is also far from bulletproof)

[tt]lPK = Me!contractorID.Value ' at the top
' rest of the code
application.echo false
me.recordsource=vbNullstring
me.recordsource="NameOfQuery"
application.echo true
Set rs = Me.RecordsetClone
rs.FindFirst "contractorID = " & lPK
If Not rs.nomatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing[/tt]

Roy-Vidar

 

[KenReay]

Hi

Like you Roy, I cannot see anything wrong with your code or the basic idea, I have used it many times, but..

I am not so sure about the basic table design

It appears to be an expenses system, when a claim is approved, that row is copied to another table, and then the original row is deleted, why not just keep it all in one table and filter out approved records based on the ConfirmedPd column ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

sorry that should have been ConfirmedP4 column

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dmassey]

I have been working on a problem very similar to this one. My problem was that I have a button on a continuous form that will delete or modify the record depending on certain criteria. After the button has been pressed, I needed the to requery the continuous form and be at the exact same position. Since my current record may be deleted, I needed the top record that was visible before the button click to be visible after the button click. The really hard part of this is to find which record is the first record visible.

I found a solution for this problem at the following URL

http://www.lebans.com/setgetsb.htm

The source code provided also allows you to keep the horizontal scrollbar at the same position through a requery of the form.

My database is a multiuser database, so what I do is the following.

1. get the current scroll position
2. Use recordsetclone to find which record is at that scroll position.
3. Requery the continuous form
4. use recordsetclone and find which record number the record is now at.
5. Set the scroll position for the form to the new position