Duplicate numbers

[Guest]

I am trying to set a warning when a duplicate project number is entered into
a field on my form. The index is set to Yes(No Duplicates), but it only
gives me the error when I have filledout the rest of the form. i need it to
prompt me when I enter the duplicate number. My table name is 2006 FB Quote
Data. The Field name is Project Number and the Form name is 2006 FB Quote
Approval Form. It is a simple database, but I am fairly new with this. Any
ideas?

 

[j_beverly]

There is probably an easier way... but this should work (and it's
something that an amateur like me can understand):

Put a hidden (Visible = No) listbox on the form [List1].
Set the listbox rowsource to = the Count of records where the Project
Number is equal to the number just entered. If the count is > 0,
display a message box with an error message.


For your Project Number "After Update" event":


Private Sub Project_Number_AfterUpdate()

List1.RowSource = "SELECT Count(*) FROM [2006 FB Quote Data] where
[2006 FB Quote Data].[Project Number] = " & [Project Number] & ";"
List1.Selected(0) = True
If List1.Column(0) > 0 Then MsgBox "Error - That number is already
used."

End Sub


=====================

 

[Guest]

Thank you. That worked.

There is probably an easier way... but this should work (and it's
something that an amateur like me can understand):

Put a hidden (Visible = No) listbox on the form [List1].
Set the listbox rowsource to = the Count of records where the Project
Number is equal to the number just entered. If the count is > 0,
display a message box with an error message.


For your Project Number "After Update" event":


Private Sub Project_Number_AfterUpdate()

List1.RowSource = "SELECT Count(*) FROM [2006 FB Quote Data] where
[2006 FB Quote Data].[Project Number] = " & [Project Number] & ";"
List1.Selected(0) = True
If List1.Column(0) > 0 Then MsgBox "Error - That number is already
used."

End Sub


=====================

I am trying to set a warning when a duplicate project number is entered into
a field on my form. The index is set to Yes(No Duplicates), but it only
gives me the error when I have filledout the rest of the form. i need it to
prompt me when I enter the duplicate number. My table name is 2006 FB Quote
Data. The Field name is Project Number and the Form name is 2006 FB Quote
Approval Form. It is a simple database, but I am fairly new with this. Any
ideas?

 

[j_beverly]

Actually I've just realized that will probably leave your form
"dirty", and you would have to hit ESC to go to the next field.
To fix that, you could add to it:

Private Sub Project_Number_AfterUpdate()
List1.RowSource = "Select Count([Project Number]) from [2006 FB Quote
Data] where [Project Number] = " & [Project Number] & ";"
List1.Selected(0) = True
If List1.Column(0) > 0 Then
MsgBox "Error - That number is already used."
[Project Number] = Null
Else
[Project Number] = [Project Number]
End If
End Sub

-----

Here's another way without adding a listbox to the form:

Private Sub Project_Number_AfterUpdate()
Dim PN As Integer 'ProjectNumber

PN = DCount("[Project Number]", "[2006 FB Quote Data]", "[Project
Number] = " & [Project Number])
If PN > 0 Then
MsgBox "Error - That number is already used"
[Project Number] = Null

End If

End Sub

--------

ALSO... you will probably want to send your users back to the Project
Number field to enter a valid number. In the next field on your form,
add a GotFocus event:

Private Sub [FIELD2]_GotFocus()
If IsNull([Project Number]) Then DoCmd.GoToControl "Project Number"
End Sub



============================

Thank you. That worked.

There is probably an easier way... but this should work (and it's
something that an amateur like me can understand):

Put a hidden (Visible = No) listbox on the form [List1].
Set the listbox rowsource to = the Count of records where the Project
Number is equal to the number just entered. If the count is > 0,
display a message box with an error message.


For your Project Number "After Update" event":


Private Sub Project_Number_AfterUpdate()

List1.RowSource = "SELECT Count(*) FROM [2006 FB Quote Data] where
[2006 FB Quote Data].[Project Number] = " & [Project Number] & ";"
List1.Selected(0) = True
If List1.Column(0) > 0 Then MsgBox "Error - That number is already
used."

End Sub


=====================

I am trying to set a warning when a duplicate project number is entered into
a field on my form. The index is set to Yes(No Duplicates), but it only
gives me the error when I have filledout the rest of the form. i need it to
prompt me when I enter the duplicate number. My table name is 2006 FB Quote
Data. The Field name is Project Number and the Form name is 2006 FB Quote
Approval Form. It is a simple database, but I am fairly new with this. Any
ideas?

 

[j_beverly]

As an amateur, the thing I like about trying to answer someone else's
question is it makes me think. And the more I think, the more I
learn.

Forget what I said before. Simply set your Validation Rule on the
textbox containing your field to:

DCount("[Project Number]","[2006 FB Quote Data]","[Project Number] = "
& [Project Number])=0

That means if you count all the records in the table which already
have that project number, the count must be zero.

I would also replace the generic Validation Text to something more
user-friendly and specific such as:
"That number is already in use. Please use another."