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
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