Unique entry

[Chris]

I have a field I call "symbol" that I need not to be duplicated. I
tried making it the primary key and also tried indexing it. The only
problem is that I have to key the whole way to the end of the record to
find out that it has been duplicated. Is there a way to make it tell
me it is a duplicate as soon as I tab out of the field I have specified
as primary or indexed?

I'd appreciate any help I can get on this!

Chris

 

[Mike]

You can put the following code for the AfterUpdate property of the control
the record source of which is the primary key of the table:

If
IsNull(DLookup("[SymbolID]","MyTable","[Symbol]=[Forms]![MyForm]![Symbol]"))=False Then
MsgBox "The value already exists!"
Me.Symbol.Undo
Me.Symbol.SetFocus
Exit Sub
End If


Do not forget to change "MyTable", "MyForm", "SymbolID" names to the names
used in your database.

 

[Chris]

I appreciate the answer above - but am at a loss exactaly where I am
keying it. Everytime I try to key it I get an error. Perhaps I am in
the wrong spot! I made Symbol my primary key and went to design view
on my form to key this - but nothing I do seems to work. Can you tell
me exactly which fields I am keying this into? And which ones I might
need to change?

Chris

 

[Ken Snell [MVP]]

Chris -

The posted solution is VBA code that would go into the event procedure
attached to the BeforeUpdate event of the control on a form that is bound to
the SymbolID field in the form's Record Source.

From your reply, I think you may be trying to use this in one of the
properties for the field in the table, not on a form? What was posted will
not work for direct data entry into a table, if that is what you're doing.

If you're using a form, tell us more about the control on the form that is
bound to the SymbolID field.

 

[Chris]

Name.....Symbol
Control Source...Symbol ID
Format....>
Decimal Places....Auto
Input Mask...
Default Value...
Ime hold...No
Ime sentence Mode...None
Validation Rule...
Validation Text...
Status Bar Text...
Enter Key Behavior...Default
Allow Auto Correct...Yes
Visible...yes
Display When... Always
Verticle...No
Enabled...Yes
Locked...No
Filter Lookup...Database Default
Auto Tab...No
Tab Stop...Yes
Tab Index...0
Scroll Bars...None
Can Grow...No
Left...1.3333"
Top...0.0833"
Width...0.8646"
Height...0.2188"
Back Style...Normal
Back Color...16777215
Special Effect...Shadowed
Border Style... .Solid
Border Color ...52479
Border width...3 pt
Fore Color...0
Font Size...9
Font Weight...Normal
Font Italic...No
Font underline...No
Text Align...General
Shortcut Menu Bar...
Control Tip Text...
Help Context ID...0
Tag...
Before update...
After update...
on dirty...
On undo...
On change...
On enter...
On exit...
On got focus...
Onlost focus...
On click...
Ondbl click...
On mouse down...
On Key up,,,
On key press...
Reading order...context
Keyboard language...system
Scroll bar align...system
Numeral Shapes...system
Left-right-bottom-top margin...0"
Line spacing...0"
Is Hyperline...NO
Smart tags...

This is what I am looking at.
Thanks

 

[Ken Snell [MVP]]

OK - you are working in a form.. that's good.

Open the form in design view. Click on the Symbol control. Open the
Properties window. Click on Event tab. Click in box next to On Before
Update, and select [Event Procedure] from the list. Click on "..." button at
far right of box. The Visual Basic Editor (VBE) window will open, showing
you this:

Private Sub Symbol_BeforeUpdate(Cancel As Integer)

End Sub

On that blank line between the two above lines, paste this code:

If DCount("*","MyTableName", _
"[SymbolID]=" & Me.Symbol.Value) > 0 Then
MsgBox "The value already exists!"
Cancel = True
End If


Save the form. Now, when you use it, the Symbol value will be tested for
duplication when the user enters it.

 

[Chris]

I did it and got this error:

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Method or data member not found
---------------------------
OK Help
---------------------------


I hit ok & this is what I see:

Option Compare Database

Private Sub Symbol_ID_BeforeUpdate(Cancel As Integer)
If DCount("*", "MyTableName", _
"[SymbolID]=" & Me.Symbol.Value) > 0 Then
MsgBox "The value already exists!"
Cancel = True
End If
End Sub

So I changed "My table name" to "borrowing" and got the same message.
Do you know what I am doing wrong?

Chris

 

[Ken Snell [MVP]]

First, as you've noted, you need to change "MyTableName" to the real name of
the table where you store the SymbolID values. I assume that this is
"Borrowing" from your reply.

