This one is very annoying...
i have a button that is supposed to update a record based on the record id. when i click the button, it not only updates the record, but it also creates a new record with just the default values i have set for the table. no other values are entered into the table. here is the code that is relevant to the problem:
Code:Dim DAOdb As Database Dim DAOrs As dao.Recordset Dim sSQL As String Set DAOdb = CurrentDb() sSQL = "Select * from StopwatchRecord where StopwatchRecord.ID=" & Me.hidID & ";" Set DAOrs = DAOdb.OpenRecordset(sSQL) With DAOrs .Edit .Fields("StartTime") = Me.StartTime.Value .Fields("EndTime") = Me.EndTime.Value .Fields("ElapsedTime") = Me.ElapsedTime.Value .Fields("Comments") = Me.Comments.Value .Update End With MsgBox "Record Saved" DAOrs.Close DAOdb.Close
Live front end in one network folder, accessible by all users. Development front end in another folder accessible only by myself (changes imported into Live as and when necessary). Back end in another folder accessed by both front ends.
One of the tables contains orders made by various people. The way the data entry form is set up is that, once a person's ID has been entered, all previous orders are displayed in a sub form at the bottom of the screen.
I'm noticing that, if two people are entering new records into it via two different PCs, it sometimes happens that one person can see an inserted record, only for it to later vanish from both the form and the underlying table. There is no pattern to these 'disappearances', but it's obviously concerning.
Possibly more worrying is that, from time to time, all records for the person being updated are being lost, not just the most recent entries being made.
I have the database set to Record Level Locking. Can anyone suggest anything else I should be looking out for or should have done?
The only other thing I can think of is that I sometimes enter records from the development version, while it is open in development mode, with just the data entry form being out of design mode. Would that make a difference? If so, why?
I am trying to issue multiple INSERT statements in a row - but it seems that only the first succeeds. If I put a 1-second delay between attempts, suddenly they all succeed (so I know the statements themselves are all valid, it's not a data issue).
These are all being issued in a loop from the same thread, so as far as I can see unless the data is being inserted asynchronously, I can't see any problem. If it is being done asynchronously - how are we supposed to know one INSERT succeeded and thus we can issue the next one?
Anyone have any idea why this might happen? I don't want to leave 'magic delays' in the code!
I have been searching and can't seem to find how to solve my problem. I have a sub form that allows adding new rows to a database. The detail contains a combobox and a few text fields. When I enter data everything works fine. However when I accidently tab into the next row (new blank row) and then try to get out of the form without adding a new row I get an error about "Index or Primary Key cannot be null" or something like that.
It is very frustrating because I can't exit the form until I add data to the new row and then leave without tabbing to much (thus creating another new row with no data).
How do I trap the new row and only allow the insert if it has data in it?
hello! I'm wondering how would I, in Access, make a query that would let me do a UNION and then insert that data into a table? Is there a way that I could achieve that in one(1) query?
I am writing the following query to insert data in tblpostroom from another table. Both the tables have same field names like Date1,582,1810.Now If I run the following query then in the fields 582,1810 of tblpostroom , the data gets stored 582,1810 respectively for all the records. But in actual fields 582 and 1810 store different data.
Code: INSERT INTO tblpostroom ( Date1, 582, 1810 ) SELECT Date1, 582, 1810 FROM Sheet1;
While in datasheet view, i enter a value in a text field, then press the down arrow key. Access then places (on it's own i might add), a value (from %$&$% thin air) which it must believe i wanted to add.
The value seems to be a prediction of what should be in the field, based on previous entries.
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.
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.
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
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
I am trying to stop access displaying the "You are about Update 1 Record" etc message when i run an update query. I know i can do this in Tools/Options screen but the problem is that the database is going to be used by multiple users, and rather than changing each persons Action query option I was wondering whether there is something i can put in to the code Globally to halt the message.
I am trying to remove random characters from a field. The field [assycode] contains a string similar to say, FGEJBF1 or ABFGYRUKC I want to remove any occurrence of "F1" normally at the end of the string but not always at the end. I used: Like "*f1*" to find the correct records, that worked fine, I then used [Assycode]-" f1" in the update to box, It wants to update 146 records I click ok then it says It couldn't due to a type conversion error. Just messing around I tried adding "F1" to these records using [Assycode]+" f1" and it worked fine. Can anyone point me in the right direction?
I have a database where two tables contain information that I need to update based on the 4 right most digits of a field. There are only 11 values that will need to be updated out of a large list of values. I'm not quite sure how to set up the update query so that I can do this.
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?
I have two tables, each has a "status" for a project. In the left table there is only one instance of each set, but in the right hand table, each set may be used more than once by different Projects.
I need the Status field of the left table to be set to "Assigned" if ANY of the sets assigned to projects in the right hand table are "Assinged"
Here is the update query I have setup but when it runs, it updates "0" rows.
8 is the id for the status "Assigned" in the SetStatus table 2 is the id for the status "Assigned" in the Status table. http://img166.imageshack.us/img166/8181/temtinv00210fp.jpg (http://imageshack.us)
After I made the query "T1", I made another query "T2", but this query need query "T1" to work with.
So, when I change the query name from "T1" to "table1", I need to open the query "T2" to add the "table1" and remove the "T1" and change the table name in the query. So, how can I update the query name? Because "T1" is same "table1' query. I just change the name, change name cause another query does't work. How can I update the query info.?
I can never get this right and it's frustrating. I have a field on the form called OverallProjectStatus - it's a combo box and it has values: green, yellow, red. If a user selects green as a value I would like BackColor of the form to turn green color. I tried both before update and after update events on this combo box as well as on the form's before and after update events and it's not working. Could someone please help me put this code in the right place. If Me.OverallProjectStatus = "Green" Then BackColor = 13434828 End If Thanks!
Can someone tell me if this is the correct/best way to do this.
I have a calculated field "savings". It is calculated on the AfterUpdate event of the "month" field.
I have 2 text boxes (std cost & quoted cost) that have manually entered amounts and one combo with "modules" To get the savings calculation my code looks at the month selected then the module selected in my query and sums up the volumes.
It then multiplies the volume X the std cost minus the quoted cost.
I have a form that has a combo box, once a user selects the required entry it the form performs several tasks after update.
If the required entry isnt listed the user clicks on the add button which opens a form to fill in the necessary details for that entry. Once complete the user clicks on the save button and the form closes updating the combo box on the previous form. But the after update tasks do not happen.
I have tried things like....
[Forms]![Frm1].reload [Forms]![Frm1].refresh
etc
But cant seem to get it to work. Any ideas?? Thanks
I had a problem a while ago about changing the value in one field and having it update a different field in another table.
I have a table Order Details where I want the user to be able to select a quantity when ordering a particular shoe.
I then want a field named UnitsOnOrder in a table Products to update its value based on the amount ordered by the user in the quantity box in the other table.
The Order Details table is a subform not that it makes any difference.
I started receiving help by a Mr M Walts, but he hasnt been around for ages and I'm so very deperate for a fix as the code isnt working correctly and I've no idea how to fix it.
Private Sub Quantity_AfterUpdate() 'will hold the SQL which will update the Products table Dim strSQL As String
'will hold the new total quantity after the change Dim intChange As Integer
'see if the quantity had a previous value, might have to seperate into two 'nested if's if it gives an error on the second part of the condition 'when OldValue is null. It might If Not IsNull(Quantity.OldValue) And (Not Quantity.OldValue = "") Then 'ok, there is a previous value, let's see which is bigger
'the change will be the new value - the old value intChange = Quantity.Value - Quantity.OldValue
'ok, now we have the value that needs to be added to the 'quantity, if the old value was bigger then it will be negative 'which is what we want Else 'if there was no old value, then all the really need to do is get the 'new value as the change intChange = Quantity.Value End If
'ok, now we create our SQL statement. strSQL = "UPDATE Products " & _ "SET Products.UnitsOnOrder = UnitsOnOrder + " & intChange & _ "WHERE Products.ProductID = " & ProductID.Value '<- or whatever your control on the form is 'called that is bound to your productID field in 'the order details sub-form 'now we have created the SQL we need, time to run it CurrentProject.Connection.Execute strSQL
'uncomment the next line to see the SQL that was generated 'debug.print strSQL
'that should do it for the first one, and it should give you an idea of how to carry on End Sub
That is the code. I get an error message upon leaving the field sayin 'one of the required parameters is missing'
Any ideas?
Any help greatly appreciated. I'm way behind the rest of my I.C.T class as they have al finished their projects.
I have a customer table with all the usual customer details (customer ID, name, address etc...), a transaction table (Transaction ID, Order Number, Customer ID, Title ID, Date, Status (order received, payment taken, ready for dispatch etc...)), and i have a Title table (Title ID, Artist ID, Name, Record Label, Quantity, etc...)
what would be the easiest way to update the title table everytime a new transaction was added to tblTransaction so that the quantity field in tblTitle would go down by one for the right title?
I have a combo box on my form that is retrieving its list of values from a table. After I choose a value I want to write it to another table's field. I can get this to work where it is writing to the other table's field, but when I open the form to see the field of where the information is supposed to be writen to it is not refreshing. What property on the combo box should I used to ensure that the field of the other table is instaneously updated when the combo box's value is chosen.
