Check For Duplicate Data Before Importing

May 21, 2007

Hello All,

I have a database where a user imports data into a table monthly. She saves the Excel file and then clicks a button in Access to import the data. My instructions say to only click that button once, but so far, she has clicked it THREE TIMES, thus importing the data three times. How can I prevent this? Here is a copy of my import code:

Option Compare Database
Option Explicit

Public Function ImportTableData()
On Error GoTo ImportTableData_Err

Dim strPeriod1 As String
Dim strMM1 As String
Dim strYY1 As String
Dim strYYYY As String
Dim strFormName As String
Dim strMDPath As String
Dim strMDFileName As String
Dim strDCPath As String
Dim strDCFileName As String


'No Warnings
DoCmd.SetWarnings False

'Import Current Month data in a temporary table
DoCmd.OpenForm "frmEnterAgingDates", , , , acFormReadOnly, acHidden
strFormName = "Forms!frmEnterAgingDates"
strPeriod1 = Forms!frmEnterAgingDates.Period1.Value
strPeriod1 = Format(strPeriod1, "mmddyyyy")
strMM1 = Left(strPeriod1, 2)
strYY1 = Right(strPeriod1, 2)
strYYYY = Right(strPeriod1, 4)
strMDPath = "S:FinanceAccounting OperationsNational AccountsAccount Recs116405.100 and 117630.300116405.100-AR Due from PAR Plans NASCO"
strMDFileName = "MD AR Due from PAR Plans_" & strMM1 & strYY1 & ".xls"
strDCPath = "S:FinanceAccounting OperationsNational AccountsAccount Recs116405.100 and 117630.300117630.300-AR NASCO Refund Due from PAR Plans117630-2007"
strDCFileName = "DC AR Due from PAR Plans_" & strMM1 & strYY1 & ".xls"

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblData_temp", strMDPath & strMDFileName, True
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblData_temp", strDCPath & strDCFileName, True

'Append tblData_temp to tblData
DoCmd.OpenQuery "qryAppend_tblData_temp_to_tblData", acViewNormal, acEdit

'Delete any blank rows that may have resulted from the import
DoCmd.OpenQuery "qryDeleteBlankRows", acViewNormal, acEdit

'Delete the temporary table
DoCmd.OpenQuery "qryDelete_tblData_temp"

Beep
MsgBox "The file has been successfully imported", vbInformation, "Files Imported"

ImportTableData_Exit:
Exit Function

ImportTableData_Err:
MsgBox Error$
Resume ImportTableData_Exit


End Function

Thank you for your help!

View Replies


ADVERTISEMENT

Tables :: How To Keep Data From Importing Duplicate Records Based On One Field

Sep 11, 2012

I'm able to import new data from excel just fine, but I can't import updated data from excel due to duplicates not being allowed for a particular field. Is there a way to keep from importing duplicate records based on one field, but still import data from other fields where the information is different from the excel file?

View 5 Replies View Related

Duplicate Check

May 2, 2005

I have a form with a couple fields. i want to check to see if i have a duplictae field. the field is batch_ID. so if somoen tries to enter a batch_id thats already in the table a message box will popup saying duplicate record and it will take me to that record ?


Could i have an example please.

View 1 Replies View Related

Check If Duplicate Afterupdate

Nov 30, 2006

Hi, I was wondering if anyone knows the VB to check if a value is a duplicate afterupdate?

Many thanks.

View 8 Replies View Related

How To Check If Value Entered Not Duplicate

Jul 28, 2005

Hi folks,
I have this situation: a table with patients information and IDs, and a table with evaluations for those patients (identified by ID).
I am trying to make it so that the user can't add the same evaluation twice. I want to do it by checking if there is already an evaluation for that patient with the same date.
I know how to show evaluations only for a patient, but I don't know how to check if the value entered by the user in the date field is a duplicate (case in which he shouldn't be allowed to enter it). I assume this is done somehow through the "validation rule" property? But how should the expression be?
Thanks a lot!

