Modules & VBA :: Preventing Duplicates On Multiple Fields (Numeric And Text)
Oct 15, 2013
How to prevent duplicates on the combination of two fields - text & numeric?
I'm currently using the code below that warns users when the combination of two fields have already been used. (Combination of the TWO fields has to always be unique so if used again will warn the user)
Works well when both fields are numeric but fails when the JobDetails field is changed to text in the main table (tblPPMPLanner)
Code:
Option Compare Database
Option Explicit
Private Function IsDuplicateRecord() As Boolean
On Error Resume Next
Dim PreviousRecordID As Long
IsDuplicateRecord = False
[Code] ....
The field that should be a text field is called "JobDetails"
View Replies
ADVERTISEMENT
Oct 12, 2012
I am trying to modify an inventory management database. I want to prevent duplicate entries to specific fields in centralized table. The table is called Work Stations. Its function will be to track various computer equipment by a specific work station name. Each of the items will have a unique asset tag.
The primary Key for Work Stations is WS-ID. The fields I am trying to prevent duplicate entries in are WS-Computer, WS-Docking Station, WS-Monitor_1, WS-Monitor_2, and WS-Switch. Each of those fields are primary keys in 4 different tables that conation more detailed information about item. The exception is WS-monitor_1 and WS-Monitor_2 have a one-to-many relationship with Mon-Asset Tag in the monitors table.
The goal I am trying to accomplish is when a work station ID is created or modified duplicate entries are prevented to those fields listed above. The computer, monitors, docking station, and switch fields in the work station table may contain data or may be null.
I have tried to set the Indexed Option to Indexed (No duplicates) for each of the fields and I have also tried setting Yes to Ignore Nulls in the index option on the table design tab for the individual fields. Both options have returned the same error stating the changes would create duplicate values, in the index, primary key, or relationship.I have not created form for this table yet as I was trying to get no duplicates option to work first. I have verified the data and the only duplicating fields are the Null fields
View 6 Replies
View Related
Mar 4, 2008
Hello All,
I'm trying to limit the data entered into a specifc field, but also the data must be unique with respect to other fields.
i.e.
Two fields: System A & System B.
Data entered into System A, can not be entered into System B.
Anyway to prevent this from recurring?
Would I use a validation rule?
Thanks in advance
View 4 Replies
View Related
Aug 16, 2014
I have an ms access Database(2013 version). There are about 10000 records. There are some columns with field property of "short text" but contains the values like that 0.4,7, 9.0 etc I would like to convert the "short text" into "double" without loosing information.
View 3 Replies
View Related
Jun 27, 2005
Hi,
I have a form and within that form there is two combo boxes and one text box. When one choses a value from one combo box, and then chooses a value for the other combo box and then enters the value into the textbox I don't want the user to be able to enter the same arrangement. That is I want to prevent duplicates on that combination. Also if they do this I want a Message Box to appear saying that that this combination already exists
I was thinking of using a multiple field index to prevent the duplicates, but I don't know if this is a wise thing to do. Can someone give me some help to see how I can prevent duplicates or offer another solution to prevent duplicates on the combination of the values.
Thanks
Greg
View 2 Replies
View Related
Jun 13, 2007
Hi, How do you normally prevent duplicates being inputted into you forms.My Idea is thisThe input text box doubles up as a dropdownlist of current values held in the database for that field.So when you access the organisation form and go to the first text box as you enter the 'I' for IBM All the orgs with 'I' appear. When you see that the organisation your inputting already exists you will stop and move on to the next form.I dont know how to do this, so currently I have set the field OrgName as Indexed: Yes (No duplicates) but this isnt really any good as the user enters all the other details and clicks submit before the error message comes up.I have attached my file for you to understand this better.
View 2 Replies
View Related
Aug 6, 2015
I have a Query contains field that is :
Code : AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE SerName='" & [SerName] & "'" & " ORDER BY NormID")
but [SerName] is a text type field that is not Primary Key.
I have in the FamilyTbl , [ServiceID] is numeric type field, and a primary key. I try :
Code ; AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE ServiceID='" & [ServiceID] & "'" & " ORDER BY NormID")
but this returns all [NAll] records. I use concatenate :
Code:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ", _
Optional pstrLastDelim As String = "") _
As Variant
[Code] .....
View 14 Replies
View Related
May 28, 2007
Hi all,
I've got a database with a members table and an events table and a participation table which is like an 'intermediary table' which just has fields for MemberID_fk and EventID_fk.
The form at the moment has a combo box for Members and a combo box for events. This works well but the problem with it is that there is no safeguards to prevent a member from joining the same event many time.
Ideally i'd like for the combo box of events to be updated once the member's been selected to only include events the member has not signed up for.
If anyone's got any suggestions i'd love to hear them. The only constraint i have is that this has to be prevented when it is entered in the form.
Cheers,
Joe
View 3 Replies
View Related
Sep 12, 2014
I have several combo box fields in a bound form where they are selecting values from a list (values stored in a separate table) and then loading a number into the bound table field when selected.
How can I put a text prompt in these fields when loading the form which gets removed when focused and of course is not permitted to attempt a save into the bound numeric fields? I've done quite a bit of searching but can only find materials about doing this on bound text fields. I've also seen solutions using Nz which don't seem to work.
View 4 Replies
View Related
Jan 8, 2007
Hi friends,
I am new to this forum, and I am facing a problem while learning MS-Access.
I am preparing a database that stores the marks obtained by students in a particular paper in addition to their particulars in tables.
A query is then generated to canculate the aggregate marks and result (Pass/Fail) for the student. It is then presented in a report fomatted as a marksheet, as well as one tabulation chart.
The database is very crude in format, but I am happy with it since I do not know programming at all, and still I could make it.
I have used in built menu of "ms-access" and mouse clicks only for generating query. It was a learn by doing method.
The problem is that my marksheets require absent students to be marked as "ABSENT" or something of that kind. If I convert my Null values to :
Expr2: IIf(IsNull([E_1]),"ABSENT",[E_1])
It shows ABSENT for null value, but refuses to make column total for E_1; On the other hand if I do not enter such expression, it makes column total in tabulation report, but fails to mark "ABSENT" to those who did not appear. So I have to lose one thing to gain another, while I need both.
Can anybody help me at this?
pc
View 5 Replies
View Related
Aug 15, 2013
Access 2007 - I am using the oft used, frequently posted and shard code from srfreemen - modified as below. now I know I sort of broke commandment # 8 - Thou shalt not copy and paste other people's code without at least attempting to understand what it does. but I honestly did try to understand what it does.
I keep getting runtime error 3078 - cannot find the input table or query 'tblEnrolment_Committee_Master'. It is the name of a table in my database but I am missing the very basic element of how to get my form (which draws all of it's fields from that table) to look it up or identify it.
Likely because of a silly basic error. Normally - I wouldn't attempt such things but my work needs me to ensure no duplicates exist in this case AND it's already a primary key - but I don't want to wait until the whole form is filled out for it to identify the duplicate!
How to get the tblEnrolment_Committee_Master to be included in the search and erase this error ...
Private Sub Entitlement_File_Number_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
[Code] .....
View 3 Replies
View Related
Dec 23, 2014
I've been playing around with a new database design and ran into a possible 'error' that I would like to avoid.
It's going to be a payroll database to store time codes for hours spent working on specific projects. I have been struggling on how to put this together to fit with what we've been doing for years and I think I hit a few breakthroughs this morning.
However I want to avoid this error of possible duplication of entry.
Simple table set up - primary key is just a running integer; Employee ID; and Week Ending Date.
I can have multiple week ending dates for a specific employee; but I want to avoid having the same employee with the same week ending date. I cannot set up either field as being unique.
Quick run of data that would be in this table:
Code:
1 ABC 11/21/2014
2 ABC 11/27/2014
3 ABC 12/07/2014
4 DEF 11/21/2014
5 DEF 11/27/2014
6 DEF 12/07/2014
7 ABC 11/27/2014
in this example, when the last row is entered I need to get a popup or some warning that this time has already been entered.
View 13 Replies
View Related
Mar 11, 2014
I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.
The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.
Code for automatic number generation is:
Dim dbs As Database, rst As Recordset, Response
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _
[Code]....
My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?
View 2 Replies
View Related
Feb 25, 2015
I have a small problem with dlookup multiple criteria. Vba code looks like this:
Code:
Label34.Caption = DLookup("[Spent_Hours]", "249_1_CHours", "[Date_Added]= " & Me.Text27 & " And [Shift] = '" & Me.Text29 & "'")
This gives following error:
Syntax error in number in query expression '[Date_Added]=4.02.2015 And [Shift] = '2'.
[Shift] column is a numeric field.
View 14 Replies
View Related
Aug 15, 2014
I am using the following code to check for duplicate tickets when importing multiple records into a datasheet view form by using the paste append function.
Code:
Private Sub Ticket_Number_BeforeUpdate(Cancel As Integer)
DoCmd.SetWarnings False
If DLookup("Ticket_Number", "Record_Store", "Ticket_Number= '" & Me.Ticket_Number.Value & "'") > 0 Then
Cancel = True
MsgBox "There were import errors, please open View Import Errors above."
End If
End Sub
The form is used to insert multiple records into the database at a single time.
That codes works to check for duplicates. And if there are none there are no popup messages.
If there are duplicates though it gives a popup for every single Ticket_Number that is a duplicate.
I am wondering if there is a way for it to give only a single popup once it completes checking all the records to be imported for duplicates.
View 14 Replies
View Related
May 6, 2014
I have a form with 15 unbound text boxes (daily temperatures) and what I am trying to do after entering the temperatures into the text boxes the user clicks an add button which will add 15 new records into the temperature table
the code I have started off with is
Code:
CurrentDb.Execute "INSERT INTO ColdTemperatures (ProductID, ColdTempDate, Temperature) VALUES (" & Lettuce & ", #" & Me.RealTime & "#, " & Me.Lettuce & ")"
which adds 1 successfully however if i repeat the code above for all 15 this Im assumming will create a potential bottleneck and slow the system down
is it possible to add all 15 records at once? do you think Im going at this the right way
View 5 Replies
View Related
Aug 1, 2013
I am trying to build a newer database 2010, based on an older one,2000, that has been locked tight and I cannot see the modules to kinda get a reference of where to start. I am trying to find a VBA code that will allow me to import a several text files to one table. The text files are all in the same format but I cannot remove the page headers and footers to get the table to look right. I have attached an example of the text file i am trying to import but it is a stripped down version for information protection.
Also, it appears in the old Database Table once imported as:
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
If I could import the text files and end up with a table like this, it would be all i need as i could run all the queries i need from this.
View 3 Replies
View Related
Oct 28, 2013
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String
my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _
& " FROM Table1 " _
& " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _
& "FROM Table2 " _
& " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
View 7 Replies
View Related
Feb 22, 2005
Hello everyone.
It has been many years since I played with this stuff and I probably wouldn't be now, if not for an emergency.
I know there is probably an example here that all ready explains what I need, but honestly, I am not real familiar with the terms and wouldn't know where to begin looking for it.
I am old and don't intend to make a career out of this, I just need to fix a database. We had a bookkeeper at our small business who, for years, maintained our mailing list. It was her own design, though she knew nothing about it and learned as she went along. We never interfered because she did her job flawlessly in her own little, confussing round-a-bout way.
She is gone now and we have to make heads or tails of this. We decided the quickest and easiest way was to blow the old db away, use as much of the basic fields that we could sacrifice and start over. It's just a simple mailing list, but it contains over 9000 records.
Her method of entering records was from the table view. Yep, starting a new line at the bottom of the table and then entering the 94 fields of information that applied to the new record.
I have created a form today that does this now and simplifies this process.
Her method of preventing multiple records, was to scroll down the table and see if she had already entered the record previously. This is my question.
My first approach to resolving this issue in my new form, was to create a ComboBox on the form to do a lookup using Last and FirstName. Due to the fact that this ComboBox will need additional fine tuning that I don't understand, when I use it, it does auto-complete the last name "Anderson" as I type it and it highlights the first "Anderson" record in the db, but It doesn't do any sorts in this same ComboBox to bring the rest of the "Anderson" records to the top so I can then check for a matching FirstName. I'm sure this requires changes in the property of the ComboBox that I don't understand.
Or, maybe I shouldn't even be using the ComboBox.
Actually, I would bet there is a way that I can alter my table so that it would not allow me to put in a duplicate record and therefore, eliminating the need to even look anything up.
Any ideas or direction with this would be greatly appreciated. Since I am only the person creating this and not the person(s) that will actually be using it, I should find a method for this that will be simple for anyone adding records.
Hopefully, in a day or so, I can be done with this and get back to my real job here as a mechanic, not a programmer. :eek:
Thanks again in advance.
View 3 Replies
View Related
May 4, 2005
Hi
I have a form and sub form, the sub form is populated from a query and has a column of checkboxes. Basically these checkboxes get checked as part of the form, but only a maximum of five are allowed to be checked. Is there a way of achieving this?
Cheers
Dan
View 1 Replies
View Related
Jun 23, 2015
im working on a report at the moment that will generate a standard NIP (nutritional information panel, similar to whats on the back of pretty much all packaged food products, see link below for an example)
[URL]
this in itself is pretty easy, however depending on which market we're selling to, different countries have different requirements for which data is to be shown, ie some markets might require we show how much sodium but not how much fiber, others might require us to show how much carbs and sugars but not how much vitamin C and so on (all up there are 16 different variables for nutritional info, due to size requirements on our packaging we cant simply display all 16 all the time)
at the moment im designing a form which has a drop down box (for the user to select a product) and checkboxes for each of the 16 variables (kilojules, carbs, iron, trans fats, sat fats, etc) following this ive got a report that has all 16 variables on it
i can make the variables that arent ticked on the form invisible (by adding a bunch of IF or Case statements, on print of the report, that determine whether the corresponding checkbox for each variable has been ticked and setting the "visible" attribute to true or false accordingly), however when they are invisible there is still a blank space where the field is meant to be ive got all the fields grouped together, is there any way of making fields invisible and then pushing all the visible fields together so there are no blank spaces?
View 3 Replies
View Related
Apr 24, 2013
A form displays information on a construction site in various text boxes. I want to enable the user to change this information but not until a save button is pressed.
Now I have the problem that as soon as I change the value of a text box, the data in the database is updated.
Is there a way to prevent these updates but still get the text fields to be linked to the attributes of a table?
View 2 Replies
View Related
Jun 20, 2012
I am building a database through access 2003 and have included a text box labeled "comments" in my design view. Now when i go to input information in the "comments" column in the datasheet view, the same text will appear for every entry. If I try to change the text in the next row, it will automatically switch all of the previous "comment" text to whatever I have entered. How do I fix this?
View 13 Replies
View Related
Aug 8, 2006
Please bare with me as I am an Absolute Access VB code novice . I have a Form in my Access DB and I found the Following code on this site and placed it behind my search button :
Private Sub SearchButton_Click()
DoCmd.OpenReport "All users", acViewPreview, , "[AUP].[First Name] = '" & Me.[Text34] & "'"
End Sub
The Search works great but only searches on the 1 Field "First Name" . How would I expand the VB code to include another 2 fields within my table . ( the other 2 fields I want to serach appear on the Form ) .
View 4 Replies
View Related
Nov 14, 2014
In 2002, I set up a database with Access 97 which included a table with a memo field. Through the years, the database has moved from Windows XP and Access 97 to Windows 7 and Access 2007.
Recently, the memo data has been difficult to edit (the display will move away from the cursor, or a new record will be inserted).
It appears to be logically straightforward to convert the memo data into multiple text fields as the memo field can be parsed for dates of entry.
View 3 Replies
View Related
Feb 17, 2015
I am trying to do is to make a for loop to insert multiple text fields in on table.
Depending on the counter (Zaehler) it should insert that representing text field. for example if Zeahler is 1 it should input whatever is inside the text field KVP_Kfm1 if its 2 then it should input the textfield KVP_Kfm2 and so on. here is the code that I'm trying to work but sadly it wont.I believe that the mistake is that my syntax is wrong but i cant figure out what is right.
Code:
For Zaehler = 0 To (Forms!frmCMP!txtAuslaufjahr - Forms!frmCMP!txtAnlaufjahr)
SQL = "INSERT INTO tblLifecycle_Projektion(ID_Berichtstand, KVP_Kfm, KV P_technisch, AeJ, MoPf, skAe, MiBst, Sonstige_Effekte, " & _
"KVP_technisch_FTR, KVP_Kfm_FTR, AeJ_FTR, MoPf_FTR, sk Ae_FTR, Sonstige_Effekte_FTR, Jahr) VALUES " & _
[Code] ....
View 3 Replies
View Related
