currentdb.execute

  • Thread starter Thread starter iccsi
  • Start date Start date
I

iccsi

I have a query which works and use the SQL for my VBA coding using
currentdb.execute strSQL, dbFailOnError, but I got too few parameters,
expect 4.


There is no parameter in required in the query.

The code is like following:


Dim strMySQL As String


strMySQL = " UPDATE qryGroupMeeting INNER JOIN
tblEDAActivityMeetingDetails " & _
" ON qryGroupMeeting.Employee_ID = " & _
" tblEDAActivityMeetingDetails.CSR_ID " & _
" SET tblEDAActivityMeetingDetails.Status = 2 " & _
" WHERE (EDA_ID= " & cmbEDA.Value & ")" & _
" AND (ActivityDate= " & "#" & CDate(Int (dtpActivityDate.Value))
& "#" & ")" & _
" AND (Meetingtype= " & cmbMeetingType.Value & ")" & _
" AND (MeetingDate= " & "#" &
CDate(Int(dtpMeetingDate.Value)) & "#" & ")" & _
" AND (MeetingStartTime= " & "#" & dtpStartTime.Value &
"#" & ")" & _
" AND (MeetingElements= " & cmbMeetingElements.Value &
")"

MsgBox strMySQL, vbOKOnly
CurrentDb.Execute strMySQL, dbFailOnError


any information is great appreciated,
 
First thing to check and double check is that all of your field names are
spelled correctly.  I know it sounds simple, but it's a common problem.

--
--Roger Carlson
  MS Access MVP
  Access Database Samples:www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
 http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L












- Show quoted text -
Click to expand...

Thanks for advise,

I did check the field name and control name and the SQL is copied
from a query which works in the same database.

Thanks again,
 
iccsi said:
strMySQL = " UPDATE qryGroupMeeting INNER JOIN
Click to expand...

Does qryGroupMeeting reference any form controls?
MsgBox strMySQL, vbOKOnly
Click to expand...

I prefer debug.print myself just so I can copy and paste the SQL back
into a query to double check.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Does qryGroupMeeting reference any form controls?


I prefer debug.print myself just so I can copy and paste the SQL
back into a query to double check.
Click to expand...

I've started in some of my apps including copying the error message
to the clipboard for all my error handlers. This makes it much
easier for users to email me about any bugs.
 
David W. Fenton said:
I've started in some of my apps including copying the error message
to the clipboard for all my error handlers. This makes it much
easier for users to email me about any bugs.
Click to expand...


I usually have it written to a log file, along with other information that
might be useful, but I like your idea. I think I'll add that in the future.
My only concern is that the user may have something on the clipboard that
they don't want to have overwritten.
 
I usually have it written to a log file, along with other information that
might be useful, but I like your idea.  I think I'll add that in the future.
My only concern is that the user may have something on the clipboard that
they don't want to have overwritten.
Click to expand...

can you please give me some example to write to clipboard or a log
file?
Thanks again,
 
in message
can you please give me some example to write to clipboard or a log file?
Click to expand...


Here are routines I use to display and log errors. I'm sure a lot of lines
will have been wrapped by the newsreader, so don't expect it to work for you
without some fixing up.

'------ start of code ------
Sub subDisplayAndLogError(ProcName As String, ErrNo As Long, Description As
String, ParamArray DataItems() As Variant)

' This subroutine notifies the user that an error has occurred and logs
' the error on the application's error-log file. Mandatory arguments
are:
' ProcName - the name of the procedure in which the error
occurred
' ErrNo - the error number
' Description - a description of the error
' In addition to these, the caller may pass a set of additional items in
the
' form of name & value pairs; that is, every two elements in the
<DataItems>
' array consists of, first, a string argument that is the name of a data
item,
' and, second, the value of that data item. These pairs will be written
to the
' log file as lines in the form "(tab) name = value".

On Error Resume Next

subLogError ProcName, ErrNo, Description, DataItems

MsgBox "Error " & ErrNo & ": " & Description & _
vbCr & vbCr & "Please contact DataGnostics for assistance.",
_
vbExclamation, "Error in " & ProcName

End Sub

Sub subLogError(ProcName As String, ErrNo As Long, Description As String,
ParamArray DataItems() As Variant)

' This subroutine logs errors to text file <appname>.log in the
application's
' home folder. Mandatory arguments are:
' ProcName - the name of the procedure in which the error
occurred
' ErrNo - the error number
' Description - a description of the error
' In addition to these, the caller may pass a set of additional items in
the
' form of name & value pairs; that is, every two elements in the
<DataItems>
' array consists of, first, a string argument that is the name of a data
item,
' and, second, the value of that data item. These pairs will be written
to the
' log file as lines in the form "(tab) name = value".

On Error GoTo Err_subLogError

Dim strFileName As String
Dim intLogFile As Integer
Dim intX As Integer

' Construct the name of the log file. First get the name of the
back-end database.
strFileName = fncDataDBName()
' From that, get just the path to the folder.
strFileName = Left$(strFileName, Len(strFileName) -
Len(Dir(strFileName)))
' Tack on the application name and the extension ".log".
strFileName = strFileName & Application.GetOption("Project Name") &
".log"

' Open the log file to append to it.
intLogFile = FreeFile()
Open strFileName For Append As #intLogFile

Print #intLogFile, _
Now(), CurrentUser(), ProcName, ErrNo, Description

' If any additional data items were specified, print them on succeeding
lines.
If UBound(DataItems) = 0 Then
If IsArray(DataItems(0)) Then
For intX = LBound(DataItems(0)) To UBound(DataItems(0)) Step 2
Print #intLogFile, Tab; DataItems(0)(intX); " = ";
DataItems(0)(intX + 1)
Next intX
End If
Else
For intX = LBound(DataItems) To UBound(DataItems) Step 2
Print #intLogFile, Tab; DataItems(intX); " = "; DataItems(intX +
1)
Next intX
End If

Exit_subLogError:
On Error Resume Next
Close #intLogFile
Exit Sub

Err_subLogError:
Resume Exit_subLogError

End Sub
'------ end of code ------

The logging function uses a function called "fncDataDBName", not included
here, which interrogates the linked tables to find out where the
application's back-end data store is located. It also gets the name of the
project from the application properties, and uses that to construct the name
of the log file.
 
do the follwing:
MsgBox strMySQL, vbOKOnly
Click to expand...
add:
debug.print strMySql


After you run your code, and the above fails. You see the sql in the debug
window. Take that sql, and paste it into a query.....does it work?
 
do the follwing:


add:
    debug.print strMySql

After you run your code, and the above fails. You see the sql in the debug
window. Take that sql, and paste it into a query.....does it work?
Click to expand...

It is a very good idea to use.
Do I need remove all debug code when I distribute the application or I
can leave in the applications?

If I leave in the application does it affect any performance?

Your information is great appreciated,
 
Back
Top