Stop duplicate records


I have an user imput form that has a unique file reference number which is typed in by the user in the first bound field. Does anyone know of a way of informing the user that the file reference number is already in use and therefore a new one must be used. At the moment the user is completing the entire form before being made aware that it would create duplicate records and therefore unable to save current record etc...Totally fustrating! any ideas Steve
Posted On: Monday 22nd of October 2012 05:57:38 AM Total Views:  265
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




Switch Board Just stopped Working

I am using access 2010, My switch board worked as far I as knew on friday and this morning (monday) it will not let me edit any items on my switchboard manager. The error is as follows: 'FillLstArg' may not be a valid setting for the RowSourceType property, or there was a compile error in the function. Any idea\t I'm just trying to add a new switchboard and make a link to it from the main switchboard. There are already 5 items on the mainbaord, all of them lead to more switchboards and like I said they all worked as of friday.
VIEWS ON THIS POST

124

Posted on:

Wednesday 17th October 2012
View Replies!

Getting rid of duplicates

oo
VIEWS ON THIS POST

128

Posted on:

Wednesday 17th October 2012
View Replies!

MS Jet database engine stopp.....

If a user pulls up a certain record, they will receive: "Microsoft Jet database engine stopped the process because you and another user are attempting to change same data at the same time" When that person is the only user that is in the system, what should I be looking for that can cause this to happen It's only on that certain record when this problem occur. The application is written in Access 2003 SP3, on a windows 2007 platform.
VIEWS ON THIS POST

151

Posted on:

Wednesday 17th October 2012
View Replies!

Microsoft Jet database engine stopped

The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time The error message appears every time when attempts to mount the database. I am using the Ms Access. I have very important data in it so please tell me what to do to save that data.
VIEWS ON THIS POST

134

Posted on:

Wednesday 17th October 2012
View Replies!

Weed out duplicates

Hi Guys, I need a little help with a project. I am running MS Office 2002 the full version with Access. In a .mdb file I have about 11000 customer names and addresses, some are duplicates because they purchased from us twice or even 3 times. Every so often we want to send them advertising about a new or improved product and it would be a waste of money and paper to send them to all because of the duplicates. The number of letters that went out last time was about 7500 and I went through them 1 by 1 to weed out the duplicates. Takes me a whole day to do that. What I need is a way to eliminate all duplicate addresses and if there are duplicates, I need to keep the newest one, because sometimes the homeowner has sold the property and I want to address it to the new owner. Also, some owners have requested that we do not send any mail to them and I have been marked with no mail in a remarks column. If you need this, here are the fields I have to work with: OrderNumber FirstName LastName Address City State Zip Date Remarks The last 2 are only for sorting purposes and will not be on the mailing Lable. If anyone has the patients to explain to me how this can be done, please respond. I appreciate your help Sven
VIEWS ON THIS POST

93

Posted on:

Wednesday 17th October 2012
View Replies!

TempSysAccessObjects -?duplicate table

(Backend on a server - local frontend) When I close my database I get a message that says TempSysAccessObjects table already exists. I have read the assoicated "help" but haven't renamed a table as is indicated. How can i track down this table to remedy the issue
VIEWS ON THIS POST

92

Posted on:

Wednesday 17th October 2012
View Replies!

How to stop query execution at the Form Start

Hi I am a new user to Access. I created couple of forms using wizard, but when I view them, they show me all the available data by default. I want to stop this, if anyone can help me plz. RMA
VIEWS ON THIS POST

92

Posted on:

Thursday 18th October 2012
View Replies!

To add a duplicate form of an existing record to a new record

