Check for duplicates with multiple criteria

My table contains a field labled "Case Number" and another field labled "Date 
Completed".  I have created an input form and included code to check for 
duplicate case numbers.  A msgbox appears with a warning.  I would like the 
code to check for a duplicate case number and then check the "Date Completed" 
 field to see if it is blank.  If it is blank I would like the existing 
record to open.  If the date completed field is not blank I would like to 
continue entering data in the form to create a new record.
The code I am currently using is:

Private Sub Case_Number_BeforeUpdate(Cancel As Integer)
If DCount("*", "Copy of DIV 3 ICT Database", "[Case Number] = '" & Me![Case 
Number] & "'") > 0 Then
MsgBox "This item already exists in the table."
Cancel = True
Me.Undo
End If
End Sub

How can I incorporate the changes?
Any help is greatly appreciated.

0
Utf
5/23/2010 2:17:01 AM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
1231 Views

Similar Articles

[PageSpeed] 21

You just need to check if Date Completed is blank before doing the Case 
Number check:

Private Sub Case_Number_BeforeUpdate(Cancel As Integer)

If Len(Me.[Date Completed] & vbNullString) = 0 Then

    Exit Sub

Else

    If DCount("*", "Copy of DIV 3 ICT Database", _
        "[Case Number] = '" & Me![Case Number] & "'") > 0 Then

        MsgBox "This item already exists in the table."
        Cancel = True
        Me.Undo
    End If

End If

End Sub

-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access
Co-author: "Access 2010 Solutions", published by Wiley


"mbparks" <mbparks@discussions.microsoft.com> wrote in message 
news:2E16B3FA-A1E0-4682-A636-A2A89AEA18F7@microsoft.com...
> My table contains a field labled "Case Number" and another field labled 
> "Date
> Completed".  I have created an input form and included code to check for
> duplicate case numbers.  A msgbox appears with a warning.  I would like 
> the
> code to check for a duplicate case number and then check the "Date 
> Completed"
> field to see if it is blank.  If it is blank I would like the existing
> record to open.  If the date completed field is not blank I would like to
> continue entering data in the form to create a new record.
> The code I am currently using is:
>
> Private Sub Case_Number_BeforeUpdate(Cancel As Integer)
> If DCount("*", "Copy of DIV 3 ICT Database", "[Case Number] = '" & 
> Me![Case
> Number] & "'") > 0 Then
> MsgBox "This item already exists in the table."
> Cancel = True
> Me.Undo
> End If
> End Sub
>
> How can I incorporate the changes?
> Any help is greatly appreciated.
> 


0
Arvin
5/23/2010 4:34:29 PM
Reply:

Similar Artilces:

multiple CD's, same bank
I've got multiple CD's opened up at the same bank. They are all under the same account number, but each under a separate heading. Each of the CD's mature at a different time, and they will be rolled over into new CD's over the next 18 years. Futhermore, my bank provides online banking services directly within MS Money, including automated download of statements. So...what would be the best way to set up these accounts in Money? Should I create one account named CD and then make a cash transaction for each CD? Should I set up each CD as an individual investment accou...

Safe Pay Check Amount Problem
I am trying to setup Safe Pay. Everything seems to be working fine except for one annoying quirk. Our bank's Positive Pay data format gives us 12 characters for check amounts ($$$$$$$$$$¢¢). The file that Safe Pay generates is working perfectly except for those checks that are greater than $9,999,999.99 (9 characters). If the check amount is greater than that (say $10,000,000.00), the value that the Safe Pay file shows for that check is 002147483647. And, even if none of the individual checks exceed $9,999,999.99, if the total of the checks in the batch exceeds that a...

