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
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] |
#6
|
|||
|
|||
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. |
#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 14:59:16 -0600, John W. Vinson
wrote: On Thu, 14 May 2009 13:15:52 -0500, Russ wrote: 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, After screwing up and putting your code in the wrong form, I finally got it located in the subform where it belongs. However it will not insert a new Item number in the ItemNo field when I add a record. I also get an error message when I try to exit the form which says: Run time error 2465. Microsoft Access can't fiind the field 'I' referred to inyour expression. (the 'I' looks like a pipe symbol that I couldn't find on my keyboard) Here's what I entered: 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 |
#9
|
|||
|
|||
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] |
#10
|
|||
|
|||
No duplicate records problem
On Thu, 14 May 2009 17:57:31 -0500, Russ wrote:
After screwing up and putting your code in the wrong form, I finally got it located in the subform where it belongs. However it will not insert a new Item number in the ItemNo field when I add a record. I also get an error message when I try to exit the form which says: Run time error 2465. Microsoft Access can't fiind the field 'I' referred to inyour expression. (the 'I' looks like a pipe symbol that I couldn't find on my keyboard) Here's what I entered: 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 The code assumes that your Subform has controls named [E85No] and [ItemNo]. If it doesn't, add controls of those names bound to the corresponding fields, or (perhaps better) textboxes named txtE85No and txtItemNo and replace the Me![E85No] and Me![ItemNo] references with Me![txtE85No] and Me![txtItemno]. The textboxes can be invisible if you wish. -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|