I use MS access 2003, so what Im looking at doing for my database is add a duplicate form (duplicating some fields in the form) of an exiting record to a new record, but the new ID number assigned should be from a sequence (ie. unsed ID numbers) and would be automatically be inputted into the ID field (PK) of the duplicate form. Ive found two pieces of code online which I cant seem to modify to suit what I require but with very limited knowledge of access I assumed these codes are very relevant or I could be wrong. Code 1: Private Sub btnDuplicate___Click() Dim dbs As DAO.Database, Rst As DAO.Recordset Dim F As Form ' Return Database variable pointing to current database. Set dbs = CurrentDb Set Rst = Me.RecordsetClone On Error GoTo Err_btnDuplicate_Click ' Tag property to be used later by the append query. Me.Tag = Me![OrderID] ' Add new record to end of Recordset object. With Rst .AddNew !CustomerID = Me!CustomerID !EmployeeID = Me!EmployeeID !OrderDate = Me!OrderDate !RequiredDate = Me!RequiredDate !ShippedDate = Me!ShippedDate !ShipVia = Me!ShipVia !Freight = Me!Freight !ShipName = Me!ShipName !ShipAddress = Me!ShipAddress !ShipCity = Me!ShipCity !ShipRegion = Me!ShipRegion !ShipPostalCode = Me!ShipPostalCode !ShipCountry = Me!ShipCountry .Update ' Save changes. .Move 0, .LastModified End With Me.Bookmark = Rst.Bookmark ' Run the Duplicate Order Details append query which selects all ' detail records that have the OrderID stored in the form's ' Tag property and appends them back to the detail table with ' the OrderID of the duplicated main form record. DoCmd.SetWarnings False DoCmd.OpenQuery "Duplicate Order Details" DoCmd.SetWarnings True 'Requery the subform to display the newly appended records. Me![Orders Subform].Requery Exit_btnduplicate_Click: Exit Sub Err_btnDuplicate_Click: MsgBox Error$ Resume Exit_btnduplicate_Click: End Sub The code above also takes into account an append query named Duplicate order details, a field column in the design view of the query is NewOrderID: CLng(Forms!Orders!OrderID) Append to Order ID and the OrderID column has in its criteria [Forms]![Orders].[Tag]. So the above code produces command button on the form, where if the button is clicked on an existing record, a new record and ID is created with the same fields of the existing record BUT does not satisfy the requirement of assigning a new ID from the a sequence. Code 2: Private Sub btn_Find_Click() Dim sString As String Dim sSql As String Dim sRS As New ADODB.Recordset Dim sConn As New ADODB.Connection Dim X As Integer Dim Y As Integer Me.txt_Result = "" sString = "" sSql = "Select TalentID From tbl_talent_database Order by TalentID" Set sConn = CurrentProject.Connection sRS.Open sSql, sConn, adOpenKeyset, adLockOptimistic If Not sRS.EOF Then With sRS X = 0 .MoveFirst Do Until .EOF Y = !TalentID ChkSeq: X = X + 1 If Y X Then 'chk to see if TalentID is sequential sString = sString & X & " " 'if it is not, then record the non sequential number into the string GoTo ChkSeq End If .MoveNext Loop End With End If Me.txt_Result = sString Set sRS = Nothing End Sub Code 2 creates a command button on the form so when I click the button in the text box all the unused numbers all displayed (ie. unused numbers refers to ID numbers not assigned to a record). In the end Im not access capable to modify or join these codes to add a duplicate form of an existing form to a new record however the new record ID number coming from a sequence. Greatly appreciate your help.
VIEWS ON THIS POST

103

Posted on:

Monday 22nd October 2012
View Replies!

Report stopped working

, Having an issue with the generation of the reports in my database, well this is happening in all databases I log into and one other person in my group is having the same issue. I have checked macro security and we are set on Medium(should be able to run the reports). I have tried to run reports from the switchboard and from the Reports list. Hourglass shows up then just disapears. I am not sure where to start looking. I am the admin on one of the databases, now I seem to be locked out of the reports section. I have tried both usernames I have for the database login(through the security wizard) to no avail. The other user having the same issue is not even logged in through the security wizard secure group and cannot get the reports to run. Does anyone have any idea on where to start looking These databases are over 5 years old on two and 3 on another, but like I said other employees who I gave the same permissions to can access the reports without issue. Not sure if there is another permission list I need to review in order to access reporting in Access, I have gone through the security wizard and have full permissions on my admin account, through the effective permissions for Access in general I am set-up as the empoyees that can print reports. I had been able to use all database report features without issue, now I am locked out. Please let me know if there is another area I need to take a look in so I can get back to report making. I do not believe it to be the database per say, since it is happening on three different databases, made by three differnt uses. I would say it is in permissions for Access, but am not sure where to look.
VIEWS ON THIS POST

