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.
View Replies
ADVERTISEMENT
Jan 12, 2013
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?
View 1 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
Oct 2, 2005
Hello,
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?
Thanks
View 5 Replies
View Related
Dec 12, 2005
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
View 3 Replies
View Related
Feb 28, 2006
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
View 3 Replies
View Related
Feb 13, 2006
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
=Int((1000 * Rnd()) + 1)
View 1 Replies
View Related
Mar 5, 2005
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.
rn2j
View 2 Replies
View Related
Mar 9, 2006
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.
View 7 Replies
View Related
Oct 3, 2013
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.
View 1 Replies
View Related
Apr 12, 2005
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?
Thanks
View 1 Replies
View Related
Mar 11, 2006
Hi All,
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
Email B_code Rating
hugh@hp.com b001 1
hugh@hp.com b002 3
hugh@hp.com b005 2
ston@hp.com b002 3
ston@hp.com b007 3
I have 5 recordsets in my table as shown above and i want to prevent any e duplicates.
Is this possible to implement to with excel vba code or directly in Access Database, either ways is fine with me.
Can any one please help me with this.
Thanks in advance
View 1 Replies
View Related
Dec 21, 2006
I'm trying to make a simple DB that we can enter our time for working on projects.
So far I have a Form with the employees name and the date in it. Then below that is a subform with the project number, name, client name, and hours.
I want to use this whole "sheet" for one days worth of work for 1 employee. New sheets will be used for each day.
When I try to make a new sheet I get an error that it cant change the table because it would be creating a duplicate entry.
How do I solve this?
Also, should it be difficult to be doing what I'm trying to do?
View 2 Replies
View Related
Jul 13, 2007
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
View 5 Replies
View Related
Aug 17, 2005
hi everyone,
i have a form that i'm trying to create a button to duplicate the record. i'm using the control wizard which produces the following code:
Private Sub DuplicateRecord_Click()
On Error GoTo Err_DuplicateRecord_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
Exit_DuplicateRecord_Click:
Exit Sub
Err_DuplicateRecord_Click:
MsgBox Err.Description
Resume Exit_DuplicateRecord_Click
End Sub
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?
many thanks for any help or suggestions.
View 1 Replies
View Related
Dec 2, 2005
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.
Thanks
View 11 Replies
View Related
Mar 13, 2014
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.
View 14 Replies
View Related
Mar 17, 2013
I am having trouble duplicating a record on a form. It gets tripped up on the phone number field ?????
The phone field has an telephone input mask. The error message is:
"The value you entered isn't appropriate for the input mask '99/99/0000;0_' specified for this field"
View 3 Replies
View Related
Feb 9, 2005
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.
View 7 Replies
View Related
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
Aug 4, 2005
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] & "*"
Any ideas???
Thanks
View 5 Replies
View Related
Sep 1, 2005
Hi,
I have table tblSample (IdNo, SCode, StaffNo) where IdNo is AutoNumber. This is sample of data,
IdNo SCode StaffNo
10 ABC 50424
11 DEF 50424
12 GHI 50424
21 ABC 50424
22 DEF 50424
23 GHI 50424
26 ABC 50424
27 DEF 50424
28 GHI 50424
Is it possible for me to delete records from IdNo 21-23 and IdNo from IdNo 26-28. Any idea? Thanx in advance..;)
View 1 Replies
View Related
Mar 6, 2007
Hello Forum,
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!!
View 3 Replies
View Related
Aug 16, 2005
I have a subform that collects the following data to put in a record in tblClass:
StudentID, Trimester, SubcatID, WorkGrade, SkillGrade
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.
Any suggestions would be great.
View 4 Replies
View Related
Jul 3, 2006
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.
many thanks
View 1 Replies
View Related
Aug 23, 2006
Hi all,
I created a form for entering employee skillset in Accounting.
I have set up three fields - one for Primary Skillset and the other two for secondary skill sets -
The skillsets are -Payables, Receivables, General Ledger, Purchasing etc.
How can I ensure that a user will not enter duplicate values in the three fields.
That is a user will not enter for examply "Payables" in the Primary Skillset, and Secondary Skillset fields.
I tried creating a multiple field index but it does ont appear to work.
I will appreciate all the help.
Thanks
View 4 Replies
View Related
