Insert And Update In Same Sql Statement

Feb 20, 2008

Is it possible to have an insert and update in the same sql statement using MS Access 2002? I could split it into seperate statements but for code purity I would like to have it in one :cool:

View Replies


ADVERTISEMENT

Invalid SQL Statement; Expected 'Delete', 'Insert', 'Procedure', 'Select', Or 'Update

Nov 8, 2004

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! =)

G

View 2 Replies View Related

"INSERT/UPDATE" Statement Error

Sep 19, 2004

I try to do an INSERT statement throught my ASP to add records to my Access DB. However I get an error when I execute this. The code in ASP is -

Code: strSQL = "INSERT INTO tbl_psm (LocID, Week, Month, Year, Rating, Remark, Action, SubmittedDateTime) VALUES ('" &cint(intLocid)& "', '" &cint(sWeek)& "', '" &cint(sMonth)& "', '" &cint(sYear)& "', '" &cint(sRating)& "', '" &sRemark& "', '" &sAction& "', '" &formatdatetime(date,vbshortdate)& "');" objConn.Execute strSQL

I get the error page -



Microsoft JET Database Engineerror '80040e14'
Syntax error in INSERT INTO statement. /PeriodicWorkScheduleRatingSubmit.asp, line 65

I tried doing a response.write onto the ASP (to get the actual SQL), got the query, copy-pasted it and ran it on MS Access - it worked fine there. The query is -

Code: INSERT INTO tbl_psm (LocID, Week, Month, Year, Rating, Remark, Action, SubmittedDateTime) VALUES ('50', '2', '9', '2004', '3', 'asd', 'asdasd', '9/20/2004');

I can't understand if it works in Access, who not work through ASP?? Is there somethings wrong somewhere? I cannot see it..

Please help..

View 2 Replies View Related

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

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?

View 2 Replies View Related

INSERT INTO Statement Help..

Mar 17, 2005

hello all

after many hours of reading access help, for my update command button,
not sure what i did wrong...

when I on the update button, its give me blank space on the table where all my contacts are stored,

I trying to updated existing client contact info, without changing contact name,
I have contact form with drop down menu, where I select clients name and view and clients information,

Update
"'" & Me!TbxComp & "', " & _
"'" & Me!TbxStreet & "', " & _
"'" & Me!TbxFloor & "', " & _
"'" & Me!TbxCityStateZip & "', " & _
"'" & Me!Telephone & "', " & _
"'" & Me!TbxFax & "', " & _
"'" & Me!TbxAcctMgr & "', " & _
"'" & Me!TbxEmail & "')"
-----------------------------------------
here is what my form code looks like,
Private Sub cboContact_AfterUpdate()

Dim ContactID As String
Dim rst As DAO.Recordset
ContactID = "SELECT * FROM Contacts " & " WHERE ContactName = '" _
& Me!cboContact.Value & "'"
Set rst = CurrentDb.OpenRecordset(ContactID, dbOpenDynaset)
If rst.EOF = True And rst.BOF = True Then
Call MsgBox("The Contact Name you entered does not exist")
Else
Me!Telephone = rst!Telephone
Me!TbxFax = rst!Fax
Me!TbxComp = rst!Company
Me!TbxFloor = rst!Floor
Me!TbxStreet = rst!Street
Me!TbxCityStateZip = rst!CityStateZip
Me!TbxEmail = rst!Email
Me!TbxAcctMgr = rst!Manager
End If

Set rst = Nothing

End Sub
Private Sub Close_Click()
DoCmd.Close
End Sub
Private Sub Form_Load()

'Set Record Source for Forms as Contacts Table
Forms!Contacts.RecordSource = "Contacts"

End Sub

Private Sub Update_Contact_Click()
Dim QrySQL As String

QrySQL = "INSERT INTO [Contacts] " & _
"(ContactName, Company, Street, Floor, CityStateZip, Telephone, Fax, Manager, Email) " & _
"VALUES(" & _
"'" & Me!TbxContactName & "', " & _
"'" & Me!TbxComp & "', " & _
"'" & Me!TbxStreet & "', " & _
"'" & Me!TbxFloor & "', " & _
"'" & Me!TbxCityStateZip & "', " & _
"'" & Me!Telephone & "', " & _
"'" & Me!TbxFax & "', " & _
"'" & Me!TbxAcctMgr & "', " & _
"'" & Me!TbxEmail & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL QrySQL
DoCmd.SetWarnings True
MsgBox "Update Was Completed"

