Queries :: Cannot Edit Query
Feb 10, 2014I have query that runs ok but will not let me edit the data when I run it. I have 4 tables going to that query , if I delete a certain table I can then edit the query.
View Replies
ADVERTISEMENT
Queries :: How To Edit Query Data
May 30, 2013I 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.
Queries :: Edit Records Selected By Query
Dec 31, 2013I have a query that selects records based on certain flags in each record. I can view the record in datasheet view, but I need to be able to edit the records selected by the query using another form. Is there any way to automate this process?
View 14 Replies View RelatedQueries :: Cannot Edit Group By Query - Fields Reverting Back To Blank
Jun 8, 2013I have a query where I group by EmpID, so duplicates do not show up. In this query I have two fields with no data. These fields are to be filled in after the query is run in a form. However, these fields are not editable due to the group by feature. I tried two unbound fields, which does allow me to type, but doesn't save the changes once I click out of the field. The field then reverts back to blank.
View 10 Replies View RelatedCan Queries Edit Tables Positionally?
Feb 8, 2007http://i76.photobucket.com/albums/j12/ogg13/untitled.jpg
Yes, its in excel, im lazy :P
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
Any insight would be stellar! Thank you.
Queries :: SQL Syntax Error After Edit
Nov 19, 2014why the first code is working and after adding a few lines it gives me a syntax error. It highlights the AS ETI. I usually work on the query grid, but I read somewhere there is a limit to the length of expression. So I ended up editing the expression in SQL view, but somehow doesn't work.Working before editing:
Code:
SELECT [Salaries YTD].[Emp#], [ETI Filter].ETI1, Sum([Salaries YTD].DaysWorked) AS SumOfDaysWorked, IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)*5)*0.5),IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000,1000,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000,1000-(0.5*([SumOfGross]-4000))))) AS ETI, DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6) AS Weeks, Sum([Salaries YTD].Gross) AS SumOfGross1
[code]...
General :: Securing A Database / Can't Edit Forms / Reports / Queries
Jan 21, 2014I'm trying to secure my database so users can't edit tables, forms, reports, queries, etc.I'm splitting the database, making an ACCDE for users:
1. I inserted code to disable the bypass key.
2. I inserted code to hide the Quick Access Toolbar (QAT) in the On_Load sub of the form that opens with the DB.
3. Deselect Navigation Pane, Allow Full Menus and Allow Default Shortcut Menus are deselected
4. Then, I use the immediate window to show the QAT, I then create an ACCDE.
How do I link this ACCDE with the original ACCDB? Am I supposed to delete tables from the front end and link the forms/reports to the back end DB?
Queries :: Allow User To Pick A Record From Continuous Form And Edit It In A New Window
Apr 3, 2014I'm using the following code to allow the user to pick a record from a continuous form and edit it in a new window. For some reason my where condition isn't working properly as the edit form always opens to the first record instead of the record associated with the "Edit" button that was pressed.
Code:
Private Sub lblEdit_Click()
DoCmd.OpenForm "frmEditPlants", acNormal, , "[PlantID] = " & Me.PlantID, acFormEdit, acDialog
End Sub
A03: Can't Edit Query Results
Dec 16, 2005SELECT idAuswertung, fiPrüffeld, fiAuswertungMaster, fiAuswertungTyp, AuswertungName, AuswertungSort, AuswertungActive, (SELECT COUNT ([idDiagramm]) FROM tkey_Diagramm WHERE fiAuswertung = [idAuswertung] ) AS AnzDiagramm
FROM tkey_Auswertung;
Why can I not edit the results when I use the Subquery above?
Without the Subquery all works fine.
Christoph
Can I Edit The Results Of A Query In VBA?
Jan 8, 2008I have a query that gives me data from a table, and I then export it as text to Excel. In Excel I do some edits, insert columns with special characters as delimiters, then copy it into a JScript data set.
This all takes some time, and has a number of steps that need to be done in a certain order - prone to errors on my part...Is it possible to use VBA to make the changes in the query to reduce the manual steps perhaps even to remove Excell from the process entirely and just save the query to a text file?
The query has 5 fields:
TxtProductCode Unique to each item
curCurrentPrice The listed price
txtHeading The Main description of the item
txtDetails Additional details
txtVender A name of who has the item
In Excel I change all instances of '"' to ' inch', then insert the delimiter fields, and the final line for the item looks like this:
["TxtProductCode","curCurrentPrice","txtHeading","txtDetails","txtVender"],
Can VBA do this, or even read the query and modify each record then write it to a text file?
Edit Replace Tables In Query??
Jul 6, 2005Is 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?
Thanks
Edit Result Of IIF Query Calculation
Dec 4, 2007Hi ..
I have an issue I do not know how to tackel. I have a select query that selects from three tables to calculate the commission for each transaction. The query is working just fine.
My problem is that I need an additional function ... What if I need to charge a special commission for that specific trade ?
I need to ammend the commission based on a figure I input in a text box from the form view.
Example:
- Commission (calculated by the query) is 100.00
- Special Commission (which is a text box on the form) is 80.00
Then Commission field = 80.00
PLEASE HELP .. Please let me know if you have any other suggestion in tackling this problem. Thanks
Query
Commission: IIf([Shares]![Currency]="USD" Or [Shares]![Currency]="CAD",IIf([Blotter]![Quantity]*[Clients]![USD_2]<[Clients]![USD_1],[Clients]![USD_1],[Blotter]![Quantity]*[Clients]![USD_2]),IIf([Shares]![Currency]="GBp" Or [Shares]![Currency]="EUR",IIf([Subtotal]*[Clients]![EUR_GBP_2]<[Clients]![EUR_GBP_1],[Clients]![EUR_GBP_1],[Subtotal]*[Clients]![EUR_GBP_2]),IIf([Subtotal]*[Clients]![HKD_JPY_2]<[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_2])))
Can't Edit Query Which Has Calculated Fields
Jan 7, 2008Hi!
I have done a query which has calculated fields, but I can't edit the stored fields directly from the query, instead I have to go to the table so as to edit it. Is this the way it works or is something wrong?
SELECT [INSTRUCCIONES DE EMBARQUE].N_OP, [INSTRUCCIONES DE EMBARQUE].[N° FAC EXP], [INSTRUCCIONES DE EMBARQUE].[FECHA CUMP], [INSTRUCCIONES DE EMBARQUE].[DERECHO PGC], [INSTRUCCIONES DE EMBARQUE].[VTO DER EXP], [INSTRUCCIONES DE EMBARQUE].[IMPORTE REINTEGRO], [DETALLE CONTENEDOR].CANTIDAD, [DETALLE CONTENEDOR].[VALOR FOB], [INSTRUCCIONES DE EMBARQUE].[TIPO DE CAMBIO], [TIPO DE CAMBIO]*[VALOR FOB] AS [PRECIO VTA (ARS)], [PRECIO VTA (ARS)]*[CANTIDAD] AS [TOTAL(ARS)], [TOTAL(ARS)]/[TIPO DE CAMBIO] AS [TOTAL(USD)]
FROM [INSTRUCCIONES DE EMBARQUE], [DETALLE CONTENEDOR];
Modules & VBA :: Using ADO To Edit Result Of A Query
Sep 30, 2013For some reason i've had to make an update on some old VBA projects of mine. In this update i switch all references of DAO to ADO. I know there are some limits to what i can accomplish with ADO (where i would need to use DAO). Anyways in some of these old projects i use an SQL statement to provide me with a limited number of records which i then modify by iterating through the recordset.
I can't get this to work with ADO. It's as if it always expects me to provide a table in the argument.
In short my question is. Is it possible to change the values returned from an SQL statement using ADO
Code:
'This is the DAO version which is exactly what i'm looking to using ADO
strSqlCommandText = "SELECT tblMain.TaskID, tblMain.Heading FROM tblMain WHERE (((tblMain.TaskID)=" & ProcessForm.txtIDValue.Text & "))"
Set objRecordset = pubObjDatabase.OpenRecordset(strSqlCommandText, dbOpenDynaset)
objRecordset.Edit
objRcsToDbTable.Fields("Heading") = ProcessForm.txtBoxHeading.Text
objRecordset.Update
This is what i've done and it doesn't work.
Code:
strSqlCommandText = "SELECT tblMain.TaskID, tblMain.Heading FROM tblMain WHERE (((tblMain.TaskID)=" & ProcessForm.txtIDValue.Text & "))"
Set objRecordset = New ADODB.Recordset
objRecordset.Open strSqlCommandText, pubObjDatabase, adOpenDynamic, adLockOptimistic
objRecordset.Edit
objRcsToDbTable.Fields("Heading") = ProcessForm.txtBoxHeading.Text
objRecordset.Update
I can think of some work around like, creating a temp table and insert/update all the records from there or creating an UPDATE SQL statement.
Edit Contain Query To Exclude Certain Results
Sep 6, 2012I've created a query where I am searching a database of job titles for contacts that are considered senior management.
One criteria I've entered into a query is as follows:
Like "*Vice*"
Which is meant to identify anyone with "Vice" in the title. The problem is I have noticed this also returns records with "service". So can I edit this query to specify that the "V" must be capitalized? I think this would serve.
Edit Fields In An Outer Join Query
Mar 13, 2008I have 2 tables (tblItemsQB and tblItemInfo) that have a 1 to 1 relationship linked by the "PartNumber" field. The tblItemsQB table contains a description field and 2 price fields (this table is downloaded from Quickbooks). The tblItemInfo table contains additional fields with information on the part that isn't contained in Quickbooks, like OrderType, Phase, SubSystem.
I import the items from QB into tblItemsQB and then do an outer join with tblItemInfo to link all the information for the part. If I have created a new part in Quickbooks, when I do the outer join (I include all records from tblItemsQB) I can see the new part but since the part number doesn't exist yet in tblItemInfo, I can't add the information to the fields in that table.
What is the easiest way to create the new PartNumbers (those that exist in tblItemsQb but not in tblItemInfo) in the tblItemInfo table so I can add the other information to it?
thanks
mike
Edit Date Range On A CrossTab Query
Nov 10, 2005Hi have a table tblImportLearnerFunding with the fields [LCCode], [Date] and [Total Learner Funding]
I want to create a table to see the funding generated for each LCCode in custom date ranges:
LCCode, DateRange1, DateRange2, DateRange3
I can almost get it to work using the crosstab query wizard, but I need to specify the date ranges. Instead of Jan I want to sum the funding in the date range 01/01/2005 to the 28/01/2005 and so on.... Does anyone have any clues?
Here's the Crosstab SQL:
TRANSFORM Sum(tblImportLearnerFunding.[Total Learner Funding]) AS [SumOfTotal Learner Funding]
SELECT tblImportLearnerFunding.LCCode, Sum(tblImportLearnerFunding.[Total Learner Funding]) AS [Total Of Total Learner Funding]
FROM tblImportLearnerFunding
GROUP BY tblImportLearnerFunding.LCCode
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Edit A Query In SQL View To Perform Multiple Actions
Aug 11, 2005Hi,
How can I edit a query in SQL view to run multiple lines of SQL?
For example I can use the GUI to create a query, then in SQL view enter the following:
DELETE
FROM Employees_copy;
which clears the Employees_copy table. Perfect. But what if I want to do this for mulitple tables in the same query eg for a second table Employees_copy_2 as well? I've tried
DELETE
FROM Employees_copy
DELETE
FROM Employees_copy_2;
which gives a syntax error, as does
DELETE
FROM Employees_copy,
DELETE
FROM Employees_copy_2;
any suggestions?
Forms :: When Using Query With Grouped By Option / Can't Edit Record In Table
Aug 19, 2015I have a table called "Workorders" that I have created a split form for and have customised it so that it had combo boxes etc to enable the user to change the record data.I then needed to add some existing queries to the form so that I could calculate some totals from records in a few other tables. This all works fine EXCEPT that I can no longer change any of the fields in the records.
I am sure that it is something to do with the fact that the 3 queries I added to the form use the "Grouped by" option and based upon my Googling, this is probably the issue.Here is one of the queries:
SELECT DISTINCTROW [Workorder Labor].WorkorderID, Sum([BillableHours]*[BillingRate]) AS [Labor Total]
FROM [Workorder Labor]
GROUP BY [Workorder Labor].WorkorderID;
I need to have the totals and I also need to have the ability to change the data..
Forms :: Search Query With Subform - Edit Records In Related Table
Nov 8, 2014I have a query which looks for like * surname*
in tblemployee fname lname dept active
this works fine and i can search using a requery button
however as deptartments are stored in tbldepts
when i change the query to retrieve the dept name instead of number directly from the table and i try to change this on the datasheet subform it changes it in tbldepts instead?
how can i change what dept the employee is in (as in change the number in tblemployee - but display the actual name?)
Bypass The No Edit Restrictionin "Total Queries"
Nov 8, 2007I have a form for displaying the Club Members info (like Name, Address etc).
I'd like on the top right of the total debit of each Member to be displayed.
For that reason I construct a Total query.
Problem is that when I'm trying to relate the MemberInfo form with this query,
all my records are locked due to the restriction that forces a Group By query.
Since in my case the info provided by this query is eitherway only for read puposes (just displaying the Balance) can you suggest me a trick or something to bypass the Access restriction concerning the Group By queries?
Thank you in advance
Forms :: Edit Record From Query Result As New Record To Another Table?
Aug 22, 2013I have a form that using "Query A" as data source. The Form need add (edit) a field value before save that Query Result to another Tabel. Is it possible to do that?
The PROCESS simply like below: Tabel A --> Query A --> Form -->Edit value a field -->Save to Tabel B
if it is possible, are there some requirements that have to be fullfilled?
Why Can't I Edit My Table?
Aug 5, 2007I had Access '97 and was able to edit my table and make changes, etc.
I upgraded to Access 2000 and now I cannot edit my table and make changes, etc. The table is not locked.
Why?:confused:
Why Can't I Edit Tables At All?
May 8, 2006I'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:
What could be the problem?
Cannot Edit In Subform
May 9, 2006Hi all, I have a search form that pulls data from a query. The data is displayed in a subform. I would like the user to be able to edit the data from the subform. I have set all the allow properties to yes and it still doesn't work. Does this problem have anything to do with relationships.
Thanks to all, this forum has been a great help for me.
Can't Edit Form
Sep 8, 2006I 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.
Tracy