If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
No duplicate records problem
I've got a fairly simple db with only 4 tables. Only two are linked
by a field called E58# with a one to many relationship. In the "many" table I want to allow a field's (Item#) value for each record to contain a number 1, 2, or 3 and not duplicate the number under the same E58# when entering data with a form. However, in table design when I give it an index of 'Yes, no duplicates' I cannot enter another Item# 1 or 2 or 3 in a different record with a different E58#. I only want to restrict duplicity when the E58# is the same. How can I do this? I've tried making multiple keyed fields using both fields but if in error a person enters the duplicate field value it is quite difficult to extract yourself from the error. Even deleting the record in error retrurns a message about no null values in key fields. |
#2
|
|||
|
|||
No duplicate records problem
BTW, I personally wouldn't use a # sign in a field name, but if it works.......
Try: Set it up as a multi-field (on E58# and Item# fields) index, not a multi-field key. Set unique to Y. You could also set it to ignore nulls. |
#3
|
|||
|
|||
No duplicate records problem
Try: Set it up as a multi-field (on E58# and Item# fields) How do you do that? I've only seen indexes on a single field with my 2k Access. You could also set it to ignore nulls. Same question. On Thu, 14 May 2009 12:50:10 -0700, Fred wrote: BTW, I personally wouldn't use a # sign in a field name, but if it works....... Try: Set it up as a multi-field (on E58# and Item# fields) index, not a multi-field key. Set unique to Y. You could also set it to ignore nulls. |
#4
|
|||
|
|||
No duplicate records problem
In 2003 it's obscure but there.....hopefully the same in 2K. ?
On the index grid, on the next line down from the first field index, enter a new line, leave the index spot blank and enter the 2nd field name. |
#5
|
|||
|
|||
No duplicate records problem
Well, I was able to create the multi field index, but it would only
warn me of the duplicate values after I tried to move off that record. Then, I could not delete that record with a delete record command but had to close the form itself. I've got the index set to ignore nulls as well. On Thu, 14 May 2009 13:41:00 -0700, Fred wrote: In 2003 it's obscure but there.....hopefully the same in 2K. ? On the index grid, on the next line down from the first field index, enter a new line, leave the index spot blank and enter the 2nd field name. |
#6
|
|||
|
|||
No duplicate records problem
On Thu, 14 May 2009 13:15:52 -0500, Russ wrote:
I've got a fairly simple db with only 4 tables. Only two are linked by a field called E58# with a one to many relationship. In the "many" table I want to allow a field's (Item#) value for each record to contain a number 1, 2, or 3 and not duplicate the number under the same E58# when entering data with a form. However, in table design when I give it an index of 'Yes, no duplicates' I cannot enter another Item# 1 or 2 or 3 in a different record with a different E58#. I only want to restrict duplicity when the E58# is the same. How can I do this? I've tried making multiple keyed fields using both fields but if in error a person enters the duplicate field value it is quite difficult to extract yourself from the error. Even deleting the record in error retrurns a message about no null values in key fields. The unique index won't work for the exact reason you describe: that field ISN'T unique. What you can do is create a two field unique index on the E58# field and the Item# field (do note that # is a date delimiter and may best be avoided in fieldnames). In addition you can have Access automatically assign the Item# and have your form check for duplicates, avoiding the table error. In the form's BeforeInsert event you can put code Private Sub Form_BeforeInsert(Cancel as Integer) Me![Item#] = NZ(DMax("[Item#]", "[table]", "[E58#] = " & Me![E58#])) + 1 End Sub and then check for duplicates before saving, just in case the user types a value into the field: Private Sub Form_BeforeUpdate(Cancel as Integer) If Not IsNull(DLookUp("[Item#]", "table", _ "[E58#] = " & Me.[E58#] & " AND [Item#] = " & Me![Item#])) Then MsgBox "Duplicate item number!", vbOKOnly Cancel = True End If End Sub -- John W. Vinson [MVP] |
#7
|
|||
|
|||
No duplicate records problem
John,
I was able to delete the record after entering your code, but the form will not insert a digit into the ItemNo field (I've renamed and removed the # symbol and replaced them with "No") when I insert a new record. On Thu, 14 May 2009 14:59:16 -0600, John W. Vinson wrote: On Thu, 14 May 2009 13:15:52 -0500, Russ wrote: I've got a fairly simple db with only 4 tables. Only two are linked by a field called E58# with a one to many relationship. In the "many" table I want to allow a field's (Item#) value for each record to contain a number 1, 2, or 3 and not duplicate the number under the same E58# when entering data with a form. However, in table design when I give it an index of 'Yes, no duplicates' I cannot enter another Item# 1 or 2 or 3 in a different record with a different E58#. I only want to restrict duplicity when the E58# is the same. How can I do this? I've tried making multiple keyed fields using both fields but if in error a person enters the duplicate field value it is quite difficult to extract yourself from the error. Even deleting the record in error retrurns a message about no null values in key fields. The unique index won't work for the exact reason you describe: that field ISN'T unique. What you can do is create a two field unique index on the E58# field and the Item# field (do note that # is a date delimiter and may best be avoided in fieldnames). In addition you can have Access automatically assign the Item# and have your form check for duplicates, avoiding the table error. In the form's BeforeInsert event you can put code Private Sub Form_BeforeInsert(Cancel as Integer) Me![Item#] = NZ(DMax("[Item#]", "[table]", "[E58#] = " & Me![E58#])) + 1 End Sub and then check for duplicates before saving, just in case the user types a value into the field: Private Sub Form_BeforeUpdate(Cancel as Integer) If Not IsNull(DLookUp("[Item#]", "table", _ "[E58#] = " & Me.[E58#] & " AND [Item#] = " & Me![Item#])) Then MsgBox "Duplicate item number!", vbOKOnly Cancel = True End If End Sub |
#8
|
|||
|
|||
No duplicate records problem
On Thu, 14 May 2009 17:08:05 -0500, Russ wrote:
John, I was able to delete the record after entering your code, but the form will not insert a digit into the ItemNo field (I've renamed and removed the # symbol and replaced them with "No") when I insert a new record. Please post your code, and indicate the datatypes of your fields. If either of them are Text fields instead of Number you need to tweak the code a bit. -- John W. Vinson [MVP] |
#9
|
|||
|
|||
No duplicate records problem
On Thu, 14 May 2009 17:15:55 -0600, John W. Vinson
wrote: On Thu, 14 May 2009 17:08:05 -0500, Russ wrote: John, I was able to delete the record after entering your code, but the form will not insert a digit into the ItemNo field (I've renamed and removed the # symbol and replaced them with "No") when I insert a new record. Please post your code, and indicate the datatypes of your fields. If either of them are Text fields instead of Number you need to tweak the code a bit. My two fields in question are both text fields. I can probably change them to Number if you think it will work better. |
#10
|
|||
|
|||
No duplicate records problem
On Thu, 14 May 2009 19:01:48 -0500, Russ wrote:
My two fields in question are both text fields. I can probably change them to Number if you think it will work better. Text should be ok, but since you're doing arithmatic on it, Number... Long Integer would be better for ItemNo. If E58No is a Text field you just need some syntactially required quotemarks, either ' or " - the singlequote is easier. Try changing the datatype of ItemNo and use Private Sub Form_BeforeInsert(Cancel As Integer) Me![ItemNo] = Nz(DMax("[ItemNo]", "[Items]", "[E58No] = '" & Me![E58No]) & "'") + 1 End Sub Private Sub Form_BeforeUpdate(Cancel As Integer) If Not IsNull(DLookup("[ItemNo]", "Items", _ "[E58No] = '" & Me.[E58No] & "' AND [ItemNo] = " & Me![ItemNo])) Then MsgBox "Duplicate item number!", vbOKOnly Cancel = True End If End Sub -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|