End Sub


please note: i have very little programming skills
Not sure if I explained this correctly..

Thank you
Your help is greatly appreciated

AA

View 5 Replies View Related

OVerflow On INSERT Statement

Mar 7, 2006

Hi there,

I am trying to perform an INSERT into a linked Oracle Table, using the following code:

INSERT INTO JTCS_OWNER_VISITS
SELECT *
FROM Visits;

Seems simple enough, there are 10,000 records in the visits table, but when i try to run this query i get an error message that simply says 'OverFlow' but no error code. This query worked earlier when i only tried to insert 1,800 records, can anyone tell me if there is a limit to the number of records i can insert. I have linked the oracle tables using ODBC drivers (SQORA32.dll) and this has worked fine against all of the other tables except this one...

Thanks

Dan

View 2 Replies View Related

Insert Statement ...order By

Jun 23, 2006

I've created a new table to hold information about female customers and it would be nice if I could glance through it in alphabetical order of their surnames.

INSERT INTO TBL_FEMALE_CUSTOMERS
SELECT *
FROM TBL_CUSTOMERS
WHERE GENDER='F'
ORDER BY SURNAME ;

In MS Access the resulting table data is NOT sorted by surname, but it works fine when I'm using my trusty SQL Server database. Can this be done in Access?
...and yes, I know I just need to use a query to sort the data, but I'm more interested to find out why it works in SQL Server and not Access.
Cheers,
Sam

View 4 Replies View Related

Insert Into Sql Statement Not Working.. Help!

Dec 5, 2006

hi guys i have a form that comes from a query with a parent and child table. Main table pk is pricingid and then the foreign key in tblHistory is pricingid.

I am trying to do an append table but i keep getting an error message
Pricing ID is the pk autonumber in tblPricing (parent table)
historyid is the pk in tblHistory and pricingid the fk(child table)
oldprice should be a currency value
the other fields are text.

this is my code:

strSQL = "Insert into tblHistory (DateChanged, Edit_UserName, OldPrice) Values(#" & now() & "#, '" & getusername() &"', " & Me.txtPrice.OldValue & ")


but i get the error message


You cannot or change a record because a related record is required in table tblPricing


any advise... i know i need to select somehow the pricingId from the parenttable, but how do i do that?

View 8 Replies View Related

Still Having Trouble With Insert Statement

Jan 19, 2005

this is what I have with all the syntax problems on the insert statement

Private Sub Save_Click()
'On Error GoTo Err_Save_Click


DoCmd.SetWarnings False


Dim EntryTime As String
Dim lsupervisor As String
Dim sSemester As String

Dim selectRequirementid1 As String
Dim selectRequirementid2 As String
Dim selectRequirementid3 As String
Dim selectRequirementid4 As String
Dim selectRequirementid5 As String
Dim selectRequirementid6 As String
Dim selectRequirementid7 As String
Dim selectRequirementid8 As String
Dim selectRequirementid9 As String
Dim selectRequirementid10 As String
Dim selectRequirementid11 As String
Dim selectRequirementid12 As String

Dim sRequirement1 As String
Dim sRequirement2 As String
Dim sRequirement3 As String
Dim sRequirement4 As String
Dim sRequirement5 As String
Dim sRequirement6 As String
Dim sRequirement7 As String
Dim sRequirement8 As String
Dim sRequirement9 As String
Dim sRequirement10 As String
Dim sRequirement11 As String
Dim sRequirement12 As String


lsupervisor = Me.Supervisor1.Column(0)
sSemester = Me.Semester1.value
EntryTime = Format(Now, "dddd, mmmm d, yyyy, hh:mm:ss AMPM")


