Check to see whether a table contains a set of fields already

Let's say I have a table that has five fields, a, b, c, d, and e

For any given values of a,b,c (say a=1,b=2,c=3), I'd like to check
whether any records in the table already have those values for those
fields.

For example:

If my table looks like

     a,b,c,d,e

     1,2,3,4,5
     2,3,4,5,6
     3,4,5,6,7
     4,5,6,7,8
     etc.

Then, the first record would be a "match" with a=1,b=2,c=3.

How do I test for that?

TIA,
Tom

0
tryit
12/15/2009 5:28:03 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
1202 Views

Similar Articles

[PageSpeed] 24

tryit wrote:

>Let's say I have a table that has five fields, a, b, c, d, and e
>
>For any given values of a,b,c (say a=1,b=2,c=3), I'd like to check
>whether any records in the table already have those values for those
>fields.
>
>For example:
>
>If my table looks like
>
>     a,b,c,d,e
>
>     1,2,3,4,5
>     2,3,4,5,6
>     3,4,5,6,7
>     4,5,6,7,8
>     etc.
>
>Then, the first record would be a "match" with a=1,b=2,c=3.
>
>How do I test for that?

Run a query something like:

SELECT a,b,c,d,e
FROM table
WHERE a=1 And b=2 And c=3

-- 
Marsh
MVP [MS Access]
0
Marshall
12/15/2009 6:02:47 PM
On Dec 15, 10:02=A0am, Marshall Barton <marshbar...@wowway.com> wrote:
> tryit wrote:
> >Let's say I have a table that has five fields, a, b, c, d, and e
>
> >For any given values of a,b,c (say a=3D1,b=3D2,c=3D3), I'd like to check
> >whether any records in the table already have those values for those
> >fields.
>
> >For example:
>
> >If my table looks like
>
> > =A0 =A0 a,b,c,d,e
>
> > =A0 =A0 1,2,3,4,5
> > =A0 =A0 2,3,4,5,6
> > =A0 =A0 3,4,5,6,7
> > =A0 =A0 4,5,6,7,8
> > =A0 =A0 etc.
>
> >Then, the first record would be a "match" with a=3D1,b=3D2,c=3D3.
>
> >How do I test for that?
>
> Run a query something like:
>
> SELECT a,b,c,d,e
> FROM table
> WHERE a=3D1 And b=3D2 And c=3D3
>
> --
> Marsh
> MVP [MS Access]

Thank you, but I wasn't looking for SQL code.  I was looking for VBA
code that will return true or false.  Is there a way to test in VBA
code whether a query is empy or not?


Thanks again,
Tom

0
John
12/15/2009 6:03:40 PM
John Harrington wrote:

>On Dec 15, 10:02�am, Marshall Barton <marshbar...@wowway.com> wrote:
>> tryit wrote:
>> >Let's say I have a table that has five fields, a, b, c, d, and e
>>
>> >For any given values of a,b,c (say a=1,b=2,c=3), I'd like to check
>> >whether any records in the table already have those values for those
>> >fields.
>>
>> >For example:
>>
>> >If my table looks like
>>
>> > � � a,b,c,d,e
>>
>> > � � 1,2,3,4,5
>> > � � 2,3,4,5,6
>> > � � 3,4,5,6,7
>> > � � 4,5,6,7,8
>> > � � etc.
>>
>> >Then, the first record would be a "match" with a=1,b=2,c=3.
>>
>> >How do I test for that?
>>
>> Run a query something like:
>>
>> SELECT a,b,c,d,e
>> FROM table
>> WHERE a=1 And b=2 And c=3
>>
>
>Thank you, but I wasn't looking for SQL code.  I was looking for VBA
>code that will return true or false.  Is there a way to test in VBA
>code whether a query is empy or not?


Since the data is in a table, you need to use a query to see
it.  You may find if more convenient to use a Domain
Aggregate function (that creates and runs an equivalent
query for you):

If DCount("*", "table". "a=1 And b=2 And c=3") > 0 Then

If that's not what you want, then you can open a recordset
on the query and check its RecordCount property.