One Entry to Multiple Rows
I have data that looks like this: X1 | Y1 Y2 Y3 Y4 X2 | Y4 Y5 Y6 Y7 And I need to get to: X1 | Y1 X1 | Y2 X1 | Y3 X1 | Y4 X2 | Y4 ...... etc. I can change the 2nd row's entries to more columns, but that doesn't seem to get me much closer to the needed format (and there are thousands of lines so I'd rather not do it manually). Any ideas? should do it. change mc to suit '===== Option Explicit Sub lineemup() Dim mc As Long Dim mr As Long Dim i As Long Dim lc As Long mc = 3 'col c mr = 1 For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row lc ...

Downloading multiple messages
All of a sudden outlook has started downloading multiple messages from my pop account (ie 22 of the same) It is also sending out 22 of the same message. How can I resolve this issue? What version of Outlook do you have? Does it download duplicates of *all* messages, or just of one or two messages? -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "GW" <anonymous@discussions.microsoft.com> wrote in message news:005101c3bf4c$e748bf30$a501280a@phx.gbl... > All of a sudden outlook has started do...

Vendor transactions and checks
I'm trying to create a vendor payment report from my program. From which tables I can get the vendor payable transaction information??? I also need to know whether there is a check aleady issue to a transaction. Thank you. ...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

Excel ask duplicate NAMES when duplicate a worksheets
I have added a NAME called "Above" where point to the cell just above the current cell. The formula is "=INDIRECT("R[-1]C",)" In some workbook, when I duplicate a worksheets, this name will remain silent and work ok. But in some workbooks, when I first duplicate a worksheets, the same name ABOVE will be duplicate and a new local name (belongs to that new worksheet) will be created. If I further duplicate that new worksheets in to a new worksheets, the third worksheets will be warned that a dupicate NAME is existed and ask whether refer to another name or use a ne...

Query to hide duplicate records
I recall that this used to easy in previous versions (Unique values only ??), but in 2007, I can't get this to work at all. I have a table with our companie's job numbers in it. The job numbers show up multiple times because of different phases of the project: ProjNum ProjDescription 3077 Univ. of Vermont/UC/LEED 3077 Univ. of Vermont/University Commons: Building Fee 3077 Univ. of Vermont/University Commons: Excess Professiona... I need to jut have a single listing of each project number, otherwise, I get repeated records in the query that looks at this information (which ...

Duplicates
In the following folders: 1. Sent 2. Inbox 3. Calendar is there a way to eliminate duplicates without going one-by-one & deleting? Thanks for any help.. T Jones ...

Can I have spell check ignore errors and alert me to real words?
Instead of stopping on misspellings and skipping real words, is there a way to stop on correct spellings to find actual words in a bunch of gibberish? Sort of an "UNspell check". For example, pass over "JJKSXO" but show me "SPELL". No, there is no way of doing that regards, Peo Sjoblom "JenLynFish" wrote: > Instead of stopping on misspellings and skipping real words, is there a way > to stop on correct spellings to find actual words in a bunch of gibberish? > Sort of an "UNspell check". For example, pass over "JJKSX...

Multiple Language Issues
Hi all, Has anyone successfully manually updated a non-English supported 5.5 to 2003? (The reason for the manual update was a broken admin account.) Mail and contacts were all exported to PST files, and then imported to 2003. However, although the text of the messages is fine (English and Russian), the text for contact names and message subjects gets gorched if they contained any Russian text. Does anyone know the correct sequence to follow to take messages and contacts in Russian (or other languages) from 5.5 to 2003, while retaining the non-English subject lines and contact names? ...

Multiple Sales Transaction for Same Work Order
I am in the process of trying to create a store level report by department, catagory, and item of the moneys received in total by both deposit on work orders and on direct sales that are not work orders. We require a 100% deposit (of a single tender type) on work orders. I then need to break this deposit down by department, catagory and item. Then I must add that to the same break down for direct sales. Thereby getting a total of all moneys received during a specified period for the store across all registers. So basically I am trying to figure out the data structure and org...

Multiple Report Dictionaries
We just acquired another company and some of the modified reports will need to be different than the ones we currently use. There are a few people that will need to work with both companies in Great Plains. Is there a way to change which reportws dictionary a company uses - or a user uses - without editing the launch file? Thanks,, Mike You could have two installations of the client on the workstation. Each installation would have a different dictionary file. The user would have to pick the right one. "MikeW" <MikeW@discussions.microsoft.com> wrote in message news:...

in Publisher I want to merge/send email with multiple attachments
Using Publisher 2007 I know how to do a merge and then email out from a list. Publisher gives the option to attach multiple attachments to the email but when the send actually goes out it only carries the first of the attachments and discards the others. Is there a trick to making multiple attachments stay attached? or is this a bug... Thanks to anyone who can clear this up.. Rust Gilbert Rust, Since you already know how to do email merges in Publisher 2007, you're probably the ideal person to answer my question...I have been trying to do an email merge using an existing publis...

Mail merge duplications in Outlook
I am attempting an e-mail merge for a letter written in Word with my Excel list as I had done in previous versions of Word. When I do this, multiple copies of the the e-mail are sent to the e-mail recipients and the <<name>> field transposes different names from other records. The sent items appears correctly in the Outlook sent folder? My Outlook is setup to send/receive through a Pop3 Account. Has anybody encountered a similar problem or does anybody know of a fix? Thanks in adavance for your help! Eric ...

Can I embed or link multiple .pdf files into or to an excel file?
I would like to link .pdf files to an excel file. Within my excel file I would like to have a column that has file names in it. Then have excel link those files to the excel file so when I print the excel file all the linked files print along with it. use hyper link "GrubbyG" wrote: > I would like to link .pdf files to an excel file. Within my excel file I > would like to have a column that has file names in it. Then have excel link > those files to the excel file so when I print the excel file all the linked > files print along with it. ...

Sending multiple Emails so each person does not get the list
We want to send out multiple emails to several email accounts. We dont want the accounts to see the list of accounts that the email went to. How does one do this in Outlook? In any contacts folder, Tools | Mail Merge is the best choice. --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 "tom" <Spamblocker@ameritech.net> wrote in message = ...

2007 duplicate accounts?
In previous versions of Money (2005 & 2006) there was a duplicate account problem. I described it previously here: http://groups.google.com/group/microsoft.public.money/msg/61a41a7b3e0efcaf http://groups.google.com/group/microsoft.public.money/msg/b9b1de03d4a5d911 I've searched the group for comments from current M07 users about whether this problem still exists, but haven't found it. If I missed it, my apologies. Would you kindly send me a pointer? If I didn't miss it, has anyone who's using M07 seen this problem? TIA - Mark I'm not using 2007 but I think what...

Duplicate Inbox
Hi! I formatted my hardrive and installed Office 2003 again. Copied the pst file. Every thing seems to be working fine, but I see two OUTLOOK TODAY in my All Mail Folder. They are exactly the same, and both of them expand at the time of receive emails. How do I remove the other one. One item has OUTLOOK TODAY icon, however the one has icon same as when you attach any other PST file. Please help If you right-click the root folder of the extra .PST file and select Close from the context menu, does it close? If not, can you remove it from your profile using File | Data File Management? -- ...

Outlook 2003
Hi, Anyone know if it is possible to save multiple calendar selection views in Outlook 2003 yet ? For example a receptionist may have the ability to view say twenty or so users calendars. When she closes Outlook and opens it up again she has to select them all over again. I see lots of people wanting to do this and being told that it's not possible...or at least the posts I've read thus far. Jim. ...

Supporting multiple excel versions from .NET
Hi, I am using excel automation to write data to Excel. I am using Excel 9 and I using early binding to create the objects. I would like to support Excel versions 9 and upwards. To run the same application on a production system, with office XP,( which has only the framework installed), should I distribute the olb file, that I used while referencing, along with my installation? Are there any other dependencies, that I need to take care? Any help would be greatly appreciated. Thanks in advance. ...

Help with Highlighting all duplicates in a row
I am trying to highlight duplicates in a row and am using the explanation: Highlighting Duplicate Entries Our first task is to highlight the cells in Range1 that are duplicates. We use Excel's Conditional Formatting tool to accomplish this. First, highlight the entire Range1. Then, select the Conditional Formatting tool from the Format menu: Format->Conditional Formatting. Change the "Cell Value Is" option to "Formula Is" and enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)>1,TRUE,FALSE) Where A5 is the first cell in Range1....

SumIf Criteria Matches
I am trying to sum the amounts in column c if they match the criteria i colum f. I can sum c if column f is a single criteria. My problem is I wan column c to sum if the criteria in column f is <>. Example: column c column f 1000 -- 13.5 2000 -- 8 3000 -- 15 4000 -- 20 I want to get the sum in column c if column f is >8 and <14.99. Thi should total 1000. I can't seem to find the right formula. I hav tried using =sumif(F2:f19,>8<14.99,C2:c19)....

How do I remove multiple hyperlinks in the same cell (Excel)?
I have multiple hyperlinks in one Excel cell, each separated by a hard return (ALT-Enter). I can remove the hyperlinks using: -- right click > remove hyperlink -- or by using a macro but I cannot get this to work on multiple links in one cell. When I do this, all links go into one line separated by a square box. I have had to create a separate line for the second hyperlink, and this is making my spreadsheet look out of alignment. Please help! ...

Check if data has already been formatted.
Hello, I have a sub() as part of a larger set of macros. This particular sub() will take the raw data pulled from an in-house reporting system and format it to suit our needs (adding a couple of columns with formulas to facilitate pivot table creation). This is the code as I have it so far: Sub FormatData() Application.ScreenUpdating = False ActiveWindow.DisplayGridlines = False ' Declarations Dim DataWks As Worksheet Dim LastRow As Long Dim LastCol As Long ' Set the DataWks variable Set DataWks = Worksheets("Data") ...