Duplicate Record Cannot Save

I am using windows 7 64 bit 8 gigs of memory Access 2007

I am developing a tracking system
I have a main form and can have one or more employees woring on the problem

I created a continuous subform using a table named EmployeeAssinment.
One of the fields is a combobox.

When I try to save a record, I receive a message stating that the record could not be saved because it would create a duplicate.

However, when I close the form "without savibg" the record, the record is actually saved in the EmployeeAssignment table.

Primary Key: ProblemID. EmpID


The fields on the form are: EmpID, ProblemID, FirstName, LastName FullName (ComboBox) and HoursWorked.

The combobox is filled from the Employee table.

Is there any way I can find out what table is having the duplication problem?

I tried to set up a recordset to save the assignment information and then set the RecordSource of the form to give me all EmployeeAssignment records for the current problem.

When I received the error, I commented out the recordsource but still get the same error.

I get the ProblemID from the main parent form.
After saving the recordset, I need to set the EmpID on the form or it doesn't get set and I wind up with a "Key Field Cannot Be Blank" error.

This is my sub:

Private Sub cmbEmployee_AfterUpdate()
On Error GoTo Err_cmbEmployee_AfterUpdate
intProbID = Me.Parent.ProblemID
Set dbAssignment = CurrentDb
Set rsAssignment = dbAssignment.OpenRecordset("Select * From tblEmployeeAssignment", dbOpenDynaset)
rsAssignment.FindFirst "EmpID='" & cmbEmployee.Column(0) & "' and ProblemID=" & intProbID
If rsAssignment.NoMatch Then
rsAssignment.AddNew
rsAssignment!EmpID = Me!cmbEmployee.Column(0)
rsAssignment!ProblemID = intProbID
Else
rsAssignment.Edit
End If
rsAssignment!EmpLastName = Me!cmbEmployee.Column(1)
rsAssignment!EmpFirstName = Me!cmbEmployee.Column(2)
rsAssignment!EmpFullName = Me!cmbEmployee.Column(3)
rsAssignment!Hours = Me.Hours
txtEmpID = rsAssignment!EmpID
' Me.intProblemID = intProbID
' Me.EmpID = Me!cmbEmployee.Column(0)
rsAssignment.Update
rsAssignment.Close
DoEvents
Me.RecordSource = "Select * From tblEmployeeAssignment Where ProblemID=" & intProbID
Exit Sub
Err_cmbEmployee_AfterUpdate:
If Err.Number = 3058 Then
Exit Sub
End If
MsgBox Err.Number & " " & Err.Description
Exit Sub
Resume Next
End Sub


Post your answer or comment

comments powered by Disqus
Hello,

I have a form that opens an existing record. I want to be able to save the record as a new record only if the last two digits of the ID (which is called Task Code) are changed. Right now, when I change the ID, it auto saves it in the form, then says I cannot create a duplicate ID because it would not have a unique ID. I believe i have the correct code for saving the duplicate record, however how do I get the form to NOT auto save? I am using Access 2010.

Hi all,

I have inherited a database with a table with plenty of duplicate records. In some cases the a record may appear more than once.

I have kindly been given the following code but somehow I cannot get it to work. I am getting a runcode error.

In my example database I have three fields a, b and c
and I have duplicate records:-


Please note I am using A2003....I have attached word doc with code for anyone's perusal.

Just amended my post to include my sample database. I am also providing, again, some background to my problem:-

I've inherited this database with a table which has accidentally been populated with records that already exist.

I have set up a sample database that more or less mimics the real one. I have omitted PKs and other database rules because that is what I have. I "simply" require a coherent piece of VBA code that will save me the trouble of sorting the records and then deleting the duplicate records that I see.

Obviously I would be working on a copy to start of with so that I would first understand the code before I embark on the real thing.

I completely understand the concerns that maybe the database is structured improperly or whether additional fields have to be included, but my main concern is deleting the duplicates.

The code I am supplying has been kindly offered to me but is not working. I believe its almost there, and I am sure it just requires some tweaking.

Basically what I need is code which will sort the table and then compare the entire contents of one record with the one below it. If it is the same, ie. all the fields are the same, then one of the duplicates should be deleted.

Thanks and any advice on the code grately appreciated.

Does this sound like a corruption problem?

I added a new field to a table, and saved the table. When the table is reopened, the new field is not there! I thought perhaps I forgot to save the field, so I tried adding the field again. When saving, the error said, "cannot save; duplicate field name".

If I go to toolsoptions and click the "show system objects" the field will show up??

