Primary key rule does not function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello expert:

Why primary key' s function avoiding dulplicate does not work when I put
this code in the before update event:
If Not IsNull(Me.[Invoice No].OldValue) Then
If Me.[Invoice No] <> Me.[Invoice No].OldValue Then
If MsgBox("Anda telah merobah!!, apakah sengaja mau merobah?",
vbYesNo) = vbNo Then
' Undo the changes
SendKeys "{ESC}"
End If
End If
End If

Waht should we do to remedy this, because I still need the function, because
it is in the invoice no. field.

Thanks in advance,

Frank
 
The purpose of this code was to prevent the users from changing the value in
the combo, and not to check for duplicates

To check for duplicates the validation should be different

If DCount("*","[TableName]","[Invoice No]=" & Me.[Invoice No])>0 Then
MsgBox "Invoice No already exist"
Cancel = True ' stop the process
End If

Note: If the Invoice field is text, then change the DSum to
DCount("*","[TableName]","[Invoice No]='" & Me.[Invoice No] & "'")
Adding single quote before and after the value

In your previous post I gave you a link you can look at with a sample code,
have you looked at it?
 
Yes Ofer, I have looked at the link, I think I better try your propose codes
herewith.
Thank you very much, I will try this now.

Frank

Ofer Cohen said:
The purpose of this code was to prevent the users from changing the value in
the combo, and not to check for duplicates

To check for duplicates the validation should be different

If DCount("*","[TableName]","[Invoice No]=" & Me.[Invoice No])>0 Then
MsgBox "Invoice No already exist"
Cancel = True ' stop the process
End If

Note: If the Invoice field is text, then change the DSum to
DCount("*","[TableName]","[Invoice No]='" & Me.[Invoice No] & "'")
Adding single quote before and after the value

In your previous post I gave you a link you can look at with a sample code,
have you looked at it?


--
Good Luck
BS"D


Frank Situmorang said:
Hello expert:

Why primary key' s function avoiding dulplicate does not work when I put
this code in the before update event:
If Not IsNull(Me.[Invoice No].OldValue) Then
If Me.[Invoice No] <> Me.[Invoice No].OldValue Then
If MsgBox("Anda telah merobah!!, apakah sengaja mau merobah?",
vbYesNo) = vbNo Then
' Undo the changes
SendKeys "{ESC}"
End If
End If
End If

Waht should we do to remedy this, because I still need the function, because
it is in the invoice no. field.

Thanks in advance,

Frank
Click to expand...
Click to expand...
 
Oh... Ofer, maybe I need to tell you the Invoice No field does not use Combo,
it is just normal field but I need to make checking if the users change
invoice no later

Is your proposed code still true?

Thanks

Frank


Ofer Cohen said:
The purpose of this code was to prevent the users from changing the value in
the combo, and not to check for duplicates

To check for duplicates the validation should be different

If DCount("*","[TableName]","[Invoice No]=" & Me.[Invoice No])>0 Then
MsgBox "Invoice No already exist"
Cancel = True ' stop the process
End If

Note: If the Invoice field is text, then change the DSum to
DCount("*","[TableName]","[Invoice No]='" & Me.[Invoice No] & "'")
Adding single quote before and after the value

In your previous post I gave you a link you can look at with a sample code,
have you looked at it?


--
Good Luck
BS"D


Frank Situmorang said:
Hello expert:

Why primary key' s function avoiding dulplicate does not work when I put
this code in the before update event:
If Not IsNull(Me.[Invoice No].OldValue) Then
If Me.[Invoice No] <> Me.[Invoice No].OldValue Then
If MsgBox("Anda telah merobah!!, apakah sengaja mau merobah?",
vbYesNo) = vbNo Then
' Undo the changes
SendKeys "{ESC}"
End If
End If
End If

Waht should we do to remedy this, because I still need the function, because
it is in the invoice no. field.

Thanks in advance,

Frank
Click to expand...
Click to expand...
 
Yes, it will work with combo and text box

--
Good Luck
BS"D


Frank Situmorang said:
Oh... Ofer, maybe I need to tell you the Invoice No field does not use Combo,
it is just normal field but I need to make checking if the users change
invoice no later

Is your proposed code still true?

Thanks

Frank


Ofer Cohen said:
The purpose of this code was to prevent the users from changing the value in
the combo, and not to check for duplicates

To check for duplicates the validation should be different

If DCount("*","[TableName]","[Invoice No]=" & Me.[Invoice No])>0 Then
MsgBox "Invoice No already exist"
Cancel = True ' stop the process
End If

Note: If the Invoice field is text, then change the DSum to
DCount("*","[TableName]","[Invoice No]='" & Me.[Invoice No] & "'")
Adding single quote before and after the value

In your previous post I gave you a link you can look at with a sample code,
have you looked at it?


--
Good Luck
BS"D


Frank Situmorang said:
Hello expert:

Why primary key' s function avoiding dulplicate does not work when I put
this code in the before update event:
If Not IsNull(Me.[Invoice No].OldValue) Then
If Me.[Invoice No] <> Me.[Invoice No].OldValue Then
If MsgBox("Anda telah merobah!!, apakah sengaja mau merobah?",
vbYesNo) = vbNo Then
' Undo the changes
SendKeys "{ESC}"
End If
End If
End If

Waht should we do to remedy this, because I still need the function, because
it is in the invoice no. field.

Thanks in advance,

Frank
Click to expand...
Click to expand...
Click to expand...
 
Ofer,