sRequirement1 = Me.category1.value & ">" & Me.subcategory1.value & ">" & Me!Label1.Caption
selectRequirementid1 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement1] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement2 = category1.value & ">" & subcategory1.value & ">" & Me!Label2.Caption
selectRequirementid2 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement2] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement3 = category1.value & ">" & subcategory1.value & ">" & Me!Label3.Caption
selectRequirementid3 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement3] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement4 = category1.value & ">" & subcategory1.value & ">" & Me!Label4.Caption
selectRequirementid4 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement4] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement5 = category1.value & ">" & subcategory1.value & ">" & Me!Label5.Caption
selectRequirementid5 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement5] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement6 = category1.value & ">" & subcategory1.value & ">" & Me!Label6.Caption
selectRequirementid6 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement6] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement7 = category1.value & ">" & subcategory1.value & ">" & Me!Label7.Caption
selectRequirementid7 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement7] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement8 = category1.value & ">" & subcategory1.value & ">" & Me!Label8.Caption
selectRequirementid8 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement8] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement9 = category1.value & ">" & subcategory1.value & ">" & Me!Label9.Caption
selectRequirementid9 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement9] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement10 = category1.value & ">" & subcategory1.value & ">" & Me!Label10.Caption
selectRequirementid10 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement10] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement11 = category1.value
selectRequirementid11 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement11] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement12 = category1.value & ">" & subcategory1.value
selectRequirementid12 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement12] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "


DoCmd.RunSQL "INSERT INTO Hours ([UniversityID], [LicenseNumber], [SiteName], [RequirementID1], [RequirementID2], [RequirementID3], [RequirementID4], [RequirementID5], [RequirementID6], [RequirementID7], [RequirementID8], [RequirementID9], [RequirementID10], [RequirementID11], [RequirementID12], [Semester], [HoursCompleted1], [HoursCompleted2], [HoursCompleted3], [HoursCompleted4], [HoursCompleted5], [HoursCompleted6], [HoursCompleted7], [HoursCompleted8], [HoursCompleted9], [HoursCompleted10], [HoursCompleted11], [HoursCompleted12], [EntryTime], [Comments]) " & _
" VALUES('" & UniversityID1 & "', '" + [lsupervisor] + "', '" & SiteName1 & "', '" + [selectRequirementid1] + "', '" + [selectRequirementid2] + "', '" + [selectRequirementid3] + "', '" + [selectRequirementid4] + "', '" + [selectRequirementid5] + "', '" + [selectRequirementid6] + "', '" + [selectRequirementid7] + "', '" + [selectRequirementid8] + "', '" + [selectRequirementid9] + "', '" + [selectRequirementid10] + "', '" + [selectRequirementid11] + "', '" + [selectRequirementid12] + "', '" + [sSemester] + "', '" & hours1 & "', '" & hours2 & "', '" & hours3 & "', '" & hours4 & "', '" & hours5 & "', '" & hours6 & "', '" & hours7 & "', '" & hours8 & "', '" & hours9 & "', '" & hours10 & "', '" & categoryhours & "', '" & subcategoryhours & "', '" + [EntryTime] + "', '" & Comments & "'); "

End Sub

View 5 Replies View Related

Insert A Timestamp Into A Sql Statement

Jun 16, 2005

Hi

Help please

My code looks something like this and it work fine by producing an email and inserting into an access database

My problem is I also want to insert a timestamp into the column that already exists in the database and I just can't get the syntax right

SQL= "INSERT INTO Employees "
SQL= mySQL & "(FirstName,LastName,Address,City,Region,PostalCode ) "
SQL= SQL & "VALUES ('" & Request.Form("FirstName") & "','"
SQL= SQL & Request.Form("LastName") & "','"
SQL= SQL & Request.Form("Address") & "','"
SQL= SQL & Request.Form("City") & "','"
SQL= SQL & Request.Form("Region") & "','"
SQL= SQL & Request.Form("PostalCode") & "')"

I appreciate I might have to do

SQL= "INSERT INTO Employees "
SQL= mySQL & "(FirstName,LastName,Address,City,Region,PostalCode ,timestamp) "
SQL= SQL & "VALUES ('" & Request.Form("FirstName") & "','"
SQL= SQL & Request.Form("LastName") & "','"
SQL= SQL & Request.Form("Address") & "','"
SQL= SQL & Request.Form("City") & "','"
SQL= SQL & Request.Form("Region") & "','"
SQL= SQL & Request.Form("PostalCode") & "')"

but I cannot for the life of me workout the SQL line needed

Help would be much appreciated

View 3 Replies View Related

Having Problems With Insert Into Statement

Mar 19, 2008

Hi everyone,

I'm kind of new to web development but not so much programming. I went to school for 2 years for C and C++ programming, as well as some database stuff... anyways , I'm having some trouble doing a simple INSERT INTO statement...

What I'd like to do is to insert a new row using two session variables, first of all I'm not entirely sure if this works? but I've tried a bunch of different things but I just cant seem to get it to work...