View 1 Replies View Related

Check For Duplicate Record By 2 Fields

Mar 12, 2007

dear all

i managed to get checking for duplicate record by ONE field ie member_no

code :

If DCount("*", "runner", "member_no = " & Me.member_no) > 1 Then
MsgBox " This member is already exist!" & vbCrLf
Me.c_memberid.SetFocus
Else
DoCmd.CancelEvent
End If


how i want to get checking duplicate record by TWO fields ie member_no and run_no ?


thanks in advance

View 1 Replies View Related

Check For Duplicate And Error Message

Nov 29, 2006

I have a form - see attached image.
The first text box is called Job_No. When a number is entered here how can I check that the number doesn't already exist when the user tabs to the next box. Then if it does exist display a custom message to the effect "This already exists" and not the Access default duplicate error message.

Thanks

Michael

View 1 Replies View Related

How Can I Check For Duplicate Entries After Leaving A Field?

Aug 30, 2005

I'm pretty new to Access, so please overlook any stupid questions :)
I have a data entry form that has one field that cannot be a duplicate. So, in the table I have it Indexed with No duplicates.
The problem is that the form doesn't notify the user that they have entered duplicate information until they close the form. So the 10 minutes they spent filling it out is wasted.
So, after consulting various posts, I did a BeforeUpdate event with the following code:
(the field that must be unique is called "LS Number", in the form "Q and D" based on the table "Q and D Database")


Private Sub LS_Number_BeforeUpdate(Cancel As Integer)
On Error GoTo LS_Number_BeforeUpdate_Err

If (DLookup("[LS Number]", "Q and D Database", "[LS Number]=Forms![Q and D]![LS Number]")) Then
MsgBox "The LS Number you entered already exists. Enter a unique LS Number", vbInformation, "Duplicate LS Number"

End If

LS_Number_BeforeUpdate_Exit:
Exit Sub

LS_Number_BeforeUpdate_Err:
MsgBox Error$
Resume LS_Number_BeforeUpdate_Exit

End Sub


When I test this by entering a duplicate LS Number, it gives me the error "Type Mismatch." Unfortunately, I have no idea what this means, nor do I know how to fix it.

Please help, my head hurts!

View 3 Replies View Related

If Statement To Check For Duplicate Entries With List Box

May 17, 2006

I have a problem where in a table called tblPatientLanguage
that contains three fields

PatientLanguageID
PatientID
LanguageID

a patient who is identified by PatientID can speak several languages
identified by the LanguageID.

The problem is with my current form and code


For Each varSelected In Me!lstLanguage.ItemsSelected
rs.AddNew
rs("PatientID") = [Forms]![frmPatientDemographics]![PatientID]
rs("LanguageID") = Me!lstLanguage.ItemData(varSelected)
rs.Update
Next varSelected



when the user click on the command button called cmdOK it will put the
data in the table "tblPatientLanguage" and corresponding fields. without
checking to see if there is already a duplicate languageID that corresponds to the
same patientID in the table

This has lead to duplicates in the table

example
PatientID = 9

if I look in the table I will see 7 entries that say

PatientID = 9 and LangaugeID = 7

I want to prevent this from happening with and if statement
I could make both the PatientID and LanguageID primary keys
this would prevent this from happening. But I would rather do it in
the VBA code? This way in will move thorugh the selections without poping an error and the person inputing the date. Any ideas sorry for being long winded. I attached the
database.

PS. If looking at the database you have to use form frmPatientDemographics and click on the language link

View 1 Replies View Related

Forms :: Check For Duplicate Serial Field

May 12, 2014

I have a simple database that I put my execptions in for the day for scanning. Is there a way that my serial field on my forum can check to see if someone already scanned that serial for that day using the "On Lost Focus" event.

View 7 Replies View Related

Compare / Check If Records Are Duplicate Using Save Button?

Jan 9, 2014