70

Posted on:

Monday 22nd October 2012
View Replies!

Eliminate duplicates

When I run a query with two tables I get duplicates These are two tables of data from the same stores but different amounts of data for each causing duplicates I need to get rid of the duplicates for the report
VIEWS ON THIS POST

79

Posted on:

Monday 22nd October 2012
View Replies!

Getting duplicates in query, but no duplicates in the tables

Can somebody please help. Im a bit of a beginner and I just cant figure this out. I have written a query based on 5 tables all in all, none of them have duplicate entries but when I link them together and display some fields I end up with some duplicates in the output. I have some calculated fields not sure if that makes any difference. Please help
VIEWS ON THIS POST

126

Posted on:

Monday 22nd October 2012
View Replies!

Join tables with duplicate records ??

, How can i remove the duplicate records with 2 join tables S/No 105 and 107 exported to excel sheet are duplicate.
VIEWS ON THIS POST

110

Posted on:

Monday 22nd October 2012
View Replies!

Switchboard stops my forms' VBA code from working?

I have several forms that I've built... Most of them have some sort of VBA code built in somewhere, like:Select a record with the key-value control, and the rest of the controls position to that record"OnChange event: Set bound control equal to the value of unbound control"...etc. Straightforward stuff. The record positioning code was created by Access's wizard for creating a combobox, for example. These forms work perfectly when I launch them from the Database window. However, I just created a switchboard, and when I launch them from the switchboard, the forms no longer work properly -for example, if I select a key value, the remaining controls remain blank. Is there a simple reason I hate being new to this... I think I'd take fewer blows in a fight with Tyson... (well, certainly I'd give up quicker! )
VIEWS ON THIS POST

93

Posted on:

Monday 22nd October 2012
View Replies!

Deleting records in Column

I'm trying to delete just ONLY the records inside of the column, Not the whole ROW. But I just don't get it. This is what I used. DoCmd.RunSql "DELETE tblMasterProfileSheet.QTY FROM tblMasterProfileSheet" Everytime when I execute that script, it's deleting the whole row. I just want to delete records inside of the QTY column, not the ROW.
VIEWS ON THIS POST

63

Posted on:

Monday 22nd October 2012
View Replies!

Removing outdated records.

I have: Database A with fax numbers: Example: - 1. John. W - 2. Mark. Y - 3. Sean .S I exported to a dbf file to do some mass faxing and realised that Mark's fax number is no long in use. (error report by the fax software) I can export the error report and save as dbf. Is there a way to make use of this dbf to update and remove Mark's record from Database A
VIEWS ON THIS POST

66

Posted on:

Monday 22nd October 2012
View Replies!

Unique records

I have a database that contains sales order information. A sales order could have multiple records (if that sales order has several different items ordered) or just one record. There is a status column in each record that shows a "C" (for closed). For items that were not delivered in a particular sales order, the status column is left blank. Here is an example: Sales Order Item Ordered Status 908111 Coaxial Cable C 908111 Transreceiver C 908111 Connector 908112 Coaxial Cable C 908112 Transreceiver C 908112 Connector C The above example shows that only 2 items from sales order # 908111 were delivered or "C" but one of them is not. For sales order # 908112, all items were delivered or "C". I want to take all records of sales order # 908111 and show it on a table of partially delivered and all records of sales order # 908112 in delivered table. I've tried different filtering options and group by but it doesn't seem to be working. Anyone has any ideas
VIEWS ON THIS POST

