MS Access VBA Code on Form Field with Multiple If's to Prevent / Allow Entry Depending on Value Entered

Question

Hello, please assist with the following:

I am trying to prevent end users from being able to enter a value in the txtPlantUsedGrams (PlantAmountUsed) field that is greater than the amount in the txtExtractionAmountAvailable (ExtractionAvailable) field, but allow entry when the txtPlantUsedGrams value is less than the amount in the txtExtractionAmountAvailable field.

The code is working in the case where PlantAmountUsed is greater than ExtractionAvailable as it triggers the msgbox and then sets the txtPlantAmountUsed field to zero. In the case where PlantAmountUsed is less than ExtractionAvailable it allows the value entered to stick (does not change it to 0), but only after the msgbox is triggered and I click ok. The ‘exit sub’ code I inserted after the first If is not working. Please help me so that the following code does not trigger the msgbox and instead exits the sub in the case where the PlantAmountUsed is less than the ExtractionAvailable. I am open to any / all ways to accomplish this. Thank you for any help:)

Location of VBA Code:

Event is being run in form (frmMedMaking) as an AfterUpdate event in the txtPlantUsedGrams field.

**VBA Code:**

Private Sub txtPlantUsedGrams_AfterUpdate()

Dim PlantAmountUsed As Integer
Dim ExtractionAvailable As Integer
Dim LResponse As Integer

PlantAmountUsed = Me.txtPlantUsedGrams
ExtractionAvailable = Me.txtExtractAmountAvailable.Value
LResponse = MsgBox("Plant Amount Used must be less than Extraction 
Available", vbOKOnly + vbCritical, "Available Extraction Amount Exceeded")

If PlantAmountUsed < ExtractionAvailable Then Exit Sub
    If PlantAmountUsed > ExtractionAvailable Then
    If LResponse = vbOK Then Me.PlantAmountUsed.Value = 0
End If

End Sub

Answer 1

It seems that your problem comes from the fact, that you call the MsgBox before the condition If PlantAmountUsed < ExtractionAvailable Then is checked.

So the code must be restructured a bit.

Resulting Code

That would be the resulting code, based on your given informations:

Private Sub txtPlantUsedGrams_AfterUpdate()
    Dim plantAmountUsed As Integer
    plantAmountUsed = Me.txtPlantUsedGrams.Value

    Dim extractionAvailable As Integer
    extractionAvailable = Me.txtExtractAmountAvailable.Value

    If plantAmountUsed < extractionAvailable Then Exit Sub

    'What if plantAmountUsed = extractionAvailable (see below)?

    If plantAmountUsed > extractionAvailable Then
        ' Why checking the result of the MsgBox when only one button (Ok) can be pressed (see below)?
        If MsgBox("Plant Amount Used must be less than Extraction Available", vbOKOnly + vbCritical, "Available Extraction Amount Exceeded") = vbOK Then
            Me.txtPlantUsedGrams.Value = 0
        End IF
    End If
End Sub

Remarks

• I changed Me.PlantAmountUsed.Value = 0 to Me.txtPlantUsedGrams.Value = 0. I expect thats what was your intention, right?

Questions / suggestions

• What if plantAmountUsed equals extractionAvailable? This is not explicitely handled yet. You could use If plantAmountUsed <= extractionAvailable Then Exit Sub for example.

• In your MsgBox you use vbOKOnly, so the result is always vbOK, what would mean, that you wouldn't really have to check the result of the MsgBox. So this could be enough:

  If plantAmountUsed > extractionAvailable Then
      MsgBox "Plant Amount Used must be less than Extraction Available", vbOKOnly + vbCritical, "Available Extraction Amount Exceeded"
      Me.txtPlantUsedGrams.Value = 0
  End If