Msgbox this Number already exists (1 Viewer)

azhar2006

Registered User.
Local time
Today, 06:44
Joined
Feb 8, 2012
Messages
202
Hello again .
I have a field in the table with properties of a number. It is filled by users. This field is roughly a unique number for each person that I don't want it to be repeated for someone else. I thought of using DCount. If a duplicate number is entered by a user, a message pops up saying that this number already exists. Make sure to type the number again. But I could not use the expression DCount in the form field.
The name of the field in the table is (StatFig).
Thank you so much
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:44
Joined
Oct 29, 2018
Messages
21,357
You would use DCount() in the BeforeUpdate event of StatFig.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:44
Joined
Feb 28, 2001
Messages
26,999
You might have two options.

First, as noted by theDBguy, use the BeforeUpdate event for that field.

Second, don't let the user enter the number. Assign the number for them with the BeforeUpdate event of the overall form, where if everything else is OK, you then use some formula to assign the next number. Then in the AfterUpdate event of the form, you can use a message box to TELL them their number. This latter option is based on the idea that you should never let a user do anything they can do incorrectly. In turn, that comes from the old "Murphy's Law" - "That which CAN go wrong WILL go wrong." (So don't LET it go wrong.)
 

azhar2006

Registered User.
Local time
Today, 06:44
Joined
Feb 8, 2012
Messages
202
You might have two options.

First, as noted by theDBguy, use the BeforeUpdate event for that field.

Second, don't let the user enter the number. Assign the number for them with the BeforeUpdate event of the overall form, where if everything else is OK, you then use some formula to assign the next number. Then in the AfterUpdate event of the form, you can use a message box to TELL them their number. This latter option is based on the idea that you should never let a user do anything they can do incorrectly. In turn, that comes from the old "Murphy's Law" - "That which CAN go wrong WILL go wrong." (So don't LET it go wrong.)
Thank you very much, my dear friend (The_Doc_Man) The number cannot be written by a user or by the program itself. This number is a military number that comes from third parties assigning this number to the registry. I am not a specialist or program. It is a number with a specific symbol.
 

azhar2006

Registered User.
Local time
Today, 06:44
Joined
Feb 8, 2012
Messages
202
You would use DCount() in the BeforeUpdate event of StatFig.
Dear friend (theDBguy) I know I'm putting the expression in the BeforeUpdate event of StatFig but can you correct the formula for me please.
If DCount("*"; " StatFig = '" & Me.txtStatFig & "'") > 0 then
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:44
Joined
Oct 29, 2018
Messages
21,357
Dear friend (theDBguy) I know I'm putting the expression in the BeforeUpdate event of StatFig but can you correct the formula for me please.
If DCount("*"; " StatFig = '" & Me.txtStatFig & "'") > 0 then
Yes, the syntax should be along these lines:
Code:
DCount("*", "TableNameHere", "StatFig='" & Me.txtStatFig & "'")
 

azhar2006

Registered User.
Local time
Today, 06:44
Joined
Feb 8, 2012
Messages
202
Yes, the syntax should be along these lines:
Code:
DCount("*", "TableNameHere", "StatFig='" & Me.txtStatFig & "'")
Code:
Private Sub txtStatFig_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblmastr", "StatFig='" & Me.txtStatFig & "'") Then
MsgBox " This Number already exists. Please enter a unique Number to continue.", , "Error"
End If
End Sub
 

Attachments

  • image_2021-11-29_225655.png
    image_2021-11-29_225655.png
    13.9 KB · Views: 311

theDBguy

I’m here to help
Staff member
Local time
Today, 06:44
Joined
Oct 29, 2018
Messages
21,357
Code:
Private Sub txtStatFig_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblmastr", "StatFig='" & Me.txtStatFig & "'") Then
MsgBox " This Number already exists. Please enter a unique Number to continue.", , "Error"
End If
End Sub
If StatFig is a Number field, then take out the single quotes delimiters. For example:
Code:
If DCount("*", "tblmastr", "StatFig=" & Me.txtStatFig) > 0 Then
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:44
Joined
Feb 28, 2001
Messages
26,999
OK, I hear you that the number is assigned elsewhere and neither you nor the user has a choice in the matter. But then, how could you get a duplicate if the number is assigned elsewhere? Are the number sources potentially fallible? OR is the problem that the user has possibly copied the assigned number incorrectly?

You can check the number quickly in the control's LostFocus event. If the number is blank, that would be one message, to please enter the number. If the number is not blank but the DCount is 1, you can issue a different message about potential duplicates. And, in the control's LostFocus event, that number should not have been saved yet, so the DCount should show up as 0. Then the only problem is to have a software flag somewhere to show that the record already has been saved so that you don't complain about someone "passing through" the record. I.e. you need to differentiate between behaviors for a new record vs. an existing one.
 

azhar2006

Registered User.
Local time
Today, 06:44
Joined
Feb 8, 2012
Messages
202
OK, I hear you that the number is assigned elsewhere and neither you nor the user has a choice in the matter. But then, how could you get a duplicate if the number is assigned elsewhere? Are the number sources potentially fallible? OR is the problem that the user has possibly copied the assigned number incorrectly?

You can check the number quickly in the control's LostFocus event. If the number is blank, that would be one message, to please enter the number. If the number is not blank but the DCount is 1, you can issue a different message about potential duplicates. And, in the control's LostFocus event, that number should not have been saved yet, so the DCount should show up as 0. Then the only problem is to have a software flag somewhere to show that the record already has been saved so that you don't complain about someone "passing through" the record. I.e. you need to differentiate between behaviors for a new record vs. an existing one.
ِAHa, good question dearThe_Doc_Man The number comes on the paper while one of the employees is performing his job, so the user transfers this number from the paper to the program. We have previously heard in the narrations that the Devil slip Adam and made him eat from that tree. This was the result of Adam's little slip that expelled him from Paradise. The data entry may inadvertently transmit a number that already exists. Therefore, you, the programmers, always try to reduce losses for users.
 

azhar2006

Registered User.
Local time
Today, 06:44
Joined
Feb 8, 2012
Messages
202
If StatFig is a Number field, then take out the single quotes delimiters. For example:
Code:
If DCount("*", "tblmastr", "StatFig=" & Me.txtStatFig) > 0 Then
Thank you my friend DBG The code worked well and the message also appears telling us what happened, but the problem is that the program keeps moving forward. I try to return to the field (txtStatFig) again, and I cannot continue until the number is changed.
Code:
Me.txtStatFig.SetFocus
Cancel = True
Me.txtStatFig.Undo
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:44
Joined
Oct 29, 2018
Messages
21,357
Thank you my friend DBG The code worked well and the message also appears telling us what happened, but the problem is that the program keeps moving forward. I try to return to the field (txtStatFig) again, and I cannot continue until the number is changed.
If you're using the BeforeUpdate event, don't forget to include Cancel=True.
 

Users who are viewing this thread

Top Bottom