mercredi 31 octobre 2018

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

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

Aucun commentaire:

Enregistrer un commentaire