validation rule (for a record in a table) with dlookup

  • Thread starter Thread starter scoebidoo
  • Start date Start date
S

scoebidoo

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
 
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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...


.
Click to expand...
 
SQL Server allows you to have triggers.. that means that you can run
this, even when people have their VBA and macros disabled

-Aaron
 
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?
Click to expand...

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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Bob,

Thank you! My problem is solved!

Thanks to everyone who responded!

scoebidoo

Bob Quintal said:
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?
Click to expand...

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 said:
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.



:

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
Click to expand...
Click to expand...

.
Click to expand...
 
Back
Top