I have a booking form database
With a date field.
I want users to be warned when they try to enter a date that already exists in the database in order to avoid double booking:confused: .....
all suggestions appreciated:)
I've got an incrementing invoice number that is just a +1 of the last invoice number. Had a problem today where one user was creating an invoice and took a while to complete the record. As soon as the record was created the invoice number went in as 123 but the record didn't save until she had filled out all the form. In the meantime someone else started to create another invoice and ended up with 123 as well because the previous record hadn't saved. Does anyone have any suggestins how I can stop this from happening? I can't save the record straight away, which would be the obvious answer as they need to fill out quite a few required fields before the record is saved so that they don't have incomplete records.
Thanks
DBL
hello,
Currently i have a database that holds information about computers. Each computer has a Service tag and is associated to a person. I need to be able to prevent duplicate service tags from being entered. I am using indexing and don't allow duplicates from the table design view. But, if a duplicate service tag is indeed entered, i want to inform the user who that service tag currently belongs too without having to search through the DB manually. I need then to give the user the option to delete the duplicated record they just entered or change the service tag that they just entered. Thanks.
Jared
I've set up a booking system and am looking to stop the system recording double bookings.
I've 3 fields, RoomID, Date and Time that need to be searched, however I can't just simply set the properties to No Duplicates (as this would mean a booking cannot be made on the same day at 9:00 and 12:00 for example).
How can I do this?
Hi again, thanks to all for the help given so far.
Is there anyway (other than making that field a primary key) to prevent the same value being entered into a different record?
E.g.
If under 'Name' Radion has already been written, I want an error message coming up saying there is already a record with radion.
I am running a query to find out duplicate in two fields in one table. Postcode and Address Line 1. Can anyone tell me the formula for only picking up the first 6 charaters in the Address Line 1 field??? I cant remember how to do it!
HELP!
I am using information pulled from a separate database to create a new database for mail merger purposes. I have created delete queries that go out and delete specific records from the new database to help us get the proper list before setting up the mail merge. However, now I am stuck. We have customers that have multiple accounts with us that are qualified for this mailing, yet each customer needs to receive only one mailing. The table looks like this essentially. Table 1id statCode acct custNumber* addy-etc. *This is the field in which we can find exact duplicates. What I need to do is remove the duplicate entries and leave at least one of the entries within the table. It does in fact need to remove the whole record, but as I said leave at least one of the records behind. Can someone please help me to find a solution to this. I guess I should add that I am not very good with Access and I use it very sparingly. I assume that the above can be accomplished using a SQL statement. Any help will be greatly appreciated. LR**EDIT** I also needed to add that I do need to exclude 000 00 0000 from being removed because of duplication.
View 6 Replies View RelatedIs there a way to find and remove duplicate entries in tables by using queries.....if so, how specifically?
View 1 Replies View RelatedHi all,
I am currently using this code in the before Update event of the Surname Textbox on a form. The first Textbox is FirstName.
The second one is Surname.
************************************************** **
If (Not IsNull(DLookup("[FirstName]", _
"Employee", "[FirstName] ='" _
& Me!FirstName & "'"))) And (Not IsNull(DLookup("[Surname]", _
"Employee", "[Surname] ='" _
& Me!Surname & "'"))) Then
MsgBox "Someone already exists with the same name! Please check for duplicates", vbCritical, "IPDMS"
Me.Undo
End If
************************************************** **
However once the user has entered the surname and tries to save the record the MsgBox is coming up.
Basically what is happening is the first name is coming up as the duplicate entry only.
I need to prevent the whole name (first name and surname together) from being duplicated.
Thanks if you can help me.
Hello,
I'm new to using VBA for MS Access. May I enlist your help, Please.
I'm working on a human resources database for a group of employees. I'm trying to automate their login process after they have opened the database so that each employee's privacy is respected. Using an initial form, employees are separated into "New User" or "Returning User". My problem concerns setting up the "New User" in the database.
The New User is directed to form "frm_NU". The form has several unbound fields which are later carried to the table "tbl_Employees". Two of the fields on the form are used to create the EmployeeID. Those are Initials [INI] and Phone Extension [EXT]. The EmployeeID will be used in a filter so that only that employee's information is made available to the user (simulated Privacy).
Because the employees are unfamiliar with MS Access and its cryptic error messages, I need to create a message box which will give a plain English warning when a user attempts to use an existing ID to create a new record. [EmployeeID] is a field in the table. it is set as a key and does not allow duplicates.
When a new user enters his/her initials and phone extension I want the new ID to be compared to the table and if found, a message should appear. This is a preemptive strike to prevent the user from getting to the more cryptic MS Access error message.
I've tried using Dlookup and Dcount to try to pass some meaningful data to the If statement variable VarX to trigger the message box; assuming that Varx should be something other than Null or Empty
Dim EmpID As Variant
Dim VarX As Variant
EmpID = [INI] & [EXT]
VarX = DLookup([EmployeeID], "qry_Employees", [EmployeeID] = [EmpID])
If VarX <Is Not Empty> Then
X = MsgBox("Another record already exists by that name", vbOKOnly)
Else
Y = MsgBox("Passed", vbOKOnly)
End If
I keep getting an error at the DLookup formula or DCount formula saying: Run-time error; 2001 You canceled the previous operation. It also appears that the DLookup never looks up anything nor does DCount count anything even though I'm purposely entering duplicate information which exists in "qry_Employees". I was expecting VarX to be either the duplicate ID or a record number or record count or something.
Any ideas or constructive suggestions?
Thanks,
Goh
hey,
sorry to bother everyone again, this is such a great forum, and ive gotten soo much help here.
i have a customer table, containing custRef, custName, drawerRef, Address, Phone, Fax
now it contains site addresses so the custName can be the same for quite a number of entries. no the combo box in my form obviously shows all entries for custName which means the same name repeated sometimes 6 or 7 times, as i only need to select one, is there a way without looking to different tables etc to just show unique entries. i dont want to list them all in another table because i dont know them all yet, so i want it to lookup the list as i enter them.
cheers
Baz
Hi,
I have a rather large database that was built in Access '97 and recently converted to 2003. I have a couple problems now. I get an error message about not being able to update the form because it would create a duplicate value in the primary key, index, or relationship. (Error 3022) I didn't build this particular database so I'm not totally sure why somethings are done the way they are. I've spent a long time going through everything to try to understand the relationships and I've asked lots of people at work to look at it, but no one seems to understand why it's doing what it's doing. The Access Help information has helped me understand some things better, but not this problem. I've gone through every table and removed all primary keys and set all indexes to allow duplicates. Then, when I was looking at the relationships and object dependancies, I think everything looks to be in order. However, when i add info directly to the table instead of using the form, the form is not updated with the new info. the table keeps the information, but isn't communicating with the form and i suspect reverse is true which is where the error is coming from. But how do I detect that for sure and how can I fix it with out starting over? I've gone through and added data to one field in the form at a time to try and narrow down what field is causing the problem, but no matter what i get the same error. Is there a way to find out exactly what field is causing the error? Thanks for any help you can offer!
Alicia
Hi everyone,
I'm aware of the wizard in MS Access that creates a new table containing duplicated entries that have been entered under one column, however, my problem is slightly more complex...
A contractor of mine has recorded information from CCTV surveys of sewers (not very glamorous I know!) in a Microsoft Access database. If you can imagine - the camera can be pushed along a pipe from both ends. Sometimes, the survey is abandoned in one direction and then repeated along the same length of pipe but from the other direction. The database contains (amongst other columns) a "start manhole" reference and a "end manhole" reference.
eg:
16014
As you can see, the information contained by both entries will be the same (as they are the same length of pipe). Therefore, I was wondering how I could get MS Access to:
1. Recognise these 2 entries as duplicates
2. Create a table that hides the duplicated entry with a shorter "SectionLength" value.
Many thanks in advance.
I'm using a pretty simple inner join on two tables but the some of the same results are showing up twice. If I just remove the inner join all the results are uniquely present. For some reason the inner join creates about 10% more records that are all duplicate entries. Any idea what might cause this? :confused:SELECT products.product_nameFROM products INNER JOIN categories ON products.category = categories.category_nameWHERE ((products.X)=True);
View 2 Replies View RelatedHello
I am trying to get a 2nd combo working from the first .
I have a table called Keystage (tblKeystage) and I have also named my 1st combo cboKeystage. then by following the vba below I created another 6 tables with corresponding names and combo's.
Private Sub cboKeystage_AfterUpdate()
On Error Resume Next
Select Case cboKeystage.Value
Case "Foundation"
cboYear.RowSource = "tblFoundation"
Case "KS1"
cboYear.RowSource = "tblKSOne"
Case "KS2"
cboYear.RowSource = "tblKSTwo"
Case "KS3"
cboYear.RowSource = "tblKSThree"
Case "KS4"
cboYear.RowSource = "tblKSFour"
Case "Post16"
cboYear.RowSource = "tblPost"
End Select
End Sub
The combo boxes both work, brilliant. My problem is that when I enter another record and make a selection from the 1st combo box, it changes all the records.
Could somebody please help.
Thanks
Wong
Hi all,
I have imported a table from Excel. The table contains a long list of names and there are quite a lot of duplicates.
How do I remove the duplicates so I can import the clean list into a value list in Access ?
Thanks in advance !
I have a table that contains doctor information including their license number. This came from a worksheet that tracked referrals from last season. I need a query that will show me only those record that have a unique MD License number. In other words I only need to see the 5-600 doctors out of the 2420 referrals in the table. Once I get the query of unique MD's I can create a report for the salesmen to use to contact them for the upcoming year.
I know this is probably simple but I can't find the solution.
Thanks in advance.
I'm trying to use access to identify how many times an indiviual appears in my database. I've got a large file which gives me all referrals, each referral comes with two identifiers, one unique to the person, another unique to the referral. This means that the person unique number may be repeated multiple times (if a person has more than one referral) but the referral number would be different each time.
I've run a query to identify the duplicates, i.e. the records where the person unique identifier is repeated. What I am trying to do is work out now how many people have been referred twice, how many three times etc. The original query looks something like this (but on a much bigger scale).
Person Referral
1 A
1 B
1 C
2 D
2 E
3 F
3 G
3 H
What I'm after is a query that will tell me that two people were referred three times (1 and 3) and that one was referred twice (2) and so on.
I'm on Access 2010 if it makes any difference.
I am attempting to make a system where the lifespan of baskets in a production process can be tracked. In order to do this I am looking to create a form to input when a new basket is introduced.Each basket has a 'disc' associated with a number on (BasketNumber), however these discs are reused once the basket breaks which is making it more difficult.
I have a form (Add_New_Basket) which feeds into a table (Active_Basket); what I am trying to do is make sure that a new basket cannot be introduced with the same disc number as an active basket. The way I am trying to determine which baskets are active is that I have an end date, that is filled in once the basket has been scrapped.
Therefore I am trying to set my Add_New_Basket form to restrict me entering a new record with BasketNumber 999, if there is already a BasketNumber 999 in the table with 'EndDate' null. If there is BasketNumber 999 and EndDate is dd/mm/yyyy then it can create the entry.My form Add_New_Basket has fields ID (which is hidden), BasketNumber, StartDate (=Date()).My table has fields: ID, BasketNumber, StartDate, EndDate
I've got a combo box that lists duplicates. I want the dupes removed.
I went to the Query Builder for my Combo Box and set Unique Values from "No" to "Yes" - Saved, then re-opened the form. It still lists dupes.
I went back into Query Builder and unchecked the box for the Patient_ID (Autonumber) field. I saved it, then viewed the Datasheet for the Query, and it correctly lists the data I want WITHOUT dupes.
However, when I go to Form View, the combo box lists several blank entries..
The entries come back when I re-enable the Autonumber field in the Query.
This is what I currently have...
SELECT DISTINCT Patients.FullName FROM Patients ORDER BY Patients.FullName;
I'm pretty new to Access, so please overlook any stupid questions :)
I have a data entry form that has one field that cannot be a duplicate. So, in the table I have it Indexed with No duplicates.
The problem is that the form doesn't notify the user that they have entered duplicate information until they close the form. So the 10 minutes they spent filling it out is wasted.
So, after consulting various posts, I did a BeforeUpdate event with the following code:
(the field that must be unique is called "LS Number", in the form "Q and D" based on the table "Q and D Database")
Private Sub LS_Number_BeforeUpdate(Cancel As Integer)
On Error GoTo LS_Number_BeforeUpdate_Err
If (DLookup("[LS Number]", "Q and D Database", "[LS Number]=Forms![Q and D]![LS Number]")) Then
MsgBox "The LS Number you entered already exists. Enter a unique LS Number", vbInformation, "Duplicate LS Number"
End If
LS_Number_BeforeUpdate_Exit:
Exit Sub
LS_Number_BeforeUpdate_Err:
MsgBox Error$
Resume LS_Number_BeforeUpdate_Exit
End Sub
When I test this by entering a duplicate LS Number, it gives me the error "Type Mismatch." Unfortunately, I have no idea what this means, nor do I know how to fix it.
Please help, my head hurts!
I have a problem where in a table called tblPatientLanguage
that contains three fields
PatientLanguageID
PatientID
LanguageID
a patient who is identified by PatientID can speak several languages
identified by the LanguageID.
The problem is with my current form and code
For Each varSelected In Me!lstLanguage.ItemsSelected
rs.AddNew
rs("PatientID") = [Forms]![frmPatientDemographics]![PatientID]
rs("LanguageID") = Me!lstLanguage.ItemData(varSelected)
rs.Update
Next varSelected
when the user click on the command button called cmdOK it will put the
data in the table "tblPatientLanguage" and corresponding fields. without
checking to see if there is already a duplicate languageID that corresponds to the
same patientID in the table
This has lead to duplicates in the table
example
PatientID = 9
if I look in the table I will see 7 entries that say
PatientID = 9 and LangaugeID = 7
I want to prevent this from happening with and if statement
I could make both the PatientID and LanguageID primary keys
this would prevent this from happening. But I would rather do it in
the VBA code? This way in will move thorugh the selections without poping an error and the person inputing the date. Any ideas sorry for being long winded. I attached the
database.
PS. If looking at the database you have to use form frmPatientDemographics and click on the language link
I have produced a label printing query which eliminates duplicate addresses from a mail list. It works beautifully.
However, when I try to replicate with a different field criteria the new query fails to produce any output at all.
The SQL code I am using is as follows:
SELECT a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-Mail List]
FROM [Mail List] AS a
GROUP BY a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-Mail List], CStr([a].[Member ID])+[a].[Address 1]
HAVING (((a.[e-Mail List]) Is Null) AND ((CStr([a].[Member ID])+[a].[Address 1])=(Select cStr(Min(b.[Member ID])) + b.[Address 1]
From [Mail List] as b
Where b.[Address 1] = a.[Address 1]
Group By [Address 1])));
The working version has 'HAVING' e-Mail News =False instead of e-Mail List is Null. The former searches for an empty check box and the latter for an empty field.
I know my way around Access reasonably well but am by no means an expert. I have created a system that I use in my business for hardware/software requests, and was told to do it in Access. I did ask that we do it on SQL Server with a Web Front End, but we are where we are.
Now, I have been on a SQL query training course so I know the basics, but am a bit confused on this one since Access has been added to the mix.To make matters more frightening, this is Access 2003!
My tables are as follows:
[T_Request]
AT Reference (primary key, autonumber)
Forename
Surname
UserID, etc etc
[T_Hardwarelist]
ID (primary key, autonumber)
Make
Model
Description
[T_UserHardware]
AT Reference
ID
[Request] is inner joined to [UserHardware] on the AT Reference column.
[UserHardware] is inner joined to [Hardwarelist] on the ID column.
The select query I have is basic and does just what it says on the tin; it shows who has what hardware.The query:
Code:
SELECT T_UserHardware.[AT Reference], T_UserHardware.ID, T_HardwareList.Make, T_HardwareList.Model, T_HardwareList.Type, T_HardwareList.Description
FROM (T_Requests INNER JOIN T_UserHardware ON T_Requests.[AT Reference] = T_UserHardware.[AT Reference]) INNER JOIN T_HardwareList ON T_UserHardware.ID = T_HardwareList.ID
WHERE (((T_UserHardware.[AT Reference])=[Forms]![F_Request]![AT Reference]));
However, when it comes to adding NEW hardware to the requestI have a form called F_Request.I have a sub form called SF_Hardware.The SF_ Hardware subform runs the aforementioned query, and shows what hardware is assigned to the parent request.If I add new hardware via a dropdown on the form, it adds it in to [T_UserHardware], but it also adds another value on the [T_Hardwarelist] table. E.g. if I add a "HP Compaq 8200 Elite" (which is stored in [T_Hardwarelist]) it adds it to the [T_UserHardware] table correctly, but creates a second (third, fourth, fifth) entry on the [T_Hardwarelist] table for the same kit.
I think its confusing because of using a select query and might have to run a 2nd query on save or something like that, I had a working version before that had the make, model etc in both tables and didn't have a 3-way relationship. It'd be nice to have the request table with the high level info, a hardwarelist table with our catalog of kit, and a userhardware table just containing the ID and Reference for the hardware/request rather than duplicating the information.
I have a mainform (F_main) and a subform associated (F_place).
F_place is the form of the table T_place.
T_place is linked to F_main by the field "BizNumber". I also have the field "Place" and of course the "Place ID" (primary key) in T_place.
"Place" in linked to a combobox (in F_place).I want to avoid, at a given "BizNumber", the same "place" to be entered in the F_place.
If different people will be entering data into a "new donor" form, how do I keep them from adding the same person a second time?
It's possible people could have the same name, and it's possible that two people at the same address could be donors. So I assume I would want a "no duplicates" on the combination of name and address, right? But how to actually do that.