right i have been searching this forum looking for answers to my problem with no luck - i have been searching other forums and i now know how to do this using oracle database which isn't much help because i am using access!
what i want to do is a simple update setting the value of one field but i want to do it on only the records which are returned by a select statement
apparantly oracle has a construct
SELECT <select query here> FOR UPDATE <update query here>
can you do this in access? or is there a workaround?
specifically what i want to do is to read in an excel file which contains all the fields for some records which are already in the database
the table they are in has a valid field (boolean) which when false effectivly means they are deleted from db (we use this instead of actually deleteing so we can duplicate search against previously held records)
i want to find all the records which are in said excel file and set valid to false
so the two parts of this are pretty simple the update is simple, the select is simple but i need to put them together
i have two tables in a one to many relationship, TBL_submissions is a table containing global information about claims submitted by contractors for work done (invoice number, total claim value, contractor etc..), each submission being a batch of claims for specific jobs (measures)
TBL_Submissions is in a one to many relationship with TBL_Measures because one submmsion contains information about many measures. Each record in the Measures table contains only one field IDSubmission relating to this global information, that is IDSubmission which is an autonumber primary key of the submissions table, i.e it is foreign key in TBL_Measures
Each submission comes in to us as an excel file and there is a form where users fill in the global information into text boxes and combo boxes then click an import button browse to the excel file and it gets pulled into a temporary table TBL_TMPSubmission
a query adds an IDSubmission field to this temporary table and then what i want to do is fill it with the autonumber of the record i have just added to TBL_Submissions by pulling all the global information from the form - i can then use a simple append query to load all the new data into TBL_Measures
The buit to add the IDSubmission filed to the temp table works fine and the append query is easy enough i had that going without the ID filed before i added relationships to my database i am trying to fill in the last entered ID with the following statement
UPDATE [TBL_TmpSubmission] SET IDSubmission = (SELECT MAX(IDSubmission) FROM TBL_Submissions)
but i get the error "operation must use an updatable query"
is this a simple syntax error or am i going about this the wrong way?
Hey All!! This is what I wanna get done. I have a big table I am querying into and extracting data and populating it into the new tables that I have constructed.
For eg: SELECT dbo_analyst.anls_id, [fst_name] & [lst_name] AS Analyst FROM dbo_analyst;
This query selects the name and the ID of the Analyst out of the big table. I want to store these values in the Analyst table that I have made. Do i need to combine an Insert or an update query with the select query to simultaneously put the values in the Analyst table. Please help me on how to go about with 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.
This database is for a livestock show that I have been working on for quite some time now. This specific 'section' is for the Supreme Competitor award, in which points are given for the showman's placing in showmanship, ONE highest placing animal in market classes, ONE highest placing animal in breeding classes, and the showman's score on a quiz. I'm having a problem assigning 'points' for a single highest placing animal in market and breeding classes.
I would like to create a query/s that selects all animals shown by an exhibitor in a market class, then take the highest placing animal that the exhibitor had and award (update the livestock table-points field) points for a single animal. This is fine until one exhibitor has MORE than one animal that received 1st place. How do I go about telling the query to just pick one, lol... it doesn't matter which 1st place animal it selects to award the points... just as long as only ONE animal is updated and not all of the exhibitor's animals that received 1st... Then do this again to select one highest placing animal from the breeding classes.
Here's a little outline just for visual sake:
Market Classes
Name Tag No Class Placing <points field update> Sally Johnson 100 1 1st 10 Sally Johnson 101 2 1st Sally Johnson 102 3 1st Kim Smith 200 1 2nd 5 Kim Smith 201 4 2nd Kim Smith 202 5 3rd
See where Sally received 3 1st places, but only one of them is selected to be updated, and Kim received 2 2nd places (her highest placing) and only one is updated.
Thanks SO much in advance... this has really got me stumped.
I'm having a small problem converting a select query I wrote into an update query. Below is my original select statement:
SELECT Mid([address3],1,InStrRev([address3]," ")-4) AS CITYx, Mid([address3],InStrRev([address3]," ")-2,2) AS STATEx, Mid([address3],InStrRev([address3]," ")+1,10) AS ZIPx, [address3] AS Expr1 FROM Exercise2 Where right([address3],1) <> "E" and address3 is not null;
The above statement basically parses the address field. Now what I need is an update statement that will use the above code. I'm needing to update the empty fields for city, state, and zip from the field address3 which contains all 3 combined.
I'm very new to Access 2000. I'm working with 3 tables.
I finally got this sql statment to work: SELECT [tlkp.Language].[LangID], [tblRawData].[LangName], [tblApplication].[AppID] FROM (tlkpLanguage INNER JOIN tblRawData ON [tlkpLanguage].[LangName]=[tblRawData].[LangName]) INNER JOIN tblApplication ON [tblRawData].[AppID]=[tblApplication].[AppId]
How can I change it to UPDATE? I want to update the tblApplication.LangID = tlkpLanguage.LangID using the joins described in the select statement?
There is no LangName field in the tblApplication. I have tried everything and I keep getting syntax errors.
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?
The coding below works fine. It presents a form with a list box of counties. Allows the user to select ALL, one or several counties and returns a query containing the clients from those counties.
The fields showing in the query are First, Last, Add1, FLAGToMap, City, Prov and Sector_Name.
I want to add in there a choice to select only the records that have are TRUE (-1) in the FLAGToMap field - just like the ALL button, this would be an ALL Selected Button let's say.
I would not know where to begin as I copied and adapted the coding below from a sample database and don't understand - at all - how the query is generated. The only coding in the form is the one below.
Private Sub cmdOpenQuery_Click() On Error GoTo Err_cmdOpenQuery_Click Dim MyDB As DAO.Database Dim qdef As DAO.QueryDef Dim i As Integer Dim strSQL As String
Hi, I was wondering why the following code would give me an invalid SQL statement message:
Dim Rs As New ADODB.Recordset Rs.Open "Manzanero # 450", CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
The error message is:
"Invalid SQL Statement; expected 'Delete', 'Insert', 'Procedure', 'Select', or 'Update" I'm just trying to open up the table "Manzanero # 450" so that I might add to its contents. I have Microsoft Active X Data Objects 2.6 library included as well. I find it strange since this is basically a line for line copy of a sample I found in a MS Access book. Please help. Thanks! =)
I have one field AccountName in customer table and another field AccountID.
In my form I would like to select from the combo box AccountName during data entry and then have the AccountID automatically update in the Account ID field.
Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I've got this multiple select listbox which writes data into a textbox:
Private Sub List2_AfterUpdate()
Dim Cursisten As String Dim ctl As Control Dim Itm As Variant
Set ctl = Me.List2
For Each Itm In ctl.ItemsSelected If Len(Cursisten) = 0 Then Cursisten = ctl.ItemData(Itm) Else Cursisten = Cursisten & "," & ctl.ItemData(Itm) End If Next Itm Me.txtCursisten = Cursisten
End Sub
And I've got a SELECT ALL button to select all records in the listbox:
Private Sub cmdSelectAll_Click() On Error GoTo Err_cmdSelectAll_Click
Dim i As Integer
If cmdSelectAll.Caption = "Alles Selecteren" Then For i = 0 To Me.List2.ListCount Me.List2.Selected(i) = True Next i cmdSelectAll.Caption = "Alles De-Selecteren" Else For i = 0 To Me.List2.ListCount Me.List2.Selected(i) = False Next i cmdSelectAll.Caption = "Alles Selecteren"
The only thing is that when I use the SELECT ALL button, the function List2_Afterupdate doesn't work anymore. There must be a simple solution but I just can't figure it out. Can anyone please help me?
I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?
I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?
Hi, is there any (reasonably simple) way to select or deselect multiple items from the List Box with individual clicks without using Ctrl key. Eg first click on an item would select it leaving all other items as they are, subsequent click on the already selected item would deselect it etc. I hope this is not too confusing and I would appreciate some help. Thanks!
i have a multiselect listbox in my form. The multiselectlistbox contains the names of different persons from tblUsers. it's allready possible to write the id's of the names to another table (tblPresent).
But what I can't manage to do is re-select the values in another multiselect listbox. This multiselectlistbox is located on my editform. I can display the values using a valuelistbox, but i need to see the non-selected items too..
When I run the below code I am getting the error "End Select without Select Case" I figured it might be because I have the "End Select" before the "End With" however when I move the "End Select" after the "End With" I get the error "Loop Without Do".
Code: Private Sub cmd_Update_Conditional_Codes_Click() Dim rs As DAO.Recordset Dim rs2 As DAO.Recordset
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
My membership database has worked fine until recently. Now I cannot save inserted data. On attempted saving "Update or CancelUpdate without AddNew or Edit" appears.
The problem. relates to 2 tables Member and Addresses. PK in the parent table Member is ID. In the Addresses Table the FK is ID. There is a One to One relation between the tables and Referential Integrity is set. I know 1 to 1 is not good but it worked fine in this small database.
