access updates wrong record

[Fred Loh]

i have a form bound to a table with a field called "Balance". on the form a
combo box is used to select a record on the form. a text box allows user to
enter a transaction amount. a button then runs code to add the transaction
amount to the "Balance" field of the selected record on the form using =>
me![balance] = me![balance] + me.textboxtransactionamount.

the problem is that, occasionally and quite consistently, for every batch of
transactions, there is at least one occurrence where the transaction amount
is added to the "Balance" field of another record, a wrong record, i.e. not
the record selected on the form.

what could be the cause of this problem? is this a known issue? i am using
access 2002.

thanks for your help and enlightenment.

 

[Klatuu]

Can you post the code and/or query you are using to do this?
There is no known bug that would cause that.

 

[Fred Loh]

ok. i will post the code on 10 may 2008

Can you post the code and/or query you are using to do this?
There is no known bug that would cause that.
--
Dave Hargis, Microsoft Access MVP

i have a form bound to a table with a field called "Balance". on the form a
combo box is used to select a record on the form. a text box allows user to
enter a transaction amount. a button then runs code to add the transaction
amount to the "Balance" field of the selected record on the form using =>
me![balance] = me![balance] + me.textboxtransactionamount.

the problem is that, occasionally and quite consistently, for every batch of
transactions, there is at least one occurrence where the transaction amount
is added to the "Balance" field of another record, a wrong record, i.e. not
the record selected on the form.

what could be the cause of this problem? is this a known issue? i am using
access 2002.

thanks for your help and enlightenment.

 

[Klatuu]

Okay, I will have a look when I get in on Monday (12)

--
Dave Hargis, Microsoft Access MVP

ok. i will post the code on 10 may 2008

Can you post the code and/or query you are using to do this?
There is no known bug that would cause that.
--
Dave Hargis, Microsoft Access MVP

i have a form bound to a table with a field called "Balance". on the form a
combo box is used to select a record on the form. a text box allows user to
enter a transaction amount. a button then runs code to add the transaction
amount to the "Balance" field of the selected record on the form using =>
me![balance] = me![balance] + me.textboxtransactionamount.

the problem is that, occasionally and quite consistently, for every batch of
transactions, there is at least one occurrence where the transaction amount
is added to the "Balance" field of another record, a wrong record, i.e. not
the record selected on the form.

what could be the cause of this problem? is this a known issue? i am using
access 2002.

thanks for your help and enlightenment.

 

[Fred Loh]

here's the code. i will post the combo box code on 12 May 2008. take note
that me.[account number] and me.[balance] belongs to that of the wrong record.

--- code starts here ---

private sub MyButton_OnClick

'do not allow blank or invalid data.
If (IsDataComplete = False) Then

GoTo Exit_commandDepositWithdraw_Click

End If

If (Me.textboxTransactionAmount.Visible = False) Then 'make sure the
textboxTransactionAmount control is visible to ensure user has keyed in the
transaction amount.

MsgBox "Please make sure the transaction amount is entered.",
vbOKOnly, MSGBOX_TITLE
GoTo Exit_commandDepositWithdraw_Click

End If

'do not allow cash transaction and non-cash (e.g. cheque) transaction to
be made in the same transaction.
'a cash transaction will require 1 form to be filled and a non-cash
transaction will require another form to be filled.

Select Case UCase(Me.textboxTransactionAction)
Case Is = "WITHDRAWAL"

'do not allow withdrawal that exceeds balance.
If ((Me.textboxTransactionAmount) > Me.[Bank Balance]) Then

MsgBox "The withdrawal amount of " +
Format(Me.textboxTransactionAmount, "$#,###.00") + " is greater than the
available balance of " + Format(Me.[Bank Balance], "$#,###.00") + ". The
withdrawal cannot be made.", vbOKOnly, MSGBOX_TITLE
GoTo Exit_commandDepositWithdraw_Click

End If

End Select

'get user's confirmation on the transaction.
If (MsgBox("You have chosen to make a " &
UCase(Me.textboxTransactionAction) & ". Is this correct?", vbYesNo,
MSGBOX_TITLE) = vbNo) Then 'UCase(Me.textboxTransactionAction) is either
"DEPOSIT" or "WITHDRAWAL".

