General :: How To Use Code In Button To Check Relationship Integrity
Aug 2, 2012
Suppose I have two tables:
"State"
"City"
Related to the relationship "one-to-many."
They also have enabled:
Enforce referential Integrity and Cascade Update Related Fields
Thus, it is possible to change the name of the state or city, but can not be deleted until the state is associated with some of the city.
I also made a form for the "state" in which is the list that contains a list of all states. The name of the list is "lstState". So that I can delete the "State" I make a button in form and I use the following code:
Code:
Private Sub DeleteState_Click ()
If IsNull (Me.lstState) Then
MsgBox "Select the state you want to delete", vbCritical
else
DoCmd.SetWarnings False
[Code] ....
Everything works fine when the state is not assigned to any one city. But the problem arises when trying to delete a state which is assigned to the city, that is when I select this state and click on the delete button then the state is not deleted - this is ok, but without any message told why the state is not deleted and that's the problem.
My question is how to make the code that the user receives a message that such State can not be deleted because there are cities that are associated with it.
Being as how I'm new in using Access I need some guidance. I have three tables I've set up my relationships as follows:
tblPatient tblContact tblPhone PatientID PK ContactID PK PhoneID PK PatientAccount FK PatientID FK PatientID FK PatientFirstName ConatctFirstName ContactID PatientLastName ContactLastName DOB Address City State Zip Intial Visit
I have set up a 1 to many between tblPatient PatientID and tblContact PatientID.
and a 1 to many between tblContact ContactID and tblPhone ConatctID
I have given each table referential integrity, to mine understanding in short it means change it in one table it changes in all tables. Please let me know if I'm headind in the right or wrong direction the way I have this set up.
I'm not sure if I am heading down the right path here. I have searched the forum but not come up with anything on the subject of deleting relationships and RI.
I have a project assignments junction table that links project and personnel tables. I want the employee details in tbl_prj_assignments to be fixed so that they are not updated by changes in tbl_Employee_details. This is so archived records will always reflect the personnel who actually worked on them rather than the people currently in that position or if they have left the company.
I had referential integrity from both parent tables to the junction table in the back end database. The project relationship remains unchanged so that any project changes or deletions removes all the related assignments.
I tried to remove the RI from the relationship between tbl_Emp_Details and tbl_Prj_Assignments by unchecking the 3 boxes for Enforce RI, Cascade update, Cascade delete. On testing I found that the junction table was still updating with changes to the employee table.
Next I tried deleting the relationship altogether with the same result. So now I am left with referential integrity being enforced where I cannot see any relationship. :confused: :confused:
The fields in the tables are all text boxes - no lookup. The junction table is populated by a subform based on the table and located on the project details form. The subform uses a combo box with an SQL statement to select the employee.
Questions:
1) Firstly, is this the best way to achieve the desired outcome?
2) Where is the referential integrity being enforced, and can it be removed?
Hello everyone. I'm not sure should I post this question here or at SQL Forum? However, my problem is this. I have 2 Tables, Table1 has AutoNumber as primary key, my second Table2 has index key field. I have one-to-many relationship on those two fields, with enforce referential integrity, cascade update and delete
The access program works fine until I upsized to SQL server. [u]Then I was unable to perform cascade update or delete. I have check SQL table (which I have little knowledge about) and seems to be okay, the relationship exists. But at ms-access I was unable to make the cascade update and delete.
tblDateReason DateReasonID(pk) EntryNumber(fk) Date Reason Entered
tblEmpMain EntryNumber(pk) EmployeeNumber Etc//
I have a 1 to many setup between tblEmpMain and tblDateReason.
When I try to setup the relationship between the "reason" field in tblDateReason and luTblReason, Referential Integrity along with the other two check boxes are greyed out.
It still shows one to many but it will not let me enforce referential intergrity.
Why is this? Did I setup the database wrong? Or am I trying to do something I shouldn't be doing??
I have attached the db.. It is split with both the backend and the front end.
I am new to access and I am trying to set a relationship between two tables, with the data types the same but keeps coming up with violates referential integrity rules....and I simply do not understand what this means, and how to solve it...
Hello I'm helping a friend with an access project and the Referential Integrity check is failing for some reason unknown to me. I recreated the entire database from scratch and added all relationships before adding any data and then added in the data. The tables giving me problems are the following:
Orders Table: OrderID - Primary Key Autonumber, Format:1000, Indexed: Yes(No Dupes) CustID - Number, foreign key to a customers table, 1-many relationship SalesID - Number, foreign key to a sales table, 1-many relationship ShippingID - Number, foreign key to a shipping table, 1-many relationship 3 different date fields and a price field
This table is filled with data.
Order Details Table: ItemID - Primary Key Autonumber, Indexed: Yes(no dupes) OrderID - Number, Dec. Places: Auto, Required: Yes, Indexed: Yes(dupes OK) RingID - foreign key to rings table, 1-many relationship StoneID - foreign key to gemstones table, 1-many relationship Ring Size - number
There is no data in the order details table.
The Orders to Order Details is a 1 to Many relationship with ref integrity and cascading updates and deletes enforced.
When I try to manually type in a record I get the "You cannot add or change a record because a related record is required in table 'Orders'" even though I added a lookup list box listing all the OrderIDs from the Order table. I know I'm typing in a valid OrderID number, but it's not taking it and throwing this error.
I am currently working in A2007. I added a table to the db to hold look up data and when I went into the the relationships to drag and drop to create the relationship it pops up an error stating that it cannot create referential integrity. See the attachment below. I checked and double checked the data types are the same.
I have a form based on a table (master) with a cbo which returns yes or no according to what users select.
I then have another form which get's its data from a query based on the above table (master). This time, the value I have selected in the previous form using the cbo, should be displayed using a check box.
This is how I would like it to work:
If Me.cbo = yes (meaning that the value selected is yes), then
check box should show be checked.
If Me.cbo indicated NO, then, the check box should not be checked.
I'm trying to get a field in my Data Access Page to display the last time a field was updated, I figured I would have to do this in HTML, after a lot of dead ends elsewhere. Here is the code:
<SCRIPT language=vbscript event=BeforeUpdate(dscEventInfo) for=MSODSC> <!-- Dim DateModified DateModified = Date 'Current System Date dscEventInfo.DataPage.Recordset.Fields("DateModified") = Date
--> </SCRIPT>
Any clues where I would enter this in the HTML source?
Here is my problem. I have several fields located on a form. The user has a problem finding the curser on what field its on. I did ok with the text fields, but I need to come up with something for the check boxes. Any Ideas
The code below works good for text fields. But I need to do the same for Check Boxes or the Check Box Label that is associated with the check Box.
Private Sub Ctl4bCode_Enter() Me.Ctl4bCode.BackColor = RGB(255, 0, 0)
End Sub
Private Sub Ctl4bCode_Exit(Cancel As Integer) Me.Ctl4bCode.BackColor = RGB(255, 255, 255)
I am planning to give different rights (AllowEdit, ViewOnly…) to the user on accessing forms and reports in a db on a single pc instead of dealing with server as my client have only one pc.
As a present requirement, db would be in one pc only and would be used by 3-4 users. I need each of users to limit their access to some of the forms and reports in the same pc. To get it, what I did is as follows:
Example:
One of my tables called tblEmployees (Back End Table) linked in FE containing following fields:
There is a login menu form. Something like 2 text boxes on the form and opening at the startup. User selects his name from a combo and types his password. If the user is available in the tblEmployees, Access opens main form and store UNumber and UName with time stamp in a local table called “T_CurrUser. If user not available in tblEmployees, it should close or give some msg.
Now, when the Main form opens (after login with correct user and pwd) and user wants to click different command buttons to open the form, I need to write code to check whether the current user have right to open the selected form (check marked to allow opening this form in the table tblEmployees) BEFORE opening it.
If check mark is set to false on F1 in the table tblEmployees, it means he can’t open form1, and a msg should pop up “Not allowed to view”..Something likes that.
1. I don’t know the code I created with simple if then, where to write ? I mean on which event of form ? On open / On Activate or where. Bcz the code should run before form opens and should check the table whether the user have check mark value true or false.
I tried to write the following code but some time it works some time doesn’t. What is wrong with code lines?
On Click of Command Button that opens Form1
Dim USR = DlookUp(“lngEmpId”,”T_CurrUser”) ‘ Local Table emp.
‘To check if Form1 check mark is set to true If DlookUp(“F1”,”tblEmployees”,”lngEmpId=USR)=True Then Docmd.OpenForm “Form1” Else End If
When another user logs on the same pc (off course, after login out of first user) the local table T_CurrUser record will be replaced with new user. Note that local table T_CurrUser contains always one record. Hopefully I described correctly.
I have created code to import and excel file and create a table from that info. Now I need to confirm that the import has the correct Datatype of Number and Fieldsize of Double for one of the columns.
I am building a database for a friend of mine and I would like to give the user the option to mark all of the checkboxes simultaneously. The Checkbox field is called "[Printed Yes/No]". I have set up the command button which is called Check All and I would like it to function like a light switch enabling and disabling all the checkboxes when disired. I am unsure how to make this happen, I would imagine that it will require vb code which I am only just begining to learn.
Thanks for any help. :)
I have attached a screen shot of the Form in question. Please Ignore the entered data it is random test data.
I am new to the forums, so if i posted this in the wrong section...please accept my apologies...
I am creating a database for recording some information about various customers. There is a date field involved, and if a certain number of days pass from that date, i want to create a button that when it is pressed it will check all the dates to see if this condition is true.
EX
Current Date is 10/21/2006, when i press the button, the database will check to see if 10 days have passed from the date recorded.
User 1 - 10/05/2006 - True User 2 - 10/10/2006 - True User 3 - 10/15/2006 - False
Then it would just display the records which are listed as true only. I was thinking of using a query, but i am not sure how to quite do so...please give your recommendations as to what i can do.
I'm new to access. I have created table with following fields.
ID No, Name, Address, Gender, Favorite Food
I want to create form to insert data into tables. I have used option buttons for Gender and Check boxes for Favorite Food. But data didn't insert to the table specially option button and check boxes data didn't insert but other data successfully inserted into the table.
Let me describe the database briefly. I have a table with two fields, item and score. Item's data type is text, and score's data type is number.
I have a form with two combo boxes and a button and some other controls. I need code for the button.
When I select an item in combo1 and another item in combo2 and then click on the button, I want the score for the item in combo1 to be one greater than the score of the item in combo2. Also, if the score of any other item is greater than or equal to the score of combo1, I want the score for each of those items to increase by one.
I have a database that involves a lot of data about ppl and I'd like to prevent duplicate entries.
I have a form that gathers data. I'd like to have a button after name, surname and birthday are written.
A click on the button would display a msgbox that says that person already exist, maybe even with the added - edit the old entry/add new record anyway/abort options.
This is code in a command button on a form to preview a report. DoCmd.OpenReport stDocName, acViewPreview, "Control Room Query", "[Lot #] = Forms![Hydro COA]![LotNumber]" What could I change in this code to use in a different command button to have the report sent as an attachment in snapshot format in an email? The user would have to fill in the address, subject and body of the email. Thanks much!
use of a query, or a report or it could involve changing the table structure.Say I have a many to one relationship, for simplicity we'll call the table on the 'many' side tblThing and the table on the 'one' side tblGroupofThings.When managing the records in these tables it is important that each Group of Things contains at least one Thing, but it isn't important that each Thing is part of a Group of Things.
Things and Groups of Things are constantly being added to the table. As I see it, the only way to manage this is from the Things table, or at least a form based on the Things table, as I am editing a foreign key for tblGroupofThings inside tblThings. However it would be easier for me to manage it from the Groups of Things end, so that my workflow goes as follows:
-I add a new Group of Things to tblGroupofThings, -I then 'put inside' that Group of Things, the Things that belong inside it (i.e. make the foreign key field in those Things point to the Group of Things.
Managing from the Things end means I have to start of with the new Groups of Things which are 'empty' at the moment, decide which things need to go in them then swtich to Things and remember which Things need to go into which Groups of Things.all the talk about putting records inside other records when I'm really talking about foreign keys. I know that's not know it really works but to the user that's how it should appear to work.
I'm an Access newbie, self-taught. I can make a basic form to input data.
What I'm trying to create is a tool for assessing aquisition of student skills. I want to be able to check that a student knows the name of a letter or the sound of a letter - but have the check recorded as a date in the datasheet.
Is this possible? And, can you explain to me how to do this in language I can understand? I'm not a programmer you see
I need to create a record of Users. I created a form with fields containing Username,FirstName,LastName & Email Address wherein the UserName is equals the value of the fields FirstName & LastName. I have a save command button and when I click it, I want it to be able to check if the new entry has a similar UserName that exists in the database, if it exists then it will allow me to edit the UserName and then save the data otherwise will allow me to save it.
