Adding New Record With Table That Has Duplicate Records
Jan 26, 2005
My situation:
I have a table with duplicate ids. When I have a forum popup another form, the forms are linked by ids. This works great except for one problem. There are duplicate ids. So if I pop up a form then it will go to the first record that that id exist. This is not good if you are going to add another record. Therefore, I need to create a unique variable for a record. The autonumber would be a great variable but it seems I cannot use it to filter. Unless someone can show me how. Is there another way to create a unique variable automatically for a new record?
View Replies
ADVERTISEMENT
Jun 7, 2015
error message I am getting when I click on my Duplicate Record button (created through the wizard).
I have two combo boxes on the main form that populates data when the user makes a selection from the combo box. First combo box populates project data and the second combo box populates equipment data. The form is working well with the two combo boxes populating the data into the main table.
Now I would like to add a duplicate record button to copy a record and paste the data as a new record. So, I added a duplicate record button using the wizard and I am receiving the following AfterUpdate error.
Run-time error 3020: Update or CancelUpdate without AddNew or Edit.
This is the code I am using to copy and paste a duplicate record:
Private Sub InputForm_DupRec_Button_Click()
On Error GoTo Err_InputForm_DupRec_Click
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend
Exit_InputForm_DupRec_Click:
Exit Sub
Err_InputForm_DupRec_Click:
MsgBox Err.Description
Resume Exit_InputForm_DupRec_Click
It seems to be duplicating the data from the first combo box, but not the second one where it errors out.
View 1 Replies
View Related
May 8, 2014
Currently if an employee attends a mandatory training session the details are entered individually into in a single table which contains all the employees' attendances to training, this is achieved via a bound form.
Works Fine.
My question is, if the training is carried out by a number of employees can this attendance be recorded on one form and assigned to each of the employees who attended?
Using Access 2003
View 3 Replies
View Related
Nov 16, 2004
I have built a qry that initially shows the correct information. For example.
tblContent has 289 records with a Type = Class.
I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.
The SQL Statement is below
SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));
The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?
http://briefcase.yahoo.com/turnerbkgabrobins
Thank you in advance for your assistance.
View 1 Replies
View Related
Feb 12, 2015
I have a form with a duplicate record button to allow records to be copied. I used this code (I think from Allen Browne), but it only copies the last record in the recordset?
If Me.Dirty Then 'Save any changes
Me.Dirty = False
End If
If Me.NewRecord Then 'Check there is a record.
MsgBox "Select a record to duplicate"
[Code] .....
View 2 Replies
View Related
Jun 9, 2014
Is there a way to merge duplicate/similar Access 2010 records into one record?
I have an Access table with 1,000 duplicate records, although they are similar and not exact duplicates. As you can see below, some records contain information that other records do not. Yet, the primary key is the same for all duplicate records. I want to find a way to merge data from filled cells of duplicate records into empty cells for each duplicate record. I do not want to concatenate the data (i.e. combine last and first name, etc.). I only want to fill empty cells if there is a match for it in a duplicate record. I will delete the newly exact duplicate records later. Short of correcting the records by hand.
Example
Code:
LastName FirstName SSN Address Phone Email
Doe John 123-45-7891 123 Anywhere St. NULL john(at)gmail.com
Doe John 123-45-7891 NULL (123)456-7890 NULL
Desired Result
Code:
LastName FirstName SSN Address Phone Email
Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
View 2 Replies
View Related
Apr 12, 2012
I have two tables that have duplicate order numbers. I would like to compare the first table with the second and then delete the records from the second that match.Can I set this up in a macro and run it every day?
View 2 Replies
View Related
Mar 4, 2013
I have this database that do contains duplicate record (not duplicate field).What I'm trying to do here is to extract record that is unique to a new table And those record that were duplicate into a new table.the *Distinct that we used in sql will not work because it is not what I wanted. using distinc will give me unique record of the whole database.say i have this database that contain an ID:
2525
2658
2658
2658
2525
3678
so what i want is to extract unique record to a unique table and those that contain duplicate in another table.so the 3678 will be put in a new table and the 2525 and 2658 in a new table,,That way I will see which file will have duplicate
View 1 Replies
View Related
May 6, 2005
I have a problem that should have an easy solution. But I can't find it.
I need to have a user add a new record. (Created a form with the fields on it, No problem , so far.)
The problem is when the user is adding a new record if they hit the PAGE DOWN Key or the mouse scroll, they then go to a new NEW record. (And if they are not paying attention they now have two new records)
How do I prevent this.
Thanks
Mike Lester
View 2 Replies
View Related
Feb 18, 2014
I have a table with at least 13.000.000 records. There are many duplicates records... For example
ID Name Family mobile car number chassis Register_Year
1 Roy Jalbout 9999 123456/G ASF4546 2005
2 Roy Jalbout 9999 854658/G GRK554JFJD 2009
3 Tony Elishah 1234 854658/G GRK554JFJD 2012
4 Sam Markos 5478 854658/G GRK554JFJD 2014
5 Roy Jalbout 9999 123456/G ASF4546 2005
As you can see ID Number 2,3,4 have the same car but every one bought from another so it's not duplicates
The duplicates here is the ID's number 1,5. So how to remove the duplicates. I remember you i have at least 13.000.000 records. I try to make a query to find duplicates then i make a copy of the original table and than i should make a primary key then append the data from the original table to the copy table, but here i have more than one criteria
When I made a query to find duplicates the result was 680.000 records and every one have a minimum 2 duplicates an maximum 4 duplicates so it's about 2.500.000 duplicates records at least....
View 5 Replies
View Related
Jun 12, 2014
I have a table which has duplicate records so I want to write down the query that will remove the duplicate records from the table.
View 1 Replies
View Related
Apr 22, 2014
I'm wondering if it's possible to create a Query / Macro / VBA or whatever works, to detect the duplicate records in a table and delete them with the push of a command button. Or, even to do this automatically when the database opens using an AutoExec macro or something ?. I'd like for one copy of each record to be left intact in the table.
View 8 Replies
View Related
Aug 29, 2013
I want query to find duplicate records, i have two field in one table
Cusip and category
cusip and category are many or duplicates
but in one cusip category should be the same if not then provide the cusip which has different category used
like this
CusipCategory
123R
456P
123R
456P
678Q
678Q
123A
result should be
CusipCategory
123R
123R
123A
View 6 Replies
View Related
Apr 4, 2012
I have one table like:
ID Type No
-----------------
1 FL 2
2 DL 3
I need the following table to be created using first table , each record should be duplicated according to value in No field:
ID Type No
-----------------
1 FL 2
1 FL 2
2 DL 3
2 DL 3
2 DL 3
View 2 Replies
View Related
Sep 2, 2013
I am trying to create a duplicate of a certain record in a table so that the user can then edit the new record - or old record even as it doesn't matter - as long as one of them is original. The idea is that any revisions / changes are stored the user may of made.
View 2 Replies
View Related
Jun 14, 2005
I am creating a small Access application that will allow me to update my Call handling system's site table.
It uses an mdb file, and I wish to add new sites (from new contracts) to the SCSite table.
I recieve regular updates of new sites from existing customers, so want to create a simple macro that will
1: import (from an excel file) the new sites and add these to the existing SCSite table
2: Check for duplicate records using the Site_Num primary key
3: Merge non duplicate records into existing SCSite table.
Is this possible, and if so - how? VBA? Macro builder? Query?
A Swift response would be great, I have had a look through previous posts - but to no avail!
View 3 Replies
View Related
Jan 29, 2015
I have a database with an import process which normalises incoming data and appends to various tables. No issues with that. I also have a function within that process which counts the number of new entries for a summary popup when the process has completed.This works by querying the staging table, prior to the append, into a recordset and using the .RecordCount to increment the count (multiple files can be imported at once so this effectively provides a running count, per file, to give a total for the whole import)
I thought it was working fine but this morning I noticed that the count which appeared on the popup was 1 greater than the number of actual new records. I checked the source files and noticed that, for whatever reason, there was a duplicate entry in there. So I presume that's why the count was out by 1.
There's no integrity issue in the main tables as the composite primary keys ensure that duplication shouldn't be a problem. Indeed, the record in question, duplicated in the source, appears only once in the main table post-import. So not too worried about that.
However, I need the count in the popup to be accurate (it tells the users how many new entries require further investugation). And what's puzzling me is that I use DISTINCT in the query, which I would have thought should eliminate any potential dupes in the recordset and thus provide the correct count. It seems it doesn't?
Code:
Public lngNewBalancesTBI As Long ' Defined in a separate module...
-------
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
[code]....
Why the dupe, which is still present in the staging table, also makes it over to the recordset, even though I'm using DISTINCT?
View 14 Replies
View Related
Jan 17, 2014
I have a table with duplicate records (which is ok) and I want to return distinct data for each client.
It works fine when there is only two fields returned however, when I add a third field to the query it no longer returns only the Distinct records - I am getting Duplicates returned.
I.E
SELECT DISTINCT tblClient.ClientNo, tblClient.Name
FROM tblClient
Works fine with only the Distinct records for each client returned
However
SELECT DISTINCT tblClient.ClientNo, tblClient.Name, tblClient.Address, tblClient.OrderValue
FROM tblClient
Now returns Duplicates!
Is there a limit to the number of fields to be returned using DISTINCT or what else could be the problem? Should I be doing this some other way?
View 5 Replies
View Related
Aug 19, 2015
I'm developing a database for the hospital I work in. One purpose of this is to keep track of patients with temporary invasive devices (there are many types such as urinary catheters, ventilators, etc.), specifically how many days each patient has a device for. Each device is associated with one patient only, but one patient may have many devices. Here's how the associated tables are set up:
1. tblPatients - PatientID (PK), LastName, FirstName, DOB,...
2. tblLocations - LocationID (PK), LocationLabel,....
3. tblDevices - DeviceID (PK), DeviceType, Device, DeviceDesc
4. tblDeviceUse - DeviceUseID (PK), DeviceID (FK), PatientID (FK), LocationID (FK) (where in the hospital was device inserted, e.g., operating room, bedside, etc.), DeviceStartDate, DeviceEndDate
5. tblDeviceDailyLog - DeviceLogID (PK), DeviceUseID (FK), DeviceDate, PatientLocID (FK) (area in the hospital that patient is in)
All primary keys except for PatientID & LocationID are Autonumbers; and the tables are linked appropriately.
Whenever an entry is made into tblDeviceUse, I want there to be new records to be automatically generated in tblDeviceDailyLog for each date between the Start and End Dates. For example, patient A123 had a urinary catheter from 1/1/2000 to 1/10/2000 that was inserted while the patient was in the ICU, but the patient was moved to the Medical Ward on 1/7/2000. So tblDeviceDailyLog should have 10 new records associated with this device, one for each calendar day, with the appropriate location for each day.
View 3 Replies
View Related
Feb 9, 2005
Hope someone can help!
I was wondering if is possible to add a series of records that are in serquence to a table by just entering the first and last numbers. For example a string of numbers starting at TP11000 and ending at TP11100, the prefix TP doesn't change. Currently I have to enter every one manually, so any help would be much appreciated!
View 4 Replies
View Related
Apr 11, 2006
I am new to Access and have the following problem. I have one table which displays Skills (memo field) a second table which displays a skill rating ( 5 choices) and a third table which list the jobs in the company. I need to compile a fourth table which lists the skills required for each job and the corresponding ratings. I would like to have a Form in which I select the job and all 560 skills are displayed in datasheet format and I can select the ones required and allocate a skill rating. The results should then be saved to the new Table.
Any ideas as to how to acheive this task.
View 2 Replies
View Related
Aug 13, 2007
Here is basically what I have and what I need to do:
I have a form with several text boxes which are going to be filled in. Clicking on a button at the bottom of the form will save this information into a table (the table associated with the form). However, it also needs to fill in a junction table with two known values derived from these text boxes.
I have FILE_NUMBER_CD and INSPECTOR_NUMBER_CD that are the only attributes in the table called "XREF_FILE_INSPECTOR" which need to be added as new records.
So basically, what I need to know is how do I add known values derived from a textbox directly into a table.
Thanks for any help!
View 1 Replies
View Related
Jun 5, 2006
Greetings all.
I'm posting this question here as it pertains to my form but it could go in tables section as well.
I developed a prototype app in Access and have just finished upscaling it
to Sybase 12.5. When I open up the associated form to add records, I am unable to do so. The built in button that allows you to do so is greyed out.
When I go into the table directly, again, no ability to add recs.
I've never come across this so if anyone has any experience with this, any help would be most welcome.
TIA,
Tim
View 1 Replies
View Related
Sep 28, 2006
Hello I need to add daily records to a related table using a form, from a button or subform displayed on a form updating the master table.
This would enter the related key to the new form ready for insertion etc.
Can this be done?
For example
Master table:
Key 1
Related table:
1 Key 1
2 Key 1
3 Key 1
jamo
View 2 Replies
View Related
Oct 12, 2006
I have a form "release_details" having fields date, version, cksum ,comments,labels, and is link to a table,
it has a button "mai"l ,on clicking this button a new form is open which has a button "send mail" on clicking this a mail is sent and pops a message "mail sent".I need when "send mail" is click it should also save the fields of form
"release_details" to the table.
cmdSend_Click()
Forms!Enter_Release_details.Dirty = False
View 3 Replies
View Related
May 20, 2013
I am using a form to add a record to a table and need to be able to specify one of the fields in the table that will be added to. This field is predetermined by another form selection. If there is a way to force a value on a title box with a control source this would also do the trick.
View 4 Replies
View Related
