Solved

Checking records

Posted on 2011-03-14
27
224 Views
Last Modified: 2012-05-11
Hi all,

I am in the process of preparing an imput form where I have to enter a book reference and a date. however in the save button I would like to indert a condition that if  the same book reference with that same date is entered a warning pop up message box will highlight that that book with that date is already is entered and that the record cannot be saved unless a different date is chosen.

I am using MSAccess 2003.

can anybody guide me through this process?

Thanks
0
Comment
Question by:PipMic
  • 10
  • 8
  • 6
  • +1
27 Comments
 
LVL 9

Expert Comment

by:sshah254
Comment Utility
Normally the data is tied to the table in the form.  You'll need to break this and code all the actions (create a query for each action like go to next record, previous record, save record, etc.).  All the data that is retrieved / saved is done through variables on the form.

Create a form to accept the values to be saved.

In the "Save" button's "Click" action, run a query to check if the data already exists in the table and take necessary action.

Ss
0
 

Author Comment

by:PipMic
Comment Utility
Hi,

Thanks for your comment.

Yes the data entered would go straight into the table. My issue is that before I save the record there will be a condition assigned to the Save Button which will check the data first.

I understand your point, however if rather than having a Save button, the data is input directly into the table in the following order
1. Book
2. Date

I'm wondering
(a)  whether it is possible for Access to stop the date entry if it finds that the book entered with the date already exists.

or

(b) whether at the end of the record entry a message box could pop up saying that a the same book has been entered with the same date.

Comments welcome.
0
 

Author Comment

by:PipMic
Comment Utility
Private Sub Save_record_Click()

If DCount("*", "BU Table", "File No = '" & me.combo76 & "' AND BU Date = '" & txt_BUDate & "'")   = 0
  ' Insert record
  CurrentDb.Execute "INSERT INTO BU Table (File No,Bu Date,Off,Memo) VALUES( '" & Me.Combo76 & "','" & Me.txt_BUDate & "','" & Me.txt_Off & "','" & Me.txt_Memo & "')", dbFailOnError
Else
   ' record found, don't insert record
   MsgBox "record exists"
End If

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

  End Sub

Open in new window


Thanks
0
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
The first thing to do is to change the design of the table so that its Primary Key is a combination of the Book Reference and Date.
That way Access will prevent the insert or update of any row that would cause a duplicate, now matter how it's attempted (for instance, shift + enter is the default keyboard shortcut to save the current record, which would completely bypass any code behind your command button. As would the page-up page-down keys, which will save the current record before navigating to the previous/next record).
The error message it presents may not be the most user friendly, but I'm pretty sure that that can be fixed (long time since I developed with Access :-))

By the way, an easier way to save a form's current record is:

    If Me.Dirty Then Me.Dirty = False
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
I partially disagree with MikeToole.  You need a unique index on these columns, not a primary key.  If you already have an autonumber field as your primary key, you should maintain that PK definition.
0
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
@aikimark: Agreed, if a PK is already in place then the Unique Index is the way to go. I was more concerned to promote the use of built-in error checking, which both approaches will satisfy.
0
 

Author Comment

by:PipMic
Comment Utility
Huh???

The code so far as shown above, was almost complete. All that was missing was a "Then" at the end  of the Dcount line, i.e. after the zero.

It then became an IF Then Else function. It works a treat now, thanks guys.Much appreciated.

I would have preferred someone to have corrected the code.  Could have saved some time..

" But I suppose, Beggars can't be choosers"

0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
@PipMic

The compiler/syntax checker should have told you what was wrong with the If statement.  You didn't need us for that.  You got sound advice on preventing duplicates independent of form code.  

* If someone opens the table (or query based on the table), a unique index will prevent duplicates.  
* If duplicates already exist, you will will be notified of their existence when you create the unique index.
* Having a unique index should make your DCount() function work faster.

====
Note: DCount() is not as efficient as DLookup(), since it passes the entire table every time.  DLookup() stops when it finds a matching record.

