Can I Somehow Edit A Table Through A Form Which Uses Subqueries?
Dec 19, 2006
Short version of question:
My research has shown that Access will not allow edits through a form that 1) has subqueries in its SELECT clause, or 2) uses aggregation such as First(). Is there any tricky way around this?
Long version of question:
I have a database form that shows an overview of orders for products (it's based on a query that pulls all current orders from the big table). I would like to handle both of these on each row:
Show where we built it last time, and
Allow the user to select where it will be built this time.
The problem is that I cannot figure a way to change the form/query such that it doesn't aggregate to find out where we built it last time, or doesn't rely on subqueries that do just that.
But I feel like there should be some way to do it--since each row displayed does rely on one and only one record in the complete table of orders. Which obviously is the record I want to update.
Is there something I can do that will accomplish this? I really do not want to have to make the user open up another form to see last time or choose this time.
I have been developing a form that will allow me to input, edit and delete data in a table.I have the input, delete, clear, and close command buttons working ok, but I am having trouble getting the edit function to work.
What happens is that I highlight the line in the table (shown on the form as a sub form) click the edit command button, and the field are populated with the current data. I then edit the field that I want, and the add, button, changes to Update. When I go to update I keep getting an error message "Cannot update 'ID'; field not updatable.Below is the code I am using, when I go to the debug the arrow points to the "WHERE ID=" line.
Private Sub cmdAdd_Click() 'when we click on button Add there are two options '1. for insert '2. for update If Me.txtID.Tag & "" = "" Then
I have a form with a drop down menu of people to filter a report of projects with the managers and up to 3 assistant managers. When I choose someone from the dropdown menu, I want all of their projects to come up on the report. Currently, only the projects that person is managing come up, not the ones they are assistant managing. In my query that is powering this report, I have joined the manager_id number in table A to an id_num field in table B. To make the assistant managers come up in the report, I need to join the assist1_id, assist 2_id, and assist3_id to id_num also. When I join assist1_id to id_num, I get the following error: The SQL statement couldn't be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in the SQL statement. The way I would like to fix this problem is by creating subqueries in the SQL view, but I don’t know how to code it. If you know how or if you know a better way of doing this, please help! :)
How many subqueries can I put into one SQL statement? I'm trying to run a query that has two subqueries, but it's not working. It works when I create and reference one of the queries separately, but when I put them directly into the statement as subqueries, it gives me a syntax error. It would really save loads of time to have this in one statement. Can someone tell me what I'm doing wrong with this query or tell me if this is possible to use the AS identifier more than once in a query?
qryTwo: WORKS! SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntityDetail.fldValue, tblEntityDetail.fldDate FROM tblEntityDetail INNER JOIN [SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, Max(tblEntityDetail.fldDate) AS MaxOffldDate, tblEntity.fldInactive, tblEntity.fldTypeID FROM tblEntity LEFT JOIN tblEntityDetail ON tblEntity.fldEntityID = tblEntityDetail.fldEntityID GROUP BY tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntity.fldInactive, tblEntity.fldTypeID HAVING (((tblEntity.fldInactive)=False) AND ((tblEntity.fldTypeID)=2))]. AS qryOne ON (tblEntityDetail.fldDate = qryOne.MaxOffldDate) AND (tblEntityDetail.fldDetailID = qryOne.fldDetailID) AND (tblEntityDetail.fldEntityID = qryOne.fldEntityID);
qryThree with separate reference to qryTwo: WORKS! SELECT qryTwo.fldEntityID FROM tblDetail INNER JOIN qryTwo ON tblDetail.fldDetailID = qryTwo.fldDetailID GROUP BY qryTwo.fldEntityID
qryThree without separate reference to qryTwo: DOESN'T WORK! SELECT qryTwo.fldEntityID FROM tblDetail INNER JOIN [SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntityDetail.fldValue, tblEntityDetail.fldDate FROM tblEntityDetail INNER JOIN [SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, Max(tblEntityDetail.fldDate) AS MaxOffldDate, tblEntity.fldInactive, tblEntity.fldTypeID FROM tblEntity LEFT JOIN tblEntityDetail ON tblEntity.fldEntityID = tblEntityDetail.fldEntityID GROUP BY tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntity.fldInactive, tblEntity.fldTypeID HAVING (((tblEntity.fldInactive)=False) AND ((tblEntity.fldTypeID)=2))]. AS qryOne ON (tblEntityDetail.fldDate = qryOne.MaxOffldDate) AND (tblEntityDetail.fldDetailID = qryOne.fldDetailID) AND (tblEntityDetail.fldEntityID = qryOne.fldEntityID)]. AS qryTwo ON tblDetail.fldDetailID = qryTwo.fldDetailID GROUP BY qryTwo.fldEntityID
Hopefully I am wording this correctly??? If anyone can help me figure this out, I would greatly appreciate it. Thanks!!
Hi, I hope someone can help me out... I've made a bunch of queries to get the data I need out of my table, they build on each other and I find that my date query is used twice. In date query I prompt for the date, when I run the percent query I get prompted twice.
How can I set this up to only prompt the user once for the date?
Here is roughly how my queries are dependent on each other
I am having a problem getting the expected results from a SQL subquery. The point of the query is to return all users and user titles that have not been entered into the database for the current month (not in tblHours). Some users have 2 titles and some just one title. Each month we enter hours worked per title so we can track labor progress. The problem lies w/ the individuals w/ 2 titles. The SQL only keys on the user id, but I need to return both user id and title. The variables called iMonth and iYear are passed from the form and the query is executed via a command button. qryTitlesPerUser contains each users title, name, and user id.
Here is the query:
strSQL = "SELECT qryTitlesPerUser.Name, qryTitlesPerUser.title FROM qryTitlesPerUser " strSQL = strSQL & "WHERE qryTitlesPerUser.UserID AND qryTitlesPerUser.title NOT IN " strSQL = strSQL & "(SELECT tblHours.UserID, tblHours.title FROM tblHours " strSQL = strSQL & "WHERE tblHours.month = " & iMonth & " AND tblHours.year = " & iYear & ") " strSQL = strSQL & "ORDER BY qryTitlesPerUser.Name"
I've looked at so many options for writing this query that I can't see the forest for the trees and hoping you kind folks will point me in the right direction.
service_id is a unique ID for each service which relates to data I will need to pull from another table.
prop_id relates to a property ID in another table, not unique as multiple services against one property.
The problem that I have is that each prop_id can have more than one service_date for the same service_year. I need to be able to find the latest service_date on a prop_id and its associated service_year and service_id.
Any pointers would be greatly appreciated. BTW, using this in Access 2003 & 2007
Some essential background first. I have a Balances table which records balances by date. I also have an Issues table where problems are logged. There is a one-to-many relationship between Balances and Issues (i.e. each Balance can have multiple Issues). I also have a Comments table where updates for each Issue are recorded. There is again a one-to-many relationship between Issues and Comments (i.e. each Issue can have multiple Comments)
There are two key date fields in the Issues table :FlagDate (the date an Issue was flagged by a user for investigation)
ResolveDate (the date said investigation was brought to a conclusion)
There is also a date field in the Comments table :UpdatedWhen (the date any given comment was added)
So the basic flow is that an Issue gets flagged (FlagDate), then various comments are added (multiple UpdatedWhen's) and finally the Issue gets resolved (ResolveDate)
I need to incorporate a trend graph which will show the counts ofNew (i.e. new issues flagged as of each day) Cleared (i.e. issues resolved each day)
Updated (i.e. issues not yet resolved but updated each day)
Unchanged (i.e. issues not yet resolved and not updated each day)
Outstanding (i.e. all unresolved issues as of each day)
This is the SQL I've put together to get that table of information on which to base my chart :
Code: SELECT [tblBalances].[BalanceDate] AS AsOfDate, (SELECT COUNT([tblIssues].[IssueID]) FROM [tblIssues] WHERE [tblIssues].[Flag] = True AND [tblIssues].[FlagDate] = [tblBalances].[BalanceDate]) AS New,
[Code] .....
The subqueries for 'New', 'Cleared' and 'Outstanding' work perfectly; the resultant dataset gives me one record for each date in the Balance table and correctly counts the number of issues falling into each of those buckets.
The problem I have is with the 'Updated' bucket. If a flagged issue happens to be updated twice on the same day (which is perfectly acceptable), it counts this twice as well. I don't want this as I just want to know how many issues were updated on any given day - not how many updates there were.
I tried using COUNT(DISTINCT) in the 'Updated' subquery but it gives me a syntax error - on further research, I don't think it's possible to use the DISTINCT keyword in a COUNT subquery (at least not easily)
I also tried grouping by IssueID within that 'Updated' subquery but it still gives me the duplicate count within the same IssueID (and returns nulls rather than zeroes for those days where no updates occured)
I think I need to add a subquery within the subquery () to only return the latest comment as of the date in question - something along the lines of :
Code: (SELECT TOP 1 [tblComments].[UpdatedWhen] FROM [tblComments] WHERE [tblComments].[IssueID] = [tblIssues].[IssueID] AND DateValue([tblComments].[UpdatedWhen]) <= [tblBalances].[BalanceDate] ORDER BY [tblComments].[UpdatedWhen] DESC) AS UpdatedWhen
But how to do this, nor if it is even feasible in Access to begin with.
Hi, I have a form called Edit Column, which contains two text boxes: ID and Description. The user types something in these two boxes and presses a button, and this button should take the value of ID and edit description for that particular ID in the table "Map". Here is the code I have written for it:
Dim d As Database Set d = CurrentDb Dim r As Recordset Set r = d.OpenRecordset("Map")
r.MoveFirst If r! = Me.Text1 Then [I]('Text1 is the text box where the user enters an ID) Do While Not r.EOF r.Edit r![Description] = Me.Text3 r.Update r.MoveNext Loop MsgBox "Successful", vbInformation Else MsgBox "This column does not exist in the Store", vbCritical End If
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 am trying to learn Access form building by creating a training database. I created a form that has all of my class information and the students who are attending the class as show in the attached jpg.
If a student has already attended a previous class I don't want to enter his information again. I want to do a lookup for the student and if the student does not exist then create a new student. Is there a way of doing this without coding?
I am trying to edit a specific record in my table. I want to know how to go about referencing it. I'm dealing with a table of books each having its own Book # assigned to it.
Example: The table has Book#(primary key), title, author, quantity.
How would i go about increasing/decreasing the the quantity by a value(i.e. 1, 2, 3, etc.) in a form that i'm in and then saving it to that specific books quantity? I'm attempting to keep track of our inventory.
We are using access 97 and I have a linked table to sql 2000. A couple of weeks ago, our sql server crashed and we had to completely rebuild it / restore all of the data. Ever since then, only one of our linked sql tables in access is acting up. Whenever we go to edit a line (sometimes, not always), we get an error which states the following:
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.
Options are Save Record (greyed out), Copy to Clipboard, and Drop Changes.
About all I have tried is re-linking the table...any other ideas?
I have a report that I run which has PT_Ins_ID in it, I am trying to edit that row with the following code.
Code: Private Sub cmd_Deact_Click() Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("dbo_Patient_Insurance") If rs.RecordCount <> 0 Then With rs
[code]....
I've also tried this.
Code: Dim rs_PI As DAO.Recordset Set rs_PI = CurrentDb.OpenRecordset("dbo_Patient_Insurance")
I want an append query to add records to one of several tables, depending upon the value of a variable entered into a form. This variable is also used to select the appropriate records. The query is invoked from the visual basic code behind the form.
Can anyone tell me how to edit the destination table from within the visual basic?
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 ?
While the form itself is bound to tbl_Orders I decided to use unbound text boxes to enter the data (knowing I was in for some work!). Reason is that if textboxes were bound would need to use a subform with multiple combo boxes and that UI did not seem suitable for the application.
I’ve completed the code to add new records to tbl_Orders and tbl_Junction from the unbound textboxes. I'm using the VBA .AddNew method and is working well. Now I need to integrate code that will allow users to edit existing records while they are viewing them on the form.
I have the code to look up the record set I want to edit in tbl_Junction and am familiar with the .Edit method but the issue is that the required changes MAY include not just editing existing records but also deleting and adding new records. For example, the initial order may have been for Apples and Oranges and the revised order may change the number of Apples, eliminate Oranges entirely, and add some Bananas. So, it seems I need to .Edit Apples, .Delete Oranges, and .AddNew Bananas?
Should I try to write code to determine where I need to .Edit/.AddNew/.Delete? -- not looking forward to that. Thought maybe could delete the entire junction record set and then just .AddNew for everything per the revised order. That should work as the display on the form would be correct for the whole order. But maybe would cause a problem as would remove the FK in tbl_Junction that corresponds to the PK in tbl_Orders?
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 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 have a form called contractors. It works exactly like I want it but it won't let me edit it. The form is based on a query. I have checked the query as stated to do on some areas of this forum and I am able to change it by typing something in a field and it also updates the form for me but I can't edit the form directly. I have allow edits, allow deletions, allow additions set to yes and I have no locks on it. Any ideas?
I have also checked help in access on this to no avail. I am attaching the database so somebody can hopefully help me.
i have created a small application for my assignment using ms access. i apply several function to dis system. initially it works but then i dono why suddenly it came out wif an error sound which mean u cant even edit anything in the form..it sounds ridiculous but dis happened to dis system. i have checked all the properties settings for that form. i compared wif my previous system which i didnt apply the complex function which just pull some data from database. all seems ok wif the settings for the new one...
I'm creating a form that has combo boxes pertaining to each of the following fields from table "Documents": DocumentID (primary key), DocumentTitle, DocumentAuthor, and DocumentYear. No combo box is used with any priority over the others. The working form will allow a user to retrieve the full document record (data for all fields) by using any combo box they want, as well as any combination of combo boxes. This means that if a selection is made in one field's combo box, the drop-down lists in the other boxes need to update based on that preliminary selection. The filtered results for each field, based on any and all combo box selections, are always shown in a single datasheet on the form.
The kicker is that when a user starts filtering records by making selections from the drop down list in a combo box, but then decides to TYPE in another field's combo box, I want the combo box they typed in to do two things: 1) filter the records for that field based on what they typed, if any records meet that criteria (e.g. they typed "B" so only records beginning with B are shown), and 2) if no records match the typed criteria, the search starts all over (at the top of the cascade), filtering all library records based only on what they typed in the most recent combo box.
Does that make sense? Any tips welcomed.
I don't know how to cascade combo boxes in this way.
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?