Do you think the best course of action is to import all the objects into a new db and try again?

Thanks for your help.
Mary

The access table has one primary key StudentID which is indexed with no duplicates. (StudentID consists of schoool ID + an auto increment #)

When a second student is entered with the same name and birthdate into the table but with a different studentid the record cannot be saved as there is a duplicate value. If the birthdate is removed the record can be saved. If another character is entered after the first name with the birthdate the record can be saved.

I have tried adding time to the record, that did not solve it. I tried changing name and birthdate to index yes duplicates okay that did not solve it.

The only relationship is on studentid, I cannot understand why the birthdate is the problem when there is no relationship or primary key on that value.

Has anyone experienced this issue and can explain why access views this as a duplicate record when in fact it is not!

Thanks

Hi (",o)

Doc No.: FIELD 1 (Step 1)
Date: FIELD 2 (Step 2)
Team No.: FIELD 3 (Step 3)
Badge No.: FIELD 4 (Step 4)

SAVE Command button (Step 5)
DUPLICATE RECORD Command button (Step 6)

Currently User1 is required to follow step 1 to 5 to input field 1 to 4, then User2 will continue with step 6 and go to erase step 4 field and input his/her value then click save in step 5.

Can step 5 & 6 be combine as 1 command button and at the same time on the duplicate record {field 1-3 remain but field 4} is auto deleted for another user to input value?

Can anyone help? Your help is greatly appreciated..

Ok so, since you guys know everything it seems, im looking for a few things here

First - i have a calculated field in a query which is the combination of entity name and date. it works great as an identifier for us since we can see when this entity's details are from. (and we want to keep the history). What i need is a button that will allow me to go to that record, edit the information and save it as a NEW record with a NEW date... clearly editing the information and clicking the ordinary save button will overwrite that record.

On the same token, is there a way to prevent or make a popup in which lets say we enter the same entity with the same date 2x it does not permit that duplicate c ombination in the record ID (now keep in mind this isnt the primary key - thogugh techinclally it could be but i was getting in trouble when i was tryign to "duplicate record and save the changes", thus why that button would be extremely helpful to avoid the duplicate record issue.

I have created a form used to enter sales details, the data entered sometimes need to be duplicated, So I added a Button to duplicate a record (using the Wizard).

Unfortnately when a duplicate record is created is does not allow you to select from the drop down lists telling you to save the Records. When saving an error message appears saying

Index or primary cannot contain a null value.

These drop down list are indexes in the main table and Primary keys in there own table.

The form gets it data from a query that links to additional tables.

I think the problems is I need to create a duplicate record in the main table and the query use the additional tables to add information as a lookup table.

I have the below code that, if a checkbox representing an additional available time on a form is checked then the record is duplicated and that additional available time is added into the time field of the new record. On the form there are numerous checkboxes that the user can select. These checkboxes represent any QC issues the product may have had when the record was created. Those checkboxes on the form are on several tabs of a multi-tab form. On the main part of the form I have a text box that shows what checkboxes were checked on the various tabs. The text box will show the user all the checkboxes they selected on the various tabs all in one place making it easier for the user to verify they have selected all the checkboxes they intended to. Because the user has to select different checkboxes on multiple tabs of the form I wanted a textbox on the main form to show the user everything they selected on the other tabs.

The code duplicates the record and changes the Time field to represent the additional time that the user selected but the checkboxes on the new record aren't selected like they are on the record being duplicated. There are a lot of checkboxes on the various tabs so instead of writing code for every checkbox that could be checked I think it would be easier to have code look at the values placed into the textbox on the main for and to have that code then check those checkboxes on the duplicated record.

Hopefully this makes sense. Below is the entire code that make this work so far. What code would I use to have the textbox evaluated when the record is created and to have the checkboxes on the form that represent the values in the textbox to also be checked when the new record is created?


	Code:
	Option Compare Database
Option Explicit
Private Function SubAddRecords()
'this function will call the Subroutine AddRecords_Click() code below so that I can use this _
function to call from the VCR control macros on the main form.
Call AddRecords_Click
End Function
Sub AddRecords_Click()
Dim varTime As String
If cb630AM.Value = True Then
    varTime = "6:30am"
    Call addentry(varTime)
    End If
If cb830AM.Value = True Then
    varTime = "8:30am"
    Call addentry(varTime)
    End If
If cb1030AM.Value = True Then
    varTime = "10:30am"
    Call addentry(varTime)
    End If
If cb1230PM.Value = True Then
    varTime = "12:30pm"
    Call addentry(varTime)
    End If
If cb230PM.Value = True Then
    varTime = "2:30pm"
    Call addentry(varTime)
    End If
If cbEndDays.Value = True Then
    varTime = "End-Days"
    Call addentry(varTime)
    End If
If cb630PM.Value = True Then
    varTime = "6:30pm"
    Call addentry(varTime)
    End If
If cb830PM.Value = True Then
    varTime = "8:30pm"
    Call addentry(varTime)
    End If
If cb1030PM.Value = True Then
    varTime = "10:30pm"
    Call addentry(varTime)
    End If
If cb1230AM.Value = True Then
    varTime = "12:30am"
    Call addentry(varTime)
    End If
If cb230AM.Value = True Then
    varTime = "2:30am"
    Call addentry(varTime)
    End If
If cbEndNights.Value = True Then
    varTime = "End-Nights"
    Call addentry(varTime)
    End If
End Sub
Private Function addentry(varTime As String)
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblProductionNumbers", dbOpenDynaset)
rs.AddNew
rs("ManHoursID") = Me.ManHoursID.Value
rs("ProductionDate") = Me.ProductionDate.Value
rs("TimeID") = varTime
rs("Line#ID") = LineID.Value
rs("ProductID") = Me.ProductID.Value
rs("OperatorID") = Me.OperatorID.Value
rs("TailOffID") = Me.TailOffID.Value
rs("LF Run") = Me.[LF Run].Value
rs("LF Produced") = Me.[LF Produced].Value
rs("Comments") = Me.Comments.Value
rs.Update
rs.Close
db.Close
End Function
Private Function ClearCheckBoxes()
cbEndNights.Value = False
cb630AM.Value = False
cb830AM.Value = False
cb1030AM.Value = False
cb1230PM.Value = False
cb230PM.Value = False
cbEndDays.Value = False
cb630PM.Value = False
cb830PM.Value = False
cb1030PM.Value = False
cb1230AM.Value = False
cb230AM.Value = False
End Function
Private Sub cb630AM_Click()
If cboTime = "6:30am" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb630AM.Value = False
Else
End If
End Sub
Private Sub cb830AM_Click()
If cboTime = "8:30am" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb830AM.Value = False
Else
End If
End Sub
Private Sub cb1030AM_Click()
If cboTime = "10:30am" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb1030AM.Value = False
Else
End If
End Sub
Private Sub cb1230PM_Click()
If cboTime = "12:30pm" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb1230PM.Value = False
Else
End If
End Sub
Private Sub cb230PM_Click()
If cboTime = "2:30pm" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb230PM.Value = False
Else
End If
End Sub
Private Sub cbEndDays_Click()
If cboTime = "End-Days" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cbEndDays.Value = False
Else
End If
End Sub
Private Sub cb630PM_Click()
If cboTime = "6:30pm" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb630PM.Value = False
Else
End If
End Sub
Private Sub cb830PM_Click()
If cboTime = "8:30pm" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb830PM.Value = False
Else
End If
End Sub
Private Sub cb1030PM_Click()
If cboTime = "10:30pm" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb1030PM.Value = False
Else
End If
End Sub
Private Sub cb1230AM_Click()
If cboTime = "12:30am" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb1230AM.Value = False
Else
End If
End Sub
Private Sub cb230AM_Click()
If cboTime = "2:30am" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb230AM.Value = False
Else
End If
End Sub
Private Sub cbEndNights_Click()
If cboTime = "End-Nights" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cbEndNights.Value = False
Else
End If
End Sub

This code is the code that looks at a cherckbox and if it is selected it will place the name of the control in the textbox.


	Code:
	Private Sub ImajePrinterJetNeedsAdjustment_AfterUpdate()

      '--------------------------------------------
      ' Procedure : ImajePrinterJetNeedsAdjustment_AfterUpdate
      ' Author    : Chris D. Mallea
      ' Date      : 10/28/2010
      '
      '
      '--------------------------------------------
10    On Error GoTo ImajePrinterJetNeedsAdjustment_AfterUpdate_Error

20        If Me.ImajePrinterJetNeedsAdjustment = -1 Then
30            Me.UserSelectedTabItems = Me.UserSelectedTabItems & _
                  "ImajePrinterJetNeedsAdjustment" & vbNewLine
40        Else
50            Me.UserSelectedTabItems = Replace(Me.UserSelectedTabItems, _
                  "ImajePrinterJetNeedsAdjustment" & vbNewLine, "")
60        End If


ImajePrinterJetNeedsAdjustment_AfterUpdate_Exit:
70        Exit Sub
ImajePrinterJetNeedsAdjustment_AfterUpdate_Error:
80    msgbox "Error Line: " & Erl & vbCrLf & "Error: (" & Err.Number & ") " & _
          Err.Description & vbCrLf & "ImajePrinterJetNeedsAdjustment_AfterUpdate" & vbCrLf & "Form_frmProductionNumbers",
vbCritical

90        Resume ImajePrinterJetNeedsAdjustment_AfterUpdate_Exit

End Sub



Hi all,

I want to create a function where users can copy all the current non empty fields in the form and then paste it in a new form. I've tried using the built in Access2010 Duplicate record feature but I am getting paste errors.

Inspecting the tables create it doesn't let me save the values straight into the table. The table is actually a linked table (split FE and BE) so I guess that might be causing an issue.

Is there a way just to populate the fields on the new form with the values from the copied form instead of populating it straight in the table? I want users only to populate table with values entered in form when they press "Save" button

So basically I only want to update the values of the controls but not update them to the table just yet. I know this can be a little bit tricky as there are certain fields I want to update first such as Enquiry No.

So, I want to be able to copy all values entered in the controls of the current form, create a new form and populate with certain default values and then paste the values from the controls of the old form...is that possible?

Hi,
I'm not sure where to post this, maybe VBA or Forms would be better, but I think it's fundamentally an index problem, so I'm posting here. I've searched both the net and here, but most of the questions are about preventing duplicate records.

I want to prevent duplicate records but save changes to existing records.

I have a form based on a table that has a 3-field-unique-index (but not the primary index, which is autonumber). I've written a function to check for existing records with the given combination of the three fields, as long as all have data (it bails if any are null). I'm calling the function from BeforeUpdate for each of the textbox controls. I was also calling it from the form's BeforeUpdate, but commented it out as it seemed redundant. I have other code there verifying that the user wants to keep the changes.

I'm trying to save the users from getting all the way to the end before discovering there's a problem. To test it, I added then deleted one character from one of the three relevant fields in an existing record, and bing, up pops my alert that I'm violating the index. Then I can't leave the field, I'm stuck there because I can't save the record.

I want it set up so that other fields in the record can be changed/updated AND so that a new record for the same person can't be entered (a dupe).

Oh, and I'm running into all kinds of problems with acCmdSaveRecord not being available. I feel like an idiot that I haven't been able to make sense of the posts about that. I can just comment it out and let Access save the record when it closes the form. But my bosses really want the users prompted about saving changes.

Any thoughts or suggestions or insights greatly appreciated. I really tried to search this out, so any tips for better searching are also appreciated.

Here's the code for the function:


	Code:
	Public Function CheckForClientDupes()
    Dim response As Variant
    Dim strFamID, strLName, strFName As String
    Dim strFilter As String
    
    If IsNull(Me!FamilyIDNo) Or IsNull(Me!strLastName) Or IsNull(Me!strFirstName) Then
        Exit Function
    End If

    strFilter = "[strFamilyIDNo] = """ & Me!FamilyIDNo & """ And " & "[strLastName] = """ & Me!strLastName & """ And " &
"[strFirstName] = """ & Me!strFirstName & """"
    
    If DCount("*", "tblClients", strFilter) > 0 Then
    
       response = MsgBox("There is already a client with this combination of FamilyID, Last and First names in this database.
Would you like to Continue Anyway (Yes) or cancel data entry and Erase Your Changes (No)?", vbYesNo, "Duplicate Client
Alert")
        If response = vbYes Then
'            DoCmd.RunCommand acCmdSaveRecord
        Else
            response = MsgBox("You have chosen to cancel your changes.  Your changes will be erased.", vbOKCancel +
vbQuestion, "Data Entry Cancelled")
            If response = vbOK Then
                Me.Undo
            Else
                Exit Function
            End If
        End If
    End If

'    Me.FamilyIDNo.SetFocus

End Function



I have a table on which I cannot set a primary key because there are duplicate records in the table. How can I use a query to show the records that are duplicated?

Robert

Hi,

Yes, you did read it correctly, I want to CREATE duplicate records!

I have a table with exam results in it with the following fields:


	Code:
	StudentNumber ExamCode Subject Grade Weighting
123456        AB123    Maths     A         1
123456        AB456    Science   B         2
567890        AB123    Maths     C         1
567890        AB999    Science   C         1

Some exams carry a 'double' or 'quadruple' weighting i.e if you get 'B' for Science, because it is a double award subject you are awarded 2 'B' grades, so I want to duplicate the appropriate records. I need it in this format to be able to run a crosstab to get the results with one row per student with one column per exam, and 2 or 4 columns for the weighted exams.
i.e


	Code:
	StudentNumber  Maths Science Science
123456         A        B      B
567890         C        C

I tried using a make table query with all data selected, then using append queries to add duplicate records for the weighted exams, running the crosstab on the new table, running the report and then deleting the table. This, however is very slow, and if the workstation crashes, (or gets switched off), before the temporary table is deleted, I am left with a large redundant table.

I thought a union query might help, but all I can get is the same records as the original table.

How can I acheive this, or, is there a better way of using the weighting field.

PS. I cannot change the way the data is in the original table, that is the way it is provided.

Thanks in advance

Pete

Hi, I have a table contains 5 fields are shown as below, how I can write a query to find out the duplicate of the records? I have created a couple of queries by using Find Duplicates Query Wizard to do it. But the results are not accurate enough.

Field 1: ID
Field 2: Trading Name
Field 3: Location No
Field 4: Location Street
Field 5: Location Suburb

ID Trading Name Location No Location Street Location Suburb
41350 Arabica Café Grey St
263 Arabica Cafe` 1 GREY STREET
41294 Arashi Japanese Resturant 41 Courtenay PL
150 Arashi Kushi Yaki Bar 41 COURTENARY PLACE



From the data sample, I could like to find out the duplicated records. One of the queries is shown as below. But they cannot pick up these records. Any better way to find the duplicated records? Please help.

SELECT DISTINCTROW [C&B-will].[trading name], [C&B-will].[location street], [C&B-will].Field1, [C&B-will].[location no]
FROM [C&B-will]
WHERE ((([C&B-will].[trading name]) In (SELECT [trading name] FROM [C&B-will] As Tmp GROUP BY [trading name],[location street] HAVING Count(*)>1 And [location street] = [C&B-will].[location street])))
ORDER BY [C&B-will].[trading name], [C&B-will].[location street];

I cannot figure out how to stop my query from producting duplicate records. it only happens on the field "TestName"

Ive tried all kinds of things including using DISTINCT and DISTINCT ROW

Here is some sql that duplicates:
SELECT tblAccountInfo.Agency, tblAccountInfo.ReferringPerson, tblClientAccounts.ClientAccountID, tblClientAccounts.AccountID, tblClientAccounts.CID, tblClientAccounts.Cutoff, tblDateTime.DonorResult, tblDateTime.DateofCollection, tblDateTime.DateofTest, tblDateTime.TimeofAppearance, tblDateTime.Temperature, tblDateTime.Creatinine, tblDateTime.SpecificGravity, tblDateTime.Notes, tblDonorInfo.LastName, tblDonorInfo.FirstName, tblDonorInfo.DateofBirth, tblDonorInfo.ID, tblReferringPerson.LastName1, tblReferringPerson.FirstName1, tblResults.TestID, tblResults.Results, tblResults.ResultsValue, tblTestAvail.TestName, [LastName] & " , " & [Firstname] & " - " & [DateofBirth] & " -- " & [ID] AS Name, [LastName1] & " , " & [firstname1] AS Ref, tblAccountInfo.Address, [City] & " , " & [State] & " - " & [Zipcode] AS [Add], tblDateTime.DateTimeID
FROM tblDonorInfo INNER JOIN ((((tblAccountInfo INNER JOIN tblClientAccounts ON tblAccountInfo.AccountID = tblClientAccounts.AccountID) INNER JOIN tblReferringPerson ON tblAccountInfo.AccountID = tblReferringPerson.AccountID) INNER JOIN tblDateTime ON tblClientAccounts.ClientAccountID = tblDateTime.ClientAccountID) INNER JOIN (tblTestAvail INNER JOIN tblResults ON tblTestAvail.TestID = tblResults.TestID) ON tblDateTime.DateTimeID = tblResults.DateTimeID) ON tblDonorInfo.CID = tblClientAccounts.CID
GROUP BY tblAccountInfo.Agency, tblAccountInfo.ReferringPerson, tblClientAccounts.ClientAccountID, tblClientAccounts.AccountID, tblClientAccounts.CID, tblClientAccounts.Cutoff, tblDateTime.DonorResult, tblDateTime.DateofCollection, tblDateTime.DateofTest, tblDateTime.TimeofAppearance, tblDateTime.Temperature, tblDateTime.Creatinine, tblDateTime.SpecificGravity, tblDateTime.Notes, tblDonorInfo.LastName, tblDonorInfo.FirstName, tblDonorInfo.DateofBirth, tblDonorInfo.ID, tblReferringPerson.LastName1, tblReferringPerson.FirstName1, tblResults.TestID, tblResults.Results, tblResults.ResultsValue, tblTestAvail.TestName, [LastName] & " , " & [Firstname] & " - " & [DateofBirth] & " -- " & [ID], [LastName1] & " , " & [firstname1], tblAccountInfo.Address, [City] & " , " & [State] & " - " & [Zipcode], tblDateTime.DateTimeID
HAVING (((tblDateTime.DateTimeID)=[Forms]![frmEnterResults]![frmEnterResultsSub].[Form]![DateTimeID]));


here is another:SELECT DISTINCTROW tblAccountInfo.Agency, tblClientAccounts.ReferringPerson, [City] & " , " & [State] & " - " & [Zipcode] AS [Add], [LastName] & " , " & [Firstname] & " - " & [DateofBirth] & " -- " & [ID] AS Name, [LastName1] & " , " & [firstname1] AS Ref, tblClientAccounts.Cutoff, tblAccountInfo.Address, tblDateTime.DonorResult, tblDateTime.DateofCollection, tblDateTime.DateofTest, tblDateTime.TimeofAppearance, tblDateTime.Temperature, tblDateTime.Creatinine, tblDateTime.SpecificGravity, tblDateTime.Notes, tblResults.Results, tblResults.ResultsValue, tblTestAvail.TestName, tblDonorInfo.CID, tblAccountInfo.AccountID, tblReferringPerson.RefPerID, tblDateTime.DateTimeID, tblResults.ResultsID, tblTestAvail.TestID, tblClientAccounts.ClientAccountID, *
FROM tblDonorInfo INNER JOIN ((((tblAccountInfo INNER JOIN tblClientAccounts ON tblAccountInfo.AccountID = tblClientAccounts.AccountID) INNER JOIN tblReferringPerson ON tblAccountInfo.AccountID = tblReferringPerson.AccountID) INNER JOIN tblDateTime ON tblClientAccounts.ClientAccountID = tblDateTime.ClientAccountID) INNER JOIN (tblTestAvail INNER JOIN tblResults ON tblTestAvail.TestID = tblResults.TestID) ON tblDateTime.DateTimeID = tblResults.DateTimeID) ON tblDonorInfo.CID = tblClientAccounts.CID;

Hello everyone. I would like your help on. I have a database just like Northwind.In the main form orders enter data and a date in the field order date. In the subform I select a value from the combobox product, after the quantity and save the record.
But what if in a new record place the same data?Duplicate record.
Is there any code in vba to prevent someone to import the same data twice?
Thank you in andance.

I have a table in my database set up so the user cannot enter duplicate records.

When I test the associated form and try to enter duplicate records, I get a message after clicking the next or new record button that I cannot go to the record. This, I assume, is Access' error message for this problem.

Could someone be so kind as to show me how to create an error message that will deliver the message, have an OK button, and refer the user back to the offending duplicate field?

Thanks...

Hi..
I cannot save the data in the forms I have made. once I close the form, all the saved infomation is lost when I reopen the form. But somehow they reman stored in the table.
Any suggesstion on how to retain the records in the form too.
Regards
Rahul

Can someone please look at the following code and explain why it's duplicating records in my MS Access database. THANX IN ADVANCE for your help!!!!

' FOR SAVING DATA TO THE DATABASE ******************************************
'************************************************* **************************

'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddRecord 'Holds the recordset for the new record to be added
Dim rsID 'Holds the recordset for capturing current record number
Dim strSQL 'Holds the SQL query to query the database
Dim RecordNum 'Variable that holds current record number

'ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'ADO recordset object to hold record for database
Set rsAddRecord = Server.CreateObject("ADODB.Recordset")
'ADO recordset object for capturing current record number
set rsID = server.createobject("ADODB.Recordset")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("WebForm.mdb")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM tbl_Request_CommDept_Print;"

'Set the cursor type we are using so we can navigate through the recordset
rsAddRecord.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddRecord.LockType = 3

'Open the recordset with the SQL query
rsAddRecord.Open strSQL, adoCon

'Tell the recordset we are adding a new record to it
rsAddRecord.AddNew

'Add a new record to the recordset
rsAddRecord.Fields("TitleOfJob") = request.form("TitleOfJob")
rsAddRecord.Fields("RequestDate") = request.form("RequestDate")
rsAddRecord.Fields("DueDate") = request.form("DueDate")
rsAddRecord.Fields("RequestBy") = request.form("RequestBy")
rsAddRecord.Fields("Dept") = request.form("Dept")
rsAddRecord.Fields("Email") = request.form("Email")
rsAddRecord.Fields("Print_Political") = request.form("Print_Political")
'-----------------------------
rsAddRecord.Fields("Print_JobType") = request.Form("Print_JobType")
rsAddRecord.Fields("Print_Other") = request.Form("Print_Other")
rsAddRecord.Fields("Print_Color") = request.Form("Print_Color")
rsAddRecord.Fields("Attached") = request.Form("FileName21") 'request.Form("FileName")
rsAddRecord.Fields("Attached1") = request.Form("FileName2")
rsAddRecord.Fields("Attached2") = request.Form("FileName3")
rsAddRecord.Fields("Print_Pictures") = request.Form("Print_Pictures")
rsAddRecord.Fields("Print_Layout") = request.Form("Print_Layout")
rsAddRecord.Fields("Print_Translation") = replace(request.Form("Print_Translation"),"'","")
rsAddRecord.Fields("Print_Quantity") = request.Form("Print_Quantity")
rsAddRecord.Fields("Print_ToBeMailed") = request.Form("Print_ToBeMailed")
rsAddRecord.Fields("Print_OtherPieces") = request.Form("Print_OtherPieces")
rsAddRecord.Fields("Print_PrintingPieces") = request.Form("Print_PrintingPieces")
rsAddRecord.Fields("Print_Comments") = replace(request.Form("Print_Comments"),"'","")
'-----------------------------
rsAddRecord.Fields("Mail_MailingPieces") = request.Form("Mail_MailingPieces")
rsAddRecord.Fields("Mail_MailPieceType") = request.Form("Mail_MailPieceType")
rsAddRecord.Fields("Mail_Area") = replace(request.Form("Mail_Area"),"'","")
rsAddRecord.Fields("Mail_Area_Desc") = replace(request.Form("Mail_Area_Desc"),"'","")

'Write the updated recordset to the database
rsAddRecord.Update


'Go to last record of the recordset and give an alias "NewReq"
rsID.Open "SELECT MAX(ReqNum) AS NewReq FROM tbl_Request_CommDept_Print", adoCon
'Assiqn variable "RecordNum" to the NewReq field; RecordNum stores that specific data *** Capture ReqNum ***
RecordNum = rsID.Fields("NewReq").Value
rsID.Close

Hi All, the database I am working on is structured as such:
frmClinicInfo starts everything off, which contains demographic information about a customer. It contains a control to open frmProductInfo_Active

frmProductInfo_Active contains filtered information about the customers' products. The data source is a query from tblProductInfo. This form also contains a control which opens frmProductInfo, which is the unfiltered listing of the customers' products. The data source is also tblProductInfo. Both of these forms contain a control to open frmServiceInfo.

frmServiceInfo has a control top open frmComponents, and here is the source of my question.

frmComponents contains information about the sub-components contained in a customer's product. There are about 20 items. If a component is replaced, then that needs to be noted in this form. In order to facilitate data entry, I put in some code which copies all data from the current record to a new one, and locks the old record. Code is here:
Code: Private Sub cmdEditComponents_Click() DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdCopy DoCmd.GoToRecord , , acNewRec DoCmd.RunCommand acCmdPasteAppend Me.AllowEdits = True If CurrentProject.AllForms("frmProductInfo_Active").IsLoaded Then Me.TeamviewerID = [Forms]![frmProductInfo_Active]![TeamviewerID] Me.MAC_Address = [Forms]![frmProductInfo_Active]![MAC_Address] Me.ServiceID = [Forms]![frmServiceInfo Subform]![ServiceID] Me.ClinicName = [Forms]![frmProductInfo_Active]![ClinicName] Me.FLM_Dev = [Forms]![frmProductInfo_Active]![SN] ElseIf CurrentProject.AllForms("frmProductInfo").IsLoaded Then Me.TeamviewerID = [Forms]![frmProductInfo]![TeamviewerID] Me.MAC_Address = [Forms]![frmProductInfo]![MAC_Address] Me.ServiceID = [Forms]![frmServiceInfo Subform]![ServiceID] Me.ClinicName = [Forms]![frmProductInfo]![ClinicName] Me.FLM_Dev = [Forms]![frmProductInfo]![SN] End If Me!ProductID = Forms![frmServiceInfo Subform]![ProductID] End Sub The person can then just change the one component which was exchanged. However, if a person hits the control by mistake or by just playing around, a new duplicate record will have been created. I want to prevent this from happening. I was thinking on putting some code in the After_Update event which will compare the forms' data with the previous record, and if both are equal, to not save the current record, but this is beyond my capabilities. If anyone can help me with this, or suggest another way to tackle this problem, I'd appreciate it.

I have a form with several data fields. I have created a command button for duplicate record. The command button works fine and copies all the data over to a new record. When I go to close the data base, a message comes up saying you have pasted a large amount of data to the clipboard - do you want to save it? How do I get rid of that message?

I have a form that has RCRA_ID and subform with the RCRA_ID as well. This field is an autonumber. When I open the form, a new RCRA_ID is created in the parent form. Then i go to enter data into the subform that is linked to the parent form with RCRA_ID and when I go to move from that subform it says I have created a duplicate record and can't save. The parent form is using a query with the table tbl_RCRA_Inventory included and the subform uses only the tbl_RCRA_Inventory.

Thanks.

Hi,
I got this very simple order form, used by several people. Often unknowingly they would place an duplicate order for the same company. I would like to capture this before record is saved and give the operator option to cancel, add or view suspected duplicate. I managed to do that, but only with single parameter. How do I add second parameter that would check for orders done in last seven days from date of current order and alert the operator? I manage to find bits and pieces of code and had help from others, see code below, but this is limit of my capabilities.

I am getting Run-time error 3075:
Syntax error (missing operator) in query expression
'PhName="PRICELINE GOULBURN" AND OrderDate Between 5/02/2011 9:16.31 PM AND #12/02/2011 9:16:31 PM#

Table name - PBSOrder_tbl
Table field - OrderDate
Form control name - OrderDate

OrderDate is recorded as Date/Time with default value Now() to record date and time of order to capture orders placed the same day by different operator for the same company.

Here is the complete code:

Dim SID As String
Dim stLinkCriteria As String
Dim iAns As Integer
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.PhName.Value
stLinkCriteria = "PhName=" & Chr(34) & SID & Chr(34) & " AND OrderDate Between " & DateAdd("d", -7, Me.OrderDate) & " AND #" & Me.OrderDate & "#"
If DCount("*", "PBSOrder_tbl", stLinkCriteria) > 0 Then
iAns = MsgBox("This job already exists! Add it anyway?" _
& vbCrLf & "Click Yes to add, No to jump to existing record, " _
& vbCrLf & "Cancel to go back to editing this record", _
vbYesNoCancel)
Select Case iAns
Case vbYes ' do nothing, let the record be added
Set rsc = Nothing
If MsgBox("You are about to add an order." _
& vbCrLf & vbCrLf & "Do you want to save this order?" _
, vbYesNo, "Order confirmation") = vbYes Then
DoCmd.Save
Else
DoCmd.RunCommand acCmdUndo
End If
Case vbNo
Cancel = True
Me.Undo
rsc.FindLast stLinkCriteria
Me.Bookmark = rsc.Bookmark ' move to last found record
Case vbCancel
Cancel = True ' suppress update, return to form
End Select
End If
================================================== ====
I will appreciate anyones assistance.

Regards

Wesley

How do I delete duplicated records without having to make a new table? I would like the records deleted from the original table.
Thanks

Hi,

From a report Form, the user selects some criteria to narrow down the report data. So, when calling the report there is a filter.

The report, calls a query with the filter sent to it. It also has a sub-report that shows some total allocations. The detail line consists of account number, comments, prior year allocation, expenses etc.

If there is duplicate data, all fields are the exact same except for 1 field.

Example of Data
Acct. FundingSortCode
----------------------------------
2255 a
2255 c
2255 d
2255 e

This FundingSortCode is not shown on the report, but it is used in the report Form selection criteria. (i.e.: when calling the report it sends ([FundingSortCode] 'a') ).

The report has 2 groupings: Community Name and Project Number.

Since I can't hide the sub-report, I had to put it under the Project Number grouping.

Between each detail grouping is a line. I need to only show 1 detail line.

But, when the duplicates records, the report gets:

line
detail information
line
sub-report information
line

I need to remove the middle line, this line is the first field in the detail section. This is happening because of the dupicate records.

Any ideas?

Thanks so much.

Karen


Not finding an answer? Try a Google search.