Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Checking records

Posted on 2011-03-14
27
Medium Priority
?
235 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
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

916 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