jauner
11-28-2005, 09:42 AM
Is there a way to control the automatic save in Access.
I need to do some processing and only save the record if certain criteria is met and if not to not save the record.
The reason this has come up is because I have a form where you enter questions where each question have a weight that based on its Account Type, Audit Type and CoreService need to add up to more then one. I need to save the record because I Need to have the updated value be included in the array. Right now even if I force the save it is not in the table until I close the form it seems. Here is the piece of code:
Private Sub Weight_AfterUpdate()
Dim varWeight As Variant
Dim dblWeightTotal As Double, lngWeightCount As Long
DoCmd.Save
varWeight = ReadWeight(CoreService, AuditType, AccountType)
For x = 1 To 10
If varWeight(x) = "" Or varWeight(x) = Null Then
varWeight(x) = 0
End If
dblWeightTotal = dblWeightTotal + varWeight(x)
If varWeight(x) > 0 Then lngWeightCount = lngWeightCount + 1
Next x
If dblWeightTotal > 1 Then
MsgBox "Total Weight in this Pot must be no more than 1", vbCritical, "Weight is over 1"
End If
If lngWeightCount > 10 Then
MsgBox "You Cannot have more then 10 questions in one Pot", vbCritical, "Too Many Questions"
End If
End Sub
The ReadWeight function is as follows:
Function ReadWeight(ByVal strCoreService As String, strAuditType As String, strAccountType As String) As Variant
' Program: ReadWeight()
' Programmer: Jesse Auner
' Date: 11/28/2005
' Description: This Function Creates an Array of Weighted values based on Query Provided
Dim dbs As Database
Dim rst As Recordset, rst2 As Recordset
Dim wrkjet As Workspace
Dim dblWeight(10) As Double
Dim x As Integer
x = 0
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblQuestions", dbOpenDynaset)
rst.Filter = "[CoreService] = '" & strCoreService & "' AND [AuditType] = '" & strAuditType & "' AND " _
& "[AccountType] = '" & strAccountType & "'"
Set rst2 = rst.OpenRecordset
With rst2
Do While Not rst2.EOF
dblWeight(x) = .Fields("Weight")
rst2.MoveNext
x = x + 1
Loop
End With
'Close Recordset and Database Connections
rst.Close
rst2.Close
dbs.Close
ReadWeight = dblWeight
End Function
I need to do some processing and only save the record if certain criteria is met and if not to not save the record.
The reason this has come up is because I have a form where you enter questions where each question have a weight that based on its Account Type, Audit Type and CoreService need to add up to more then one. I need to save the record because I Need to have the updated value be included in the array. Right now even if I force the save it is not in the table until I close the form it seems. Here is the piece of code:
Private Sub Weight_AfterUpdate()
Dim varWeight As Variant
Dim dblWeightTotal As Double, lngWeightCount As Long
DoCmd.Save
varWeight = ReadWeight(CoreService, AuditType, AccountType)
For x = 1 To 10
If varWeight(x) = "" Or varWeight(x) = Null Then
varWeight(x) = 0
End If
dblWeightTotal = dblWeightTotal + varWeight(x)
If varWeight(x) > 0 Then lngWeightCount = lngWeightCount + 1
Next x
If dblWeightTotal > 1 Then
MsgBox "Total Weight in this Pot must be no more than 1", vbCritical, "Weight is over 1"
End If
If lngWeightCount > 10 Then
MsgBox "You Cannot have more then 10 questions in one Pot", vbCritical, "Too Many Questions"
End If
End Sub
The ReadWeight function is as follows:
Function ReadWeight(ByVal strCoreService As String, strAuditType As String, strAccountType As String) As Variant
' Program: ReadWeight()
' Programmer: Jesse Auner
' Date: 11/28/2005
' Description: This Function Creates an Array of Weighted values based on Query Provided
Dim dbs As Database
Dim rst As Recordset, rst2 As Recordset
Dim wrkjet As Workspace
Dim dblWeight(10) As Double
Dim x As Integer
x = 0
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblQuestions", dbOpenDynaset)
rst.Filter = "[CoreService] = '" & strCoreService & "' AND [AuditType] = '" & strAuditType & "' AND " _
& "[AccountType] = '" & strAccountType & "'"
Set rst2 = rst.OpenRecordset
With rst2
Do While Not rst2.EOF
dblWeight(x) = .Fields("Weight")
rst2.MoveNext
x = x + 1
Loop
End With
'Close Recordset and Database Connections
rst.Close
rst2.Close
dbs.Close
ReadWeight = dblWeight
End Function