Button to check if entry already in use (1 Viewer)

cwitton

New member
Local time
Yesterday, 20:58
Joined
Feb 20, 2012
Messages
7
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!
 

John Big Booty

AWF VIP
Local time
Today, 13:58
Joined
Aug 29, 2005
Messages
8,263
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.
 

cwitton

New member
Local time
Yesterday, 20:58
Joined
Feb 20, 2012
Messages
7
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
 

John Big Booty

AWF VIP
Local time
Today, 13:58
Joined
Aug 29, 2005
Messages
8,263
Do you have some sample data you are able to post? I'd really need to have a look at the DB structure.
 

missinglinq

AWF VIP
Local time
Yesterday, 23:58
Joined
Jun 20, 2003
Messages
6,362
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)>
 

cwitton

New member
Local time
Yesterday, 20:58
Joined
Feb 20, 2012
Messages
7
Thanks Linq its all good now. Many thanks to you both for your help on this.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom