Tables :: SKU Generator - Duplicate Record Prevention
Oct 29, 2012
I am working on a SKU generator and I need to prevent duplicate SKUs, but each SKU is made from several different fields. i.e. Brand, Color, Design, etc.
I was wondering if Concatenating would be OK for this situation. I know concatenating usually results in redundant data and I try to avoid that.
I would like to just index (no duplicates) the entire record... is this possible? I can't just set that attribute to each field because there will be many duplicates on a per field basis. i.e. Brand will be duplicated every time I use the same vendor for a different shirt.
I can't just go back and address duplicates once they occur. I need to prevent them from occurring in the first place.
I am the administrator for a 2010 Access Client Database that consists of many clients with their information and we have three users whom go in and edit-add information to this Database and it hasn't been a problem until two users have tried to edit the same client record at the same time and then we have had some issues...
So, not sure this is even possible, but can one prevent more than one user being on the same client record? Is there a way to have a message come and say this record is in use?
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
Does anybody know of a quick and easy way of preventing data insertion to a table from a form, so that table doesn't get updated unless all form fields are populated?
Kinda stuck at deciding what I should do here.. What would be the best way to prevent a user from entering data into a tabular form without making sure a certain member exists first?
for example if I have a columnar form
BOXA BOXB (invisible or disabled by default)
1 of 2
I can have it check to see if BOXA has data if it DOES i can make BOXB available for data entry
but lets say I have a tabular form....
BOXA BOXB BOXA BOXB
1 of 2
How can I make it so that BOXB will not accept data entry UNLESS its corresponding BOXA has data in it already?...
A:[asldkfjaskd] B:[AVAILABLE FOR EDIT] A:[] B:[NOT AVAILABLE UNTIL MEMBER IS CREATED]
It needs to be in tabular form too.. sigh.. anyway.. thanks for any help you might be able to provide
I've been asked if I can create a database which will act as a quote generator. There will be 5 components to make up the whole product. However, the options available for components 3, 4 and 5 will be dependant on the components chosen for 1 and 2. i.e. there may be 5 options for each component. If part 1a is chosen, then only parts 2c, 2d and 2e will be available. If part 1a and 2c are chosen, then only parts 3a and 3d are available and 4b, 4d and 4e are available. ...does any of this make sense?????
My question is... can anyone tell me how I should create the tables and the relationships to enable me to create a database like this. I am completely stumped. It may be that this is completley beyond me, but i'd like to at least be able to give it a try. Any help would be appreciated. Thanks
Ok, I apologize in advance for my lack of knowledge but I need Help!!
I have a database form that has a O_Num field that populates the Order table. Now the client wants the order number to be a randomly generated number. this is the primary key and I cant use autonum because there is another field in the table that is autonum.
I have tried to use the following in the OnClick field, doesnt work
Hello i've got to make a project for school and thought I would make a rental shop like Blockbuster. But I need to create a number that will be assigned to each customer when they become a member. So I need to generate a random number that uses a certain amount of digits (I was thinking about 8) and that won't be used again can you help.
My company does daily, weekly, and monthly drawings using mountains of entry tickets and a barrel that has seen better days. So needless to say this system has to go. I hope to be able to do it digitally.
I would like to do a random number generator to pick the winners based off of a ticket number range. The ticket numbers issued to the customer would correspond to the customer account number. To complicate things I would really love to have the random numbers displayed on the screen so people feel they are watching the randomization pick in progress with the winner name popping up.
I have searched Help for Rnd function, as well as googled. I haven had any luck finding what I need. Is what I am looking to do possible in access? I had something similar in Excel a few years ago but lost it.
I'm having trouble with my VBA module.I have a random test generator which pulls records based on a category from my table into a temp table using a make table query. I use the following code:
Private Sub Command2_Click() DoCmd.SetWarnings False DoCmd.OpenQuery "1", acViewNormal, acEdit DoCmd.OpenReport "WrittenExam", acViewPreview, "", "", acNormal Reports!WrittenExam.lblTitle.Caption = "Exam Name" DoCmd.OpenReport "WrittenExamAnswerSheet", acViewPreview, "", "", acNormal Reports!WrittenExamAnswerSheets.lblTitle.Caption = "Exam Name - Answer Sheet" End Sub
My querry makes the table, and then generates two reports (my exam, and the answer sheet). I'm getting an error 3211, saying the temp table is already in use by another process when trying to generate both reports. I used a Macro before, but I have a need for custom report headings, so I'm using VBA.
I have a database with a table that uses a numeric field as a PK. If I use the built in "Duplicate" Command button on a form it creates an exact duplicate of the record, which then Access won't allow because the PK is no longer unique.
Does anyone know of a way that I could make the number be the max number + 1 when I click the "Duplicate" Button?
I have a recored set and i dont want a duplicate of the record set in the same table.
Say if my table name is businessskills and contains three fields(columns) and their names(Email , B_code, Rating) highlighted in red with data are as below
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!
When i try to execute the code via a comand button, i get an error and the duplicate record operation does not occur. one thing i noticed is that i have a lot of lookup fields (i.e. FK's to lookup tables one-to-many relationships) in underlying table being populated by the form. i've created combo boxes on the form to populate the FK's in the underlying table. the error that occurs when trying to use the above code produces a "paste errors" table and in that paste errors table instead of the bound column values from the combo boxes (i.e. PK values from the lookup tables) it shows the display values from the combo boxes. i'm not sure if this has do with anything, but i couldn't figure out why it was doing this.
does anyone have any ideas how i could get this duplicate record procedure to work?
I have a form (with subform in it) and i fill it with different values. After some time, i need to fill another record (new one), with similar values.Is it possible to make a duplicate of my previous record, and put it in the "new record" so that i won't have to fill all fields again, only change excisting ones to different values.
On my form I've got an afterupdate event that checks if the information entered already exists and this works absolutely fine. However what I would like is the option to go to the existing record if one is found, but I can't get it to work.
This is my code currently;
Code: Private Sub Job_No_AfterUpdate() If DCount("*", "PACKING", "[Job No>]='" & Me.[Job No] & "'") > 0 Then If MsgBox("Job Number already exists! Go to record?", vbYesNo, "DST PLANNER") = vbYes Then Dim rs As Object
[Code].....
The check for the Job Number works fine but when I click Yes on the message box, the form stays on the current record instead of moving to the existing record.
I am creating a database that has 3 main tables. When i go to relationships it shows my three main tables and then it shows 2 more. The 2 extra tables are name inventory_table1 and stock_tbl1, these 2 tables were created by access for some unknow reason. some how it is creating a duplicate of my tables and they are not linked to anything in my database. How do i get rid of these tables so they will not show up in my relationship view.
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 ?
I have a search form passing text from unbound controls to a query. These two fields (AuthorName & Title) of the query are from two different tables (Authors & Papers) set up in a many to many join. The 3 dummy records I am using are as follows:
Record 1 AuthorName: Smith (Author 1) Title: SmithTitle
Record 2 AuthorName: Smith (Author 1) AuthorName: Jones (Author 2) Title:SmithJonesTitle (i.e. two authors for this record)
Record 3 Author: Jones (Author 2) Title: JonesTitle
If I stick in 'Jones' into the Author field and 'JonesTitle' in the Title field I correctly get 1 result from the query (record 3). However, oddly, if I put in Smith and SmithTitle I get two records (2 x record 1)!!!!! There aren't two records!!! Similarly if I leave both blank I get all the records and again there is a duplicate of the Smith record (and only this one is duplicated!)
The query criteria are:
[AuthorName] Like [Forms].[Search]![Author] & "*" [Title] Like [Forms].[Search]![Title] & "*"
I have a problem with duplicate records in my query. I know why the duplicate records are occuring and I have spent a ridiculous amount of time trying to fix the problem, but I'm really at a lost and am quite desperate for some help.
Here's some background info that shows the cause of the problem: I have two tables. In the first table (tblMainData), there are three columns of interest (P1, P2, & P3). The second table (tblGroupList) has two columns (PN & GroupList). A relationship exists such that the data in "PN" is parital-text values of the data in columns "P1", "P2" and "P3".
For example, "P1" in tblMainData may contain "A1235XX". The "A1235" is what's important, so that's one of the values that I have in "PN".
Continuing, I have a user form that uses the "GroupList" field as the RowSource for one of my combo boxes (cboGroup), and this field contains an (ALL) option. So, when a value is selected from cboGroup, I use the following Criteria filter Like [PN] & "*" in my query (qryFilterGroup) to look for those partial text fields that match what's in fields P1, P2 and P3. The results are then shown in a subform.
Here-in, I believe, lies the source of the duplicate records. In my userform, if "(ALL)" is selected under the cboGroup combo box, a new record is created for each field P1, P2 and P3 in tblMainData that contains data and that is related to the partial text matching Criteria. A new field is created because each field corresponds to one of the values in the "GroupList" field.
This is really difficult to explain, and I don't really know if I should continue without writing a book. If someone or someone(s) could be so kind, please have a quick look at my attached database as it's obvious to see my problem. Any help is greatly appreciated!!
Before the record is written for the first time OR edited/updated to the table, I want to search tblClass and determine if the new/updated info will create a duplicate record.
In this specific case, a duplicate record will be defiened by a record where the only fields being considered would be StudentID, Trimester and SubcatID. The fields WorkGrade and SkillGrade should not be considered.
The code I came up with was the following and it was put in the BeforeUpdate:
Dim conn As ADODB.Connection 'Connection Object Dim rst As ADODB.Recordset 'Recordset Object Dim strSQL As String 'SQL statement for open statement
' Create object variables Set conn = CurrentProject.Connection Set rst = New ADODB.Recordset
' Create sql to search for records in tblClass that match 'studentID, Trimester, and Subcatagory in the form record being added strSQL = "SELECT * " & _ "FROM tblClass" & _ "WHERE fldStudentID = " & StudentID & " AND " & _ "fldTrimester = '" & Trimester & "' AND " & _ "fldSubcatID = " & SubCatID & ";"
' Open recordset rst.Open strSQL, conn, adOpenKeyset, adLockOptimistic
If rst.RecordCount >= 1 Then ' record already exists in tblClass msgbox "Record already exists!",, "Duplicate Record Error" Me.cboSubcatID.SetFocus Cancel = True End If
' Close and disassociate object variables rst.Close conn.Close Set conn = Nothing Set rst = Nothing
This code worked great except when I went to edit an existing record. When I went to change a grade (WorkGrade or SkillGrade) on an existing record, it told me that I could not enter the record because the record already existed (ie, the record I had open and was editing). I am not sure if it is my code that I need to edit or if it is the placement of the code I need to change.
I have a document database that often deals with multiple copies of a document. Each copy must have a record of its own. With the add new record form I would like the user to be able to add the extra copies automatically by duplicating the first entry "n" times but also incrementing the copy number field by one for each copy. Got the duplicate copy done OK but am stuck on how to increment the copy number. This database forms part of my yearly assessment and is due in a couple of weeks so would welcome some help.
