By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,849 Members | 2,059 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

Is It possible to bybass the special situation

P: 26
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command42_Click()
  2. On Error GoTo Err_Command42_Click
  3.  
  4.     Dim strSQL As String
  5.     Dim strsql2 As String
  6.     Dim db As Database
  7.     Dim strMsg As String
  8.  
  9.     Dim rs As DAO.Recordset
  10.  
  11.     Dim stDocName As String
  12.     Dim stLinkCriteria As String
  13.  
  14.     Set db = CurrentDb()
  15.     strSQL = "SELECT * from A "
  16.     strsql2 = " WHERE A_NUMBER  = '" & Me.txtSSN & "' "
  17.     Set rs = db.OpenRecordset(strSQL + strsql2, dbOpenSnapshot)
  18.  
  19.     If rs.EOF = False Then
  20.  
  21.         MsgBox "Person with the same A number already exists in the DB", vbCritical
  22.  
  23.     Else
  24.  
  25.         strSQL = "INSERT INTO A  (A_NUMBER) VALUES ('" & Me.txtSSN & "')"
  26.         db.Execute strSQL
  27.         Me.A_LIST_SUBFORM_QRY_subform.Requery
  28.  
  29.         stDocName = "LOP"
  30.         stLinkCriteria = "[A_NUMBER]=" & "'" & Me.txtSSN & "'"
  31.         DoCmd.OpenForm stDocName, , , stLinkCriteria
  32.  
  33.  
  34.     End If
  35.  
  36.       Set rs = Nothing
  37.       Set db = Nothing
  38.  
  39.  
  40. Exit_Command42_Click:
  41.     Exit Sub
  42.  
  43. Err_Command42_Click:
  44.     MsgBox Err.Description
  45.     Resume Exit_Command42_Click
  46.  
  47. End Sub
  48.  
  49.  
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~~
Oct 20 '08 #1
Share this Question
Share on Google+
11 Replies


DonRayner
Expert 100+
P: 489
Change Line 19 to

Expand|Select|Wrap|Line Numbers
  1.  If rs.EOF = False or rs!A_NUMBER = 000000000 Then
  2.  
Oct 20 '08 #2

P: 26
Change Line 19 to

Expand|Select|Wrap|Line Numbers
  1.  If rs.EOF = False or rs!A_NUMBER = 000000000 Then
  2.  


Thank you, I will try tomorrow.
Oct 22 '08 #3

DonRayner
Expert 100+
P: 489
Thank you, I will try tomorrow.
Oops, I looked at it again and that won't work. It should have been

Expand|Select|Wrap|Line Numbers
  1.  If rs.EOF = False and rs!A_NUMBER <> 000000000 Then
  2.  
Oct 22 '08 #4

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

Expand|Select|Wrap|Line Numbers
  1.  If rs.EOF = False and rs!A_NUMBER <> 000000000 Then
  2.  

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

P: 26
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:

Expand|Select|Wrap|Line Numbers
  1. strsql2 = " WHERE A_NUMBER  = '" & Me.txtSSN & "' And A_NUMBER <> '000000000'"
  2.  
  3.  
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.
Oct 22 '08 #6

DonRayner
Expert 100+
P: 489
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?
Oct 22 '08 #7

P: 26
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?
Oct 23 '08 #8

DonRayner
Expert 100+
P: 489
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.
Oct 23 '08 #9

Expert Mod 2.5K+
P: 2,545
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
Oct 23 '08 #10

P: 26
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~~
Oct 23 '08 #11

NeoPa
Expert Mod 15k+
P: 31,489
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.
Oct 27 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.