I've done this tons of times, so I don't understand what's happening... The title says it all: when I update the ID of one record of the main table, the referenced records on related tables won't update even though they have a relationship between them with the option "Cascade update" clicked.
In the attached database, the main table "Expedientes" is related to table "Actuaciones".
Hi,
I have a database with a couple of tables. The primary table has a primary key called "StaffNo". I checked the relationships between the primary table and each related table, if I have ticked on the boxes for referential integrity, cascade updates. All done. If I now create a query, which selects the primary table and one related table and enter a new StaffNo, I would expect to see the new StaffNo not only in the primary table but also in the related one. But there is nothing. Do I expect wrong? Have I missed sth.? I read a few posts in this and other forums reg. referential integrity, but I have no clue, what's wrong. :confused:
Any quick help is much appreciated.
Thanks :)
(named: Invalid Field Definition - Unable to set up cascade update but I have done what the moderator advised and have not been able to fix a very similar problem.
I have planned a database and must adhere to the plan or change it and basically I initially said that I would like to enforce referential integrity to synchronise my tables and I would also like to cascade update and delete to save input time because changes will be automated between table (where fields are connected.).
I have attached a plan I did if that makes the purpose of my database clearer.I have attached my database and have established the relationships* without referential integrity enforced I would like between my tables but don't know how to make them one-to-many relationships nor can I enable cascade update or cascade delete or enforce referential integrity.
I am new to databases (and to this forum) and have been promised by my line manager that I won't have to specialise, I don't think I have created the relationships but they are supposed to be between Vehicle ID and Hire Number, and Customer ID and Hire Number.
Is this scenario considered bad design form? (see attached image)
I'll never delete a Customer or Site for whom a a Job exists. I need historical data to remain intact. I'll just mark a Customer or Site as [InActive] so they can't be used, when appropriate.
If the relationships are considered good form, then what is considered good form when deciding upon which relationships to set to Cascade Update? Do I even need to update autonumber foreign keys?
On upsizing this database, SQL complains that this arrangement is a cyclic update and won't create the relationship unless I tell it to use Triggers. This leads me to question whether I'm correctly using Cascade Updates in my Access db's.
Thank you.
What I'm trying to do is making Cascade update and delete between two tables. For example in my case I have Field called Full name in table1 and I want the same field in table2 that will update when something is added in table1 and vice versa. I have tried relationship but I got an error that those fields does not have any unique value.
View 3 Replies View RelatedI'm a newb to Access and SQL and database management. I have a main table and a dependent table with a 1 to 1 relationship, relationship integrity and cascade update and delete. I used an append query after I had modified several records, and after the append deleted one of the records from the main table. It remained in the dependent table. Access didn't catch the disparity even after a restart. I broke the link and tried to reestablish it, and of course Access wouldn't let me. I deleted the record manually from the dependent table and all was well.
Why would referential integrity/cascade delete not be recognized after an append query?Access 2010.
This is the second time in two days that cascade delete has been broken in two days after using a query on the main table. Yesterday, after referential integrity was broken, deleting the records from the dependent table allowed me to restore Cascade Delete functionality and it worked for the rest of the day.
i have an sql statement:
aa = "hello"
Tasklist.RowSource = "SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < aa ORDER BY [Tasks].Staff_Name;"
how can i make it look the the data i put in "aa" rather than prompt a msg box asking for "aa"?
we have a main table for tracking pickups....some of the fields are combo boxes with relationships to other tables....one such relationship is units (ml, gallon, ounce, etc)...in the main table everything works just fine....a user enters the location info, the amount and the corresponding units. thus, an example would be a user picks up something from building X, room 101, 10, Gallons.....the 10 refers to the gallons, the user picked up 10 gallons...
now for the query:
i'm creating a query for which i will base a report on...in query design if i just select the above info everything works fine....it comes out on the report as 5 Gallons....however, what i'd like to do is concatenate the two fields so that they take up less space on the report and are more visibly appealing...here's what i tried in query design:
Amt: [amount]&Space(1)&[units]
this does not work....in the units table that provides Gallons as a selection in the combo field, Gallons has a key value of 5.....thus, when i run the query using the above code i get 10 5 - where 10 is the amount the amount and 5 is the key from the combo box...
i've tried bringing in the units table but then i get a type mismatch....how can i concatenate the two fields and show the proper units? this has to be simple but i cannot get it to work... :mad:
Hi Lads
Hi guys, I want to return all the records which contain information under a particular column heading, hence the statement thats supposed to return all tables where Sector colum is not empty. I am new to SQL and Access so not sure why this simple query isn't working. It is a subform and displays the results in the "details" section of the form below the controls.
I think it could be something to do with the fact that its a subform, but Im not sure about this.
Me.RecordSource = "SELECT * FROM [tblRecordInfo] WHERE Sector <> "" "
any ideas guys? any assistance is greatly appreciated
thanks in advance
I have a six table database and have a query to show all records,(at the moment) but although i have put 3 records in for testing it only shows the first record. Any help gratefully recieved i'm pulling hair out.:mad:
View 3 Replies View RelatedI have a table filled with computer monitor information (tblMonitorManagement). I had query that uses combo boxes (cboSerialNo, cboRoom, cboDepartment) on a form for search parameters. The query is called qryMonitorManagement_Sub. The results are displayed in a subform (frmMonitorManagement_Sub). This works for both my desktop and also my laptop based forms/queries/tables.
Although this form works when using cboRoom and cboDepartment, it does not work when using cboSerialNo, so I decided to simplify it down to get to the root of the problem...here's what I did.
I created a new form, created a combo box (again called cboSerialNo), and had it look up the serial numbers in tblMonitors. Simple.
I created a query called qryMonitors_Test, added all fields from tblMonitors (not using the * option). I then set the SerialNo field criteria to the contents of the cboSerialNo on the new form. Simple. For now I'm ignoring searching on anything else.
I ran the query with no search parameters before I added the criteria, and it obviously displayed all the records in tblMonitors. Fine.
After adding the criteria (using Build...to prevent typos), I used the form to select a serial number, run the query and get no results returned.
If I copy and paste the same serial number and change the SerialNo criteria in the query to search for that it works fine. As soon as I tell it to search for the combo box contents it returns no data.
This is driving me completely bat-poo. It's nothing I've not done a squillion time before but it just refuses to work. I've got to assume it's a query issue as opposed to a form issue, but things don't get much simpler than this should be!
I have a query that filters two tables. 1) Status 2) Name
The status is 1,2,3,4,5 from an option group on my form.
In the query I have <>3 for the status, whereby I only want records that are status 1,2,3,& 5.
When I run the query with all names it works perfectly, however I am trying to run the query for only a select few names versus all of them.
The problem is I do not know how to select only a few names versus all of the names and make it work. When I try to add names i.e "Doe, John" my Status pulls all records including option 3.
Can anyone help? I do not even know where to start seaching.
Thanks.
Fen How
Hi Guys,
I've tried searching the forums for an answer with no joy.
I'm trying to create a very simple DELETE query, which I've never had an issue with before.
Basically I have two tables
Dbo_PDRPerson (PersonID, RoleID, PDRID)
tbl_InfoByPDR (PDRID)
Basically I want to delete all records in Dbo_PDRPerson where a corresponding PDRID record exists in tbl_InfoByPDR
My current method:
DELETE dbo_PDRPerson.PersonID, dbo_PDRPerson.RoleID, dbo_PDRPerson.PDRID
FROM dbo_PDRPerson INNER JOIN tbl_InfoByPDR ON dbo_PDRPerson.PDRID = tbl_InfoByPDR.PDRID;
This tells me to "Specify the table containing the records you want to delete"
Any ideas
Many thanks
Hi
Using a toggle box to ask the following question, i have data that contains either OPEN or CLOSED against each record. I tried using the following in the criteria for the specific data to either select all the OPEN records when the box is clicked and i would like to select all records OPEN and CLOSED when it is nto clicked
IIf([Forms]![MEQN_ProdACStn_Picker]![Toggle38]=True,"OPEN","")
This didn't work and a number of other attempts failed as well...
How do i do it in the criteria selection or is it not possible
Cheers
Newbie Jasper
I am a very new Access user. Just getting into it. I have created a database and want to do something that seems simple to me but I can't figure it out.
I have a database containing membership information for a local fishing club.
I want to be able to create a situation where I can do the following.
Enter a membership number and have the members info displayed so that I can then make any changes and update it. I want to be able to do this one member at a time so that I can update as changes come in.
Can anyone help.
I see a lot of really interesting ways to do this via code as an automated action, but what I can't seem to find is how to do this manually?? I will look into the automation at a later time and probably use some of these wonderful ideas. For the moment, however, I just need to know a simple way to update my front end table after I have redesigned it in the back end. MSAccess help files can't seem to point me in the right direction. I've already changed my back end table, but can seem to make those changes reflect in the front end. I would appreciate any help with this, thanks.
View 1 Replies View RelatedHi,
I used a before Update procedure to check if the date of a textbox is later than another text box. However, nothing seem to happen when I enter the wrong date.
Here is my code:
Private Sub txtDateOfCompletionForHandover_BeforeUpdate(Cancel As Integer)
Dim anticipatedCompletionDate As Date
Dim dateCompletionHandover As Date
'assign vars values
anticipatedCompletionDate = txtAnticipatedCompletionDate.Value
dateCompletionHandover = txtDateOfCompletionForHandover.Value
'If date1 refers to a later point in time than date2, the DateDiff function returns a negative number
If DateDiff("d", anticipatedCompletionDate, dateCompletionHandover) < 0 Then
MsgBox "You are trying to Handover house before the house is finished!" & vbCrLf & vbLf & _
"Please enter a later date than Anicipated Completion Date above.", , "House Handover"
'empty textbox and keep focus
txtDateOfCompletionForHandover.Text = ""
txtDateOfCompletionForHandover.SetFocus
End If
End Sub
Any help will be very much appreciated.
B
I have witten a code in the close event of a "Primary" form that would update a Combo boxe on a "secondary" form only if the "secondary" form is open.
ie:
Private Sub Form_Close()
If Forms!frmEnquiry.Open Then
Forms!frmEnquiry!CboCustomer.Requery
Else
DoCmd.Close
End If
End Sub
This code keeps giving me an error, is anybody has got any idea why?
Thanks in advance.
Hello. I'm trying to design a simple database for a bicycle recycling charity. I hit a brick wall when realising that I had never before attempted to update a record. :eek: Until now I've just used Access to add new members to a bus user's organisation.
If the shop takes delivery of 20 tyres, I need to add 20 to the current stock.
Is it possible to achieve a simple Form that adds to or subtracts a quantity from current stock, or do I have to learn some access programming?
I've looked through some textbooks and can't find a simple update solution.
Thanks for any suggestions. :o
I am trying to update the value in one field (RUS Prop Class Document) of a form based upon what is selected in another field (RUS Prop Class Code). I plan on this being a hidden field so I was creating an after update expression in [RUS Prop Class Code]. My problem is that the value returned is the very nice and working query but not the value of the query. I'm missing something that I think is simple but cannot figure out with my limited resources and late hours.
Here is what I have for the after update expression in [RUS Prop Class Code]:
Private Sub RUS_Prop_Class_Code_AfterUpdate()
[RUS Document].Value = "Select [tbl_List of RUS Classifications].[RUS Prop Class Document] " & _
"FROM [tbl_List of RUS Classifications] " & _
"WHERE [tbl_List of RUS Classifications].[RUS Prop Class ID] =" & Me.[RUS Prop Class Code] & ";"
End Sub
Thanks ahead of time
Hi all,
I'm sure this is easy to do but for some reason i just cannot get it working. Hopefully someone here can tell me where i'm going wrong.
I have two tables in a database and the have a linking unique field. I want to update a column in one table from a column in another where the linking ID field matches.
Here's what i have at present.
Update Table_1 inner join Table_2 On Table_1.ID = Table_2.ID
Set Table_1.[CHI Number] = [Table_2]![CHI_No]
This is just returning the CHI Number column as blanks instead of what i expected would be the same as Chi_No.
Can anyone shed some light on this for me?
Thanks in advance
Hi all,
I'm trying to use an update query, but somehow, it doesn't store anything in my database. This is my code:
sql = "UPDATE CONSTRUCTIEF SET ConstructiefScore = " & strGebouwConsScore & " AND ConstructiefCommentaar = '" & strGebouwConsOmschrijving & "' WHERE ConstructiefID = " & strGebouwCons
DoCmd.RunSQL sql
When the execution reaches this peace of code, I actually get the right warning message because he's going to update a table, but when i'm looking in the table afterwards, nothing has been updated.
Anybody got any clue?
Thnx in advance,
Freak
Hello all,
I have the below update query running
UPDATE tbl_master SET tbl_master.COLLRAW =
Iif(tbl_master.PSTATE = "WV","WV",
Iif(tbl_master.PSTATE = "MA","MA",
Null))
WHERE tbl_master.EXCLUDEREASON Is Null
but when I add this line:
UPDATE tbl_master SET tbl_master.COLLRAW =
Iif(tbl_master.PSTATE = "WV","WV",
Iif(tbl_master.PSTATE = "MA","MA",
Iif(tbl_master,MSPBANK in ("751","752","753","854","855"),"GS",
Null)))
WHERE tbl_master.EXCLUDEREASON Is Null
It give me an error message that says "Wrong number of arguments in Query Expression." Can anyone tell me what is wrong with the query above?
Thanks in advance,
Vassago
Hi Everyone,
Here's the situation:-
I have a patient record with a specific consultant assigned to the patient. In some instances, the patient may request a different consultant. What I want to do is to allow the user to change the consultant and to save details about the change e.g. file no, old value, new value, date etc.
This is what I have done so far:-
I created a Query to retrieve details from 3 tables and assigned the value to be changed to a variable (oldvalue)
I then allow the user to select from a combo box the new consultant.
Details of these changes are then written to a table called tblchanges.
All the above is working.
The problem occurs when I try to update the consultant table, by replacing the old consultant with the newly selected consultant.
Can someone look at the code and let me know first if I am approaching this problem correctly and then why the code is not working.
Private Sub Combo36_Change()
Dim flno As String
Dim fldnme As String
Dim oldval As String
Dim newval As String
Dim tdate As Date
Dim ttime As Date
Dim sqn As String
Dim yrn As String
flno = PFILENO
fldnme = "Radiation Oncologst"
oldval = Text29
newval = Combo36
tdate = Date
ttime = Time
'Insert change values into tblchanges
strSql = "INSERT INTO tblchanges " _
& "( fileno,fieldname, oldvalue, newvalue, datechg,timechg ) " _
& "VALUES ('" & flno & "','" & fldnme & "','" & oldval & "','" & newval & "',#" & tdate & "#,'" & ttime & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
strFILENO = (Left(Me!PFILENO, 4) & "/" & Right(Me!PFILENO, 4))
'update consultant table with new value
stsql2 = "UPDATE tblatdocts SET roncologist = '" & newval & "' "
stsql2 = stsql2 & "WHERE tblatdocts.FILENO = '" & strFILENO & "' "
DoCmd.SetWarnings False
DoCmd.RunSQL stsql2
DoCmd.SetWarnings True
End Sub
Thanks so much for your help.
Will
Hi guys,
I have a text field in a database.
I run an update query and the database updates when the form field is text e.g 'location' or numerical '1234'. However if it is mixed, e,g VV1234 it fails to update and i get an update error.
Is this an Access thing? Is there a work around?
Thanks
ronan
Hello,
I am still wet behind the ears in regard to Access so your help and support is much needed.
I have created a form in Access where i am having trouble with a textbox. In short this box is called [Shift] i want it to be populated by the result the system returns in another Textbox which is called [Time], this box is set up as Time().
So below is what i need it to do when the Form is used.
Textbox: [Time] = (07:00 - 14:59) would return "Earlies" in Textbox: [Shift]
Textbox: [Time] = (15:00 - 22:59) would return "Lates" in Textbox: [Shift]
Textbox: [Time] = (23:00 - 06:59) would return "Nights" in Textbox: [Shift]
I have tried the VBA below without success in this task so i can only assume it is due to the fact that the Time field is system generated or the fact it is in time format:
Private Sub Time_AfterUpdate()
If Me.Time >= "23:00 And < 06:59" Then
Me. Shift = "Nights"
End If
End Sub
Any Help you give is much needed, Thanks