i have a table called tbl1. in this table i have field month, year, or trainee. is it possible that above mentioned three field found same data table automatically delte those rows. so i found only unique data. or their is another way to make this possible.
View RepliesI've been trying to use some code I've found to prevent a user from entering a duplicate ID. I've tried this:
Dim Answer As Variant Answer = DLookup("[SAR_ID]", "Request", "[SAR_ID] = '" & Me.SAR_ID & "'")
If Not IsNull(Answer) Then MsgBox "Duplicate SAR ID Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
Cancel = True Me.SAR_ID.Undo
Else:
End If
but it displays the message even if I don't enter a duplicate.
Attached is my many to many relationship setup. I would like to prevent the possibility of entering the same person more than once for the same training event. I am assuming that I do this by setting an index setting for a field the junction table?
View 3 Replies View RelatedTable in my access 2010 is configured to have a unique records (no duplicates)which has now records more than 2000 so i copied the table and pasted Structure only. what i would like to have is that new table which presently is empty should not add any record which is already available in old table. While entering data in new table i would like users to see the error if they try to enter the record which was previously entered in old table.
View 8 Replies View Relatedthere are many threads about Table Linking but i coud not find one that answers my specific questions.
1. Can i either prevent database from linking to my tables? (or give permissins )
2. If not, can i make the table read only when linking?
3. Is there a way to confirm what databases are linked to my tables?
When I join three tables I get duplicated records in Access SQL. How can I solve this? Using distinct is no options.
View 4 Replies View RelatedIs there a way to prevent Access from the absurd notion it needs to concatenate 'DBO_' as prefix to tables linked from SQL Server db? :mad:
Thanks.
I'm learning how to do Many to Many relationships and I think I've got the basic idea down, I am just not sure how to prevent the same 2 records from being associated twice.
For example:
My practice database deals with Authors and Books.
I was able to set it up so that Mike Gunderloy and Susan Harkins are both Authors of the book: "Upgrader's Guide to Microsoft Office System 2003".
I was also able to set it up so that Mike Gunderloy and Susan Harkins are both Authors of the book: "Automating Microsoft Access 2003 with VBA".
The thing I want to prevent is something like this:
Book:
- Automating Microsoft Access 2003 with VBA
Authors:
- Mike Gunderloy
- Susan Harkins
- Susan Harkins (Duplicate)
And vise versa
I have a table called tblDeliveryOverview and a table called tblPurchaseOverview in the relationship there is no link between each other but i've used a query to link the one field from tblDeliveryOverview to tblPurchaseOverview.
How can i prevent a record update from happening when i update a record in tbleDeliveryOverview and the same gets updated in tblPurchaseOverview, which i don't want to happen.
I am creating essentially a contact database; however, I want to be able to upload new groups into the database from time to time. Some people in the groups may already be listed somewhere else. If I am using an ID number to identify each separate profile; how do I prevent a duplicate profile entry? Will I have to manually check each one? Also if I use the auto-number function, how do I tell access to create a new set of numbers that are different from ones previously assigned? I anticipate about 30 separate groups adding up to around 5,000 names. I don't really care to check that many manually.
View 1 Replies View RelatedI'm building a database at work to see if we can replace some of the "buhzillion" spreadsheets we currently use to track data on sites and employees across the country. I've set up the primary excel tables the way I want to import them, but Access keeps converting my Employee Numbers (mostly 7 digit numbers, "text" format in excel) to scientific notation during the import process and then giving me errors because my primary key "EmployeeNumber" has duplicates. How do I tell Access to import these as text instead of numbers? I'd really like to not have to type in data for 100+ employees and over 1000 sites, you know?
Thanks in advance.
In a simple Access 2003 database that has two table. A contacts table and a Report table.
I would like to be able to prevent the deletion of any contact that is still being used in the Reports table. The problem is that they are in a backend of a linked database. So the enforce referential integrity doesn't work. Plus I don't think I would like it anyway as it seems kind of dangerous to the reports table.
Is this possible to do what I need through the use of form coding?
I've just come accross a problem where pasted data dissapears from view. It's caused by people being a bit careless and copying the line above (from word or notepad for example), which adds a return and then the data drops out of view.I really want to create a validation rule to make it impossible for returns to be pasted but I'm not sure how.
View 2 Replies View RelatedI have built a database and within it my queries I use for reporting make tables in a separate database. This allows me to work in my data base and update info as needed. My problem is that my make table queries wont run if anyone has one of the report pivot tables open. I have added macros to all the Excel files that pull from the reporting DB but I still have several times when I cannot update because someone is using the pivot table, Is there a way to allow me to update the report DB's while the pivot tables are open? I have tried using manual refresh methods and disabling auto update on open but the result is the same.
View 2 Replies View Related1. I have a database (see attached) with three tables all of them with the same fields. The first three are numbers (InCo_No, Proto_No, Year_No). Each of these fields (numbers) can be the same in the other table(eg. Year_No), but the combination of the three cannot be.
How can I prevent the entry of a duplicate combination of these three fields?
2. I want to have a form to fill the three tables separately, depending the values in the other fields.
How can I do this?
I have set up a database that each user will put on their own computer, as it builds a running list made specifically for that user of chosen items to be exported to Excel, and because the remote locations have issues when accessing a shared network.
The db verifies the network user ID against a table to allow it to open. This to prevent any unauthorized user from accessing, and if db was copied or forwarded, only authorized users could see the data.
If the user is not on the list, it advises "Not an Authorized User", then exits Access. (I disabled the shift on opening, hid navigation bar, etc.) When further testing this security, I realized that even though users can access only the forms that I allowed, if they were to open another Access db, they could link or import the main table.
So all of my security precautions are for naught, as anyone with access to the db could see the confidential data. Even if they were not valid users, they could still get to the data if they knew how to import from another db. Is there any way to lock down the main table, so that it cannot be exported? Or be imported into another database?
I don't want to allow more than one instance of (HolDte) for the same EmployeeID
PHP Code:
If DLookup("[HolDte]", "tblHour", "[EmployeeID]='" & Me.EmployeeID.Value & "'") <> Me.HolDte.Value Then
Exit Sub
End If
I have a combo box on my form which loads fields from a table and displays them using
Code:
SELECT DISTINCT table_team.team FROM table_team;
I then use
Code:
=[qry_showteamforedit]![team]
in the default value for the combobox to show the team which is saved in the current record.This is the qry_showteamforedit:
Code:
SELECT table_team.team
FROM table_team RIGHT JOIN table_staff_details ON table_team.ID = table_staff_details.team
WHERE table_team.ID = table_staff_details.team;
My problem is when I move through the records, if I change the selected value using the combo box it changes the actual value in the table from the one that was selected to the new one. If I was on record 1 and the teamid saved in there was 1 . It would display "team one" but if I changed that to "team two" it would change record one to say "team two" instead of "team one".I have been searching and found that this is because it is bound to the table so need to remove the text from Control Source, which when I do, breaks it, and it doesn't display the saved team.
what I would like it to do is display all the teams, but default to the one saved by using the id saved in the main table, but allow me to change this value. I would also like a second cascading combo box which will display a list of subteams dependent on what main team was selected and again, default to the values saved in the main table. I have managed to get cascading combo boxes working but combining them with my tables and queries is proving difficult. This is how my tables would be ( just showing the relevant fields)
Staff_table
ID Name teamID
1 Dave 1
2 Tom 1
3 Matt 2
team_table
ID team subteam
1 team1 subteam1
2 team1 subteam2
3 team1 subteam3
4 team2 subteam4
Is it is the subteams that will be unique I would like to save the subteam ID to the teamID field of the staff_table. that way i can retrieve the team and the subteam using the same ID.
1) HOw can I move a record from one talbe to another when a specific drop-down option is selected. Example: a combo box with "Active"; "Pending"; "Closed" I want all closed recoreds in a table just for closed records to reduce clutter. They're not going to be referenced often if ever, we just want them so we have them if we do need them.
2) How can I prevent changes to the design of a form/query/report/etc.
I am using Access 2010. How do I prevent the object typed into the textbox on a queryform being written to the table. The result from the name typed into the textbox on my query form correctly produces the result from the query, and my macros then produce the correct report, which I can either print or close due to the controls in the heading of the report. However, when I view the table, the name typed (only) has been inserted into the correct field as a new record in the table. Is there a macro I can add (I assume to an event in the query form) to prevent this happening?
View 8 Replies View Related I'm creating an equipment hire database, and I was wondering how I would not permit overlaps of equipment hire ie. equipment being borrowed before it is returned. I have a query that lists all bookings that overlap, but I am unsure of how to implement this into the table to prevent overlaps.
The SQL for the query is as follows:
SELECT Bookings.[Booking ID], Bookings.[Instrument ID], Bookings.[Borrow Date], Bookings.[Return Date], Bookings_1.[Booking ID], Bookings_1.[Instrument ID], Bookings_1.[Borrow Date], Bookings_1.[Return Date], ([Bookings_1].[Borrow Date]>[Bookings].[Return Date]) Or ([Bookings_1].[Return Date]<[Bookings].[Borrow Date]) Or ([Bookings].[Instrument ID]<>[Bookings_1].[Instrument ID]) Or ([Bookings].[Booking ID]=[Bookings_1].[Booking ID])
[Code]...
Thank you for taking the time to look my question. Hopefully I can be pointed in the correct direction.
I am trying to stop duplicates in my customer table, the table is as follows
CustomerID PK
Salutation
Surname
Address1
Address2
Address3
City
PostCode
Phone
Cell
I have the surname, address1, City & Phone number Indexed(Duplicates ok)
but if the combination of all 4 fields match then I want this to be not allowed.
I guess maybe I should make the primary key the combination of all 4 fields, not sure how to do that though.
Hope this makes sense.
Thanks
Hi all
In my client data entry form i have a key field clientid if a duplicate id is entered it allows the whole form to be completed and wipes entire form on exit, this is ok but how can i get an error message to warn about the duplication of id.
Thanks
Dave
I have a simple data entry form where I can key the "row", "COLUMN", SPACE "a" and space "B" onto an access 2010 table. Is there a way that if I key the same four fields, e.g., A5AB that I get a message back before updating telling me that combination already exists?
I do generate a calculated data field, which in this case would be "a5ab" and which is displayed on report screens. Could the value of this calculated field be stored on the Table and be used to prevent the same value from again be entered on the Table?
Hi,
On our database, we have a form that has 2 calendars. A start date and an End Date. The users fill in the name, and a couple other fields. Then select the start date, and end dates.
When they hit submit, the form enters into the table this info for each date in-between the start and end dates. For example if the users enters they will be taking vacation, then they enter 8/1 as a start and 8/10 as a end date. It will make an entry for each day. This works pretty well for us.
But I would like to improve it if possible.
Let say, a user a month ago made an entry that they would be work 8/23 at home. So in the database it has 8/23 at home.
Well, this week they decide they are going to take a vacation 8/21-8/25. So they make the new entry with start date 8/21 and end date 8/25. The form enters all the info just fine.
But if someone runs a report they see 8/23 at home, and also 8/21, 8/22, 8/23 ect on vacation. So gets a little confusing to where they actually are
So I was wondering if there is a way, for the database to prompt the person making the entry, that there is already an entry for 8/23 and ask if they want to delete it or save it? Then continue on creating the entries for the rest (8/24 and 8/25 in this example?).
Has anyone seen something like this? I was going to search, but not really sure what to search for on the forum.
Thanks
Hello, I am suing an append query to append new tables to an existing table. However, I only want to append the records that are not in the existing table. I have three variables: Category, Group, Project. None of them are unique. How to wirte this query.
For example: In existing Table:
Category Group Project
A 1 Pro1
A 1 Pro2
A 2 Pro1
B 1 Pro1
B 2 Pro1
Now I have two records:
A 1 Pro3
A 1 Pro1
I only want to append A 1 Pro3 to the existing table.
Thank you for your help.