-- 
Marsh
MVP [MS Access]
0
Marshall
12/15/2009 7:01:31 PM
tryit wrote:
>Let's say I have a table that has five fields, a, b, c, d, and e
>
>For any given values of a,b,c (say a=1,b=2,c=3), I'd like to check
>whether any records in the table already have those values for those
>fields.
>
>For example:
>
>If my table looks like
>
>     a,b,c,d,e
>
>     1,2,3,4,5
>     2,3,4,5,6
>     3,4,5,6,7
>     4,5,6,7,8
>     etc.
>
>Then, the first record would be a "match" with a=1,b=2,c=3.
>
>How do I test for that?
>
>TIA,
>Tom

If you set up a unique index on a set of fields, then Access will do it for
you. Won't be pretty, but it will work -- but you can override the typical
message to fix it. 

Otherwise, you can get Trevor's code here:
http://www.mvps.org/access/modules/mdl0012.htm

and then use TCount() to determine if the record exists already.  Just create
the criteria string in your form and pass that to TCount... something like

Dim strCriteria As String
strCriteria = "[Field1] = '" & Me.txtField1 & "' AND [Field2]=#" & Me.
txtDateField & "#"

If tCount("FieldToCount", "SourceTable", strCriteria)>0 Then
   MsgBox "Record exists already", vbokonly
Else
  MsgBox "Record doesn't exist", vbOkOnly
End If

If you use Trevor's code, you can do things like allow some number of
duplicates (say 3) or something similar (you aren't limited to the "no
duplicates" rule - you can implement a "maximum number of duplicate values"
rule).

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

0
PieterLinden
12/16/2009 12:59:28 AM
Reply:

Similar Artilces:

Setting rules and alerts to run automatically on Spam folder?
I am attempting to undo a filter error that places valid email into the Spam folder. If I manually run the Rules and Alerts in the Spam folder, it will move the "spam" messages back to the Inbox. Is there a way to make the rule automatically run from a folder other than the Inbox? Hi Stu, [....] > Is there a way to make the rule automatically run from a folder other > than the Inbox? No. You could use another SpamFilter program (not Outlook spam filters) with more functions or have at first a look on this site for some further information about the Outlook Junk filte...

How do I set number formats that will be applied only if a number.
Hi you may post your question as body of your message :-) -- Regards Frank Kabel Frankfurt, Germany "Jim Gentile" <JimGentile@discussions.microsoft.com> schrieb im Newsbeitrag news:2678B459-1954-4258-A570-155E25F18F52@microsoft.com... > ...

Database Utility not seeing Identity
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Email Client: pop Entourage 2008. <br><br>When trying to run Entourage it tells me that it can't access my data. I need to run the Database Utility. Entourage then crashes and opens up the database utility. It doesn't see my identity, so I can't repair it. <br><br>I have done some searching and found some things to try. All of which were old, none of which worked. So I thought I would check and see if there is a solution yet. <br><br>I have moved the identity and ...

merge MSN Bill Pay & Checking Acct
I want to merge my checking account with MSN Bill Pay but only see that I can merge MSN Bill Pay with my checking account (i.e., you lose all of your checking account information). Does any one have any suggestions? -- cheers, Tom G ...

Excel2007 VBA
If I create a PivotTable with a field called DATE containing dates formatting as dd/mm/yy, I then use this macro to remove Saturdays and Sundays from the PivotTable. Except it reads the dates a mm/dd/yy until they are 'out of scope' and then they are read as dd/mm/yy. Why? Sub HideWeekendsFromPivotTable() Dim pivotName As String Dim pivotDate As Date Dim z, pivotCount As Long pivotName = ActiveSheet.PivotTables(1).Name With ActiveSheet.PivotTables(pivotName).PivotFields("Date") pivotCount = .PivotItems.Count ...

Authorizing checks with RMS
I recently removed PCCharge and setup my syetm to work with a preferred merchant provider to allow native processing of credit & debit cards. Since check verification/authorization was handled by PCCharge to Telecheck, check authorization is now not being done. How is check authorization accomplished using a preferred provider? Thanks, Richard Unfortunately, it's not. PC Charge is the only option for check processing in RMS as far as I know. TPI or some other vendor may support it through an add-in, but RMS as shipped has no way of doing both checks and debit... -- -- Gle...

Check names not working for multiple address books / contacts
Hello! I have upgraded from outlook 2003 to outlook 2007, my contacts are stored in pst. I used to be able to search for a name via check names and it displayed me all the results from global address list and from my contacts, but now it only does one. It does not matter which one I set in the checking order, whichever is top will be the one that provides the result. I would like it to show me both results, how would I go about to set that up? Thank you Deana More accurate and complete information would be helpful. The Global Address List is only available to those who c...

