Insert... ; Select @@identity
Feb 19, 2006I work with project access (*.adp)
I want to know if it possible to take the last ID after the sql INSERT QUERY
my code:
strSQL = "INSERT INTO missionFather ....."
DoCmd.RunSQL strSQL
thanks!
ADVERTISEMENT
Reset Identity Key
Mar 15, 2007Hi there....
How can i reset the identity key?
Thanks
Danny
Insert Into ... Select Error
Nov 2, 2005I`m trying to insert some rows of the table [Produse finite dbo_SC03XX00] into the table [Loturi dbo_SC33XX00].
So, I tryed to run the following queries:
A) INSERT INTO [Loturi dbo_SC33XX00] ( [Loturi dbo_SC33XX00].SC33001, [Loturi dbo_SC33XX00].SC33002, [Loturi dbo_SC33XX00].SC33003, [Loturi dbo_SC33XX00].SC33005)
VALUES (select [Produse finite dbo_SC03XX00].SC03001,'01','000000000999',5000 from [Produse finite dbo_SC03XX00]);
But, when I want to save the query I get the following error:
"Syntax error. in query expression 'select [Produse finite dbo_SC03XX00].SC03001'"
B) INSERT INTO [Loturi dbo_SC33XX00] ( [Loturi dbo_SC33XX00].SC33001, [Loturi dbo_SC33XX00].SC33002, [Loturi dbo_SC33XX00].SC33003, [Loturi dbo_SC33XX00].SC33005)
VALUES select [Produse finite dbo_SC03XX00].SC03001,'01','000000000999',5000 from [Produse finite dbo_SC03XX00];
and I get the error:
"Syntax error in INSERT INTO statement."
I thought that the select query might be wrong, so I tested it:
"select [Produse finite dbo_SC03XX00].SC03001,'01','000000000999',5000 from [Produse finite dbo_SC03XX00]"
and it runs correctly.
How can I solve the problem? How can I insert the specified rows from the table [Produse finite dbo_SC03XX00] into the table [Loturi dbo_SC33XX00]?
Thank you in advance.
INSERT SELECT Statements
Mar 1, 2007I was wondering if you can an INSERT SELECT statement for 2 tables w/ different data source?
Like one's from Oracle and the other's from Access?
Please help :D
How To INSERT INTO Values And Select...
Apr 28, 2007My thinking must not be right as I can't get this to work but i have a table with
Date AMT
02/05 50
02/06 20
and soforth....
I am summing up each week and putting this in a table with values
WeekBeginningDate WeekEndingDate SumofAmt
I am trying to do
INSERT INTO tblTwo ("02/05/2007", "02/09/2007", SumOfAmt)
SELECT sum(Amt) FROM tblOne
where TDate >=#02/05/2007# and TDate <= #02/09/2007#
The two dates in the INSERT INTO are just values and the SumOfAmt is from the select. Can someone help me with this? Thanks in Advance!!
Login Identity Issue
Feb 12, 2006I have this form that searches a table to find a unique username for the user. Let me be clear this is not the primary key, but it is as unique since there can not be more than one in table. What i have been able to do is search the table and retrieve a unique id. Example jason brown comes into your company
Fname = jason
lname = brown
what the code will do is create a user id based on his name. the first typical id will be jbrown, but lets say in this insistance jay brown is taken. will what my code does is it returns jabrown. the problem i am having is that i want to find out how to output the name of the user who has the orginial name. example jabrown is returned for jason brown, now a subform will return the current information:
John Brown, jbrown, table1, and the list could go on. This is where i am stuck, i get an error.
Code:Private Sub btnUserID_Click()On Error GoTo Err_btnUserID_Click' this module is designed to check and return a unique user id.' If a user id is not unique enough it will continue to add characters until either there are no more' letters to count or it finds the unique id desired.Dim strUsername As StringDim booMatch As BooleanDim intChar As IntegerCurrentDb.Execute ("delete * from tbl_temp")booMatch = FalseintChar = 1 'defines how many characters to count forstrUsername = Left(Me!txtFname, intChar) & Me!txtlname 'statement counts the first character in the first name and combines it with the last name. Do While booMatch = False 'boomatch is false since it will asume the record is not present booMatch = IsNull(DLookup("[Username]", "tbl_Users", "[Username] = '" & strUsername & "'")) 'Here since boomatch will be assigned the value of the new username entered. 'if a value is equal then a value of true will be returned If booMatch = False Then ' since no match has been found yet boomatch continues meet criteria Dim firstname As String firstname = "SELECT tbl_users.[fname]" & _ "FROM tbl_users" & _ "WHERE tbl_users.[username]='" & strUsername & "';" CurrentDb.Execute "INSERT INTO tbl_temp (UserFirstName, UserLastname, username) VALUES ('" & firstname & "','" & [txtlname] & "','" & strUsername & "');" intChar = intChar + 1 'Boomatch will add one more character to char to move to the next letter strUsername = Left(Me!txtFname, intChar) & Me!txtlname 'this line will assign a value of username to strusername End If LoopMe.txtUsername = strUsername ' user name is assisgned to our VB text boxIf intChar > 1 Then ' if our word counter is greater than one then the following values are visible Me.txtintchar = intChar Me.txtintchar.Visible = True Me.lbintchar.Visible = True Me.Form2.Visible = True Else 'if it is 1 or less are values will be false Me.lbintchar.Visible = False Me.txtintchar.Visible = False Me.Form2.Visible = FalseEnd IfForms![form1]![Form2].Form.RequeryExit_btnUserID_Click: Exit Sub Err_btnUserID_Click: MsgBox Err.Description Resume Exit_btnUserID_Click End Sub
the highlighted part is where the code does not seem to be working.
Insert A Select Statement In A Text Box
Aug 1, 2006hi guys. i was hoping you guys could help me, i have a combo box "cbocontract" which gets populated according to a selection from another combobox(cboAll). now i have another combo box(cboStatus) which according to what the user selects in cbocontract list box it should display active or inactive, now i have the row source from the cboStatus like this:
SELECT DISTINCTROW Test.Status FROM TEST WHERE (TEST.Facility=forms![Change of Status]!txtInvoice.value) and (TEST.PM_Contract_ID=forms![Change of Status]!cboContract);
it works perfectly, however is there a way to make put this code in a text box? how do i insert the select distinctrow into a textbox??? it should only display one value according to what the user selects in the cbocontract combo box... also, the user should be able to edit this textbox.
:o
Modules & VBA :: INSERT INTO SELECT Statement
Mar 4, 2014I have an append query that contains an IIF statement. I want to code that into a VBA function. The SQL view of the query looks like this:
Code:
INSERT INTO tmpAvailInv ( NUID, Inv_Name, F_Name, M_Name, L_Name, Role )
SELECT tblPeople.NUID, tblPeople.[F_name] & IIf(IsNull([M_Name])," "," " & [M_Name] & " ") & [L_Name] AS Inv_Name, tblPeople.F_Name, tblPeople.M_Name, tblPeople.L_Name, tblPeople.Role
FROM tblPeople
WHERE (((tblPeople.Role)="Investigator") AND ((tblPeople.Archive)=False));
What I wrote for the VBA code is this:
Code:
Dim strSQL As String
Dim db As Database
Set db = CurrentDb
[code]....
Where it chokes is on the IIF statement with the double-quotes in it. I've tried several combinations with single quotes and double double-quotes. I'm just not getting it.
Calculate Age Using Persons Identity Number
Apr 25, 2012I am using 2010 and have a bound field called Policyholder ID number in a table called Policy Information. I would like to calculate how old a person is by using the identity number which is 13 digits (South African ID) and the format is as follows - 7009215069084. Using the first 6 digits this person was born on 21 Sept 1970 and is 42 years old ( Current year of 2012 minus year of birth of 1970 = 42)
How I can include a calculated field so once I type in the identity number the age of the person reflects in a field called Current Age.
Select Records To Insert Into A Field From Pop Up Form
Jul 4, 2005Hi all,
I have a table which contains business details (name, address etc) and also a field for clients. I then have a table which contains client details. Is there anyway I can click a button on a form containing business details which brings up a form containing the clients which would allow me to click the clients I want to be inserted onto the clients field on the business details table?
Does anybody have an example of this.
Thanks for your help!
Difficulty Combining Insert Into Or Select Into With A Union, Please Help!
Jan 23, 2007I am having difficulty getting a query to work, there's probably something really simple I'm missing. . . I can get the Union function to work but I can't get it to create a table for me. The basic query looks like this:
INSERT INTO test
SELECT * FROM [Design] WHERE (Design.CID Not Like "*-*")
UNION
SELECT * FROM [Release] WHERE (Release.CID Not Like "*-*")
It works fine and shows me the table I want without the first line, but I would like it to input into a new or existing table. Any help would be greatly appreciated!
Can You INSERT INTO Table With SELECT.. UNION Queries
Mar 29, 2005 I have a UNION of two SELECT queries. It works very well but I need the end-user to be able to modify the data so I am using INSERT INTO tablename. This will work for one query at a time but if I try to use the UNION it reports “Syntax error in FROM clause”. Can you use UNION in this case or can anyone see my error?
The full statement is rather complex, I have added a few spaces and linebreaks to make it readable as follows:
INSERT INTO TableCompleteForMailing
SELECT [Address List].[FamilySurname] AS Surname,
[Address List].[DEARFirstnames] AS FirstName,
[Address List].[Mailing] AS Mailing,
[Address List].[Christmas Mailing] AS OtherMailing, [Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Address List]
UNION
SELECT [Names].[LastName] AS Surname,
[Names].[FirstName] AS FirstName,
[Names].[MailingList] AS Mailing,
[Names].[Selected] AS OtherMailing,
[Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Names],[Address List]
WHERE [Names].[AddressListID]=[Address List].[AddressListID]
ORDER BY Surname, FirstName;
Queries :: Checking Format (Identity Of Field)
May 28, 2013I've got a database with an 'address 1' field. Sometimes if it's just a number, excel formats the field as a date. Is there anything to check the field to identify any that are showing as dates as opposed to just normal fields. For example identify all customers who have some variation of dd-mm-yyyy in the address-1 field.
View 10 Replies View RelatedQueries :: Basic Delete Insert Select Statement
Nov 8, 2013what is wrong with the following statements. I'm new to access SQl. This would work in ms sql.
Code:
DELETE from Table1
INSERT INTO Table1(email, productid, datecreated, datesend) values ('adf', 5, '10/10/2012','10/10/2012')
Select * from Table1
Forms :: Insert / Select Values From Selection Combo Box
May 20, 2013I want to insert obtain marks of the subjects. Find the attached my db. when i select combo value my requirement is to insert the all records which associate with the combo. but when i select combo value it will happen nothing.
View 7 Replies View RelatedInvalid SQL Statement; Expected 'Delete', 'Insert', 'Procedure', 'Select', Or 'Update
Nov 8, 2004Hi, 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! =)
G
Modules & VBA :: Allow User To Select Record From Subform And Add It To Table - Insert Statement
Jun 18, 2013I have a sub form with staff records on it within a main form. I am trying to allow the user to select a record from the sub form and add it to a table, here is my code which, to me, looks correct. However it gives me an error saying "Syntax error in INSERT INTO"
Code:
Private Sub Command3_Click()
Dim dbs As Database
Dim sqlstr As String
Set dbs = CurrentDb
Forename = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_forename, "")
Surname = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_surname, "")
[Code] ....
Insert Records Into Table Based On The Select Criteria From The First Table.
Mar 22, 2005Hi
I have a small database with 4 tables that I am using for the current problem.
The tables are call, parents, mailman, orders.
Call and parents are related by the call ID (a primary key in the Call table.).
Mailman and orders are related by a Unique Id (a primary key in the mailman table.).
Forms involved are frmmain and frmsub.
Frmmain contains the call table information in the main form and parents information in the subform.
When a user enters a call with call ID and enters the operator name and parents information in the sub form,
When a user clicks the OK button on the main form, necessary changes should take place
if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.
Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information.
Orderid being autonumber.
Uniqueid from the mailman table.
Orderdate system date.
Ordertype should be “Mailman”
Select Query + Insert Query
May 11, 2006Hallo everyone,
I have two tables in the database table1(col1,col3,col2) and table2(col1, col2). First of all i will have to select col2 from database and for each and every value of col2 i need o make an insert query in the table1(..,..,col2).
It will be helpful for me if anyone call solve this problem for me.
aravind.s
Tables :: Use Insert Into Command To Insert Filepath Into Table That Adds Blank Label
Aug 28, 2014I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
INSERT Query - Insert New Data Only
Jul 2, 2010Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.
TBL_NEWDATA { Person_ID, Department_ID }
TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID)
SELECT Person_ID, Department_ID
FROM TBL_NEWDATA
WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
Modules & VBA :: Programmatically Find And Select Item In Multi Select List Box
Apr 23, 2015I 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?
View 2 Replies View RelatedForms :: Select Record On Subform To Select Default Value In Combo Box?
May 6, 2014I 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?
List Box One Click Select/deselect With Multi Select
Aug 28, 2004Hi,
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!
Queries :: Insert Into One To Many PK And FK Insert
Mar 25, 2015I have a normalized DB with one to many relationships, using Primary and Foreign Keys.
I need to do inserts and maintain the PK/FK relationship, which means when I add a new PK I need to insert that PK as FK in other tables in the same transaction.
How do I do this in Access? SQL Server I use transactions, but I can't lock up the tables in Access like that.
I did a bunch of searching and found nothing, which leads me to believe I am way off in my thinking. Below are the dirty details
I have 2 tables, tblName and tblPhone. 1 name can have many phones.
tblName has PKName. tblPhone has PKPhone, FKName.
I have a form where user enters a new Name and PhoneNumber.
Name gets inserted to tblName, assigned with PKName = 100
Phone should get inserted into tblPhone with PKName.
IE Insert into tblPhone (FKName, PhoneNum) VALUES (100,"212-555-1212").
The dumb way I am doing it now is I insert to tblName, query tblname for the PK, then write to tblPhone. This can't be right.
Select Values In Multi Select Listbox
Aug 19, 2005i 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..
hope someone can help me out