Here is my insert statement:

sqlInsert = "INSERT INTO Events (Participant_Code,Date)
VALUES ('" & code & "','" & date & "')"

Now I've tried both, where I have a variable that has Session("code") and another with Session("date") as well as just using Session("code") where the variable is in the insert.

Here is the whole code, I have a file called database.asp that has function that can be called to open, read, update or close a connection.

call connection("writeyourselffree2.mdb")

sqlInsert = "INSERT INTO Events (Participant_Code,Date)
VALUES ('" & code & "','" & date & "')"

call rsUpdate(sql, conn)

call close(conn)

Heres the error that I have right now:

Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Anyways I appreciate any help you can give me!

Cheers

View 1 Replies View Related

Insert Statement Fail

Apr 26, 2014

If I use this INSERT it works fine :

Code:
strSQL = "Insert into tblParkeerbeheer_log select * from parkeerbeheer where [ID] = " & Forms![parkeerbeheer]![fldID].Value & ""
DoCmd.RunSQL strSQL

But if I want to do the same with multiple records with Pnummer (personell number) as criteria in the WHERE clause :

Code:
strSQL = "Insert into tblParkeerbeheer_log select * from parkeerbeheer where [Pnummer] = " & Forms![parkeerbeheer]![Pnummer].Value & ""
DoCmd.RunSQL strSQL

It asks me to prompt the personell numer, while that number is present within the prompt box

View 3 Replies View Related

Insert Into Statement With Tables From 2 Databases

Dec 3, 2005

Dear members,

I have two databases, Source and destination ;

strSrceDB = "C:WSS_DBRehabilitated_Water_Supply_Kulyob.mdb"

StrDestDB = "C:DBWSS_Khatlon.mdb"

I would like to append records to the destination table (Contractor_GIS) when there are no matching records in the source table (Contractor).

My code is as follows:


Private Sub CommandButton1_Click()

Dim strTemp As String
Dim strSQL As String
Dim StrDestDB As String
Dim strSrceDB As String
Dim mdb As DAO.Database

On Error GoTo ErrorHandler
strSrceDB = "C:WSS_DBRehabilitated_Water_Supply_Kulyob.mdb"
StrDestDB = "C:DBWSS_Khatlon.mdb"

'Make sure it is there
If Dir(StrDestDB) = "" Then
Call MsgBox(StrDestDB & " does not exist", vbOKOnly, "Aborting...")

ElseIf Dir(strSrceDB) = "" Then
Call MsgBox(strSrceDB & " does not exist", vbOKOnly, "Aborting...")
Else

strSQL= "INSERT INTO Contractor_GIS("
strSQL = strSQL & "System_ID,"
strSQL = strSQL & "Contractor_Name)"
strSQL = strSQL & "IN '" & StrDestDB & "' "
strSQL= strSQL & "SELECT Contractor.System_ID,"
strSQL = strSQL & "Contractor.Contractor_Name "
strSQL = strSQL & "FROM Contractor IN '" & strSrceDB & "' "
strSQL = strSQL & "Contractor_GIS IN '" & StrDestDB & "' "
strSQL = strSQL & "where Contractor.System_ID <> Contractor_GIS.System_ID;"

Set mdb = DBEngine.OpenDatabase(StrDestDB)
Debug.Print strSQL
Call mdb.Execute(strSQL)
mdb.Close
Set mdb = Nothing
DoEvents
MsgBox ("The Geodatabase tables have been successfully appended!")
End If
Exit Sub

ErrorHandler:
strTemp = Err.Description & " [Update_SystemTab]"
Call MsgBox(strTemp, vbCritical, "Contact Help Desk")
End Sub

I get Syntax Error in "Insert into" clause.

Any help on this will be most appreciated.

Thanks in advance.

Irshad

View 2 Replies View Related

Syntax Error In Insert Into Statement

Feb 1, 2007

What is the syntax error in this Insert Into statement ?

INSERT INTO RawData(RunID,fullName,name,category,type,subType, numberOfLines,virtual,date,namespace) SELECT 257 ,fullName,name,category,type,subType,numberOfLines ,virtual,#1/3/2007#,namespace FROM RawData WHERE namespace ='customer.demo' AND RunID =256

View 2 Replies View Related

Insert A Select Statement In A Text Box

Aug 1, 2006

hi 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

View 4 Replies View Related

Modules & VBA :: SQL INSERT INTO VALUES Statement