I need to create a record of Users. I created a form with fields containing Username,FirstName,LastName & Email Address wherein the UserName is equals the value of the fields FirstName & LastName. I have a save command button and when I click it, I want it to be able to check if the new entry has a similar UserName that exists in the database, if it exists then it will allow me to edit the UserName and then save the data otherwise will allow me to save it.

View 3 Replies View Related

Modules & VBA :: Check For Duplicates When Importing Multiple Records Into Datasheet View Form

Aug 15, 2014

I am using the following code to check for duplicate tickets when importing multiple records into a datasheet view form by using the paste append function.

Code:
Private Sub Ticket_Number_BeforeUpdate(Cancel As Integer)
DoCmd.SetWarnings False
If DLookup("Ticket_Number", "Record_Store", "Ticket_Number= '" & Me.Ticket_Number.Value & "'") > 0 Then
Cancel = True
MsgBox "There were import errors, please open View Import Errors above."
End If
End Sub

The form is used to insert multiple records into the database at a single time.

That codes works to check for duplicates. And if there are none there are no popup messages.

If there are duplicates though it gives a popup for every single Ticket_Number that is a duplicate.

I am wondering if there is a way for it to give only a single popup once it completes checking all the records to be imported for duplicates.

View 14 Replies View Related

Importing Data

Jun 6, 2006

I'm using a combo box control for searching employees. Which the empID is the unique key, the form is scrolling each record, do not want it to do that. When i click on an ID, it only changes ID's but not the information thats tied to that ID. How can I fix that? any valuable information is well appreciated. Thank you

View 3 Replies View Related

Importing Data

Jul 16, 2007

Someone recently answered my query with regards to uploading data to an access table.

The first coloumn is an auto incremented number.

Will there be any problems with importing data - when one field is auto increment?

Obviously I wont be uploading values for this column - can that be done?

View 14 Replies View Related

Help With Importing Data

Nov 14, 2007

Hi,

Another question on this database I'm working on.

I have my database pretty much ready to go, but they would like to import data from another access database into this one.

The problem I'm seeing is, I've been working so hard to normalize my database with the proper relationships etc.

The database I'm importing from, the data there are all stored in huge tables.

so for example in my database I have our main table, that holds all like data. Then my other tables for specfic data that then relate to the main table ID.

The other database has everything in 1 table.

I've created a query and got what I needed from the other database, but not sure how to import it into my main table, and have it so the 'specific' data goes into the right table, and so the Main_ID field points to the correct record in my main table.

I don't think I explained that very well.

Here is how my structure is in the new database

tbl_main
Main_ID
Code_ID
Effective_Date
End_Date
Createdby_ID

tbl_CCE
Main_ID
Codeinfo
etc
etc

now the old database, everything is in 1 table and I'm not sure how to break it down so after I import the table CCE/Main_ID is the same as the Main_ID in my main table.

I'm still not sure if I explained that very well....

View 3 Replies View Related

Importing Data

Aug 10, 2005

Hi
I'm trying to import data from an Excel spreadsheet into an empty table using the Import Wizard (File/Get External Data/Import). All's well until I'm presented with the window that tells me that I can "store" my data either in a new or existing table. However the radio button and Existing Table name field is grayed out. Any ideas gratefully received.
Baz

View 4 Replies View Related

Importing Data Help

May 15, 2007

I'm hoping someone can help me with what (I think) may be a minor oversight by me.

I'm trying to import data from an Excel Spreadsheet into a table that I have already created for my job.

Both the table and the excel spreadsheet have a common factor which is an identification number. I would like to import the data into the existing table so that the additional values fall under the appropriate identification number.

I hope that makes sense - if not let me know and I'll try to explain further.

So my question is - is this possible? I searched the help files and some internet resources and couldn't find anything this very specific kind of importation.

If anyone could help me I would greatly appreciate it as you would save me tons of time.

Thanks in advance!

View 1 Replies View Related

Importing Data From Mde

Feb 18, 2008