GoTo Exit_commandDepositWithdraw_Click

End If

DoCmd.OpenReport "report TransactionRecord" 'print a record of the
deposit/withdrawal transaction.

'update transactions history table.
Dim cnn1 As ADODB.Connection
Dim rstTransactionsHistory As ADODB.Recordset
Dim rstCashInHand As ADODB.Recordset
Dim strTransactionReference As String

' open a connection.
Set cnn1 = New ADODB.Connection
cnn1.Open DATABASE_CONNECTION_STRING
'Set cnn1 = Application.CurrentProject.Connection

' open transactions history table.
Set rstTransactionsHistory = New ADODB.Recordset
rstTransactionsHistory.CursorType = adOpenKeyset
rstTransactionsHistory.LockType = adLockOptimistic
rstTransactionsHistory.Open "TransactionHistory", cnn1, , , adCmdTable

' open cash in hand table.
Set rstCashInHand = New ADODB.Recordset
rstCashInHand.CursorType = adOpenKeyset
rstCashInHand.LockType = adLockOptimistic
rstCashInHand.Open "CashInHand", cnn1, , , adCmdTable

'update transactions history table.
rstTransactionsHistory.AddNew
rstTransactionsHistory![Account Number] = Me.[Account Number]
rstTransactionsHistory![Date] = Me.textboxDate
rstTransactionsHistory![Time] = getTimeIn24HoursFormat(Time)

Select Case UCase(Me.textboxTransactionAction)
Case Is = "DEPOSIT"

rstTransactionsHistory![Amount] = Me.textboxTransactionAmount

Case Is = "WITHDRAWAL"

rstTransactionsHistory![Amount] = (Me.textboxTransactionAmount) * -1
'store number as negative number for withdrawal.

End Select

rstTransactionsHistory![Transaction Reference] =
Me.textboxTransactionReference
rstTransactionsHistory![Transaction Code] = Me.comboSelectTransactionType
rstTransactionsHistory![Remarks] = Me.textboxRemarks

Select Case Me.comboSelectTransactionMode
Case Is = "CASH"

rstTransactionsHistory![Transaction Mode] = "C" 'cash transaction.

Case Is = "NON CASH"

rstTransactionsHistory![Transaction Mode] = "Q" 'non cash transaction.

End Select

rstTransactionsHistory.Update

'update cash in hand table
If (Me.comboSelectTransactionMode = "CASH") Then 'update cash in hand
only for cash transaction.

Select Case UCase(Me.textboxTransactionAction)
Case Is = "DEPOSIT"

'add amount to cash in hand balance.
rstCashInHand![Cash Balance] = rstCashInHand![Cash Balance] +
Me.textboxTransactionAmount

Case Is = "WITHDRAWAL"

'deduct amount from cash in hand balance.
rstCashInHand![Cash Balance] = rstCashInHand![Cash Balance] -
Me.textboxTransactionAmount

End Select

End If

rstCashInHand.Update

rstTransactionsHistory.Close
Set rstTransactionsHistory = Nothing
rstCashInHand.Close
Set rstCashInHand = Nothing
cnn1.Close
Set cnn1 = Nothing

'update member's members' bank balance.
Select Case UCase(Me.textboxTransactionAction)
Case Is = "DEPOSIT"

'add amount to member's balance.
Me.[Bank Balance] = Me.[Bank Balance] + Me.textboxTransactionAmount

Case Is = "WITHDRAWAL"

'deduct amount from member's balance.
Me.[Bank Balance] = Me.[Bank Balance] - Me.textboxTransactionAmount

End Select

Me.Requery 'update display in form and [Bank Balance] field in members
table.

If (Me.comboSelectTransactionType.Column(4) = True) Then 'check if
transaction type auto increment transaction number.

UpdateLastUsedTransactionNumber 'update table with last used
transaction number.

End If

'remove amount of previous transaction so user don't accidentally
deposit/withdraw amount more than once by clicking the deposit/withdrawal
button.
Me.textboxTransactionAmount = 0

