I have the code below which is giving me grief as I have copied it from a previous usage and it worked fine. I have tried it in a beforeupdate and now as a seperate button on the form and it gives me the same error message; I have tried to put a save command in to save the record without any luck
the code is
If DCount("[Batch]", "tbl_Data_Wine_Batch", "[Batch]=" & Me.Batch) > 0 Then
Cancel = True
End If
The error message is
Run time error 2001
you cancelled the previous operation.
The problem comes up in the dcount expression.
Any help would be great as it is an important part of the project and we can move on once we have overcome this
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
Hey, all! Thanks for helping, here is my situation.
I have a table with about 70,000 records that have duplicate Address field values. The rest of the field values for those records are different. When I do a find duplicate querry I get the result that 17,000 records have the same address. However, when I do the append qurrey as instructed here: http://support.microsoft.com/?kbid=209183 I get a total of only 600 records in the new table. I have tried deleting all of the indexes for both the new and old table, with no luck.
I'm using Access 2000 on XP Pro.
If anyone could help with this I would greatly appreciate it!
say i have a table called tblEmployees and i want to find people who have the same name but different employee numbers, can that be done with a query or will that require VBA code? i won't know the names ahead of time, i don't know who has the same name.
for example, columns: first_name, last_name, employee_num, phone_num, office_num
and i want it to return James Smith with employee_num = 1234 and James Smith with employee_num = 9876.
I need to pull our records which have the same entries in Rows 1-4 and then add up Row 5. For example The above query would make three more queries i.e One 1,2,2,0, 44
Two 1,2,2,1, 7
Three 0,1,1,4, 2
I would then wish to recombine them into one table Final 1,2,2,0, 44 1,2,2,1 , 7 0,1,1,4, 2.
Is this possible and how would I go about doing this!? Been scratching my head for awhile now! thanks eveyrone! Sue
hi all, im trying to make a query so that when someone updates a field in a form it searches a table (Companytable) which is a list of company names. If it exists i want it to then display a msgbox " already exists" sort of thing! trying to do this in the criteria field on the query. anyone have any ideas or useful resources, ive not done this for a while and cant remember much of IF THEN sort of stuff. mainly not sure if im barking up the right tree, i can search dependent on a set value but not sure how to do it dependant on whats been typed into a form..any pointers would be appreciated! thanks! James
Our Contacts database has around 4000 individual contacts (and growing), which have been pulled together from four or five seperately maintained databases. As such there are bound to be duplicate records.
What would be a good way of querying the database to find duplicate entries (i.e. entries where the first name and surname are the same)? Short of putting everything in alphabetical order and manually scanning them to see if there are two names the same next to each other I don't know of a way to do it. Also, the manually-scanning method won't work if the names are spelled a little differently (Davies and Davis).
I am sure this must be simple, but I can't get my head around it.
I have a query that looks at client bank account numbers, and finds duplicates.
There are a LOT of duplicates, because we have several accounts for the same person.
What I would like to do is find all the records where the account number and sort code match, but where the account NAME is different.
I am trying to find any records where the same account number is being used on more than one account.
All the fields are in a single table.
This is what I have so far, which just finds duplicate sort codes and account numbers; SELECT CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE, CPL_ACCOUNT.BANK_NAME FROM CPL_ACCOUNT WHERE (((CPL_ACCOUNT.ACCOUNT_NUMBER) In (SELECT [ACCOUNT_NUMBER] FROM [CPL_ACCOUNT] As Tmp GROUP BY [ACCOUNT_NUMBER],[SORT_CODE] HAVING Count(*)>1 And [SORT_CODE] = [CPL_ACCOUNT].[SORT_CODE]))) ORDER BY CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE;
Access 2007 - I am using the oft used, frequently posted and shard code from srfreemen - modified as below. now I know I sort of broke commandment # 8 - Thou shalt not copy and paste other people's code without at least attempting to understand what it does. but I honestly did try to understand what it does.
I keep getting runtime error 3078 - cannot find the input table or query 'tblEnrolment_Committee_Master'. It is the name of a table in my database but I am missing the very basic element of how to get my form (which draws all of it's fields from that table) to look it up or identify it.
Likely because of a silly basic error. Normally - I wouldn't attempt such things but my work needs me to ensure no duplicates exist in this case AND it's already a primary key - but I don't want to wait until the whole form is filled out for it to identify the duplicate!
How to get the tblEnrolment_Committee_Master to be included in the search and erase this error ...
Private Sub Entitlement_File_Number_BeforeUpdate(Cancel As Integer) Dim SID As String Dim stLinkCriteria As String Dim rsc As DAO.Recordset Set rsc = Me.RecordsetClone
I have to deal with string arrays that store text. I need info on copying, comparing, appending arrays. Also on passing arrays as parameters to subs or functions. Where I can get to this info quickly without having to browse through many screens.
In addition to this I have some questions:
I have to find the duplicates of values in an array. Here is the code that I use.
Code: Sub FindDuplicates() Dim I As Integer, J As Integer, IEND As Integer, text() As String ReDim text(IEND) For I = 1 To IEND - 1 For J = I + 1 To IEND If text(I) = text(J) Then text(J) = "" Next J Next I End Sub
It works but is not performing well. Are there more efficient ways of doing this?
I have a find duplicates query with the following SQL:
Code: SELECT tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4, tblData.ID, tblData.Line, tblData.CoCd, tblData.[Document record number], tblData.PurchDoc, tblData.Reference, tblData.Curr, tblData.[Entry dte], tblData.Status, tblData.Version, tblData.Outcome FROM tblData WHERE (((tblData.Vendor) In (SELECT [Vendor] FROM [tblData] As Tmp GROUP BY [Vendor],[Loccurramount EUE],[Last4] HAVING Count(*)>1 And [Loccurramount EUE] = [tblData].[Loccurramount EUE] And [Last4] = [tblData].[Last4]))) ORDER BY tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4;
This works fine however I want to add another clause to the WHERE and I'm not sure how. At the moment the query highlights duplicates where the Vendor, Loccurramount EUE and Last4 match. I want to further restrict it so that it only finds duplicates where the Vendor, Loccurramount EUE and Last4 match BUT the number shown in version Does Not Match
So if two records have the same details for Vendor, Loccurramount EUE and Last4 and also have the same Version number then they don't show in the result.
Employees submit information into a form which translates the information into a table. The table has been in use for years. By accident some employees were writing to an archived table while others were writing the the active table. This resulted in a field called "WorkID" being duplicated across the two tables.
Bottom line I am trying to write a query finding duplicates across tables but basing the duplicate only on certain columns.
I have the below code but I want to add a grouping to it so that if there is a duplicate building number it will not list it. Is there a way to add a grouping into the code to do this?
I get a very strange error when trying to create a find duplicates query. When i try to select a table that has duplicate values it brings up a message box and it says that i must pick a table that has at least one field that's not a memo or OLE data type. I look at my table and I have niether of those in it...pretty much just text and number fields. Does anyone know what's going on?
I used the wizard to create a find duplicates query. I know for a fact that 15 records are duplicates. I'm getting over 300 records. When I dumped the data into Excel and did a pivot table to count the number of records by account number only 15 had a total count of 2. All others only had 1. Why are the ones with no duplicates showing? I just want to see the 15.
I have a Find Duplicates query that I created with the Query Wizard.
IF I run the query and find duplicates, I can close the resultant datasheet with the close button.
IF I run the query and find duplicates and delete them, I can close the resultant datasheet with the close button.
IF I run the query after I have found and deleted duplicates and closed the window, I can close the resultant datasheet (with no records) with the close button.
BUT IF I run the query when there are no duplicates, it opens the datasheet with no records, but you can't close it with the close button. You have to exit the database completely.
I don't know SQL or VB. Have to do this in Access.
I thought about creating an If statement in a macro, but am not sure of the syntax, but this would be the basic statement I would make:
IF Find Duplicates query returns no records, THEN Cancel Event. End IF
I have a report with 2 access tables (1 Master table and another a daily feed table)
The Master table keeps a log of all incoming records. (once append it to this table, should not show in future reporting)
The Daily feed information within the last 48 hours. (uploaded from an excel report into access temporary table)
When the daily feed table gets completed, I append the records and updated them into the Master to avoid duplication.
When I upload the daily feed table and I match it against the Master table to find duplicates, how can I delete the duplicates from the Daily Feed table?
This is my code to find duplicates:
SELECT CMPreport.ID, CMPreport.MbrName, tblMaster.ID FROM CMPreport LEFT JOIN tblMaster ON CMPreport.ID = tblMaster.ID WHERE (((tblMaster.ID) Is Not Null));
I have a simple database. an articles table, authors table, categories table, not much else.
So one article can have many categories. To manage this, I have my 'articles' table (pk autonumber ID, plus other fields), a 'categories' table (pk autonumber ID, txt description), and I created a linking table 'article categories' autonumber ID, number articles (related to article ID in article table) and number categories (related to category ID in categories table)
Then I made a form for articles, with a subform for categories (taking the category field from the categories table).
1st problem - the category field in the subform appears as a text box, not a drop down list of category descriptions. I deleted it and added manually a combo box with the category field from the categories table as control source, telling it to store the value in the field 'category' in the 'article categories' linking table. This gives me the drop down list.
2nd problem - I select one category in the subform, when I try to select a second, it tells me "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
I checked the fields in the 'article categories' table and they are not set to 'no duplicates'.
The strange thing seems that I can enter this ok in the table, just not the subform.
I know this problem has already been discussed here, but I could not find an answer to my case in the previous posts, so I apologise if this is repetitive.
I think this is simple but I am a novice...
I tried to attach the database to this - both normal and then zipped but it says 'invalid file' (it is only 1mb).
I'm having a small difficulty in using the code example ghudson posted for Browse [Find a directory or file]. First off, the code works great. Thanks a lot for posting it!
The problem I am having is when linking a folder via dBrowseDirectory. The folders I am linking are on a shared network drive and I am trying to figure out how to set the initial directory to default to the networked PC. This way the user doesn't have to navigate to the shared drive every time. All they would have to do is navigate to the particular folder to link (2 clicks instead of 10+).
The dBrowseFiles function allows to set an initial directory, however I cannot figure out how to set it for dBrowseDirectory. Is there any way to do this by altering your code for dBrowseDirectory?
I found code at this website that is supposed to determine if a specific program is running on your computer. I have a couple questions about the code here. One, when I run it just the way it is and call it in the immediate window with the ?fIsAppRunning("word") command, it tells me "True" whether I have Microsoft Word open or not.
My other question is this: After the code is working properly, how can I add new programs to it? There are two programs the end user might need open to complete a process. One is called Extra and one is called tn3270 (this is how they show up in the Processes tab of the Task Manager). I think in order to add them to this code, I need to know a "class" to associate with them.
I've got a string variable with a value that could be typically
"ABCD|123|R"
The string needs to be split into its three parts, the pipe symbol being the separator. Then the middle numeric string must be converted back to a long.
I know i could do a loop which identifies each character in turn, but my question is:
Is there a VBA function that can pick out the position in the string of the "|" characters, so i dont need a loop ?
Private Sub NextApplication_Click() On Error GoTo Err_NextApplication_Click DoCmd.GoToRecord , , acNext
Exit_NextApplication_Click: Exit Sub
Err_NextApplication_Click: If Err.Number = 2105 Then MsgBox "Cannot navigate to the next record. This is the last record." Else MsgBox Err.Description End If
Resume Exit_NextApplication_Click
End Sub
but even when this error occurs nothing is being properly handled the way i specified - any ideas ?
I am new to access. I have an application which has a custom tool bar. I tried to find the corresponding code for the custom tool bar by using the name of the tool bar. But was unsuccessful.
I want ot find the corresponding code for it. Where can I find the custom tool bar code in the VBA code of the applcaition?Actually, I want to add macros, queries and forms as a dirsct short cut in the existing custom tool bar.How to add macros and queries to the existing tool bar??