I have read your code, but I want to tell you that I should have said that
duplicate is not allowed if the supplier number is the same, in otherword, we
will only ban the same number from the same supplier. How can we do it if the
supplier number but we already linked it with the supplier invoice table.

Thanks,

Frank


Ofer Cohen said:
The purpose of this code was to prevent the users from changing the value in
the combo, and not to check for duplicates

To check for duplicates the validation should be different

If DCount("*","[TableName]","[Invoice No]=" & Me.[Invoice No])>0 Then
MsgBox "Invoice No already exist"
Cancel = True ' stop the process
End If

Note: If the Invoice field is text, then change the DSum to
DCount("*","[TableName]","[Invoice No]='" & Me.[Invoice No] & "'")
Adding single quote before and after the value

In your previous post I gave you a link you can look at with a sample code,
have you looked at it?


--
Good Luck
BS"D


Frank Situmorang said:
Hello expert:

Why primary key' s function avoiding dulplicate does not work when I put
this code in the before update event:
If Not IsNull(Me.[Invoice No].OldValue) Then
If Me.[Invoice No] <> Me.[Invoice No].OldValue Then
If MsgBox("Anda telah merobah!!, apakah sengaja mau merobah?",
vbYesNo) = vbNo Then
' Undo the changes
SendKeys "{ESC}"
End If
End If
End If

Waht should we do to remedy this, because I still need the function, because
it is in the invoice no. field.

Thanks in advance,

Frank
Click to expand...
Click to expand...
 
You can use a multiple field index to prevent records from having duplicate
supplier number + Invoice number combinations

To create a multiple field unique index (Compound index)
--Open up the WorkingTable in design mode
--Select View: Index from the menu
--Enter Name for Index in first blank cell under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
--Continue moving down and selecting fieldnames until all needed are
included.
--Close the index window and close and save the table


If you want that to be the primary key, you can do this quickly in a table
design view by selecting the fields (I believe up to 10 are allowed in an
idex) and then selecting Edit: Primary Key from the menu.
(To select multiple fields at one time, hold down the control key while
clicking on the "row indicator" square on the far left of the row.)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Frank Situmorang said:
Hello expert:

Why primary key' s function avoiding dulplicate does not work when I
put
this code in the before update event:
If Not IsNull(Me.[Invoice No].OldValue) Then
If Me.[Invoice No] <> Me.[Invoice No].OldValue Then
If MsgBox("Anda telah merobah!!, apakah sengaja mau merobah?",
vbYesNo) = vbNo Then
' Undo the changes
SendKeys "{ESC}"
End If
End If
End If

Waht should we do to remedy this, because I still need the function,
because
it is in the invoice no. field.

Thanks in advance,

Frank
Click to expand...
Click to expand...
Click to expand...
 
Hi John,

Can we do your suggestion although supplier no is in another table?. I have
the supplier table linked to invoice table.

Thanks,

Frank

John Spencer said:
You can use a multiple field index to prevent records from having duplicate
supplier number + Invoice number combinations

To create a multiple field unique index (Compound index)
--Open up the WorkingTable in design mode
--Select View: Index from the menu
--Enter Name for Index in first blank cell under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
--Continue moving down and selecting fieldnames until all needed are
included.
--Close the index window and close and save the table


If you want that to be the primary key, you can do this quickly in a table
design view by selecting the fields (I believe up to 10 are allowed in an
idex) and then selecting Edit: Primary Key from the menu.
(To select multiple fields at one time, hold down the control key while
clicking on the "row indicator" square on the far left of the row.)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

:

Hello expert:

Why primary key' s function avoiding dulplicate does not work when I
put
this code in the before update event:
If Not IsNull(Me.[Invoice No].OldValue) Then
If Me.[Invoice No] <> Me.[Invoice No].OldValue Then
If MsgBox("Anda telah merobah!!, apakah sengaja mau merobah?",
vbYesNo) = vbNo Then
' Undo the changes
SendKeys "{ESC}"
End If
End If
End If

Waht should we do to remedy this, because I still need the function,
because
it is in the invoice no. field.

Thanks in advance,

Frank
Click to expand...
Click to expand...
Click to expand...
 
No. You can only build an index on the fields in one table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Frank Situmorang said:
Hi John,

Can we do your suggestion although supplier no is in another table?. I
have
the supplier table linked to invoice table.

Thanks,

Frank

John Spencer said:
You can use a multiple field index to prevent records from having
duplicate
supplier number + Invoice number combinations

To create a multiple field unique index (Compound index)
--Open up the WorkingTable in design mode
--Select View: Index from the menu
--Enter Name for Index in first blank cell under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
--Continue moving down and selecting fieldnames until all needed are
included.
--Close the index window and close and save the table


If you want that to be the primary key, you can do this quickly in a
table
design view by selecting the fields (I believe up to 10 are allowed in an
idex) and then selecting Edit: Primary Key from the menu.
(To select multiple fields at one time, hold down the control key while
clicking on the "row indicator" square on the far left of the row.)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

:

Hello expert:

Why primary key' s function avoiding dulplicate does not work when I
put
this code in the before update event:
If Not IsNull(Me.[Invoice No].OldValue) Then
If Me.[Invoice No] <> Me.[Invoice No].OldValue Then
If MsgBox("Anda telah merobah!!, apakah sengaja mau
merobah?",
vbYesNo) = vbNo Then
' Undo the changes
SendKeys "{ESC}"
End If
End If
End If

Waht should we do to remedy this, because I still need the function,
because
it is in the invoice no. field.

Thanks in advance,

Frank
Click to expand...
Click to expand...
Click to expand...
 
Back
Top