RunSql

Aug 24, 2006

I have a table with one entry- just a date that holds the last day of the month for a function that reminds the user to do something. I have some code that's supposed to change this entry via an update query- but it's not working- literally, no errors, just nothing. I tried running it in an actual query- but still nothing.
The query should update the table endCurrMonth to the last day of the month. I've tried hardcoding various dates- still nothing
DoCmd.RunSQL ("UPDATE tableMetrics SET tableMetrics.endCurrMonth = DateSerial(Year(Date()), Month(Date()) + 1, 0)")
anyone know what I'm doing wrong?
thanx

View Replies


ADVERTISEMENT

ADO, DAO Vs RunSQL

Nov 2, 2006

After a hiatus of a few years I find myself back writing Access applications, so I need to get caught up a bit.

First of all help me out with the whole ADO, DAO RunSQL thing. In the past whenever I needed to do any database operations I almost always used straight SQL with DoCmd.RunSQL, e.g.:

lsSQL = "INSERT INTO tblUsers CenterID, WorkerID..."
DoCmd.RunSQL (lsSQL)

Most other coders seem to use some recordset approach, e.g:

Set rst = dbs.OpenRecordset("tblUsers")
rst.AddNew
rst("ClientID") = Me.ClientID.Value
rst("WorkerID") = Me.WorkerID.Value
...
rst.Update

The only time I ever used recordsets was when I needed to loop through each record and apply some logic that was too convoluted for SQL or at least too convoluted for me to write in SQL.

So, what's the advantage of using recordsets - whether ADO or DAO - over RunSQL?

View 9 Replies View Related

RunSQL

Sep 12, 2006

Hi trying to get my runsql to work

DoCmd.RunSQL "INSERT INTO tblLogTimer (Operator, Dato, LogOn, LogOff, LogTime, Status) VALUES ('" & StrOperator & "', '" & Datotext & "', '" & MeetTime & "', '" & LeaveTime & "', '" & LogTime & "', '" & StrMeetStatus & "')

But if keep updating all the records and I'm only interesting to get the 1 updated...so I've been trying this ....

DoCmd.RunSQL "INSERT INTO tblLogTimer (Operator, Dato, LogOn, LogOff, LogTime, Status) VALUES ('" & StrOperator & "', '" & Datotext & "', '" & MeetTime & "', '" & LeaveTime & "', '" & LogTime & "', '" & StrMeetStatus & "') WHERE Operator=me.txtLogOperator"""

but the access keep telling that I'm needing a ";" but where should I put it???

View 1 Replies View Related

DoCmd.RunSQL

Jul 27, 2006

I've the following SQL query in my database:

DoCmd.RunSQL "INSERT INTO tblPlanner ( RACF, [Date], [Day Capacity], [Role Title], TimeWork ) SELECT tblStaff.RACF, [txtday1] AS Expr1, tblStaff.[Daily Capability], tblStaff.[Role Title], tblStaff.[Contract mins] FROM tblStaff WHERE (((tblStaff.TeamName) Like [txtTeamName])) WITH OWNERACCESS OPTION;"

The problem is everytime it runs it informe that the query will change data in the table. What can I do to stop it?

Thanks

View 3 Replies View Related

RunSQL For Update

Oct 1, 2007

Can someone please tell me where I went wrong with the following:

SQL = "UPDATE tblSoldCase " & _
"SET tblSoldCase.[Case Name] = tblProspect.[Case Name], tblSoldCase.[Admin Letter App/Decl] = tblProspect.[Admin Leffer App/Decl]" & _
"Where (((tblProspects.[Case Track Nbr]) = " & Me.txtCaseTrackNbr & "));"

DoCmd RunSQL SQL

I want run a update SQL

View 5 Replies View Related

Help On RunSQL Statement

Jul 13, 2006

have this code on a on click event of a button on a form

Code:lngMyEmpID = Me.cboEmployee.value yes = "Yes" DoCmd.RunSQL ("UPDATE Users " & _ "SET [loggedIn] = '" & yes & "' " & _ "WHERE [lngEmpID] = lngMyEmpID;")

it prompts the user to enter the value for lngMyEmpID,
i have tried to change the WHERE to

"WHERE [lngEmpID] = '" & lngMyEmpID & "'

buth then get a data type mis match error

the lngEmpID is the column name of the table and its an autonumber

the lngMyEmpID is a number tied to a combo in which the user selects the username.

any ideas on how to get this working

View 1 Replies View Related

Simultaneaus Deletion Using RunSQL

May 18, 2005

can some one help.

i have two tables table1,table2 each with one same field Country.

In my RunSQl query, i want to delete all records in table1 and table2 simultaneously from one button with a certain country.
The problem is how can i do this from may be one RunSQL statement
i tried to use two deletion runSQl commands, but only one is executed. Actually the one that comes second.
If i put doevents in the middle of these statements, only the first is executed.

Here is my code:

Private Sub RemoveCtry_Click()
docmd.setwarnings false
Docmd.RunSQL "DELETE * FROM table1 WHERE (Country='" & USA & "')"
Doevents
Docmd.RunSQL "DELETE * FROM table2 WHERE (Country='" & USA & "')"
docmd.setwarnings true
End Sub

if I use One Statement:

Docmd.RunSQL "DELETE table1.Country,table2.Country FROM table1,table2 WHERE ((table1.Country='" & USA & "' ) AND (table2.Country=' " & USA & " '))"
I get an error that i have to specify the table to delete from!!!!

View 14 Replies View Related

Error: DoCmd.RunSQL

Jul 12, 2007

Hi guys,
what's the problem in this code:

DoCmd.RunSQL "SELECT * FROM SecounderyInfo WHERE [LangEs]=Yes;"

every time i excute it an error appear:
Run-time error '2342'

thanx

View 1 Replies View Related

Help For Newbie , DoCmd.RunSql

Mar 2, 2008

hello,

im creating a small database and am extremely new to vba, ive got stuck on running a select query from within the vba code itself, the book im currently reading while learning about this stuff suggests my code whould work but after looking into it i believe its wrong as ive read various threads saying you cannot use docmd.runsql with a select query, just wondering if anyone could help and throw some light on how to get this little bit of code working. below is the part of my code that falls over,

basicaly it should lookup the weight based on what the parcel type is and find the price, the parcel type is worked out earlier in my code and is held in strParcelType, theirs probably an easier way to do this as well but have'nt got that far in my book :)

intWeight = Me.txtWeight

Select Case intWeight
Case 0 To 100
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[0-100g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 101 To 250
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[101-250g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 251 To 500
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[251-500g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 501 To 750
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[501-750g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 751 To 1000
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[751-1000g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 1001 To 1250
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[1001-1250g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
End Select

Me.txtEstimate = strParcelType & intCost

many thanks to any gurus who can point me in the right direction or show me a easier way to do this

View 11 Replies View Related

Docmd.RunSQL Upadate Problem ??

May 10, 2007

Hey guys.

I have a VBA application i am writing.
I am trying to use an sql statement with docmd.runsql to insert a value into my table that matches a particular record. (which will be the one open). For testing purposed i have stripped down my code.

When running the code it works, however it puts chinese symbols in all the other fields of the record and dose not insert the record into the CapExFileName Field. Then when you try and delete the record it comes up with no search index found.

Any idea on why it would be doing this ?

here is the code.


.......



Dim SQLstring As String

SQLstring = "UPDATE Assets SET Assets.CapExFileName = 'Test' WHERE Assets.Barcode = 'Testies1234'"

Docmd.RunSql SQLString


....



Mark.

View 3 Replies View Related







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