VB coding problem

[faizulhu]

I am trying to create a code that will allow data to be saved if the condition of the 2 fields is met. The 2 fields are "Copies" and "Borrow".

The thing I want to do is as follows:

If borrow<3 and return>1 then allow the record to be saved and subtract 1 from the value of return and add one to the value of borrow.

If borrow is not <3 then show msgbox1 and delete the last record of the table.

If return is not >1 then show msgbox2 and delete the last record of the table.

If both of the condition are negetive, then show msgbox3.

I wrote the folllowing code:
Private Sub Command22_Click()
On Error GoTo Err_Command22_Click

If Copies.Value > "1" And Borrow.Value < "3" Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Copies.Value = Copies.Value - 1
Borrow.Value = Borrow.Value + 1
Else
If Copies.Value = 1 Then
If MsgBox("There are no copies of this book in library", vbInformation + vbOKOnly, "Error") = vbOK Then
DoCmd.GoToRecord , , acLast
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit Sub
End If
If Borrow.Value = 3 Then
If MsgBox("The Borrower has reached his Borrowing Limit", vbInformation + vbOKOnly, "Error") = vbOK Then
DoCmd.GoToRecord , , acLast
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit Sub
End If
If Borrow.Value = 3 And Copies.Value = 1 Then
If MsgBox("The Borrower has reached his Borrowing Limit And There are no copies of this book in library", vbInformation + vbOKOnly, "Error") = vbOK Then
DoCmd.GoToRecord , , acLast
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit Sub
End If
End If
End If
End If
End If
Exit_Command22_Click:
Exit Sub

Err_Command22_Click:
MsgBox Err.Description
Resume Exit_Command22_Click

End Sub



The problem is that even if the borrow and return value does not match the condition, it allows the record to be saved but the value of the 2 fields are not updated. But i wanted it to show the message boxes.

I am a beginner in Access so please elaborate the solution of my problem.

 

[Pat Hartman]

1. do not use the the DoMenuItem methods. They refer to menu items from the menus as they existed in A95!!!! That would be 11 years and 5 versions ago.
2. Edits that validate multiple fields should be performed in the form's BeforeUpdate event. The only code in the button click event should be the save command:

If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
End If

Attempting to save the record will force the form's BeforeUpdate event to run and that will validate the data. If an error is found, cancel the update event with:
Cancel = True
3. Numeric values should not be enclosed in quotes.
4. Storing calculated values violates second normal form.
5. Each physical book should have its own unique ID and record. So, you would usually have two tables. A parent table that defined a book and a child table that defined a copy of that book. That would allow the library to have 0 - infinity copies of any defined book.

I think there is a library template or some other type of template that provides an example of rental activities, available from the MS downloads site. I haven't looked at them if there is one and I usually find that the MS examples are too simplistic as well as frequently violating accepted professional norms but the examples may give you some ideas on table structure and how to handle items that are loaned.