Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
unique field 1
- Thread starter andzejek
- Start date
-
1
- #3
TheAceMan1
Programmer
How are ya andzejek . . .
See Ya! . . . . . .
Be sure to see FAQ219-2884:
I believe [blue]jrbarnett[/blue] has posted your solution, however I'm curious as to just what your validation is looking for? . . .andzejek said:
Be sure to see FAQ219-2884:
- Thread starter
- #4
Thank you jrbarnet, but this is already in place in my database. Problem is, so Access is saving data to the table when forms are filled and at this point reporting error. You have to go back to right field and modify it. I thought so it would be nice to have some kind of validation procedure which will check for uniqueness as you typing data to the field, and let you know instantly or before you will go to the next field, so your entry is not unique.
andrew
andrew
TheAceMan1
Programmer
andzejek . . .
For the secnario you've presented I usually perform my own validation using the [blue]BeforeUpdate[/blue] event of the field. If a duplicated is detected the user is notified with a message and focus remains in the field via use of the [blue]Cancel[/blue] arguement. At this point the user can correct the data or [blue]Esc[/blue] to cancel the entire record. I perfer this method simply beacuse access comes up with a system message (soooooo ugly) via the Index property in the table.
Also Note: while you type is not logical here as everything could match up until the last character.So why not let the user make the full data entry and then check . . . aka the [blue]BeforeUpdate[/blue] event of the field.
Before you try the following, in the table, for the field, be sure to set the [blue]Required[/blue] property to [blue]No[/blue] and the [blue]Indexed[/blue] property to [blue]No[/blue] (since your validating yourself!).
In the [blue]BeforeUpdate[/blue] event of the field, copy/paste the following code ([blue]you![/blue] substitute proper names in [purple]purple[/purple] . . . Note: the code is setup for a datatype of text. If numeric, remove the two single quotes [red]'[/red] in [red]red[/red] where you see them in the criteria):
[blue]Your Thoughts! . . .[/blue]
See Ya! . . . . . .
Be sure to see FAQ219-2884:
For the secnario you've presented I usually perform my own validation using the [blue]BeforeUpdate[/blue] event of the field. If a duplicated is detected the user is notified with a message and focus remains in the field via use of the [blue]Cancel[/blue] arguement. At this point the user can correct the data or [blue]Esc[/blue] to cancel the entire record. I perfer this method simply beacuse access comes up with a system message (soooooo ugly) via the Index property in the table.
Also Note: while you type is not logical here as everything could match up until the last character.So why not let the user make the full data entry and then check . . . aka the [blue]BeforeUpdate[/blue] event of the field.
Before you try the following, in the table, for the field, be sure to set the [blue]Required[/blue] property to [blue]No[/blue] and the [blue]Indexed[/blue] property to [blue]No[/blue] (since your validating yourself!).
In the [blue]BeforeUpdate[/blue] event of the field, copy/paste the following code ([blue]you![/blue] substitute proper names in [purple]purple[/purple] . . . Note: the code is setup for a datatype of text. If numeric, remove the two single quotes [red]'[/red] in [red]red[/red] where you see them in the criteria):
Code:
[blue] Dim Msg As String, Style As Integer, Title As String
Dim Criteria As String, DL As String
DL = vbNewLine & vbNewLine
Criteria = "[[purple][b][i]FieldName[/i][/b][/purple]] = [red][b]'[/b][/red]" & Me![purple][b][i]ControlName[/i][/b][/purple] & "[red][b]'[/b][/red]"
If Not IsNull(DLookup("[[purple][b][i]FieldName[/i][/b][/purple]]", "[purple][b][i]TableName[/i][/b][/purple]", Criteria)) Then
Msg = "Duplicate Data Entry Detected!" & DL & _
"Duplicates for this field are not allowed!" & DL & _
"Modify your data entry or hit 'Esc' to abort the record! . . ."
Style = vbInformation + vbOKOnly
Title = "Duplicate Error Detected! . . ."
MsgBox Msg, Style, Title
[b]Cancel = True[/b]
End If[/blue]Be sure to see FAQ219-2884:
- Thread starter
- #6
Thank you for help. This code is working fine but I still have some problem. I use in this field "OnKeyDown" event to enable next field and switch focus to this field. Look like "OnKeyDown" event is before "BeforeUpdate" event. I tryied to use switch nCtr=0 which in "BeforeUpdate" is nCtr=1 and "OnKeyDown" should working only if nCtr=0 but for some reason it does not work:
Private Sub CUST_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Or KeyCode = 9 Then
If nCtr = 0 Then
Me.First_Name.Enabled = True
Me.First_Name.SetFocus
Me.CUST.Enabled = False
End If
End If
End Sub
for some reason it is also setting focus when nCtr=1.
Andrew
Private Sub CUST_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Or KeyCode = 9 Then
If nCtr = 0 Then
Me.First_Name.Enabled = True
Me.First_Name.SetFocus
Me.CUST.Enabled = False
End If
End If
End Sub
for some reason it is also setting focus when nCtr=1.
Andrew
- Thread starter
- #7
TheAceMan1
Programmer
andzejek . . .
Don't know why your bothering with keydown, but disable the code and in its place try this in the [blue]Lost Focus[/blue] event of the control:
See Ya! . . . . . .
Be sure to see FAQ219-2884:
Don't know why your bothering with keydown, but disable the code and in its place try this in the [blue]Lost Focus[/blue] event of the control:
Code:
[blue] Me.First_Name.Enabled = True
Me.First_Name.SetFocus
Me.CUST.Enabled = False[/blue]Be sure to see FAQ219-2884:
- Thread starter
- #9
- Status
