Is It possible to bybass the special situation

[jayjayplane]

I have one text box, and one command, text box for inputting 9 digitS number,,the command to check if the inputs are duplicated, if duplicated, then give error message, my code:

Private Sub Command42_Click()
On Error GoTo Err_Command42_Click
 
    Dim strSQL As String
    Dim strsql2 As String
    Dim db As Database
    Dim strMsg As String
 
    Dim rs As DAO.Recordset
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    Set db = CurrentDb()
    strSQL = "SELECT * from A "
    strsql2 = " WHERE A_NUMBER  = '" & Me.txtSSN & "' "
    Set rs = db.OpenRecordset(strSQL + strsql2, dbOpenSnapshot)
 
    If rs.EOF = False Then
 
        MsgBox "Person with the same A number already exists in the DB", vbCritical
 
    Else
 
        strSQL = "INSERT INTO A  (A_NUMBER) VALUES ('" & Me.txtSSN & "')"
        db.Execute strSQL
        Me.A_LIST_SUBFORM_QRY_subform.Requery
 
        stDocName = "LOP"
        stLinkCriteria = "[A_NUMBER]=" & "'" & Me.txtSSN & "'"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
 
 
    End If
 
      Set rs = Nothing
      Set db = Nothing
 
 
Exit_Command42_Click:
    Exit Sub
 
Err_Command42_Click:
    MsgBox Err.Description
    Resume Exit_Command42_Click
 
End Sub
 
 

this is a working code, but right now I want to bypass the '000000000', that means I only allow to be duplicated only if txtSSN value is '000000000' , anyone can help on this? Many thanks~~

[DonRayner]

Change Line 19 to

 If rs.EOF = False or rs!A_NUMBER = 000000000 Then
 

[jayjayplane]

Change Line 19 to

 If rs.EOF = False or rs!A_NUMBER = 000000000 Then
 

Thank you, I will try tomorrow.

[DonRayner]

Thank you, I will try tomorrow.

Oops, I looked at it again and that won't work. It should have been

 If rs.EOF = False and rs!A_NUMBER <> 000000000 Then
 

[jayjayplane]

Oops, I looked at it again and that won't work. It should have been

 If rs.EOF = False and rs!A_NUMBER <> 000000000 Then
 

The logic is not right, it can't let 000000000 repeatly input, anybody else can help on this , many thanks!

[jayjayplane]

The logic is not right, it can't let 000000000 repeatly input, anybody else can help on this , many thanks!

And when I tried to modify the code as this:

strsql2 = " WHERE A_NUMBER  = '" & Me.txtSSN & "' And A_NUMBER <> '000000000'"
 
 

when input the 000000000 in text field again and click the command to add new record, the 00000000 didn't be inserted again as duplicates, but went into the old a_number is 000000000 old record.

[DonRayner]

The logic is not right, it can't let 000000000 repeatly input, anybody else can help on this , many thanks!

By chance is the A_Number field set to no duplicates in your table?

[jayjayplane]

Yes, the a_number is not allow duplicate, but it is a possible that someone who without a_number, then have to give 000-000-000 to him/her, so there is possibility more than one person without a_number, that's why it only allow 000-000-000 duplicated.

anyone else can help me?

[DonRayner]

Yes, the a_number is not allow duplicate, but it is a possible that someone who without a_number, then have to give 000-000-000 to him/her, so there is possibility more than one person without a_number, that's why it only allow 000-000-000 duplicated.

anyone else can help me?

You've answered your own question then, if your field won't allow duplicates then you can't put a duplicate number in no matter how you try to get arround it.

[Stewart Ross]

JayJayPlane, we are all volunteers here - so please understand that if your database is set not to allow duplicates, then as Don said in his last post you can't work round this (nor should you!) no matter how hard you try.

Please don't waste the efforts of the volunteers on this site by overlooking the most obvious of constraints which you already knew of when you posted. Databases behave very systematically, and cannot ignore 'no duplicates' rules...

Please understand also that asking for others to assist whilst ignoring the guidance you have already been given is at best unhelpful and at worst rude to our volunteer posters.

MODERATOR

[jayjayplane]

JayJayPlane, we are all volunteers here - so please understand that if your database is set not to allow duplicates, then as Don said in his last post you can't work round this (nor should you!) no matter how hard you try.

Please don't waste the efforts of the volunteers on this site by overlooking the most obvious of constraints which you already knew of when you posted. Databases behave very systematically, and cannot ignore 'no duplicates' rules...

Please understand also that asking for others to assist whilst ignoring the guidance you have already been given is at best unhelpful and at worst rude to our volunteer posters.

MODERATOR

Hi Forum leader,

Sorry about that, I have no any intention to be rude and waste anyone time.
My database is not too restrict, just based on the reqirement, I didn't set a_number is PK, although it should be, but I just don't want it duplicated, except for those without a_number, can repeatly give the 000-000-000.

Anyway, thanks everyone here help me, bow~~bye~~

[NeoPa]

JayJay,

Although this may seem confusing to you, the answer you need is in this thread already.

Make sure the field is set up so that duplicates ARE allowed.

Use Don's amendment to your code to ensure only all 0s can be duplicated.