Forms :: Prevent Deletion Of Contacts Needed In Other Tables
May 17, 2013
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?
View Replies
ADVERTISEMENT
Mar 24, 2007
I have a database with several forms, tables, queries etc. and it works fine. One of the users of the database accidentally deleted a record which was displayed by a query. I have selected AllowDelections = No in all object properties. Despite this, the record got deleted. Is there a way to prevent such deletions? Kindly help.
View 1 Replies
View Related
Jun 14, 2013
Been experimenting on some code to prevent deletion or changes to records that meet certain conditions.
The problem I am having is the code is contradicting each other preventing some of the records to be locked down.
Code:
Private Sub Form_Current()
'Code 1 - Prevent edits if 21 day period has lapsed
If TestDate2(Me.txtStartedHidden) > 0 Then
MsgBox "Editing NOT allowed - 21 day time period has been reached"
Me.txtBasic.Locked = True
[Code] ....
Rules are:
Edits allowed if invoice date not reached
Edits allowed if within 21 day but NOT if invoice date is reached
Edits prevented if invoiced date reached and if records still within 21 days
Edits prevented if invoiced date reached & records past 21 days
View 2 Replies
View Related
Nov 28, 2014
In my Membership Database (Access 2010), I have a Table entitled [Foreigners] in which are stored the names of prospective members of the organisation and the name and ID of the Interest Group they are proposing to join.
When a formal Application to join is received their personal details are recorded using an Entry Form related to the main [Mail List] Table.
The current procedure then is to manually delete the entry from the [Foreigner] Table and finally to record their Interest Group data using another Entry Form entitled [GroupMembers]. This relies on an operator remembering to do the necessary.
It occurs to me that this process could be automated. I wondered whether it could be entirely automated or would be best effected with a button on the main Entry Form, such that on completion of data entry of personal details the button would be activated to do the necessary deletion and addition.
The [Foreigner] Table is an entirely stand-alone table, having no relationship with the other concerned tables. The only common factor will be the Member Name, which in the main table is a concatenation of First Name & Surname..
View 8 Replies
View Related
Apr 29, 2014
Ok, I have 3 tables. One lists a contact in conjunction with the branch of the company and the trips that contact takes. The second lists a contact in conjunction with the branch of the company and which team they work with (may work with many). The third should list their email address and their phone number. Is it possible to pull the contact name and branch of company from tables 1 and 2 into 3 automatically, such that all I have to input into table 3 is additional contact information? If that is possible, is it also possible to only pull each contact/branch of company pairing once (I don't want four entries for Joe Shmoe/Sales, even if he's taken 4 trips)?
Table 1
Branch of Company
Trip Dates
Trip Location
Contact
Sales
4/1/14-4/12/14
Chicago
Joe Shmoe
HR
6/2/13-6/4/13
New York
Jane Doe
Table 2
Branch of Company
Company Team
Contact
Sales
Blue Devils
John Deere
Sales
Jets
John Deere
Sales
Jets
Joe Shmoe
HR
Sharks
Jane Doe
Table 3Contact
Branch of Company
Email
Phone
Jane Doe
HR
jane.doe@company.com
800-555-1234
Joe Shmoe
Sales
joe.shmoe@company.com
800-555-1235
John Deere
Sales
john.deere@company.com
800-555-1236
View 4 Replies
View Related
May 15, 2013
I need setting up a history table for contacts and the companies that they are associated with. I am sure this will be obvious to some of you database veterans but I am fairly new to Access and I can't seem to figure out the best way to accomplish what I am trying to do.
Here is what I need to do:
When a contact's employment status changes, I need to change the contact's current company association but somehow maintain his or her association with the previous company so that s/he can still be associated with past projects.
So, in my contacts table (TBLContacts), I have a foreign key field "CompanyFK" that links to my companies table (TBLCompaniesPK). There is a one to many relationship between TBLCompanies and TBLContacts.
I want the CompanyFK field to be the current company but somehow link the person with past companies too so that the project directories and subforms will continue to show the contact's association with the parent company.
Maybe I don't need a history table but something else?
I have a similar problem with companies that change name, too. How to deal with takeovers, name changes, mergers, etc.
View 5 Replies
View Related
Sep 24, 2012
I have a contacts database and I am trying to set the relationship between the contacts table and the locality table. The contacts table has a LocalityID field that is a long integer and the Locality table has an autonumber as the PK. When I drag the LocalityID on one table to the other LocalityID I get the Can't create this relationship. When I look at the Edit Relationship dialog box the primary table is the Locality table not the Contacts table. I want set up a lookup on the contacts form that relates to locality.
View 4 Replies
View Related
Apr 14, 2006
I have a form with a sub-form and a sub-sub-form :p
If I try to delete the record being viewed in the sub-form using
With DoCmd
.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End With
Then I here a ding (like you do when you try to delete a record) but I see no record delete confirmation box come up, if I hit enter, it deletes. So its there, but I can't see it, it's not behind the form either.
Any ideas?
Cheers
View 2 Replies
View Related
Jan 9, 2013
I have researched the 'party' data model but it is a bit too complex for what I'm seeking. For those familiar with it, I don't really need the intermediary relationship from-to tables.
I'm interested in ideas about setting up a data structure that will allow users to search contacts or select contacts in dropdowns regardless if the contact type is a person or an organization.
Obviously the fields needed for both are different and the biggest issue is the name field because the person contacts are
The way I am accomplishing it now is writing the company name, or "first name " & "last name" for a person, to kind of a bridge table when a new record is inserted into the person table or the organization table...kind of inefficient.
Is this a relationship thing or should I just write a function to create a temporary recordset when needed?
View 3 Replies
View Related
Jul 31, 2014
I am having a problem with my Contacts Database. I have attached it for your review.
My main form entitled "frmContact", is a form to enter a new contact. I have a Notes subform, and I want to be able to enter multiple notes for one person. I have a note for John Doe, and tried to enter a new note for him and got this error message:
"The LinkMasterFields property setting has produced this error: 'The object doesn't contain the Automation object 'tblcontact"."
Why is it doing this? I tihnk I have the relationships correct, and everything...
View 13 Replies
View Related
Mar 12, 2008
Is 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.
View 14 Replies
View Related
Jun 11, 2015
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 2 Replies
View Related
Sep 12, 2012
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
View 5 Replies
View Related
Dec 1, 2013
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.
View 1 Replies
View Related
Feb 6, 2014
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 Related
Oct 30, 2014
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 Related
Feb 23, 2005
I'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.
View 10 Replies
View Related
Jan 2, 2013
Table 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 Related
Sep 26, 2014
there 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?
View 12 Replies
View Related
Feb 26, 2013
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 Related
Jul 10, 2012
I 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 Related
Nov 4, 2014
1. 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?
View 3 Replies
View Related
Jan 29, 2007
How many tables would I need for the following:
Order#
customer
date/time order received
date/time order due
courier (the order might be shipped via two or more different couriers)
dept (the order might be sent to three different depts.)
the order will be for making photocopies of different sizes
and different width
If I need to provide more information, let me know.
Any help would be appreciated
Thanks.
View 2 Replies
View Related
Aug 11, 2015
I am putting the student names in a form. I want it to alert me that a name is duplicated. But thats not this simple. For example a name is Jennifer Locus but I type jenifer with one . Can access guess that this might be the same person and suggest me to correct the spelling or enter it as a new person?
View 2 Replies
View Related
Oct 16, 2013
the proper code to prevent duplicate records in my form?
I'm using:
Private Sub Ctl_Lname_BeforeUpdate(Cancel As Integer)
Dim dupCount As Long
dupCount = DCount("*", "Clients", "[LastName]= '" & Me.[LastName] & "'" & " And " & "[PreferredName] = '" & Me.[PreferredName] & "'")
If dupCount <> 0 Then
Beep
MsgBox
[code]....
"This name already exists in the database. Please check that you are not entering a duplicate person before continuing.", vbOKOnly,
View 10 Replies
View Related
Apr 25, 2013
I just need a simple method to prevent a member being imput twice on a form.
I have two fields firstname and surname ( this is necessary).
I want to stop duplication of the same person being entered when staff use the form.
How do I stop this?
View 2 Replies
View Related
