Yes No buttons

Sometimes you need the user to answer Yes or No in order to carry out a function. This usually happens when you need the user decide if the user wants to carry on with a posting entry.

Dim LResponse As Integer

LResponse = MsgBox("Do you wish to continue?", vbYesNo, "Continue")

If LResponse = vbYes Then
   {...statements...}
Else
   {...statements...}
End If

Rounding function

When you do calculations, you may wish to round your numbers.

You used to have to use the RoundCC function. It gets easier now!

https://www.techonthenet.com/access/functions/numeric/round.php - For detailed information

In your Calculated field, you may wish to do this: Round([Item_cost]/3,0) which means, you do not have any decimal places, everything is rounded to the nearest dollar.

Check for duplicates

In the sales, purchases... forms, you want to avoid keying in duplicate entries. However, ms access does not afford you a simple method to stop an input in forms.

You have to create a count query and add it as a subform.

To check for duplicates, you will add in an event

If (Forms![Frm_tmp_arrecptmain]![Tmp_arrecdup subform].Form![CountOfReceipt_No]) <> 0 Then
MsgBox "Duplicate number, please enter new number", vbCritical, "Profit 2017"
DoCmd.GoToControl "Bank_value"
DoCmd.GoToControl "Receipt_no"
End If

Delete query for last record in the table

Sometimes you would like to format a table for reporting purposes.

The last row would usually be the sum of all the above records.

To delete the last row and append a new sum, you can use this query.

DoCmd.RunSQL "" & _
"Delete [PPE c].ID" & _
" FROM [PPE c]" & _
" WHERE ((([PPE c].ID)=DMax(""[ID]"",""[PPE C]"")));"