Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-23-2012, 03:01 AM   #1
cwitton
Newly Registered User
 
Join Date: Feb 2012
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
cwitton is on a distinguished road
Button to check if entry already in use

I have a db with various tables including one called Policy Details. Users use a form to enter new records etc. All the policies have a reference number which acts as the primary key and which is usually generated by a separate application system.

There are rare occasions (but they do happen about once a month) where that second application system is slow or back logged and so we record records using a 'Dummy' reference, which we go back and change later.

The dummy reference has a format (eg 00001T or 00002T) but is up to the user to input it/think it up.

I want to add a button on to the form that will check for the user whether the reference is already in use and have a simple error message to say 'sorry, already in use' at that point when the user has done the availability check. As it stands, they fill in all the details, and when it comes to saving the record, there is an error message saying the changes requested are not successful as would create duplicate values in the index or primary keys.

Any clues as to the coding behind this? Or even a snazzier tool that will (when selected) in put the next available Dummy reference. I have very little expectation of the latter, but the former would be brilliant.

The Text Box is called [Risk Reference]
The relevant table is [Policy Details]

Many thanks!

cwitton is offline   Reply With Quote
Old 02-23-2012, 03:09 AM   #2
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: Button to check if entry already in use

Perhaps the discussion and code presented in this thread will give you some pointers.

Also avoid using spaces and other special characters in Object and control names. Limit your self to alpha and numeric characters and the underscore, so for example TBL_TableName, FRM_FormName etc.
__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 02-23-2012, 07:09 AM   #3
cwitton
Newly Registered User
 
Join Date: Feb 2012
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
cwitton is on a distinguished road
Re: Button to check if entry already in use

Ok so it sort of worked. And very much didn't. Now everything comes back and says its duplicate! Any clues John?

Private Sub RiskReference_BeforeUpdate(Cancel As Integer)
If DCount("RiskReference", "[Policy Details]", "RiskReference= " & "'" & Me.RiskReference & "'") > 0 Then
Me.Undo
MsgBox Me.RiskReference & " already in use", vbOKOnly, "Whoops!"
End If
End Sub

cwitton is offline   Reply With Quote
Old 02-23-2012, 02:48 PM   #4
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: Button to check if entry already in use

Do you have some sample data you are able to post? I'd really need to have a look at the DB structure.
__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 02-23-2012, 03:58 PM   #5
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,300
Thanks: 11
Thanked 716 Times in 666 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: Button to check if entry already in use

You've got your Square Brackets in the wrong place and some other extra stuff. Line
Code:
If DCount("RiskReference", "[Policy Details]", "RiskReference= " & "'" & Me.RiskReference & "'") > 0 Then
should be
Code:
If DCount("RiskReference", "Policy Details", "[RiskReference] ='"  & Me.RiskReference & "'") > 0 Then


Linq ;0)>
__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
The Following User Says Thank You to missinglinq For This Useful Post:
John Big Booty (02-24-2012)
Old 02-24-2012, 03:41 AM   #6
cwitton
Newly Registered User
 
Join Date: Feb 2012
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
cwitton is on a distinguished road
Re: Button to check if entry already in use

Thanks Linq its all good now. Many thanks to you both for your help on this.
cwitton is offline   Reply With Quote
Old 02-24-2012, 08:13 AM   #7
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,300
Thanks: 11
Thanked 716 Times in 666 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: Button to check if entry already in use

Glad we could help!

Linq ;0)>


__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Reply

Tags
button , duplicates , forms

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
does the check box cause double entry? 1jet Modules & VBA 5 10-01-2008 01:50 AM
Check that entry does not exist msaunders26 Forms 1 02-20-2008 04:07 PM
Check for entry - Any comments? reclusivemonkey Code Repository 1 12-12-2005 05:17 AM
Check for no data entry bunji Forms 3 11-04-2005 08:42 AM
check if their is a new entry amoona Forms 3 01-25-2005 11:35 PM




All times are GMT -8. The time now is 10:51 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World