Solved

Checking records

Posted on 2011-03-14
27
230 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 6
  • +1
27 Comments
 
LVL 9

Expert Comment

by:sshah254
ID: 35133325
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
ID: 35137215
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
ID: 35174934
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 27

Expert Comment

by:MikeToole
ID: 35180318
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 46

Expert Comment

by:aikimark
ID: 35180766
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
ID: 35181111
@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
ID: 35182158
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 46

Expert Comment

by:aikimark
ID: 35182464
@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
ID: 35183768
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
ID: 35183859
The point was that the button code becomes redundant if a unique index is put in place
0
 

Author Comment

by:PipMic
ID: 35184096
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 46

Expert Comment

by:aikimark
ID: 35184164
@PipMic

Do you know how to create or change a table index?
0
 

Author Comment

by:PipMic
ID: 35184351
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 46

Expert Comment

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

Author Comment

by:PipMic
ID: 35184462
not sure how to do that!
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35184545
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
ID: 35184620
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
ID: 35184650
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 46

Expert Comment

by:aikimark
ID: 35184876
@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
ID: 35187883
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
ID: 35188057
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
ID: 35188390
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
ID: 35188698
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
ID: 35189465
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
ID: 35190845
Plenty of patience from this expert who slowly led me to a good solution
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

623 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