Jun 7, 2013

I'm now trying to speed up data entry within my database and have hit a brick wall with one part.Basically, this is within a form (for 'clauses') of which there is a one-to-one relationship with 'the Applicability' table (a series of fields with 'Yes/No' values for each and a related key field) - i.e. for each clause there are a series of circumstances when it will apply. I've set this up to create a record when one doesn't already exist using the default values (i.e. all applicable).

The method to speed up data entry is to have a pop-up form with unbound fields to list common Clause fields - including 'Applicability' as a subform. When entering a series of Clauses you tend to find they have the same 'Applicability' as their neighbour, so I would like to create a corresponding record in the Applicability table with the values set on the 'ClauseQuickAdd' form (Technically the 'ApplicabilityQuickAdd' sub form).I've edited an existing code within my database to provide two global variables (strAppFieldList and strSubAppFieldList) to put into the following statement:

Code:
DoCmd.RunSQL "INSERT INTO Applicability ( AppRelClause, "" & strAppFieldList & "") VALUES ("" & Me.Clause_ID & ", " & strSubAppFieldList & "")"

Bringing up Error 2498 "An expression you entered is the wrong data type for one of the arguments". So I presume that I haven't quite got the format right for transferring yes/no values (strSubAppFieldList). See below for an extract of strSubAppFieldList:

Code:
Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Manufacturer], Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Supplier], ...

View 6 Replies View Related

Modules & VBA :: DLookup In INSERT Statement

Jul 10, 2013

I need my INSERT statement to DLookup tbl_module_repairs and insert the information from a field based on a WHERE condition of primary key matching on the form and table. Here is what I have but it will not work on the last value:

Code:
Dim lookModType1 As Variant
lookModType1 = DLookup("module_type", "tbl_module_repairs", "prikey = " & Me.txt_prikey1.Value & "")
SQLtext4 = "INSERT INTO TBL_RF_TECH_LOG ([TechID], ActionStatus, Barcode, EquipmentDescription) Values (Left([txt_techid_rework_in], 2),'In Rework', '" & Me.txt_bc1 & "', " & lookModType1 & ");"

DoCmd.RunSQL SQLtext4

Note: My DLookup works fine by itself for other uses.

View 5 Replies View Related

Modules & VBA :: INSERT INTO SELECT Statement

Mar 4, 2014

I 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.

View 4 Replies View Related

Forms :: How To Write Insert Statement

Jul 31, 2014

The row source for the combo-box is

SELECT tbl_p.P_ID, [plast] & " , " & [pFirst] AS Expr1 FROM tbl_p ORDER BY [plast] & " , " & [pFirst];

If notinlist I would like to add the the new name, how should I write the INSERT Statement?

View 1 Replies View Related

Queries :: Put INSERT INTO Statement In A Query?

Apr 3, 2013

Where would you put the INSERT INTO statement in a query? Would it go after the select statement but before the From statement or would it go at the end?

View 2 Replies View Related

French Characters In Insert Statement?

May 8, 2015

i'm trying to execute an insert statement from an ASP.Net form page such as:

