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?
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.
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
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..
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
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.
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.
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
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?
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...
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.
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)
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.
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?
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.
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?
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.
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?
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.
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.
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.
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
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?
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,
