If and Else - very simple question

[tygereye]

Hi there
I have undertaken the adventure of building a database for my company to log test results of raw materials.
I have set up the tables and forms and everything is working perfectly. The only problem I have is the following.
I want a popup message to appear when a batch number is duplicated, so no duplicate batch entries are logged.
The problem is, even though a batch number is NOT duplicated, the message pops up regardless, and the entry is cancelled.
What I need to happen is that, if the number is not duplicated, the record must be entered as if there were no conditions.
Here is the code I used. I got it from another forum from 2003(?)
Private Sub Batch_Number_BeforeUpdate(Cancel As Integer)


If DCount("[Batch_Number]", "tblBatches", "[Batch_Number]='" & Me.Batch_Number & "'") Then
MsgBox "This batch number has already been logged!" & _
vbCrLf & "For Re-Assays, add suffix RE1 or RE2, as necessary.", vbOKOnly, "Duplicated entry"
Cancel = True
Me.Undo
Exit Sub
Else 'Do nothing
End If
End Sub

Thanks in advance!
Brenda

[RuralGuy]

Try this code:

Private Sub Batch_Number_BeforeUpdate(Cancel As Integer)
   With Me.RecordsetClone
      .FindFirst "[Batch_Number]='" & Me.Batch_Number & "'"
      If Not .NoMatch Then
         MsgBox "This batch number [" & Me.Batch_Number & "] has already been logged!" & vbCrLf & _
                "For Re-Assays, add suffix RE1 or RE2, as necessary.", vbOKOnly, "Duplicated entry"
         Me.Batch_Number.Undo
         Cancel = True
      End If
   End With
End Sub

[tygereye]

Thanks, RuralGuy!
Access did complain when I entered the duplicate, but gave this pop-up instead:
Run-time error '3345'
Unknown or invalid field reference '[Batch_Number]'.

When I chose the option to de-bug, the line starting with ".FindFirst..." was highlighted.
I will value your advice again.

[RuralGuy]

Does the Record Source of this form have a field named "Batch_Number"? Is the "tblBatches" table the Record Source of this form?

[tygereye]

Thanks, RuralGuy - the field in the table is named LotNumber. Should I just replace every "Batch-Number" with LotNumber in your code above?
I am not at the database now, but will try it then tomorrow.

[RuralGuy]

We need to know some information first. Is the "tblBatches" table the Record Source of this form?

[tygereye]

Yes, it is.

[tygereye]

Perhaps this is significant:
The table in question is called tblBatches.
The field is called LotNumber.
However, when I have the form open in Design View and select the field in question (LotNumber) and click on 'View Code', the dropdown menu of all the items in the form does not contain 'LotNumber', only 'Batch_Number'. That's probably why I used that name in my initial code instead of LotNumber.
Upon further investigation I found (still on the Code page) on the left under Properties, it says, "Properties - Batch Number".
"Batch Number - Text Box" is shown just below where there's a drop-down menu for all the items in the form.
Next to (Name) it says "Batch Number"
Next to ControlSource it's "LotNumber"
Next to EventProcPrefix it says "Batch_Number"

Hopefully this will shed some more light on the subject.

[RuralGuy]

It is very common and actually recommended that controls are named different than the field to which they are bound. It makes it much easier to reference either one in code because they have different names. Change your code to:

Private Sub Batch_Number_BeforeUpdate(Cancel As Integer)
   With Me.RecordsetClone
      .FindFirst "LotNumber ='" & Me.Batch_Number & "'"
      If Not .NoMatch Then
         MsgBox "This batch number [" & Me.Batch_Number & "] has already been logged!" & vbCrLf & _
                "For Re-Assays, add suffix RE1 or RE2, as necessary.", vbOKOnly, "Duplicated entry"
         Me.Batch_Number.Undo
         Cancel = True
      End If
   End With
End Sub

...and it should then work.

[tygereye]

OK.
We are making progress. However, it's not yet exactly what I need.
I would like the pop-up to appear as soon as the duplicate entry is entered. At the moment it popped up only when I saved the new record after completing all the info on the form.
And the error message that popped up was (=[MacroError].[Description]), instead of the text specified for the message box.

[RuralGuy]

You should see the error as soon as the cursor attempts to leave the Batch_Number control! It should not allow the cursor out of the control unless it is either blank or contain a non-duplicated value. It also sounds like you might have a macro in the BeforeUpdate event of the form. Do you?

[tygereye]

Not intentionally. How do I look for it?
The only other code I can see (and what I have put in) is this:
Private Sub Form_Current()
If Me.NewRecord = True Then
Combo40.SetFocus
End If
End Sub

But it shouldn't affect that?

[RuralGuy]

Are you sure it is in the BeforeUpdate event of the Batch_Number control?

[tygereye]

Hi RuralGuy
Here's a screenshot. Does this help?

[RuralGuy]

Have you single stepped the code to see if it is executing? Something is definitely wrong here.