Using An If Statement To Condition After Update

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 Replies


ADVERTISEMENT

Forms :: IF Statement - Condition Based On Status Of One Checkbox

Aug 2, 2014

In an access form, I have several checkboxes. There is one checkbox titled "Complete". The complete checkbox needs to be true only if all other "non master" checkboxes are true. If all of the non master checkboxes are not checked, I need the complete checkbox to be false. This is the code that I am currently using on the after update command:

If me.checkbox1 and Me.checkbox2=True then
me.completed=True
else
me.completed=False
end if

This works fine if there is no "and" in the if statement and the condition is based on the status of one checkbox as opposed to many (Also I have about 15 checkboxes that must be checked before the "complete" checkbox is true).

View 14 Replies View Related

Queries :: Multiple Query Criteria IIF Statement - True Condition

Feb 24, 2015

I'm trying to have a single or multiple query criteria based on what the user checks on a form.

I can't get the True condition to work at all, I get no records. Here is what I'm using

IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,[TempVars]![EID] Or 86,[TempVars]![EID])

If I just put
[TempVars]![EID] Or 86
in the Criteria it works just fine.

View 14 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

Forms :: Update Value In A Field Based On A Condition

Jan 25, 2015

I have a calculated field in the form footer which adds up the number of boxes that have been ticked for the received field

=Sum(IIf([Recieved]=Yes,1,0))

If the ticks equal to 3 then I want to update the status field in another table to "Active".I am trying this VBA code but it won't work.

Code:

If Text9 = "3" And custNumber = tblCustomers.custNumber Then
tblCustomers.Status = "Active"

View 3 Replies View Related

Queries :: Update Table Based On Condition?

Aug 7, 2015

I want to update a table called tblFinalOrder, that looks like this:

In particular, I want to update each column separately with the number 1 taken from table tblSAP_XWP_SW:

My problem is that Access doesn't properly update the table how I want. I join both tables using an INNER JOIN on the SAP number. In the column AEMenge you see some 1's. So what he should do is writing these 1's into the appropriate column in my tblFinalOrder table. The condition is that the SAP number must already be in my tblSAP_XWP_SW table. If he doesn't find a 1, then skip it.

Here is my code so far. This one should update column "DynaCT". The funny thing is that DynaCT isn't available in my columnlabel column but he writes a 1 anyways (or in this case anohter number, I was playing around with it). This is what I don't understand.

Code:
UPDATE tblFinalOrder AS a
INNER JOIN tblSAP_XWP_SW AS b
ON a.SAPNr = b.sapxwpsw_sapnr
SET a.DynaCT = 1
WHERE a.SAPNR IN (SELECT sapxwpsw_sapnr FROM tblSAP_XWP_SW);

I mean, he should only write the 1 into the specific column of table tblFinalOrder, if he finds the SAP number in tblSAP_XWP_SW and if there is a 1 in this line in column AEMenge.

View 3 Replies View Related

Modules & VBA :: Subform Checkbox Used As Condition To Update Records

Aug 26, 2013

My subform consists of a list of tasks that are waiting to be verified. in order to verify tasks, the user scrolls through the list of tasks and checks a checkbox (discrepancyverified) on each record they wish to verify. After the user has finished checking all the records they wish to verify, they click a verify button on the main form which should then go back through each record and update the verifieddate value of any that are checked to today.

This is what I have so far:

Code:

Private Sub Command19_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant

[code]...

View 3 Replies View Related

Update Statement

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

Help With Update Statement

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

Iif Statement After Update Problem

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

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 7 Replies View Related

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 2 Replies View Related

Error In UPDATE Statement

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

Sql Update Statement Error

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

Update Checkbox Value With IF Statement

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

Update On A Query With A Join Statement

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

Syntax Error In UPDATE Statement..

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

Modules & VBA :: How To Use Variables In Update Statement

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

Error 3464 With An UPDATE Query Statement

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

Queries :: Syntax Error In Update Statement

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

Modules & VBA :: Update Query End Of Statement Error

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

Modules & VBA :: Can't Find Field In Update Statement

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

Forms :: How To Break Update Statement Into Multiple Lines

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

Queries :: Update Statement With Null Dates - Getting Syntax Error

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

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

Modules & VBA :: Update And Amend Records In A Table - Error Trying To Execute SQL Statement

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







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