69

Posted on:

Monday 22nd October 2012
View Replies!

Performance Problem - 340,000 records

Hi , First time in this forum I am producing an MS Access 2003 application with linked tables to MS SQL Server 8.0 Enterprise Edition that contains a range of tables populated with extensive amounts of Legacy data on a view only basis. I have gone down the .mdb route as opposed to the .adp route. Bill OF Materials ========= In this particular instance I have created two normalised and related tables in MS SQL Server from a flat file containing 340,000 records of invariable quality and meaning in order to represent a searchable Bill Of Materials in an MS Access form. Using the Linked Table Manager I have linked MS Access 2003 to these tables. tbl_BOM_Header Header ID (PK) Catalogue_Numb Catalogue_Desc In an MS Access form - frm_BillOfMaterials - I have represented all 34,000 records in a listbox (lstbox_BOMHeaders) based on a query qry_BOMHeaders. This listbox will be populated by the result of the query depending on what the user types into a text box at the top of the form tbl_BOM_Header_Item SID (PK) Header_ID (FK) BOM_Item_Catalogue_Number BOM_Item_Catalogue_Desc + (several other sparsely populated variables) I have created a continuous subform for these BOM Items and inserted subfrm_BOMHeaderItems into frm_BillOfMaterials. There are 340,000 BOMHeaderItems records (10x) When the use selects the BOM header records from lstbox_BOMHeaders, the OnClick() event executes the following code: Me.RecordsetClone.FindFirst "[Header_ID] = " & Me![lstbox_BOMHeaders] Me.Bookmark = Me.RecordsetClone.Bookmark which works fine - up to a point. Basically my problem (if you haven't guessed it already) is that the retrieval of BOMHeaderItems for each Bom Header record is at best slow or at worst crashes my MS Access application. Please can anyone advice me based on their experience of either: 1) a modification to this approach that will yield quicker retrieval and stop my application from crashing. 2) a different approach that will yield quicker retrieval and stop my application from crashing I have reviewed a couple of the standard articles such as: http://support.microsoft.com/kbid=209091 Any technical advice, guidance or support would be very much appreciated, Many
VIEWS ON THIS POST

118

Posted on:

Monday 22nd October 2012
View Replies!

Combobox to list records

, I have a table with the following fields: FATHERFIRSTNAME FATHERLASTNAME CHILDNAME1 CHILDNAME2 CHILDNAME3 CHILDNAME4 Is there a way I can list the above records using a combo in a way like this : PAUL SMITH (FATHERFIRSTNAME + FATHERLASTNAME) JOHN SMITH (CHILD1) MARY SMITH (CHILD2) LOUIS SMITH (CHILD3) ELENA SMITH (CHILD4) I know that crosstab queries only allow 3 colums therefore was looking for some way to bypass the problem. Thank you.
VIEWS ON THIS POST

60

Posted on:

Monday 22nd October 2012
View Replies!

How to output records into list box with 3 coloum headings

Hi every one. i want out put records into a list box with 3 column headings named as playerno, initials and name. The way i created the list box is by putting list value for the row source type and name the list box as lstPlayers. But unfortunately get all the the records Fields one after one not one row at a time. I be happy if some one tell how to fix this
VIEWS ON THIS POST

98

Posted on:

Monday 22nd October 2012
View Replies!

Seeing certain records based on a checkbox

How would I get a report to only show records that have a check box called "Display" checked I have tried some code, I even put it in a couple of different places, but it still doesn't work. I put the code on the on click event of the command button that opens the report and I tried the code in the on open event of the report. It did not work either time. Here is the code that I tried. 'Display only records that have the box checked If IsNull(Display) Then DoCmd.GoToRecord , , acNext End If End Sub Can anyone help me\t learnasugo
VIEWS ON THIS POST

83

Posted on:

Monday 22nd October 2012
View Replies!