'clear data of current transaction to prepare for next new transaction.
This will prevent old data from being saved accidentally.
Me.comboSelectAMember = ""
Me.textboxAccountNumber = ""
Me.textboxBankBalance = ""
Me.textboxName = ""
Me.textboxMemberRemarks = ""
Me.comboSelectTransactionType = ""
Me.textboxTransactionAction = ""
Me.textboxTransactionReference = ""
Me.textboxRemarks = ""
Me.commandDepositWithdraw.Caption = "Deposit/Withdrawal"
Me.commandDepositWithdraw.Enabled = False
Me.labelAmountToDepositWithdraw.Visible = False
Me.textboxTransactionAmount.Visible = False
Me.comboSelectTransactionMode = ""

end sub

--- code ends here ---

 

[Fred Loh]

here are the code behind the combo code and the subroutine IsDataComplete.
Last week there was a batch with only one transaction and still the wrong
record was updated.



--- code starts here ---
Private Sub comboSelectAMember_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Account Number] = '" & Me![comboSelectAMember] & "'"
Me.Bookmark = rs.Bookmark

Me.textboxAccountNumber.Value = Me![Account Number] 'display account
number of selected member.
Me.textboxBankBalance.Value = Me![Bank Balance] 'display bank balance of
selected member.
Me.textboxName.Value = Me![Name] 'display name of selected member.
Me.textboxMemberRemarks.Value = Me![Remarks] 'display remarks for
selected member.

End Sub



Private Function IsDataComplete() As Boolean

'do not allow invalid or empty value to be entered.

Dim bDataComplete As Boolean

bDataComplete = True 'initialise result

'member name must be selected.
If (IsNull(Me.comboSelectAMember) Or IsEmpty(Me.comboSelectAMember) Or
Me.comboSelectAMember = "") Then

MsgBox "A member has not been selected. Please select a member.",
vbOKOnly, MSGBOX_TITLE
Me.comboSelectAMember.SetFocus
bDataComplete = False
GoTo Exit_IsDataComplete

End If

'transaction type must be selected.
If (IsNull(Me.comboSelectTransactionType) Or
IsEmpty(Me.comboSelectTransactionType) Or Me.comboSelectTransactionType = "")
Then

MsgBox "A transaction type has not been selected. Please select a
transaction type.", vbOKOnly, MSGBOX_TITLE
Me.comboSelectTransactionType.SetFocus
bDataComplete = False
GoTo Exit_IsDataComplete

End If

'do not allow negative amount unless transaction is for "opening
balance". allow negative amount when keying in opening balance because some
members have negative balances.
If (Me.comboSelectTransactionType <> "OB") Then

If (Me.textboxTransactionAmount < 0) Then

MsgBox "The amount to deposit/withdraw cannot be less than
zero.", vbOKOnly, MSGBOX_TITLE

If (Me.textboxTransactionAmount.Visible = True) Then

Me.textboxTransactionAmount.SetFocus

End If

bDataComplete = False
GoTo Exit_IsDataComplete

End If

End If

'do not allow zero amount.
If (Me.textboxTransactionAmount = 0) Then

MsgBox "The amount to deposit/withdraw is a zero. There is nothing
to deposit/withdraw.", vbOKOnly, MSGBOX_TITLE

If (Me.textboxTransactionAmount.Visible = True) Then

Me.textboxTransactionAmount.SetFocus

End If

bDataComplete = False
GoTo Exit_IsDataComplete

End If

'do not allow a blank amount.
If (IsNull(Me.textboxTransactionAmount) Or
IsEmpty(Me.textboxTransactionAmount) Or (Me.textboxTransactionAmount = ""))
Then

MsgBox "The amount to deposit/withdraw is a blank. There is nothing
to deposit/withdraw.", vbOKOnly, MSGBOX_TITLE

If (Me.textboxTransactionAmount.Visible = True) Then

Me.textboxTransactionAmount.SetFocus

End If

bDataComplete = False
GoTo Exit_IsDataComplete

End If

Exit_IsDataComplete:

IsDataComplete = bDataComplete
Exit Function

End Function


--- code ends here ---