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
|
|||
|
|||
validation rule (for a record in a table) with dlookup
Hi
In a table with a double primary key, I want, before the input of a new record, to check it doesn't exist. To solve this problem, I presume, using a validation rule is the best option. Assuming that Field1 and Field2 are both primary keys and the combination of both keys must be unique, what's the best validation rule? Can something like as follows work? Dlookup("Field1","Table1","Field1=" & [Field1] & " AND Field2=" & [Field2]) Is Null |
#2
|
|||
|
|||
validation rule (for a record in a table) with dlookup
If ((DLookup("[PrimaryFieldInTable]", "[TableName]", "[1stControlOnForm] ='"
& Form!1stFieldInTable & "' AND [2ndFieldInTable] = '" & Form!2ndControlOnForm & "'"))) Then 'Something here like MsgBox "This record may already exist", vbOKOnly, "SomeTitle" End If -- Wayne Manchester, England. "scoebidoo" wrote: Hi In a table with a double primary key, I want, before the input of a new record, to check it doesn't exist. To solve this problem, I presume, using a validation rule is the best option. Assuming that Field1 and Field2 are both primary keys and the combination of both keys must be unique, what's the best validation rule? Can something like as follows work? Dlookup("Field1","Table1","Field1=" & [Field1] & " AND Field2=" & [Field2]) Is Null |
#3
|
|||
|
|||
validation rule (for a record in a table) with dlookup
Dlookup is slow.
If your table is an Access database table it will automatically reject a record with the same combination of primary keys so why not just trap the error that will be thrown? Try this in your form's On Error event: If DataErr = 3022 Then MsgBox ("This is a Duplicate!") Response = acDataErrContinue 'suppresses the built in error message End If Jon "scoebidoo" wrote in message ... Hi In a table with a double primary key, I want, before the input of a new record, to check it doesn't exist. To solve this problem, I presume, using a validation rule is the best option. Assuming that Field1 and Field2 are both primary keys and the combination of both keys must be unique, what's the best validation rule? Can something like as follows work? Dlookup("Field1","Table1","Field1=" & [Field1] & " AND Field2=" & [Field2]) Is Null |
#4
|
|||
|
|||
validation rule (for a record in a table) with dlookup
Thank you for your quick reply!
All right! It works! TX!! Using this code in the "beforeupdate"-event of a combobox, how can I prevent writing a null-value? My code: Private Sub combobox1_BeforeUpdate(Cancel As Integer) .... If check =true Then Cancel = True MsgBox("a message") Me.combobox1.Undo End Sub The cursor stays in the combobox and is ready to accept a new value. But I don't want enter a new value in this case (when the check =true). When trying to leave the combobox I always get messages about null-values aren't allowed (which is correct). How can I leave the combobox without any further messages? "Wayne-I-M" wrote: If ((DLookup("[PrimaryFieldInTable]", "[TableName]", "[1stControlOnForm] ='" & Form!1stFieldInTable & "' AND [2ndFieldInTable] = '" & Form!2ndControlOnForm & "'"))) Then 'Something here like MsgBox "This record may already exist", vbOKOnly, "SomeTitle" End If -- Wayne Manchester, England. "scoebidoo" wrote: Hi In a table with a double primary key, I want, before the input of a new record, to check it doesn't exist. To solve this problem, I presume, using a validation rule is the best option. Assuming that Field1 and Field2 are both primary keys and the combination of both keys must be unique, what's the best validation rule? Can something like as follows work? Dlookup("Field1","Table1","Field1=" & [Field1] & " AND Field2=" & [Field2]) Is Null |
#5
|
|||
|
|||
validation rule (for a record in a table) with dlookup
Hi Jon Lewis
Thanks for the reply! I've tested your suggestion. The result of trapping the error leaves me with a combobox with a wrong value. When the error appears is there also a solution to leave the combobox without any messages and no record added to the table? Maybe it is important to know that the combobox is on a subform. "Jon Lewis" wrote: Dlookup is slow. If your table is an Access database table it will automatically reject a record with the same combination of primary keys so why not just trap the error that will be thrown? Try this in your form's On Error event: If DataErr = 3022 Then MsgBox ("This is a Duplicate!") Response = acDataErrContinue 'suppresses the built in error message End If Jon "scoebidoo" wrote in message ... Hi In a table with a double primary key, I want, before the input of a new record, to check it doesn't exist. To solve this problem, I presume, using a validation rule is the best option. Assuming that Field1 and Field2 are both primary keys and the combination of both keys must be unique, what's the best validation rule? Can something like as follows work? Dlookup("Field1","Table1","Field1=" & [Field1] & " AND Field2=" & [Field2]) Is Null . |
#6
|
|||
|
|||
validation rule (for a record in a table) with dlookup
SQL Server allows you to have triggers.. that means that you can run
this, even when people have their VBA and macros disabled -Aaron On May 6, 3:56*am, scoebidoo wrote: Hi In a table with a double primary key, I want, *before the input of a new record, to check it doesn't exist. To solve this problem, I presume, using a validation rule is the best option. Assuming that Field1 and Field2 are both primary keys and the combination of both keys must be unique, what's the best validation rule? Can something like as follows work? *Dlookup("Field1","Table1","Field1=" & [Field1] & " AND Field2=" & [Field2]) Is Null |
#7
|
|||
|
|||
validation rule (for a record in a table) with dlookup
=?Utf-8?B?c2NvZWJpZG9v?= wrote
in : Thank you for your quick reply! All right! It works! TX!! Using this code in the "beforeupdate"-event of a combobox, how can I prevent writing a null-value? My code: Private Sub combobox1_BeforeUpdate(Cancel As Integer) ... If check =true Then Cancel = True MsgBox("a message") Me.combobox1.Undo End Sub The cursor stays in the combobox and is ready to accept a new value. But I don't want enter a new value in this case (when the check =true). When trying to leave the combobox I always get messages about null-values aren't allowed (which is correct). How can I leave the combobox without any further messages? undo the whole record, not just the combobox. If check =true Then Cancel = True MsgBox("a message") Me.combobox1.Undo me.undo me.undo ' a second time just to be safe. end if End Sub "Wayne-I-M" wrote: If ((DLookup("[PrimaryFieldInTable]", "[TableName]", "[1stControlOnForm] ='" & Form!1stFieldInTable & "' AND [2ndFieldInTable] = '" & Form!2ndControlOnForm & "'"))) Then 'Something here like MsgBox "This record may already exist", vbOKOnly, "SomeTitle" End If -- Wayne Manchester, England. "scoebidoo" wrote: Hi In a table with a double primary key, I want, before the input of a new record, to check it doesn't exist. To solve this problem, I presume, using a validation rule is the best option. Assuming that Field1 and Field2 are both primary keys and the combination of both keys must be unique, what's the best validation rule? Can something like as follows work? Dlookup("Field1","Table1","Field1=" & [Field1] & " AND Field2=" & [Field2]) Is Null |
#8
|
|||
|
|||
validation rule (for a record in a table) with dlookup
Bob,
Thank you! My problem is solved! Thanks to everyone who responded! scoebidoo "Bob Quintal" wrote: =?Utf-8?B?c2NvZWJpZG9v?= wrote in : Thank you for your quick reply! All right! It works! TX!! Using this code in the "beforeupdate"-event of a combobox, how can I prevent writing a null-value? My code: Private Sub combobox1_BeforeUpdate(Cancel As Integer) ... If check =true Then Cancel = True MsgBox("a message") Me.combobox1.Undo End Sub The cursor stays in the combobox and is ready to accept a new value. But I don't want enter a new value in this case (when the check =true). When trying to leave the combobox I always get messages about null-values aren't allowed (which is correct). How can I leave the combobox without any further messages? undo the whole record, not just the combobox. If check =true Then Cancel = True MsgBox("a message") Me.combobox1.Undo me.undo me.undo ' a second time just to be safe. end if End Sub "Wayne-I-M" wrote: If ((DLookup("[PrimaryFieldInTable]", "[TableName]", "[1stControlOnForm] ='" & Form!1stFieldInTable & "' AND [2ndFieldInTable] = '" & Form!2ndControlOnForm & "'"))) Then 'Something here like MsgBox "This record may already exist", vbOKOnly, "SomeTitle" End If -- Wayne Manchester, England. "scoebidoo" wrote: Hi In a table with a double primary key, I want, before the input of a new record, to check it doesn't exist. To solve this problem, I presume, using a validation rule is the best option. Assuming that Field1 and Field2 are both primary keys and the combination of both keys must be unique, what's the best validation rule? Can something like as follows work? Dlookup("Field1","Table1","Field1=" & [Field1] & " AND Field2=" & [Field2]) Is Null . |
Thread Tools | |
Display Modes | |
|
|