What I am doing is attempting to update a table through a form. I have been reading up on update queries, but I am not quite understanding how to use them. I have a table containing consumables that we constantly issue and receive. I have a form for each of these functions. Example: I order two ink cartridges from a vendor through a purchase order. When they arrive, I would like to enter the qty. I am receiving, then have it automatically add it to the table value. I don't know where to implement the arithmetic. Also, for PO's with multiple items, what's the best way to update the table with those?
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.
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?
Erm, I know this sound a bit silly....but I woudered if there was a way of adding anew record to a table using vb code?
My table is called tblTransmissions
The fields are TransmissionCode, AutoNumber TransmissionDate, Date/Time TransmissionTime, Date/Time TransmissionStatus / Text TransactionCode / Number (Linked to another table by One-To-Many)
When I hit a command button, I would like to update the recordset by firstly adding a new record. Then set Date & Time to Now, Then TransmissionStatus to a control on a form, same with the Transaction Code.
Normally I would place the values on a form and update the values that way, but just wondered if this was possible!
I am chasing a quick solution to what I am sure is a simple thing.
In my database I have a table of items that have to be held for different periods of time (one item type kept for 5 years, another for 3 etc). In another table, linked to the first through the item type, I have dates entered against records which, using the first table, will calculate the date I can get rid of the item.
My problem is that the period of time for some items to be held has now changed, and I want to change all the relevant records to update to the correct disposal date.
I have tried just changing the period items are held for (in the form), and that will provide the correct date for future records, however I want to refresh the old records as well so they are all correct.
Is there an easy way to achieve this without going through each individual record.
The problem is I removed my primary key (if you can even use a primary key for this) and there's no record number option on the query. I just need 800, it doesn't matter which 800.
I was thinking of adding a field with a series of unique identifiers, but I don't know how to autofill sequentially.
Hi all I have a table "tblEmployee" which has "name, employee_no, manager, etc" I recieve from HR a updated spreadsheet which can have new employees or just updates. ie new manager name. I can run an update query to update changes based on the employee_no which is constant. How can i add new starters from this list ?
I am a new user to MS Access and i'm creating a stores account for my works, however i'm having problems with the updating of records after colleagues have been issued with kit. I have three table (employees, kitlist and issuelist) what i want is that when i issue kit to employees this updates the item in the kit list to reflect that i have issued an item, ie. take the number away from the units in stock. This is probably really easy however cannot find a solution to this problem.
I have a couple questions I hope that you can help me with. I have an asp page that is retrieving a bunch of records from an Access Database based on a field value (in my case its based on Department). They are then placed in a table format in the asp page. The user can then modify one or many records. Everything was working fine and then we upgraded our IIS Service from IIS5 to IIS6. Once we did that I started getting errors. I was able to update certain departments and not others. This told me the code was good and that the database permissions etc were good. It has to do specifically with the value or similar. I looked further and came to realize that it appears there were to many records to update. The Departments with a few records updated fine. THe ones with 500-1000 were erroring out. I went into the database and deleted the records from 1000 to 500 and everything works.
Does anyone know if there is a record limit to update?
Is there a function on ASP that I can use like Me.Dirty that will search first for updates and then just update the single record?
Is this a result of IIS6?
Here is a bit of my code: This is the area that it is bombing out on....I am not an expert and believe I might have hit an ARRAY size limit. Does that make sense?
I am looking for a way to update certain records in a table with an incremented value of +1 each time.
For example, TBL_MAIN will have fields REF_NO and INCREMENT_VALUE. In field REF_NO, the same record may appear more than once and the INCREMENT_VALUE field needs to update by +1 each time. I have attached a simple snapshot of the table. In this example, I would like ref ABC123 have it's INCREMENT_VALUE increased by +1 each time following on from the last incremented value of 12460. I believe this may involve DMAX function, For Next loop and possible DAO recordset code but don't really know where to start!
I am building a debt management program in A2k. I have created a tabbed control which contains 6 pages based on a set of queries. These pages are a budget template and the intention is that when this form is inserted as a subform into the customers form the budget will open as a blank template with the budget categories displayed. The clients budget will then be entered and saved. The Budget table contains 56 budget categories.
At the moment when I insert the budget form and use it the only records that change are the template. I want the first 56 records to remain with zero values so that they display as blank for a new client but I want the total 56 new records to have the customer ID associated so that when that client record is accessed again their budget will appear. I have attached the SQL code for the query that displays a sub portion of the budget.
SELECT Budget.BudgetID, Budget.ClientID, Budget.SectionNumber, Budget.BudgetTitlesID, Budget.Description, Budget.Cash, Budget.Credit, Budget.Frequency, IIf([frequency]=1,([cash]*52/12),IIf([frequency]=2,([cash]*26/12),IIf([frequency]=3,([cash]),IIf([frequency]=4,([cash]/12),0)))) AS Totalscash, IIf([frequency]=1,([credit]*52/12),IIf([frequency]=2,([credit]*26/12),IIf([frequency]=3,([credit]),IIf([frequency]=4,([credit]/12),0)))) AS Totalcredit FROM Budget WHERE (((Budget.ClientID)=[forms]![customers].[clientid]) AND ((Budget.BudgetTitlesID)=1)) OR (((Budget.ClientID) Is Null) AND ((Budget.BudgetTitlesID)=1));
My thought was to do an after update event that would copy the clientID from the main form to the sub form and populate all the records in the budget. However, I have only succeeded in populating the individual record that has changed.
I probably haven't explained the problem sufficiently to get some help but would appreciate any thoughts.
I am in the early stages of building my 1st relational database (see attached). Ones I have built in the past were built on a knowledge of the sample ones provided with A97. I have since realised (from this excellent site) everything I have done in the past was completely wrong! One big table to hold all info causing hundreds of duplicate records - Doh!
My Db is being designed to keep a record of all the refurbished dictation/transcription stock we hold.
I have a question or two (basic stuff but i'm learning (slowly)).
1. Do my relationships look ok so far?
2. I want user to simply select a model in "Tbl_UnitDetail", and some how automatically lookup the rest of the info such as make and type from "Tbl_unit" New models will be added to "Tbl_unit" by administrators as and when they become available. How can this be done?
Any advise or comments on my limited knowledge would be greatly received. Many thanks.
This may seem a really dumb question but I just can not get my head around the best way of solving it.
I have two tables orders and orders_tmp the table orders is created form the orders_tmp table. The order is created by a single field createorder (yes/no)from the order_tmp table. I run an append query to copy the records into the orders table. this woks fine.
The problem I am trying to solve is how do I now go back and change the order for what ever reason. I need the order_tmp table to now also include my existing order.
I have a table "volumes" with the following fields: product, year and one field for each month of the year. I update the data in this table monthly. From that table I calculate a total volume for each "project" through an After Update event on my "Cut In Month" field on my form Right now, when I update my volumes in the table I have to go through and trigger the After Update event on each and every record to get the calculated field to represent the updated volumes from the table.
I would like to have one button on my form that does this all at once. Is this possible without binding it to the table?
Hello, This is probably a very basic question, but I can't think how to answer it myself. I want to use the built-in calendar on a form, click it once, and set the date for ALL the records in the table because the table contains records that are part of a batch. Do I try to use the date selected as criteria in an Update Query? Do I try to use the date selected to set the DefaultValue for the field? I know that many disagree with the viability of the built-in calendar, but my question isn't really about the calendar as much as it is about transferring the data to every record.
It is not really 1.74 million records, but Access thinks it is, for some reason.Here's what happens: I get a CSV file in with 2196 lines. There are 2 date fields that are formatted poorly. Sometimes it is mddyyyy and some times it is mmddyyyy. I import the data to one table and then export everything to another table except those two fields. There are two date/time fields in the new table that are left empty at first. I then run 2 update queries to format and convert these poorly formated date fields. The query simply joins to the two tables on 3 fields and then updates the date field. When I hit the preview button on the query it takes about a second and says it will update 2196 records. Perfect.When I actually run the query, it takes about a half hour and tells me it will be updating 1.74 million records. Any ideas why this is happening? If and when the query runs there are still only 2196 records in both tables.
1 table = personal (All Personal Info) 2 table = professional ( All Professional Skills of the person in personal)
2 forms as under
1 form = personal ( Here i put all Personal Infor of Person) 2 form = Professional ( here when i select name of the person from DropDown Box entered in Personal table it shows his nic no. automatically in next field, now here is problem if person is already added and i select his name from the drop down list his info from professional table shld be displayed also. so i can make changes in it and if there is no information added in professional table of that person then we can add them easily and like that we can easily navigate through record buy just selecting name of person from the drop downloa list)
NOTE: Sample Database is added if anyone can make changes in it.....
Hello, This is probably a very basic question, but I can't think how to answer it myself. I want to use the built-in calendar on a form, click it once, and set the date for ALL the records in the table because the table contains records that are part of a batch. Do I try to use the date selected as criteria in an Update Query? Do I try to use the date selected to set the DefaultValue for the field? I know that many disagree with the viability of the built-in calendar, but my question isn't really about the calendar as much as it is about transferring the data to every record.
Hello, I would like to update multiple records in one query. The scenerio is that i have 3x3 (3 in row and 3 in column) text feilds and i want to update it once, in one query after entering the data in text fileds. Can anybody help me please......????
I have an access database with about 200 records. Names, ID's, addresses, a checkbox called "apply_to_all" and a date field which is selected via a combobox.This combo box is called "date_started", and a textbox called "prefix". This textbox has a default value of "Given on".
What I am trying to accomplish is: A date is selected from the combobox, then a checkbox("apply_date_to_all") is checked and this date is applied to ALL records in the database along with the default value that is in the prefix textbox. (Concantenattion?)
I assume this can be accomplished by some soft of sql statement?
I have a table with about 1,2million records. I get an Excel list of about 35000 weekly, using which the table has to be updated: the records, that exist get fields updated, the ones that are new have to be added.
What is the fastest way to do this?
I'm trying with this code, but it's awfully slow, even if I read the update ranges into memory:
Code: Set cn = CurrentProject.Connection Set Rs = New ADODB.Recordset Rs.Open "Select * from tbl_TTextract", cn, adOpenDynamic, adLockOptimistic i = 2 Do While i < lr_2 + 1 'update With Rs
I'm looking for a way to fill out 5 records in one row in a table at once. I want to update a supplier and price per unit for supplier via a form. I have price per unit 1-5 and the price will be the same for all 5 records. Is there a way to do this through append or update query and if so which is best in my situation?
I 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?
Hi I'm using MS Office 2000, I have an Access database table that contain many fields, namely I need to update FieldA from an Excel Spreadsheet according to the specified primary key of CustomerID. Is there a way to write automate process to import the records from an excel spreadsheet and then place those records in FieldA of the Access Database according to the corresponding CustomerID? The reason I ask is that these two fields are not exactly in the right order in my spreadsheet and my table and to do it manually will take lots of effort.