I have my sister using the db (in mde form).
When I make design changes to the mdb, I need to import (freshen) the data from her mde. (We're in different states) Obviously, when I do this, I get a duplicate table renamed.
If I do a query to freshen the data, will an update query just copy the changed/new data without the unchanged ?
Or is it best to just delete everything in the table and append the data from the mde table ?
Or is there a better way to do this that I'm not thinking about ?

View 14 Replies View Related

Data Importing

Feb 18, 2008

Hi everione

I have to make a db that imports data from a textfile. I read the posts about automating the proces but I have two other problems. The text file is generated by a proces control software and has a .gbc extension ( I tried to solve this by importing to excel and then to acces) and the textfiles has a limited number of entries araound 300 so after al the records in the textfile are writen the records are overwriten. Is it posibel to make acce to read only the new records and adi it to a table witouth deleting the old records? the record has a date and time included.
Im a acces user for only two weeks so i would be greatful for any help.

View 5 Replies View Related

Importing Data

Sep 11, 2006

HI I would like to design a form where user could be given an option to browse a database .mdb file and then select a table from that database that appears in a combo box and then a query should retreive all values from that selected table. I have tried with recordsset thing but error comes as Type Mismatch for

Set rs = Currentdb.OpenRecordset("strqry")

or when i tried using application.access thing. I still could not retreive list of tables. Thanks very much for suggessions that I am looking forward to.

Cheers

Abid

View 2 Replies View Related

Importing Data To Back End

Jul 25, 2005

Hi,

I have a DB that relies on text file imports as well as input and I recently split it. Unfortunately now my refresh script that updates the tables based on the text files imports them into the FE rather than the BE. I have messed around with workspaces but am struggling to get a solution. Ideally i'd like to be able to call DoCmd commands directly in another database but if anyone could give me the code to execute a function I can take it from there.

Thanks, Tom

View 3 Replies View Related

Importing Execel Data

Aug 9, 2005

I need to import a named range of cells form Excel 5 worksheet into an access table. I was hoping to get some sample code I could modify for my own project. I have already looked at the sample cosde that came with Access. It has been no help for me.

View 1 Replies View Related

Importing Data Between Databases

Aug 24, 2005

I have searched and not found exactly what I am looking for..so here is my question.

I have a large database that works fine, IF people would stop trying to play in it!! What I want to do is to restrict access to everyone except managers.

So, I copied the db for warehouse users to input their data (picker id, no. of minutes) that holds only one table and the form that opens automatically (they do not have access to anything but the form) for their input...Then, at the end of shift I want the managers to be able to click a button on Switchboard that copies all entries for day into the original db to run their reports. (These dbs are on a network)

So, how do I manage this? I want to use a macro and transfer database table, then run an append query to put the current records in original table...I can do that, but I dont know how to delete the records in users db. It needs to clear out all records in that db when it has been transferred... I want all control to be in managers hands. All the users can do is input their data.

I know you can help, so I will say thank you now.

View 8 Replies View Related

Importing Data From MySql

Apr 5, 2006

Hi,

is there any other way to import data from a MySql database then via MyODBC I'm experiencing quite some problems with it (v3.51.12)? , especially with character coding...

thx
Miha

View 6 Replies View Related

Importing Data Question

Apr 19, 2006

i have a data file that is formated similar to this

A1111222223333344445555
B2222211115555444433333

i need it to be automatically imorted to a data base in the same order but each row formated the same such as:

A1111222223333344445555
B1111222223333344445555

any idea how i could do this so every time i imort a file like this that it would format automatically?

View 1 Replies View Related

Importing Data From Excel?

Dec 5, 2006

I need to import 1600 people from excel.

as the program that we use to use was free and setup for what we needed. That has the ability to create a excel sheets with the 1600 people on.

Now all the fields are different, and i'm not sure how to do it.

When importing do you get the option to put the fields in the correct place, or do i need to edit them? and then import.

Another thing the address layout with fields will be completely wrong. I believe it used separate fields for each line of the address where in my DB i use 2 fields 1 address and 1 postcode.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved