I get an error "update or cancel update without add new or edit" which seems to point to this code.I am using MS Access 2010.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Store when record was last modified and by who.
'Initially stores when the record was entered.
If Me.NewRecord = True Then Exit Sub 'Exit if new record
Me.DateLastModified.Value = Now()
Me.LastModifiedBy.Value = getUser()
End Sub
My membership database has worked fine until recently. Now I cannot save inserted data. On attempted saving "Update or CancelUpdate without AddNew or Edit" appears.
The problem. relates to 2 tables Member and Addresses. PK in the parent table Member is ID. In the Addresses Table the FK is ID. There is a One to One relation between the tables and Referential Integrity is set. I know 1 to 1 is not good but it worked fine in this small database.
Browsing the all of the existing records is fine.
I have a subform which asks a user to confirm the update before it happens - if the user selects no - then the update is cancelled.
When the parent (they are not related record wise) form is closed the update on the subform is attempted - if the user selects no - how can I cancel the close of the parent form. At the moment a message saying "Cannot save the record at this time - changes may be lost". Which I don't want to see.
Thanks
is anyone has idea about how to undo update for subform?
i have a parent form(linked to A table) and a subform (linked to another table). the parent table has two button-save and cancel. i was wondering how the subform cancel update if the parent form's cancel button has been clicked? the code Me.undo will undo the parent form's record only, since the subform record has been saved into access.
I am trying to use a Combobox BeforeUpdate Event to achieve a required result.
I have two combo boxes inside a frame. The first is a year number 2011, 2012, 2013, 2014
The Second is the Week No ranging from 1-52, but 1-53 in some cases and this updates accordingly with the year number selected.
When the user has selected Week 53 and then changes the year to a year where only 52 weeks exist I want to Cancel their event.
I have already made a function to determine if 53 weeks exits however when I come to do the Cancel Event, the combobox for the year doesn't change back. It stays on the users new selection.
Below is the code that I am trying to use:
Private Sub cmbWeekNoFromYear_BeforeUpdate(Cancel As Integer)
If UpdateWeekNoSelections Then
Cancel = True
Me.cmbWeekNoFromYear.Undo
End If
End Sub
'cmbWeekNoFromYear is the name of the combobox which holds the year number
'UpdateWeekNoSelections Simply returns True if I want the users change cancelled for cmbWeekNoFromYear
I have a form from a query.
In this form i have a Combo Box and a Text Box.
What i want to do is select a value from in the Combo Box and get the corresponding value in the Text Box
Ex:
(Combo Box) (Text Box)
BillNumber CustomerName
I have tried to put an Event Procedure on the AfterUpdate (or OnChange) of the Combo Box, and i wrote in it:
------------------------
Private Sub COFNumber_Change()
' Find the record that matches the control.
Dim rs As Object
If Me![BillNumber] <> Null And Me![BillNumber] <> " " Then:
Set rs = Me.Recordset.Clone:
rs.FindFirst "[Bill Table_CustomerName] = '" & Me![BillNumber] & "'":
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
-------------------------
I try the form, and i get the following message:
"Update or CancelUpdate without AddNew or Edit"
I dont know what this means, and i dont know how to get it working.
Anyone has an idea?
Thanks in advance.
A.
Hi all,
I have a form that displays fields from two tables. I have set the properties of Allow Edit, Allow Additions, Allow Update, Data Entry to NO. However I want to allow users to be able to add new, edit/update and delete records from the form by clicking on respective command buttons.
the problem is that the code that I am using is giving me errors that says that I "You cannot go to the specified field" I need help on proper VB code to use that will allow me to enable input and update on the records in the table.
Private Sub cmdAdd_Click()
Me.Location_ID.Locked = False
Me.Location_Parent.Locked = False
Me.Zone.Locked = False
Me.Description.Locked = False
Me.Child12.Locked = False
DoCmd.GoToRecord , , acNewRec
End Sub
Dont know if that makes any sense, but I want to add a new record and i get errors
Neeeeeed some help ASAP....
Hi
I have a form with avout 4 fields based upon a query to allow user to edit an entry if needed.
I want to put a cancel button on so that if they change their mind after having changed a field it reverts to its original value - so for instance they decide to change a land line phone number to a mobile, exit the field but then have a brain storm and decide not to do this so hit the cancel button.
At the moment my cancel button just closes the form (and accepts the overwrite) which would be fine if I am using ADO but not if I am using a bound form.
I am sure there is a simple answer (since I cannot be the only person who thinks this option might be a good idea). I am just not sure how to look for it.
Any thoughts or ideas?
Thanks
I wrote the following statements to update a SQL server database:
Dim MyDb As Database
Dim MyRs As Recordset
Set MyDb = DBEngine.Workspaces(0).OpenDatabase("AMD", dbDriverPrompt, False, "ODBC;DATABASE=AMD;DSN=Remote")
Set MyRs = MyDb.OpenRecordset("SELECT AllAttendanceEvents.* FROM AllAttendanceEvents ORDER BY AllAttendanceEvents.EntryTime DESC")
MyRs.MoveFirst
While Not MyRs.EOF
MyVal = MyRs!EntryTime
Debug.Print MyVal
MyRs.Edit
MyRs!Ix = 50099
MyRs.Update
MyRs.MoveNext
Wend
When it execute the red line statement, it generates the error:Cannot update. Database or object is read-only.
This error is generated isnpite that I already checked the boxes: SELECT, INSERT, UPDATE, DELETE in the Permissions page of the tables?
Please advise.
I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.
In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.
The problem is when both companies already have existing records in the table.
So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?
I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?
Hi Guys,
I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.
It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.
Any suggestion guys & gals?
Many thanks
Tim
Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers
View 3 Replies View RelatedI'm relatively new to access. I probably use about one tenth of one percent of what it has to offer because I have trouble understanding it at this time. I need to know how to update a yes/no object.
View 2 Replies View RelatedI need to update a field with a Sum() on event.
View 12 Replies View RelatedI am working on a DB for work. Currently the form has 3 options, each one selected hides/shows different fields pertaining to that option. Works great when you are putting data into the tables using the form. Problem is when you start that the beginning and click 'next'. When clicking next you do not see the fields show or hide. How do I make this happen?
View 3 Replies View RelatedI've recently come across a problem whilst saving an edited record..I get the following message but only sometimes..could not update currently locked by another session on this machine..Sometimes the record saves without a problem (mainly on the first edit). However, any further edits of the same record mainly but not always triggers the above message. I can't work out why it's happening, It's a split database but I'm the only user on a standalone PC (so no other users editing the same record). Sometimes I get lucky and the record save even after the first edit but not always. There doesn't seem to be any pattern.
Record locks for the form are set to NO LOCKS
Recordset type is set to DYNASET
In settings -
Default open mode it set to SHARED
Default record locking is set to NO LOCKS
Open database by using record-level locking IS CHECKED
This has been working fine for years so I'm a little stumped as to why it should happen now. The only thing I've changed recently is a memo field from Plain text to Rich text.Whilst writing this I just went back and double checked and found that if I change any other field (not the memo field) the record saved just fine. So the problem is to do with the Rich Text memo field only.
In access 2010 if there is a table "log" with
Item Amount
Water 100
Egg 200
and another table "setup" with
Item Amount
Water 20
Egg 50
Can the "setup" be used to update the "log"
Water 80
Egg 150
i import data into a Landing table, this will always contain d days woth of data, i then run an apend query into another table were the records are stored up to 20 days, the problem i face is some of the record data may change for eample a field called ShippedQty may be 0 in a monday but on tuesday it may say 5
Import on monday
Product ShippedQty Date
123____ 0 ________01/01/2013
Import on Tuesday
Product ShippedQty Date
123____ 1 ________01/01/2013
I need to look at any changes and updatein the master table, is there a way to do this.(I cannot change the import as its the only way i can get the data)
I am trying to UPDATE records from the Master form by clicking a button to a new window -
frmCustomerMaster (main form) - a button will trigger to UpdateCustomerMaster new window and pass all the data to the new window.
UpdateCustomerMaster (New window Form) On Submit Update values.
frmCustomerMaster (main form) - is currently locked by properties, I do not want main form to be editable.
how to pass the data to new window and how to update the records?
I am in the process of updating a database to 2010 (.accdb) and cannot get past a problem. We are using Access 2010.
I updated the back end data to .accdb with no problem. I can't link the front end to that data without updating the front end to .accdb as well. There are no tables in the front end.
I try to update the front end to /accdb and I get the error message 'You are trying to convert an encoded database. Decode the database, then try again.'
How do I decode the database? I tried setting a password and then unsetting it, but that made no difference. I have not previously encoded the database.
can't finish the update to .accdb data. The alternative is to stay with 2003 MDB data. Is that a bad thing?
I have a simple MyAddress Project. It consists of one main table, a couple of queries, some forms and reports. Most fields in the table are typical text fields. The project allows me to select records from the table and print labels for those selected records. One field is a "yes/no" field. If the field value is true, the label prints; otherwise it does not. I have a form which allows me to scan the list of records and mark those I wish to print. So far so good. I am trying to add two buttons to the form to enable me to 1. Clear All and 2. Check All. I am trying to use an UPDATE statement to activate when I click the appropriate button. However, when I execute, I get the error message "Compile error: Sub or Function not defined".
The name of the table is tblMyAddresses.The firld I am trying to UPDATE is PrtLbl.The update value will be true or false depending on the button clicked. Since I want to change the values in all records, there is no WHERE required. The code I am trying is as follows:
Option Compare Database
Public Sub CheckAll_Click()
Update tblmyaddresses
Set PrtLbl = False
End Sub
I have a table called [Tasks] which has the following relevant fields:
[Task ID]
[Location]
I have a form that people use to create new tasks and update current ones. Location on the form is a combo box where people can pick from 5-6 different locations.
What I want:
After someone selects a new location from the combo box and changes the task location, I want to create a new record in a table called [Task Progress] with the [Task ID] of the task whose location was changed, the new [Location], and the [Date] and [Time] it was changed. It would look like
Code:
[Progress ID] [Task ID] [Location] [Date] [Time]
1 5 Station 1 1/1/2011 12:13:01
2 8 Station 2 1/3/2011 01:53:29
3 5 Station 2 1/5/2011 11:13:05
4 5 Station 3 1/6/2011 12:35:22
What should I put in the AfterUpdate event for the [Location] combobox to make the above happen?
Note: All my tables are ODBC linked to SQL Server (can't use data macros).
From Excel 2007 to Access 2007 I want to keep my Access database sync with my Excel SpreadSheet. I not to often change Excel but when I do Access will be updated.
Error #: -2147217887
"Field cannot be updated."
My connection string works fine and recordset is up and running!In my Excel file:
Code:
rs.MoveFirst
For k = 1 To Sheets("2013").Cells(Rows.Count, "A").End(xlUp).Row - 4
If rs!Index <> k Or rs!total <> Sheets("2013").Cells(k + 4, 5).Value Then
rs!Index = Sheets("2013").Cells(k + 4, 1).Value
rs!DatePaid = Sheets("2013").Cells(k + 4, 2).Value
rs!WhatPaid = Sheets("2013").Cells(k + 4, 3).Value
[code]...
I have a form and a subform in it. I added New cancel button in the form so that the the user can cancel the record creation and no record will be inserted in the parent table.
But when details are entered in the subform (a datasheet) row records will be created in the subform table. what is the correct method or how to cancel these records if the user choose to click cancel button on the parent form.
I have a pretty normalized Access Database. The table that I am trying to add new records is tbl_returns and has 4 fields: return_ID, serial, reason and inv_num.
When I sell a card (or a range of the cards) an invoice including all the information is saved as a record in a new row in tbl_invoices.
If a vendor wanted to return a card (or a range of the cards) in the next visit (weeks or months later) I will accept and in most cases they want me to switch the cards with new ones. Therefore in a new invoice (different date and invoice number) I will give him new cards and return the cards that he wanted to change or return.
Now I have to assign NULL to the inv_ID field in tbl_allPins in order to make it available for sell in future. At the same time I want to have a record of the returned cards including serial number , the reasoning of return, invoice number and/or a little note about each one/range of the card(s).These are to be recorded in tbl_returns as you can see.
For instance you want to return serial numbers between 9876 and 9880 (includes 5 cards) because of the "scratch off problems" and your invoice number is 22222, using frm_returns. After you process it and then open the tbl_returns to check the result, you will see 6 records are added instead of 5. I was able to understand why it is happening (I believe so!) but I could not fix it. Also I cold not write a VBA to remove the inv_ID in front of the related serial numbers in tbl_allPins.
Also in another trial was ended up to creating the Form1. Form1 looks better (has no extra records) but I have trouble to navigate through the records in tbl_returns. There was a sub-form added but it was showing all the records in tbl_returns which is unwanted.
By the way, serial numbers and PIN numbers are each a unique number in tbl_allPins.
suddenly all the old queries crash (message: enter parameter value). The table name is Exactly the same. Columns in the new table are also the same. Is there a way to "fix" the parameter value so that all the queries update at the same time.
View 7 Replies View Related