Update Statement Or Query From VBA
Dec 24, 2004
I am trying to update the fields of a table from comboboxes and textboxes on a form. I don't want all the fields to receive data from the objects on my form directly. Some of the bound controls are not visible and data would be entered into them from visible comboboxes instead. I am not sure exactly how to do that. I tried to devise a query that would take the values from the comboboxes and equate them to those of the hidden controls, but that didn't work. I tried inserting an update command in code but that came up with an error. If some one could help me I would be thankful.
View Replies
ADVERTISEMENT
May 23, 2005
Hi,
I am trying to do an update on a table with a query which has a join as follows but getting an error:
UPDATE RoleObjects
SET AccessType = 1
FROM RoleObjects
INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID
WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')
I tried to debug the code and found that the following part of the code with the Select clause works fine.
SELECT *
FROM RoleObjects
INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID
WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')
Do u have any suggestions.. ??
Thanks a lot for your time.
View 1 Replies
View Related
Jun 1, 2005
Hi everyone,
I'm trying to execute an update query by using the DoCmd.RunSQL. Here is my statement:
DoCmd.RunSQL "UPDATE T_Facturation SET TotalQuantiteDiesel = TotalQuantiteDiesel + " & Me.TxtQuantiteDiesel.Value & ", " & _
"TotalInst = TotalInst + " & Me.TxtInst.Value & ", TotalDemo = TotalDemo + " & Me.TxtDemo.Value & ", " & _
"TotalCoutDiesel = TotalCoutDiesel + " & Me.TxtCoutDiesel.Value & ", TotalLocationHQ = TotalLocationHQ + " & Me.TxtLocationHQ.Value & ", " & _
"TotalTempsHQ = TotalTempsHQ + " & Me.TxtTempHQ.Value & ", TotalLocation = TotalLocation + " & Me.TxtLocation.Value & ", " & _
"TotalTransfo = TotalTransfo + " & Me.TxtTransfo.Value & ", TotalAutreDep = TotalAutreDep + " & Me.TxtAutresDep.Value & ", " & _
"TotalCable = TotalCable + " & Me.TxtCable.Value & ", TotalElectricien = TotalElectricien + " & Me.TxtElectricien.Value & ", " & _
"TotalTempExt = TotalTempExt + " & Me.TxtTempsExt.Value & ", " & _
"TotalTransportGeneratrice = TotalTransportGeneratrice + " & Me.TxtTransportGeneratrice.Value & ", " & _
"TotalTransportTransfo = TotalTransportTransfo + " & Me.TxtTransportTransfo.Value & " WHERE NoAutoInstallation = '" & Me.TxtNoAutoInstallation.Value & "';"
My problem is that when I execute this code, I got the error:
Run-time error '3464': Data type mismatch in criteria expression.
I tried to change my query statement, but I cannot find my error. I'm using Access 2003. I'm really lost.
Can someone help me please!
Thanks a lot
View 2 Replies
View Related
Aug 10, 2015
I'm trying to write an UPDATE line in VBA, but whenever I put the table name I get an "Expected End of Statement" error.This is line currently;
Code:
strSQL = UPDATE "MASTER PLANNER" SET [SET 2] = TRUE WHERE "[ID] = " & Me.PlannerID
It doesn't matter whether I change the table name to square brackets, quotes or nothing at all, it always gives me the expected end of statement error.
View 2 Replies
View Related
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
May 6, 2013
I would like to have a table update query populate cells in a field, based on an IIf statement (below), but states that the IIf statement arguments still need to be enclosed in parentheses. I don't understand what the problem is, I have two sets of opening / closing parentheses.
Code:
IIf IsNull (=Mid( [CONTRACTOR_TASK]![TASK_NAME] ,6,6))
View 3 Replies
View Related
Jan 28, 2008
Hi,
I have the following SQL update statement, but I can't seem to get it working. Would anyone know what I'm doing wrong?
DoCmd.RunSQL ("UPDATE Document_Table " & _
"SET Document_Table.Title = '" & Me.Title & "' " & _
"Document_Table.Date = '" & Me.Date & "' " & _
"Document_Table.Location = '" & Me.Location& "' " & _
"Document_Table.Description = '" & Me.Description & "' " & _
"Document_Table.Entered_Date = '" & Me.Entered_Date & "' " & _
"Document_Table.Approval_Date = '" & Me.Approval_Date & "' " & _
"Document_Table.Available_Date = '" & Me.Available_Date & "' " & _
"Document_Table.Keywords = '" & Me.Keywords & "' " & _
"Document_Table.Document_Number = '" & Me.Document_Number & "' " & _
"Document_Table.File_Size = '" & Me.File_Size & "' " & _
"WHERE Document_Table.Document_ID = '" & Me.Document_ID & "' ;")
Thanks in advance for your help!
View 10 Replies
View Related
Jan 5, 2005
I am after some help please.
I have the following as part of an sql statement in visual basic to update a table with a value in a text box in a form:-
DoCmd.RunSQL "Update[CostComponentDetail] set [CostComponentDetailDescription]=(Forms![Edit SKU NCP Cost Data]!TextBox1.1
However when I try to run it, it does not like the full stop in the name of the text box "TextBox1.1".
I have tried putting it in square brackets, normal brackets etc but I am completley stumped.
I really don't want to change the name of the text box as the format affects how the form is populated.
Is there a way of using a control with a full stop in the name or can it just not be done at all?
Any help would be greatly appreciated.
View 3 Replies
View Related
Mar 1, 2007
I am working on a bill of materials database. Once a record is entered, the current total appears. After the record has been updated, I would like to use a iif statement that determines what the tax should be. I already have my iif statement that is working. My problem is that the calculation takes a small amount of time to actually calculate. By the time the calculation is made, the iif statement has already run giving me false data. Just to test the iif statement out, i put it in a command button and it works every time i click it. Any advice on how i can run this iif statement after the calculation is made? I have also tried to put it in the On change event of the field that is calulating and it still won't work.
Thanks
Gregg
View 7 Replies
View Related
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 2 Replies
View Related
Oct 31, 2006
Hi, im very new to visual basic programming...what i am trying to do is implement an If Statment on AfterUpdate for my form:
If record_is_new Then
Dim db As DAO.Database
Dim RS As DAO.Recordset
Set db = CurrentDb()
Set RS = db.OpenRecordset("LTOT_second_oxygen", dbOpenDynaset)
RS.AddNew
RS![HospitalNumber] = Me![general_info.HospitalNumber]
RS.Update
RS.Close
db.Close
Set RS = Nothing
Set db = Nothing
.........more of this kind of code
Else nothing
End If
any ideas on the vb code that would do this?
View 5 Replies
View Related
Aug 24, 2005
Hi everyone,
I keep getting this error whenever i try to run the ASPcode. Can anyone please help me
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
I have two tables data1 and data2 in the same access database (data.mdb)
here is the query
strDBPath = Server.MapPath("data.mdb")
Set cnnSimple = Server.CreateObject("ADODB.Connection")
cnnSimple.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
Set rstSimple = cnnSimple.Execute("SELECT sponser2 FROM data2")
Do While Not rstSimple.EOF
spo=rstSimple.Fields("sponser2").Value
set tamt = cnnsimple.Execute("SELECT SUM(amount1) FROM data1 WHERE sponser1='"&spo&"' ")
Set rstSimple1 = cnnSimple.Execute("UPDATE data2 SET amount2= "&tamt(0)&" WHERE sponser2='"&spo&"' ")
rstSimple.MoveNext
Loop
View 2 Replies
View Related
Aug 25, 2006
im trying to update to the database with the following sql statements:
FUNCTION fixQuotes( theString )
fixQuotes = REPLACE( theString, "'", "''" )
END FUNCTION
sql #1
sqlstring = "update Asset set AssetID='"&fixQuotes( assetid )&"', AssetLabel='"&fixQuotes( assetlabel )&"', WarrantStart='"&fixQuotes( warrantystart )&"', Engrave='"&fixQuotes( engrave )&"', Status='"&fixQuotes( status1 )&"', Make='"&fixQuotes( make )&"', Model='"&fixQuotes( model )&"', AssetCategory='"&fixQuotes( assetcategory )&"' where AssetID='"&assetid&"' "
sql #2
sqlstring = "update Asset set " &_
"AssetID=' " & fixQuotes( assetid ) & "'," &_
"AssetLabel=' " & fixQuotes( assetlabel ) & "'," &_
"WarrantStart=' " & fixQuotes( warrantystart ) & "'," &_
"Engrave=' " & fixQuotes( engrave ) & "'," &_
"Status=' " & fixQuotes( status1 ) & "'," &_
"Make=' " & fixQuotes( make ) & "'," &_
"Model=' " & fixQuotes( model ) & "'," &_
"AssetCategory=' " & fixQuotes( assetcategory ) & "' where " &_
"AssetID=" & AssetID
ive tried both @ different times but they dont work. the adodb connection's open
View 14 Replies
View Related
Aug 5, 2014
What is the correct way to update the checkbox value in a table with an IF statement?
Desired Result:
Check if UserID is not 'ME'
If it is not 'ME' then uncheck the box in the table.
My current faulty code below:
NoUser = DLookup("[UserID]", "[TABLE]") If NoUser <> "ME" Then
[AllowLogin] = 0
End If
View 1 Replies
View Related
Jun 27, 2005
Hello,
Hopefully this is an easy one! but for the life of me i can't see what im doing wrong , help is appreciated.
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/diary_editE.asp, line 272
SQL = "UPDATE diary SET dte =" & Request.Form("dte") & ", " &_
" eTime= '"& ChkStr(Request.Form("tim")) & "', " & _
" eEnd= '"& ChkStr(Request.Form("endt")) & "', " & _
" text_field = '" & ChkStr(Request.Form("title")) & "', " & _
" eLocation = '" & ChkStr(Request.Form("locat")) & "', " & _
" eContact = '" & ChkStr(Request.Form("conta")) & "', " & _
" eSpeak = '" & ChkStr(Request.Form("speak")) & "', " & _
" eAudiin = '" & ChkStr(Request.Form("audi")) & "', " & _
" eAudiex = '" & ChkStr(Request.Form("aude")) & "', " & _
" eVisitors = '" & ChkStr(Request.Form("evisit")) & "', " & _
" ePromo = '" & ChkStr(Request.Form("prom")) & "', " & _
" details = '" & ChkStr(Request.Form("details")) & "', category = " & Request.Form("cat") & _
" WHERE id = " & Request.Form("ID")
my_conn.Execute SQL
If anymore info is required please tell me, but basically i can insert into the database no problems , but when it comes to updating what is in there i recieve the above error
Thanks in advance
View 3 Replies
View Related
Apr 10, 2014
What is the syntax for using variables in a VBA update statement? I have the following that I want to use to update a record field.
Code:
Dim thisTbl as String
If Answer = vbYes Then
If MedicationInvNo2 <> "" Then
thisTbl = "tblMyMedData"
Else
thisTbl = "tblMedData"
[code]....
I'm getting run-time error '3144' when the database tries to run the SQL. So I'm assuming my syntax is wrong (specifically in the WHERE clause)?
View 3 Replies
View Related
Nov 27, 2014
I have 2 tables called MakeTable1 and DBO_TBL_Activity
Im trying to update MakeTable1 with the values from TBL_Activity when both activity.StartDate and maketable1.Dates match but also acticity.IDStaff and Maketable1.ID Match
Below is the SQL i have so far
Code:
UPDATE [MakeTable1].[Detailsa] SET [dbo_tbl_activity].[details]
WHERE [MakeTable1.Dates)=[dbo_tbl_activity].[StartDate] AND [MakeTable1].[id]=[dbo_tbl_activity].[idstaff]);
The error is :syntax error in update statement
View 2 Replies
View Related
Aug 19, 2014
My update statement is shown below. What this is to do is change the PGTIN record in PP TBL to the GTIN that is in the UPC TBL where the UPC Code on the form is the same as the UPC Code in the UPC TBL. I am getting the error message "can't find field..." I have double and triple checked the field names but cannot find my error. Both PGTIN and UPC Code are text fields.
strSQL = "UPDATE [PP TBL] SET [PGTIN] = '" & [UPC TBL].[GTIN] & "' WHERE ([UPC TBL].[UPC Code] = '" & Me.UPC & "');"
View 6 Replies
View Related
Mar 7, 2014
I am unable to break the UPDATE statement into multiple lines and now I am getting "Syntax Error in UPDATE statement"
Following is the multiple line Update Statement
Dim strsql As String
strsql = "Update tblcurr" & _
"SET tblCurr.Currencyname =" & _
"[forms]![updatecurrency]![txtcurname]" & _
"WHERE (((tblCurr.Currencycode)=" & _
"[forms]![updatecurrency]![txtcurcode]));"
DoCmd.RunSQL strsql
Following is the one liner Update statement which, works perfectly
strsql = "UPDATE tblCurr SET tblCurr.Currencyname = [forms]![updatecurrency]![txtcurname] WHERE (((tblCurr.Currencycode)=[forms]![updatecurrency]![txtcurcode]));
View 4 Replies
View Related
Jul 5, 2013
My issue is that I am trying to update a date field. When I do the date field may have a date or may be a null. When I try to pass in a NULL date with no quotes, I get a syntax error. When I have single quotes in the statement and a null value is passed in, I get an invalid use of date.
Dim DENIEDDATE1 As Date
If (Not IsDate(rs.Fields("DENIED_DATE"))) Then
DENIEDDATE1 = Null
Else
DENIEDDATE1 = "'" & rs.Fields("DENIED_DATE") & "'"
End If
update table1 set table1.denieddate = " & denieddate1 & " 'get Update syntax error with this statement
update table1 set table1.denieddate = '" & denieddate1 & "' 'fails due to invalid use of null
View 8 Replies
View Related
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
Jun 18, 2013
I'm trying to create a function to update and amend records in a table.
The update part works and updates existing records with new data but I'm getting an error with the insert part.
Run time error 3078
The Microsoft Office Access database engine cannot find the input table or query 'FALSE'. Make sure it exists and that its name is spelled correctly.
Nothing called 'FALSE' so not sure what that means?
Code:
sSQL = "INSERT INTO Pupil_tb (PupilID,Class,PupilName,etc ) " _
= "SELECT PupilImport_tb.PupilID, Class, PupilName, etc FROM PupilImport_tb " _
& "LEFT JOIN Pupil_tb " _
& "ON Pupil_tb.PupilID=PupilImport_tb.PupilID " _
& "WHERE Pupil_tb.PupilID Is Null "
CurrentDb.Execute sSQL, dbFailOnError
View 5 Replies
View Related
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
Jun 28, 2005
Hi Guys,
I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.
It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.
Any suggestion guys & gals?
Many thanks
Tim
View 9 Replies
View Related
Nov 29, 2006
Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers
View 3 Replies
View Related
Sep 22, 2006
Hi all..
Here is what I have:
I have a query that is running a search form. The query is made up of three tables:
1.tblMain
2.tblSpouse
3.tblChildren
When I have the query looking at just tables 1 and 2 it works fine. But when I put in table three my query doesn't work correctly.
Here is what it is doing. On my search form I have a main window that lists all of the entries into the database. I then have two subforms one for spouse and one for children. The spouse now shows this:
Last Name
Washington
Washington
Washington
Washington
Washington
Washington
First Name
Janine
Janine
Janine
Sally
Sally
Sally
(two wives, 1 current, 1 past)
And the children show this
Last Name
Washington
Washington
Washington
Washington
Washington
Washington
First Name
David
David
Mines
Mines
Foy
Foy
And the one entry that does not have any children is no longer showing up in the main window with the other entries.
Here is the sql of the query:
SELECT tblMain.EntryNumber, tblMain.DriversLicNumber, tblMain.EmpNumber, tblMain.FName, tblMain.LName, tblMain.MInitial, tblSpouse.LName, tblSpouse.FName, tblSpouse.MInitial, tblSpouse.DriversLicNumber, tblChildren.LName, tblChildren.FName, tblChildren.MInitial, tblChildren.DriversLicNumber, *
FROM (tblMain INNER JOIN tblChildren ON tblMain.EntryNumber = tblChildren.EntryNumber) INNER JOIN tblSpouse ON tblMain.EntryNumber = tblSpouse.EntryNumber
WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And [tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.EmpNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
ORDER BY tblMain.EntryNumber, tblSpouse.EntryNumber, tblChildren.EntryNumber;
Sorry for the long post but I wanted to make sure most if not everything was covered.
Thanks
R~
View 14 Replies
View Related