How do I create Check list
1.I am new to excel and want to create a simple check list. I would like to have the first column check boxes and then the following columns discription and notes. Can someone give me a step by step on how to do this. I have created spread sheets but am not familiar with check boxes. 2.Also, Can someone tell me the difference between Access and Excel? Geek 1) Not sure what you're aiming to do with the checkboxes, but if you right click on the toolbar area, you will get a list of available toolbars. You want either the 'forms' toolbox or the 'controls' toolbox. Genera...

Change management
Thanks in advance. Some co-workers use a third party analysis tool built upon a complex, proprietary 2005 db. To assist their efforts to validate their inputs to this tool, I long ago created a some views in another db on the same server. The views join some 18 or more tables from the proprietary db. All the joined table names are fully qualified with the prefix 'dbname.dbo.' Also created were functions and sprocs that also reference the proprietary db. These views, sprocs, & functions are used by both an Excel application and by a DotNet app, i.e., they'r...

setting a dedicated place to open an "xls" file
Gentlemen, I do work at the office evey day on spread sheets and save to a disk, which I bring home. Is there any way that I can "lock in" a dedicated point to retrieve my files once I have saved them to "c:\documents & Settings\my documents. When I click "Open and go to "A" drive to get the file from the disk that I took home, my computer "remembers" that this was the last place I opened a file from. And when I am in Excel and want to open a file, the computer looks in "a". Thanks Raymond Tools / Options / General Tab - Default File loc...

Check text field value against underlying table or query of combo box
Hello, I have a form on which the user can enter new records or update existing ones. If the user enters a new record on the form, I would like to have an 'After Update' event on one of the text fields, which is the item# to check if the value entered already exists in the underlying table, tbl_item. How can I call the table from the 'After Update' event of the text field and check for that value in the table? Thanks in advance. Regards, A. Crawford antcraw@swbell.net wrote: >I have a form on which the user can enter new records or update >existing ones. >If the us...

Preventing multiple check boxes from being checked
I have a worksheet with over 20 rows of check boxes with 5 check boxes in each row. (from the forms toolbox) I would like to prevent the user from checking more than one box in each row. Is this possible? BTW, this forum has been a great help. I'm so glad that expert Excel users are so willing to share their wealth of knowledge. Thanks again. Jae Put them in a group box, again from the forms toolbar. Drag the group box onto the sheet, then drag the checkboxes in. -- HTH RP (remove nothere from the email address if mailing direct) "Jae" <Jae@discussions.micro...

querying unrelated tables
I have two tables - Employee_PunchTime table and Holidays Table. The PunchTime table contains three fields representing EmployeeID, Time-in and Time-out; and the Holiday table contains two fields representing name of holiday and the date. If the payroll period is 5/31/2010 (Mon) thru 6/4/2010 (Fri) and 5/31/2010 is a paid holiday, I need to create a query that shows punched time and paid holiday on a separate column. On the paid holiday, there would obviously be no Time-In and Time-Out record. It will look something like this: EmpID Date Time-in Time-out Paid ...

Printing Checks
I'm having a problem printing checks from Money 2003. I have three accounts that come up in one .mny file. Two of the accounts use 3-on-a-page checks and I want the name and address printed on them. One account uses voucher checks and needs to be nudged a little south. When I go to print checks I have to be aware of what account I'm in and change the settings by hand to make sure the checks print correctly. How can I make the check-print settings stick with the account I'm using. It's a pain to have to remember every time. I'm frequently forgetting and screwing up a pag...

where on Exchange 2000 do you set distribution size limit?
I have a client who wants to create a large distribution list. I know it's a setting on the Exchange server but where? thanks in advance! ...

Employee Check Format-Graphical
Does anyone know of a TK that shows how to convert one of the employee check from a text report to a graphical report? We do not have Direct deposit, I looked in the Report Downloads from Great Plains and did not see one there either. thanks -- Doug There's no secret here. Just un-check the "Text Report" option in the Report Definition window. And then modify the report so it looks like you want it. This will take some time. The reports don't transfer from text to graphical nicely. "Doug" wrote: > Does anyone know of a TK that shows how to convert...

