Don't know if this is possible, or if im being just plain stoopid:
Is there a vb command that will update a record without having to use a query?
My problem is this - I have an HR database which has allows us to add employees that are going to start. We then set their [Activity Status] from "Starting" to "Active". At the moment this is done manually, but what i would like to do is have this automatically change when the [Start Date] = Date().
The code i tried to use (but is obviously wrong) is:
If [Activity Status] = "starting" And [Start Date] < Date Then
Set [Activity Status] = "Active"
End Sub
Any thoughts what i might change "Set" to, to make this work?
i want to be able to create an On Click Event when pushing a command button that will run an Update query to update a record and after it has been updated that specific record will pop up on a Form and be displayed. i know a different way is to run the Update query and then have it displayed in a Select query but i want it to be displayed on a Form instead. is it possible?
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?
The last version of Access I've used was 97 but I'm getting back into it. I've read a couple of things that recommend creating a form based on a query, not a table, especially if a calculated field is involved.
When I create a select query based on 1 table, I can change/add/delete records right in the results of the select query, which will carry over to the form just fine.
However, when I use an additional table and join them in my select query, I can no longer update any of the fields that show in the query result. The link I'm using is just a 1 to 1.
How can I get around this? I'm using the second table just for lookup purposes (use the value of one of the fields in a calculation), but I want to be able to update the fields from table 1 from the form.
The uploaded database has two main forms. The first is used to enter customer data, the second runs from a query and shows all those records that have been completed in the first form (frmCustInfo - AgentLog = Yes) but not completed in the second form (frmControlsSource - AnalystLog = No)
Then more data is input into one of the subforms (frmControlChecks). Now in order for me to see the results of the queries connected to two other subforms I have to move off the record and back to it (for the record to be saved and then the queries run)
After moving back to the record, the query results on the right (Information Only) are manually added to the last subform 'Input Two', then the user would move on to the next record and do the same. ------------------------------------------------------------------------------------------------------------- What I need it to do then is this: When the user tabs off the last field in the subform (frmControlChecks), the queries to the right (two subforms) would run, showing the results and also populating the last subform (frmAnalystInput)
The user would then click AnalystLog and move to the next record.
I hope this is possible without redoing the forms/queries because it's taken ages to get this far
I've also added info in this sample database and some working data if you want to take a look...thanks for any help
I have a list of codes that need to be changed if it is part of the a list that need to be updated after being entered into the database. I created a function that holds the old values and what they need to be updated to. To get this accomplished on a form do I just need to write a Update query and then reference the query to the appropriate field I am trying to update with new codes for the 11 codes needing to be changed and leaving the other codes the same.
I can do this in like 5 seconds in SQL Server but I can't get the query to even ALLOW me to update records.
Table1 - Columns Query1 - ColumnDesc
Code: SELECT DISTINCT C.Column FROM Columns C INNER JOIN ( SELECT Cols.Column, Count(Cols.DataType) FROM (SELECT DISTINCT Columns.Column, Columns.DataType FROM Columns) Cols WHERE Cols.DataType = 'char' OR Cols.DataType = 'varchar' GROUP BY Cols.Column HAVING Count(Cols.DataType) > 1 ) C2 ON C.Column = C2.Column;
Simple, straight forward query, that grabs all the rows from table Columns where there are more than one DataType per Column [name], and either one of those datatype strings are 'char' or 'varchar'.Now I want to UPDATE table Columns to set all of the columns whose "column" value is in the above query, and set all those DataType values to 'VarChar'. Thus I run that UPDATE query, and the above query should come back empty afterwords in SQL Server I would simply write:
Code: UPDATE Columns SET DataType = 'varchar' WHERE EXISTS ( SELECT 1 FROM ColumnDesc INNER JOIN Columns ON ColumnDesc.COlumn = Columns.Column )
And this would already have been done.But Access doesn't like that syntax. using the designer it created sql like this:
Code: UPDATE Columns INNER JOIN ColumnDesc ON Columns.Column = ColumnDesc.Column SET Columns.DataType = 'varchar'
But that wasn't an "Updateable" query.I have to run this on several patterns and right now I'm completely screwed if I can't get this to work.
My end goal is to have: SELECT DISTINCT Columns.Column FROM Columns return the same # of rows as SELECT DISTINCT Columns.Column, Columns.DataType FROM Columns
for each duplication I will have to do different algorithm, but I can't even get one update query to work so I'm currently frustrated (and ready to reaffirm my belief that Access should have been discontinued 5 versions ago).
Upon closing my frmInventory the amount stock of stock is checked against a minimal stock value. If the stock amount is below a set minimal value a subsequent form is opened telling you that stock is low and an email message is generated to notify a manager. I have a checkbox on that form which is set to "True" upon close using an update query. The checkbox is there to give users the option to either send or not send a reminder message that stock is low when a message has already been sent earlier.
The problem is that using that update query ALL records are set to "sent=true" and not just the 1 record I intend.
This is my code in the "on close" event:
DoCmd.OpenQuery "qryUpdateEmailMinimal_True
and here's the SQL:
UPDATE tblInventory SET tblInventory.emailSentMinimal = True;
I assume what is missing is a reference to an inventoryID number. How do I do that?
i have this problem that is bugging the crud out of me: sql="UPDATE bedrifter SET pr=" & Request.Form("pr") & ",totalindexedpages=" & Request.Form("tip") & ",totalinboundlinks=" & Request.Form("til") & ",description='" & Request.Form("dsc") & "' WHERE created='" & Request.Form("ts") & "'"
conn.Open connStr conn.Execute(sql) conn.close() Set conn = nothing
when i run this code it updates the correct record (line in my access db) but then it also adds a new line with only that info in the update query. why is it doing this? when i update using the ID instead of using the timestamp in the WHERE clause it works fine. really frustrated...
I have this update query that is triggered by an after update event on a main form. The record being updated are in a continuous subform. It works well except from the last added/modified record. If I save and close the form and then open it again it works for all records but if modify or add a record, the update query will not work for that last modified/added record.
I have tried several things such as save record, use dirty = false for the on exit event of the subform control but nothing works. Here is the procedure:
Code: Private Sub cboPoCurrency_AfterUpdate() On Error GoTo ErrHandler Dim db As Database Dim strSql As String Dim lngID As Long Dim dblRate As Double Set db = CurrentDb
I have two tables. PreOrder and Order Tables. When our customer actually order a product, I want all information of that product to move from PreOrder table and append it to Order table. Currently, my user has to run an append query to add the record from PreOrder and add it to Order table. After that, she has to run a delete query to delete the record from PreOrder table. Since our query is setup to run by date, and order number..My user has to type the same thing twice. Is there anyone out there know the easy way? Thanks in advance..
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
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 have a record which is updated by customer services dept. for collection of container. Every time we received the collection alert from client we update into system. Sometimes we got more than one collection alert per day. Although the Job No is same but the Date Received the Time Received is different.
Example :
ID Job No Date Time -------------------------------- 1 1000 1-8-2005 8:00 2 1000 1-8-2005 8:30 3 1000 2-8-2005 8:30 4 1001 1-8-2005 9:00 5 1001 1-8-2005 9:30
I just want the last update of data and the result shuld be
ID Job No Date Time -------------------------------- 3 1000 2-8-2005 8:30 5 1001 1-8-2005 9:30
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 hope I can convey what I am trying to figure out. I am at a loss right now. I have a form where I input personnel information. One of the fields is for how many months experience they have with the program. What i would like is for this record to update itself every month. for example, if they initially had 2 months experience, I would enter 2. Every month therafter, that number with change to 3, 4, 5...etc. Not everyone comes to our section with the same experience, so most will have a different starting number.
This information would be seen on the personnel form, and in a report to show experience levels. Other than those two, it is not called upon.
I have a table with over 700 contact records, when I open contact form and select the contact I to want update , it only updates the first row in the table message "you about to update 1 row(s)"
table set up ContactID is the PK autonumber
please advice
AA
Private Sub Update_Click() ' Dim QrySQL As String On Error GoTo Err_Handler
Hello all! I'm using a form in data entry mode to add new orders into a table. When the Save button is pressed, an append query posts the order number and date to another table, and then enables a subform for data in that table. The subform is linked to the main form by the order and date to get only the record that was just entered/created.
While the subform does display the desired record, the user cannot update other fields in that table through the subform. I've searched all over the forums, and I'm probably missing something really simple. I've tried turning data entry on and off, changing different subform properties but to no avail. I can filter out the proper record on the subform, I just can't update it! Any help would be much appreciated. Thanks!
I am a relative Newbie so if my question sounds stupid, bear with me:
I have a form with a subform linked to a query.
On the subform i have a checkbox[Recieved] and when checked adds a date to another field [Year] on the subform which in turn updates the record in a table[Orders].
I then click a command button on the form which runs a crosstab query based on the updated fields in the table [Orders].
This works well except for the last record ammended which does not update the [Year] field in the table [Orders]. When the command button is clicked it is still the current record in the subform.
clicking another record in the subform solves this or closing the form and reopening it before running the crosstab query.
Is there an easier way to automate the update? So that the user does not have to select another record first.
Been away from access db's for a couple of years and have suddenly job i have to do, and of course the client wants it finished yesterday :rolleyes:
Anyway, my problem is that i have a simple form with suppier details in it - and a combo box which shows the products that that supplier has. The combo box runs on a custom query object i created that matches the supplierID's. Simple!
But for some reason when i change the supplier record, the combo box doesn't with the new list of products. The dynamic QUERY updates just fine, if i check it, however these results aren't reflected in the listbox.
If i use a subform, then that updates just fine. Alas, not the list box, though.
I must be forgetting something simple! Is there some kind of update event i have to create?
I have a form(F_Roll) that runs from a query(Q_roll) which asks the user to input the date. New records are made from this form, and it also allows the user to look at the records from past dates. On this form, my next button creates a new record for that date and fills a field called "RollNumber" with a running counter based on the CurrentRecord. If no roll is present for that date, "RollNumber"=1. If the "Next" button is pushed and "RollNumber"=1, then on the next record, "RollNumber" is filled with 2. If 2 then 3,etc. "RollNumber" is my primary key for the table(T_Roll), and actually, I have the date in front of the counter. The data in "RollNumber" is like "071206-1", "071206-2", "071206-3", "071206-4", etc. What I want to do is have a button on my main form(F_Roll) that will open a new modal, pop-up form on top of my main form that allows the user to enter data for the next record(roll) while still on the current record(roll). I can get it to work if the next record(roll) has already been made. But if I make a new record through the pop-up form and then try to goto the next record I my main form, I get a primary key error about making duplicate primary keys when trying to save the record. The problem is(I think)- The query for the main form has already ran, so it doesn't recognize the new record made from the pop-up form. When the next button is pushed, it is creating a duplicate record(roll) in the primary key. Does anyone have any ideas how to work around this? I am using Access 2003
I need to know how to check if there is a record set matching a name and insert record if there is not one, otherwise just update if a record does match. See code: Code:<%Name = request.form("name")picture = request.form("picture") opencnset rs = Server.CreateObject("ADODB.RecordSet")'if no record matches the nameSQL1= "INSERT INTO background (Name, Picture) VALUES ('" & name & "', '" & picture & "')"'elseSQL1= "UPDATE background SET Picture = '" & picture & "' WHERE Name= '" & name & "'"cn.Execute SQL1closeCN%> Thanks in advance for any help you all can provide!!!! -Chris Gordon