When you clicked OK in the popup message box, and the VBE opened to show you
the error, it most likely highlighted a line in Yellow color. It helps us to
help you if you identify which line is highlighted in yellow so that we can
more easily see what might be the problem. I am assuming that the
If DCount("*", "MyTableName", _
"[SymbolID]=" & Me.Symbol.Value) > 0 Then
lines are the ones that were highlighted; and my next two suggested changes
are based on that.

You noted earlier in this thread that the control name is Symbol, and that
it's bound to the Symbol ID field:
Name.....Symbol
Control Source...Symbol ID

In the code you posted, the sub name shows that the control name is "Symbol
ID", not "Symbol". If this is correct, then change these two lines of code
If DCount("*", "MyTableName", _
"[SymbolID]=" & Me.Symbol.Value) > 0 Then

to these lines:
If DCount("*", "Borrower", _
"[Symbol ID]=" & Me.[Symbol ID].Value) > 0 Then

Make the changes noted above and try again. If the compiler still gives you
an error, post back and tell us which line is highlighted in yellow and what
the error message is.

 

[Chris]

I'm back. Sorry it took so long - I was off for two days.

{The first line shown below is highlighted yellow and the next two are
highlited in blue}

Private Sub Symbol_ID_BeforeUpdate(Cancel As Integer)
If If DCount("*", "Borrower", _
"[Symbol ID]=" & Me.[Symbol ID].Value) > 0 Then
MsgBox "The value already exists!"
Cancel = True
End If
End Sub


Does the error have anything to do with before and after update? In
Mike's instructions from earlier he says after update and ours says
before update? See below:

You can put the following code for the AfterUpdate property of the
control
the record source of which is the primary key of the table:

Just trying to help. Let me know what to do next. Thank you.

Chris

 

[Ken Snell [MVP]]

OK the "Method or data member not found" error is saying that it cannot find
a control named "Symbol_ID" or "Symbol ID" on your form, and thus it cannot
tie the Symbol_ID_BeforeUpdate procedure to it. What is the real name of the
control with which we're working here?

The next line has too many If words. Change it to

If DCount("*", "Borrower", _
"[Symbol ID]=" & Me.[Symbol ID].Value) > 0 Then

 

[Chris]

Highlighted in yellow:
If DCount("*", "Borrowing", _
"[Symbol ID]=" & Me.[Symbol ID].Value) > 0 Then

It says "text box Symbol ID". The control under 'data' we are working
on is Symbol ID. IME mode under 'other' shows no control.
Chris

 

[Ken Snell [MVP]]

Open the form in design view. Richt click on the control that you're using
for the symbol data. Select Properties. Select Other tab. What is in the
Name box?

 

[Chris]

Symbol ID

Chris

 

[Ken Snell [MVP]]

Is the field "Symbol ID" in table "Borrower" a numeric field or a text
field?

 

[Chris]

Text.

Chris

 

[Ken Snell [MVP]]

Then you'll need to delimit the control's value with ' characters:

If DCount("*", "Borrowing", _
"[Symbol ID]='" & Me.[Symbol ID].Value & "'") > 0 Then

 

[Chris]

OK - that worked. The only problem I have now I am stuck in the box
because it won't accept null. If I am on my last symbol entry I am
stuck. Can you help me with this>?

Chris

 

[Ken Snell [MVP]]

I assume that an empty string will be satisfactory as a "replacement" for
Null in the test. Try this:

If DCount("*", "Borrowing", _
"[Symbol ID]='" & Nz(Me.[Symbol ID].Value, "") & "'") > 0 Then

 

[Chris]

Private Sub Symbol_ID_BeforeUpdate(Cancel As Integer)
If DCount("*", "Borrowing", _
"[Symbol ID]='" & Nz(Me.[Symbol ID].Value, "") & "'") > 0 Then
MsgBox "The value already exists!"
Cancel = True
End If
End Sub

I replaced the second two lines (see above) with what you gave me & the
response from the form was the same. Should I have taken any of the
other lines out?

Chris

 

[Ken Snell [MVP]]

I think that we'll need to use the bang operator here instead of the dot
operator.

Private Sub Symbol_ID_BeforeUpdate(Cancel As Integer)
If DCount("*", "Borrowing", _
"[Symbol ID]='" & Nz(Me![Symbol ID].Value, "") & "'") > 0 Then
MsgBox "The value already exists!"
Cancel = True
End If
End Sub