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 |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
No duplicate records problem
On Thu, 14 May 2009 17:48:10 -0600, John W. Vinson
wrote: 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. Although I did initailly change the field names to E58No and ItemNo, I neglected to change the control names in the form. I did change them after your advice, but I'm still getting the above problem. |
#13
|
|||
|
|||
No duplicate records problem
On Thu, 14 May 2009 17:48:10 -0600, John W. Vinson
wrote: 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. I changed the field types to Number Long integer. In the ItemNo field of the form, I've got 4 in the validation rule property. When I enter 4 in that field I get the required validation text I wanted, but after deleting the digit or trying to delete the entire record I cannot get out of the field or close the form without the same message popping up. |
#14
|
|||
|
|||
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] |
#15
|
|||
|
|||
No duplicate records problem
On Thu, 14 May 2009 20:50:04 -0500, Russ wrote:
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. I changed the field types to Number Long integer. In the ItemNo field of the form, I've got 4 in the validation rule property. When I enter 4 in that field I get the required validation text I wanted, but after deleting the digit or trying to delete the entire record I cannot get out of the field or close the form without the same message popping up. oops... we're crossing messages in the two different subthreads here! I just posted how to do it with text fields. You're better off with numbers, just leave out the ' marks in my other answer. The BeforeInsert code should make it *unnecessary* to manually enter an ItemNo value, since it will calculate one for you automatically. Can you just hit EscEsc to cancel editing the record? Why the validation rule anyway - you want to limit it to three items? Note that a validation rule of 4 permits -12320185123 as a valid itemno... g -- John W. Vinson [MVP] |
#16
|
|||
|
|||
No duplicate records problem
On Thu, 14 May 2009 20:18:34 -0600, John W. Vinson
wrote: On Thu, 14 May 2009 20:50:04 -0500, Russ wrote: 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. I changed the field types to Number Long integer. In the ItemNo field of the form, I've got 4 in the validation rule property. When I enter 4 in that field I get the required validation text I wanted, but after deleting the digit or trying to delete the entire record I cannot get out of the field or close the form without the same message popping up. oops... we're crossing messages in the two different subthreads here! I just posted how to do it with text fields. You're better off with numbers, just leave out the ' marks in my other answer. The BeforeInsert code should make it *unnecessary* to manually enter an ItemNo value, since it will calculate one for you automatically. Can you just hit EscEsc to cancel editing the record? Why the validation rule anyway - you want to limit it to three items? Note that a validation rule of 4 permits -12320185123 as a valid itemno... g You are right about not having to enter a number in the ItemNo field. I had been manually entering it myself, but when I started writing the description of the ItemNo one was entered for me. Very good. I've put the validation rule in there to liimit the entry to no more than 3 items per E58No. However, it does not appear to be doing it's job since the code was able to enter a 4th ItemNo. I will change it to 1 Or 2 Or 3 to eliiminate the minus numbers, but I don't think that will solve the *4th ItemNo* problem. |
#17
|
|||
|
|||
No duplicate records problem
On Fri, 15 May 2009 07:50:26 -0500, Russ wrote:
You are right about not having to enter a number in the ItemNo field. I had been manually entering it myself, but when I started writing the description of the ItemNo one was entered for me. Very good. I've put the validation rule in there to liimit the entry to no more than 3 items per E58No. However, it does not appear to be doing it's job since the code was able to enter a 4th ItemNo. I will change it to 1 Or 2 Or 3 to eliiminate the minus numbers, but I don't think that will solve the *4th ItemNo* problem. Trap the fourth itemno in the BeforeInsert event instead: Private Sub Form_BeforeInsert(Cancel As Integer) If DCount("*", "[Items]", " [E58No] = " & Me![E58No]) = 3 Then MsgBox "Only three items per E58 please", vbOKOnly Cancel = True Else Me![ItemNo] = Nz(DMax("[ItemNo]","[Items]","[E58No] = " & Me![E58No])) + 1 End If End Sub The problem with the validation rule is that it fires too late - after you've entered all the data for the record and then try to save it. -- John W. Vinson [MVP] |
#18
|
|||
|
|||
No duplicate records problem
On Fri, 15 May 2009 09:46:49 -0600, John W. Vinson
wrote: On Fri, 15 May 2009 07:50:26 -0500, Russ wrote: You are right about not having to enter a number in the ItemNo field. I had been manually entering it myself, but when I started writing the description of the ItemNo one was entered for me. Very good. I've put the validation rule in there to liimit the entry to no more than 3 items per E58No. However, it does not appear to be doing it's job since the code was able to enter a 4th ItemNo. I will change it to 1 Or 2 Or 3 to eliiminate the minus numbers, but I don't think that will solve the *4th ItemNo* problem. Trap the fourth itemno in the BeforeInsert event instead: Private Sub Form_BeforeInsert(Cancel As Integer) If DCount("*", "[Items]", " [E58No] = " & Me![E58No]) = 3 Then MsgBox "Only three items per E58 please", vbOKOnly Cancel = True Else Me![ItemNo] = Nz(DMax("[ItemNo]","[Items]","[E58No] = " & Me![E58No])) + 1 End If End Sub The problem with the validation rule is that it fires too late - after you've entered all the data for the record and then try to save it. Very nice John! Thank you very much for the help and the extra added info. Russ |
|
Thread Tools | |
Display Modes | |
|
|