I have a commitment table in my database, and normally people will input commitments through a form.
However each month I will have a flat file upload from excel directly into the Commitment Table, these will be up to 1500 lines. (they are downloads from other systems, that we want to have in our database and i can't link directly to these systems).
I created a macro to do this, and attached it to a button and tested it and it works fine.
My issue is this, once I've uploaded it once, next month what I would like to happen is when I upload it again that it changes the original commitment should any of the details be different or it inserts a new commitment if it didn't already exist. (After I have uploaded the original data, I can download it to get the commitment ID to attach to the excel files if necessary.)
I've created an ADP project containingnumerous tables. One (and only one!) table in the set will not open in edit mode while in Access. Here are the particulars:
1) The table was created from scratch in SQL Enterprise Manager, 2) The table is editable in SQL Enterprise Manager, 3) The table permissions appear to be set correctly (and are the same as all the other tables which all allow editting in Access) 4) I open the Access project and double-click the table. The "add record" navigation button is disabled, and any attempt to edit a field elicits a "This recordset is not updatalbe" error. 5) However, I can open the table in Design mode in Access, make changes, and save them.
I have ODBC linked tables to a DB2 database.When I try to edit the values in some of the tables (open the table in dataview and edit one record) I get a 3021 Error -No current record.On other tables (linked the same way) I don't get this error
If I try the update by running a query on the same table, I get no errors.
The system I have is a german Access 2010 on german WinXP and a connection to DB2 LUW v9.7
Is there any set of settings necessary for ODBC to work with access and vice versa?
Hi I have a form, which displays data from a query recordsource. One of the textboxes is a "Comments" textbox, which I would like the user to write in directly, which in turn is a direct link to the data in the table so it is updated immediately.
At the moment, the textbox does not seem to be editable. Is there a standard way to do something like this ?
I have a Union query that is linked into about 10 different excel spreadsheets on our drive and it pulls back only 3 columns of data (Document Number, Date Registered and Date Implemented). I then have another report which refers to the results of this query and does its own thing from thereon.
Here is a sample of the SQL:
SELECT CINT(RIGHT(RT_01000.[Rev-Trac Number],5)) as [Document Number], RT_01000.[CC_RT Issue Date] as [Date Registered], RT_01000.[Actual Delivery Date] as [Date Implemented] FROM RT_01000;
UNION SELECT CINT(RIGHT(CC_RT_01000.[Rev-Trac Number],5)) as [Document Number], CC_RT_01000.[CC_RT Issue Date] as [Date Registered], CC_RT_01000.[Actual Delivery Date] as [Date Implemented] FROM CC_RT_01000;
My Issue:
As it is above, the query will run, but we end up with duplicate numbers in the "Document Number". It only brings back the final 5 digits from each of these files and ignores the differenciating part of the number, the "CC_RT_" or the "RT_". This causes huge issues in the file which then goes onto use these results afterwards.
My Plan: (failed)
I thought I could simply update the "5" to an "11" to catch anything named "RT_00001" and also "CC_RT_00001" serperately so I can clearly see which is which without any duplicates. However, I recieve the below error when doing so:
"Data type mismatch in criteria expression".
I've been playing with this for hours and unfortunately many many reports reply on these results being correct. It is also not possible to change the names of the RT and CC_RT files that it links into.
I'm trying to create a table with an entry for month/day, no year. I want to create a report that can will sort the birthdays based on month in ascending order.
I'm currently using the text field to put in, "10/30" or "1/3" but when I try to order the dates, instead of "1,2,3,4,5,6..." I get "1, 10, 11, 12, 2, 3.." etc.
I want to have a query that I run at the end of each Month to track a workers production and to store that data in a table for each worker by month. Can this be done?
Something like
Worker Jan Feb Mar Mike 45 33 95 Sue 125 44 02
If this can be done could someone explain to me how to do this. I don't even know if a table can be used to keep stats over months and years.
I have a table with product numbers and their cost. That table has 3 fields : SKU, Cost, Month. (The cost can change every month, therefore the list is built to have a cost / month)
I want the user to be able to input the data in the table using the form but I would like the form to be sorted Alphanumerically for the SKU numbers and then per month (in their normal order not alphanumerically).
I have a combobox (named month for testing purposes) to force the user to select one of the 12 months and the field type is Text.
Also I am unsure how to "autosort" (or apply a permanent sort) the form whenever it's opened. Will it be applied if I just set the sort and then restrict the user from seeing let's see design mode?
I have a table full of dates of meetings through out the year...
Example
tMeetingDates
16th August 2005 18th September 2005 19th October 2005 23rd November
-----
i also have a report that i print out each month that has the date of the meeting on it... i currently edit the date manually.
I was wondering if there was a way to automate this facility, so that the report looked to the table of dates and looked for the current Months Date that is stored i the table.
i then want this date to be displayed in the Report.
So in this instance if i am running a report for tomorrows meeting being the 19th October it would display that date in the report....regardless of when i run the report...
Obviously if i run the report on the first of November because the month has changed it would then display the date of the November Meeting...
I'm accessing an iSeries database with MS Access and Client Access ODBC driver. This works fine for reports and such, but not for editing or updating.
Trying to use edit queries, I get error 3073 (..must use an updatable query). But if I just open a table in Access and don't use a query, you should be able to edit directly into the table. But for me, this isn't possible.:confused:
I have an MS Access 2007 desktop database that has been in use for many years. Of the numerous tables, I have three tables that now require users to update some fields via the web. I do not want to move to SQLServer Express, SQLite, MySQL, PostgreSQL... as they are not my skill set.
My question is what is the best architecture to achieve this end so I can automate the presentation data from my desktop to the web pages and have any updates appear back on the desktop?
Is there a way to edit/replace tables in a query after you've copied the query? Basically, I have created a query and would like to copy the quesry. However, I would like to easily change some of the tables within the query. Is there a way to edit/replace the tables?
Data similar to this is imported into the database I am creating for my users. If you notice, in the servername field, some records have no information. Those records imply that the servername above it is the servername in question. Is there any way, via SQL to say something to the effect of:
If the previous records servername field is 'Server 1' then fill in the servername field for the current record with 'Server 1'
Ive thought about transferring the entire table into an array in VBA, and trying to manipulate it that way, since the data is never going to exceed a full page or so. However, im not even sure how to do that yet :P
Some days ago I made it by using "query", but now I forget it how I make the relation on this situation. Here is my problem.I have 3 Table on mdb file, named Table: A, B, Status.Table Status have One Filed with 1 Data: Dishonor
Table A have three fields
Sl Number: (Auto Number) Status: Lookup wizard-data of (Table-Status)-Default Value is "Honor" Amount:Number
Table B have two Fileds Sl Number: Number Status: Lookup wizard-data of (Table-Status)
After Entering Some data on Table A it's Look like as:
Asl numberstatusamount 1Honor5222 2Honor855 3Honor988 4Honor7777 5Honor777 6Honor9999
[code]...
Now I want to change the Status of SL Number Honor to Dishonor so I fillup data on Table B is as like following
sl numberstatus 5Dishonor
Now how I can get the result as following by using query:sl numberstatusamount 1Honor5222 2Honor855 3Honor988 4Honor7777 5Dishonor777 6Honor9999 7Honor6666 8Honor7777 9Honor666
I have three tables: Vehicles; Vehicle Reallocated; and Vehicles Retired. I have a form that runs a query to find all the info in the Vehicles tbl that is not "Retired", not visible in the form. I then have the option to toggle to a Reallocated or Retired form. When i toggle to the reallocated form, i have the like fields in that table (ie Van #, Vin, Make etc) pulling the info from the hidden subform with the vehicle query, so i do not need to fill in repeat data. However, when i add a reallocated date and the new clinic that vehicle is for, i get the record ID for the vehicle reallocated table as expected, but when i save none of the data moved over from the query saves in the record?
How to get all the data on the reallocated form to save?
I am working from an existing database which is just two table. The main table has a massive amount of redundancy and duplication and needs splitting into, at first glance, 5 tables.
After I have run my various make table queries and added a Primary Key and FK field to the new tables how do I populate the FK with the Parent PK.
I thought I could simply add all the fields from the new table and then create an adhoc join in an update query to populate the PK to the FK. When I do this however I get "You are about to update 0 records"
I have tried the table analyzer but it doesn't give the correct options to split the table the way I need.
To make a long story short I have a command button that runs a query and opens a subform based on that query. I want to be able to update the records that are returned (via the subform) but I am getting an error - "This Recordset is not updateable". I've tried changing the recordset property to Dynaset (Inconsistent Updates) but that didn't seem to change anything.
I've created a form (callled Contact) that contains a subform (called Sales). The Sales subform is a datasheet that displays sales for each contact and changes when the record in the Contact form changes. All works fine.
My problem is that I want to display some big text fields from the Sales subform in the Contact form so that I can view them more easily. So I've cut and pasted the fields from the Sales form onto the Conact form and tried to change their Control Source. But nothing I do works.
What should happen is that when a record in the Sales subform changes, the fields that have been cut & pasted onto the Contact form should also change. They do change, but I can't edit the text in them. What have I done wrong?
I am assisting my employer by combining two databases into one. Both databases have the same field "structure" but the data differs. When creating my append query.
Let's say that there're 2 workers: Worker A, and Worker B, and they are fixing some engines. There's 2 different engines: Engine A, and Engine B. And these workers are fixing these engines for all day, and in the evening they have to register how many engines they've fixed.
And my job is to design Access database for them. I thought it will be very easy to do; I've created table with columns: Date, Worker, Engine, Quantity - so every day Worker A and Worker B can register, that they have repaired some amount of Engines A and Engines B.
But there's a problem that I also have to register how many Engines have arrived to the factory, and how many of them weren't repaired on time (E.g. 15 Engines A have arrived, Worker A fixed 5, Worker B fixed 5 so there's 5 engines left for tomorrow)
I've figured out that I should somehow create table IncomingEngines with columns Date, Engine, Incoming, Fixed, Undone (field Fixed should be completed automatically every day for every engine - it would be a sum of engines A and engines B fixed by worked A and worker B - so I could fill Incoming field manually)
It's easy to create this kind-of Query, but I can't add column to querry, or edit it.
I've got a table (called table1), which a select query (called qryTable1) read it and is referenced in a form (called frmTable1).
So now my form display fields of the query of the table.
On the form, below the text boxs for data entry, there is a subform that displays the entire table - table1.
I've done this so I can see some fields of the old data, that helps me to enter new ones. Hope u follow so far... --- This where I'm stuck. When I was half way entering data in the top section of the form (ie using text boxes), if I go to the subform below to copy some values from a field, ACCESS then either adds a new or edit the record. I'm wondering if there is a way to stop ACCESS automatically doing this? I want to be able to add a record when THE add record button is depressed, and that's the only way to add record.
I have a table structure that I guess could be called a dual one-to-many-to-one, as can be seen in the attached Relationships screen print. Customer loan files are maintained in an AllFiles table, tied to the Customers and Loans tables through the CustomerFiles and LoanFiles tables. Users access the data from an unbound form called FileProcessing, where they can select a customer, and a loan for that customer. The files linked to that customer and loan then appear on a form called Files. However, I need the data on Files to be updateable, and I'm having a hard time coming up with a Files datasource that can be updated. The SQL that retrieves the needed data is:
Code: SELECT AllFiles.* FROM (AllFiles INNER JOIN CustomerFiles ON AllFiles.FileNum = CustomerFiles.FileNum) INNER JOIN LoanFiles ON AllFiles.FileNum = LoanFiles.FileNum WHERE LoanFiles.LoanNum=[Forms]![FileProcessing]![tbLoanNum] AND CustomerFiles.CIFKey=[Forms]![FileProcessing]![tbLoanCIFKey];
I tried creating another query that just had the AllFiles table linked to the above query, but the data still couldn't be updated.