insert into documents (Name,NameFrench,Message,MessageFrench) values('info','information','See details ','Voir l'affiche pour des details');

I get an exception on the insert statement.

If I copy the statement directly into a test query in the database, I get the following error: Syntax error (missing operator) in query expression "Voir l'affiche .... "

The insert dies on the " l'affiche " word. I need the those french characters in the database as they are pulled from those fields and are required to be displayed with the proper accent characters.

know a way to escape the French special characters?

View 3 Replies View Related

AutoNumber Field And SQL String INSERT INTO Statement

Apr 4, 2008

In my statment below the ID is an autonumber. I placed it in the first part ofthe SQL string..but not where I have to variables referenced...

I have to add it to the SQL statement...Its not a varible although I can make it one by getting the largest number nad adding one...
Do I have to make it a non-autonumber and do it like I said above

Or is there another way to treat the autonumber (ID) in the SQL INSERT INTO statement...

THanks




Code:MySQL = "INSERT INTO dbo_data ("MySQL = MySQL & "ID,Name,Owners_Residence,Notice_Expiration,Notes,N otice_Number,Status,Notice_Date,Property_Address,N uisance_Type,Addition_Name,Range,Lot,Block,Zip_Cod e,Display_PID,Sec_tion,Township,Property_City,Prop erty_State,Property_Zip"MySQL = MySQL & ") values ("MySQL = MySQL & "'" & varName & "','" & varOwners_Residence & "','" & varNotice_Expiration & "','" & varNotes & "','" & varNotice_Number & "','" & varStatus & "','" & varNotice_Date & "','" & varProperty_Address & "','" & varNuisance_Type & "','" & varAddition_Name & "','" & varRange & "','" & varLot & "','" & varBlock & "','" & varProperty_Zip & "','" & varDisplay_PID & "','" & varSec_tion & "','" & varTownship & "','" & varProperty_City & "','" & varProperty_State & "','" & varZip_Code & "'"MySQL = MySQL & ");"

View 14 Replies View Related

Adding User Input To An Insert Statement

Feb 14, 2008

Greetings,

I have a form that contains an unbound listbox that is populated from a table. The user selects multiple records in the listbox and then clicks on a "Commit" button. The "Commit" button then copies portions of the records to another table. (And now that I am analyzing the flow of data, I realize that not all the data needs to be copied over, but I'll fix that at a later time).

Anyway, the code looks like this:

Code: Dim lst As Access.ListBox Dim rownum As Variant Dim vHEDR, vLeague, vFname, vMI, vLname, vClass, vQual, vSex, vYouth As String Dim intResponse As Integer Dim sqlstr As String Set lst = Me![lstHistorical] DoCmd.SetWarnings False For Each rownum In lst.ItemsSelected vHEDR = CStr(lst.Column(0, rownum)) & "," vLeague = "1," vFname = Chr(34) & lst.Column(1, rownum) & Chr(34) & "," vMI = Chr(34) & lst.Column(2, rownum) & Chr(34) & "," vLname = Chr(34) & lst.Column(3, rownum) & Chr(34) & "," vClass = CStr(lst.Column(4, rownum)) & "," vQual = CStr(lst.Column(6, rownum)) & "," vSex = Chr(34) & lst.Column(8, rownum) & Chr(34) & "," vYouth = Chr(34) & lst.Column(9, rownum) & Chr(34)'<<Here>> sqlstr = "INSERT INTO tbl_RosterTest (HEDR, LeagueID, Fname, MI, Lname, ClassID, QualID, Sex, Youth) VALUES (" & vHEDR & vLeague & vFname & vMI & vLname & vClass & vQual & vSex & vYouth & ")" DoCmd.RunSQL sqlstr Next rownum DoCmd.SetWarnings True MsgBox ("Persons successfully registered")

At the <<Here>> point, I would like to proc a single dialog box the query the user for some additional information - 2 items from comboboxes and one is a yes/no. Is there a way to gather the info so I can insert the values into the INSERT string?

Thanks.

View 8 Replies View Related

Modules & VBA :: Syntax Error In INSERT INTO Statement

Jan 22, 2014

I am having a problem with below and getting a run-time error 3134

Code:
LastOrderNumber = DMax("Order", "Model_types")
NewOrderNumber = CLng(LastOrderNumber + 1)

CurrentDb.Execute "INSERT INTO Model_types (Order) " _
& "VALUES (" & NewOrderNumber & ")"

The field 'Order' in Model_types is a Long Integer.

View 4 Replies View Related

General :: Syntax Error In INSERT INTO Statement

Jul 31, 2012

I'm trying to run the following query

INSERT INTO Enrolled_Students (Last Name, First Name, Address, town/city, county, postcode, phone number, date of birth, age)
SELECT Last Name, First Name, Address, Town/City, County, Postcode, Phone Number, Date of Birth, Age
From Candidate Details
Where IsNumeric (Student ID);

and i'm receiving the error stated above

what im doing wrong?

View 12 Replies View Related

Modules & VBA :: Multiple Variables For Insert INTO SQL Statement

Sep 28, 2013

Look at the below SQL 'INSERT INTO' statement ? I'm trying to insert multiple variable values into an 'INSERT INTO' statement. I'm getting the below error message. The code is listed below. I started out with two (2) variables, but will have thirteen to insert into a table. Also, in the code below is the VBA statement to retrieve the variable data. I'm getting the data, but cannot insert the data into the table.

Private Sub Test2_Click()
Dim strSQL As String
Dim strSalesman As String
Dim strContentArea As String
DoCmd.SetWarnings False

[Code] ....

Error
Microsoft Visual Basic popup
Run-time error '3061'

Too few parameters. Expected 1.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved