2nd persistant messagebox occuring

[Aussiebear]

On entering new residue results via the New Residue form on the Risk Levels sheet, a second messagebox is occuring and I'm wondering why. My guess is that its retesting for a 'blank".

The initial code was

[VBA]Private Sub txtVendorTest_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim c As Range
Set c = RiskLevels.Columns("J:L").Find(txtVendorTest.Text)
If Not c Is Nothing Then
MsgBox "Test No. exists in Cell " & c.Address(0, 0)
Cancel = True
End If

End Sub[/VBA]

I have attached a stripped down version of the workbook. On the Risk Levels sheet, click on the New Residue button and a form will show.

Enter "462" in Grower ID, a caption will show "Agripark", enter "T2269-03-01" in Vendor Dec test and press enter. Up will pop a messagebox "Test No. exists in Cell J18" which is correct. Clicking "Ok" will get a second messagebox with an incorrect message and is harder to get rid of.

I have changed to code to the following, but the focus is not being reset to the initial entry box.

[VBA]Private Sub txtVendorTest_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim c As Range
Set c = RiskLevels.Columns("J:L").Find(txtVendorTest.Text)
If Not c Is Nothing Then
MsgBox "Test No. exists in Cell " & c.Address(0, 0)
On Error GoTo 0
'Clear data from form and reset focus to GrowerID
Me.txtGrowerID.Value = ""
Me.lblGrower.Caption = ""
Me.txtVendorTest.Value = ""
Me.txtGrowerID.SetFocus
End If

End Sub[/VBA]

What do I need to change to fix this issue? I'd prefer on entering an existing test number for the code to simply popup the messagebox indicating that the test number already exists and its cell location, then allow the user continue by re-entering a new residue result.

To view the code you will need to enter a password '"Shona"

[Bob Phillips]

[vba]

Private Sub txtVendorTest_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim c As Range
Static fReEntry As Boolean

If Not fReEntry Then
fReEntry = True
Set c = RiskLevels.Columns("J:L").Find(txtVendorTest.Text)
If Not c Is Nothing Then
MsgBox "Test No. exists in Cell " & c.Address(0, 0)
On Error GoTo 0
'Clear data from form and reset focus to GrowerID
Me.txtGrowerID.Value = ""
Me.lblGrower.Caption = ""
Me.txtVendorTest.Value = ""
Me.txtGrowerID.SetFocus
End If
fReEntry = False
End If

End Sub
[/vba]

[Aussiebear]

Thanks Bob

[Bob Phillips]

My pleasure.

BTW, shame about the match <snigger>

[Aussiebear]

The truth is your guys came ready to play...... our guys thought that by turning up they would be handed the win.

Congratulations to England for a game worth playing.

Tonight I heard a news snippert that suggested that the Australian Captain thought we had choked..... I believe that we had rigour mortis setting in ten minutes before the kick off.

I watched the match, and we were out gunned in every department. Our lineouts were ordinary,our scrums third rate, the ruck and maul were simply not attended....and the mistake rate was something you would see from a second string amateurs... very schoolboyish at best.

I feel absolutely gutted.....