Set user permission to have right to create mail-enable account
Dear All, Can I know how can I set the minimum permission to have right to create mail-enable account. I have root AD called test.com, child dc called hk.test.com (installed exchange2003 server), and cn.test.com(only installed ESM) cn.test.com 's mail accoount store in hk.test.com 's exchange server, now I would like to assign permission to cn.test.com some domain user to have minimum permission to create mail enable account in Active directory user and computer snap in. When I create mail-enable account, I have no choice in "maiboxl store" option except root admi...

What table are notes stored in?
I have notes linked to Cases, but I can't find the SQL table they are in! Can anyone help? Thanks! Nevermind, I found it: AnnotationBase "AW" wrote: > I have notes linked to Cases, but I can't find the SQL table they are in! > Can anyone help? Thanks! AW, annotationbase. HTH, Dave ------------------------------------------------- David L. Carr, President Visionary Software Consulting, Inc. Main #: 971-327-6944 Introducing our newest software product... VAST - an auditing solution for Microsoft CRM 1.2 VAST shows you "Who Did What When" in Micros...

Check Names = "Exchange Server computer is busy..."
Here's what I got: Active Directory on Server 2003 1 Domain Controller also running DNS and WINS No other DNS or WINS servers (I know, I know) Static IP Addresses...NO DHCP Exchange Server 2003 on a member server Client running W2K SP4 Outlook 2000 SP3 Exchange has 4 mailboxes set, but nobody is using it. I'm still testing things out. When I try to connect to Exchange via my Outlook 2000, I get the following message: "The function cannot be performed because the Microsoft Exchange Server computer is busy. Try again later" I've tried it from 3 different client comp...

page sees session variable as null until refresh
I’m having a problem with a web app that sees a session variable as null until a page reload. These are the steps: 1. Web app loads an array of string data into a session variable 2. Go to another page using response.redirect(“mynextpage.aspx”) 3. Return to current page using window.open(“mypage.aspx”,”_self”) call in javascript file 4. Upon returning, process initially sees session variable as null (this is not good); 5. refresh page using browser green arrows button, process now sees session variable as not null, and contains the array (this is what I need) Other items:...

Pivot Table Report: Getting average of difference of two date columns
Hello I have data in excel sheet as: WORK_ID PROJECT START_DT END_DT 1001 P1 1/1/2006 1/10/2006 1002 P1 1/25/2006 2/20/2006 1003 P1 3/15/2006 3/20/2006 I want to have a report that will give me average time taken to finish a work in a project. That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report ...

Could very much use a Field List
I apologize if this is out there already, but it seems that at least a few field names changed from 1.2 to 1.3. Although I had downloaded a field list before, from here, I cannot even find that original post, let alone any more recent that might contain the correct field names. I need to do some custom SQL and would prefer not to reinvent the wheel. Thanks in advance very much to anyone who could help. Bud Izen Salem Oregon Do you have MS Access? Make a new project and attach to the database as your data source. You will be able to see all the tables and the field names. It has been ...

Form
Hello, I am trying to create a project progress form. The is completed twice a month. I have created a table that has the periods as follows: Report Num Period Start Period End ------------------------------------------------------ 1 01 Jan 10 15 Jan 10 2 16 Jan 10 31 Jan 10 etc.... The second table has the work items in the project: Item # Work Desc. % Total % this Period % Cumulative -------------------------------------------------------------------------------- 1 ...

Printing MICR checks
Hello, We are having an issue printing checks on the Select Payables Checks window. When we try to print the checks we receive the error message below: "Warning: You have MICR but are not in a MICR screen. To use MICR, abort this screen and set up security access." The user has access to this screen but I'm not sure what this error message means. There was nothing related to this in the Knowledge Base either. Has anyone else received this error message before and if so how did/do you resolve it? Thanks! The user has the third party mekorma check writing module ...

XP clients unable to see GAL users in 1 OU
We have just installed exchange 2003. We have 2 locations and each location has a DC - a Win2K box and a Win2003 box. Our users on Outlook 2000/Win98 at the remote location (uses a Win2K DC) can see all users in the Exchange 2003 GAL. Users at that location using Outlook 2003/WinXP cannot see any users from 1 OU in the AD. The AD is replicating normally and authentication traffic is normal. The OU in question is the OU for the remote location users. What is different about Outlook 2003/XP vrs Outlook 2000/Win98 in this regard? Thanks, Roy Older clients use Exchange to handle the query and...