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
|
|||
|
|||
Unique entry
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 |
#2
|
|||
|
|||
Unique entry
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" wrote: 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 |
#3
|
|||
|
|||
Unique entry
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 |
#4
|
|||
|
|||
Unique entry
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. -- Ken Snell MS ACCESS MVP "Chris" wrote in message ups.com... 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 |
#5
|
|||
|
|||
Unique entry
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 |
#6
|
|||
|
|||
Unique entry
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. -- Ken Snell MS ACCESS MVP "Chris" wrote in message ups.com... snipped This is what I am looking at. Thanks |
#7
|
|||
|
|||
Unique entry
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 |
#8
|
|||
|
|||
Unique entry
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. -- Ken Snell MS ACCESS MVP "Chris" wrote in message ups.com... 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 |
#9
|
|||
|
|||
Unique entry
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 |
#10
|
|||
|
|||
Unique entry
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 -- Ken Snell MS ACCESS MVP "Chris" wrote in message ups.com... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Memo Field | Sam | General Discussion | 12 | November 10th, 2005 09:16 PM |
How do I make a unique entry | Lynn Bales | New Users | 9 | August 15th, 2005 10:55 PM |
How do I create a table of unique records from two or more tables | Rubble | Running & Setting Up Queries | 5 | June 23rd, 2005 12:05 PM |
Query on unique | Steven | Running & Setting Up Queries | 3 | February 5th, 2005 12:24 AM |
decipher log of scanpst.exe | km | General Discussion | 0 | July 18th, 2004 09:00 AM |