Note: If performance is a concern, please read this excellent article:
http://www.experts-exchange.com/A_1921.html
0
 

Author Comment

by:PipMic
Comment Utility
Hi,

I did not mean to be rude. I appreciate your comments but you must appreciate that we are all at different levels and what may seem obvious for experts is not as obvious for others. Sometimes we are looking for simple solutions as we plod away at learning new stuff.

Anyway thanks for the article. My first impression is that its a bit complicated, but still i'll have a look at it.

Thanks

0
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
The point was that the button code becomes redundant if a unique index is put in place
0
 

Author Comment

by:PipMic
Comment Utility
Oh,

By the way, how do you make both fields unique (together). I.e. uniqueness must be when a record contains the same data on the same two fields??

Grateful for advice
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
@PipMic

Do you know how to create or change a table index?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:PipMic
Comment Utility
I think so , thats in the table's properties - indexed - No duplicates.

I suppose i can do that for both fields, but i want the combination of both fields.

thanks
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
you define both fields in adjacent rows for a single index.
0
 

Author Comment

by:PipMic
Comment Utility
not sure how to do that!
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
the index dialog is a grid.  the first column is for the index name (should be unique), the second column is for field names.  The third column defaults to
Ascending.

In the check boxes below, you define whether the index is a PK or unique.
0
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
In table design choose Indexes. Create a multi field index by supplying multiple field names for the same index name
21-03-2011-21-19-17.png
0
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
mmm - I didn't really mean to name the index 'snother', but, anyway, it doesn't matter what it's called.
There's a bit of redundancy here, the index is shown as a primary key and also a separate index. Use one or the other!
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
@Mike

I wish you had posted a screen shot that  was less confusing.  If a primary index exists, there is no need for an additional unique index.  (no matter what it is named)
0
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
Sorry, I thought I asked for a 'Preview', but it got posted for real.
As I said in the follow up post, use one or the other of the Primary key  and the unique index.
0
 

Author Comment

by:PipMic
Comment Utility
totally lost..

i'm using A03...Ok have tried this indexing thing and it works... but the message that pops up is an Access one. i would prefer to create a more pertinent message box which highlights the error to the user... i.e something like "this book is already allocated to this date"

Is that possible using the indexing method?

Thanks

0
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
You can intercept the form's error event and replace the standard message with one of your own.

Open the form in design mode and choose the Event tab in the property sheet.
Select the On Error event and create an Event Procedure, Access should display the following in the code editor

     Private Sub Form_Error(DataErr As Integer, Response As Integer)

     End Sub

Complete as in the attached example, using your own field names in place of ID and BookDate
Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Const conDuplicateKey = 3022
    If DataErr = conDuplicateKey Then
        Response = acDataErrContinue
        MsgBox "Record already exists for ID " & Me.ID & " and date " & Me.BookDate
    End If
End Sub

Open in new window

0
 

Author Comment

by:PipMic
Comment Utility
Thanks Mike,

Tried last bit of code and it works a treat.

Just wondering just before i give up. Would it be possible fro the error message to appear on entering the second piece of data.

I wouldnt want the user entering all the data to find at the end that the record that all the data for that reference and date is not available!!

Would that be possible?

Thanks
0
 
LVL 27

Accepted Solution

by:
MikeToole earned 125 total points
Comment Utility
The error isn't triggered until there's an attempt to save the record.
To get a message earlier you'd need to put code in the afterupdate event of the second piece of data..
If it's suitable for your situation you could just save the record:
     If Me.Dirty then Me.Dirty = False
If it's not a duplicate the user will then just continue to enter data to the row, but now in edit rather than insert mode.
Probably better though would be to do a DLookUp in the control's afterupdate event.
It's still worth keeping the index, it will make the DLookUp quicker and you can be sure that no duplicates can ever get into the table.
0
 

Author Closing Comment

by:PipMic
Comment Utility
Plenty of patience from this expert who slowly led me to a good solution
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now