Paula
As Nelson on the Simpson's says, "Ha-ha!" Send the doughnuts to a homeless shelter.
Bill
--- In MS_Access_Professionals@yahoogroups.com, "pstr0ud" wrote:
Bill! You are SO correct! I thought I'd deleted ALL the code related to the 'timer' form, but left a small snippet of code which kept creating my space bar and other problem. THANK YOU for your wisdom! OK ... where do I send the donuts?
... sorry for the delay of several days in getting back to you - was on another project.
Again, THANK YOU!!!
Paula
--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" wrote:
Paula
I bet a year's supply of doughnuts (mmm...doughnuts!) that you have a form with a timer open. When timers fire they "refresh" the code window and spaces at the end of lines get dropped.
Make sure all forms and reports are closed when you write code.
Bill
--- In MS_Access_Professionals@yahoogroups.com, "pstr0ud" wrote:
Duane, you are a dear! Your code worked just fine. I'll look at your positioning of the double/triple quotes cause I don't always understand when they are applied. (Any references re. this would be appreciated.)
RE: the space prior to the "WHERE " ... for the past week I have been having difficulty with coding in a module in Access -- when I press the space bar, a space is made, and then I continue entering and the space is omitted. Obviously I then go back and put the spaces in. Greatly slows down my work. Another issue I've recently had is when I declare a new variable, such as strName, the leading letter will be changed to a capital (StrName). This I can live with, but not the space issue.
What I've tried today: got a new keyboard - same issues. Went to another PC and downloaded this Access database app, and have the same space issue.
Are you aware of any problems or remedy for this?
Again, MANY thanks!
Paula
--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom wrote:
Looks like you are missing a space prior the "WHERE "
I would try something like following which assumes Semester is a text field:
strCounselorEmailQuery = "SELECT S.MUID, [FirstName] & ' ' & [LastName] AS StudentName, " & _
"B.Semester, B.RWAR, B.RWAR_Date, B.CAA, B.CAA_Date, B.CPRB, B.CPRB_Date, B.CounselorFName AS CounselorFN, B.CounselorEmail "
strCounselorEmailQuery = strCounselorEmailQuery & "FROM tblStudent S LEFT JOIN tblCollegeActionHistorical_RptB B " & _
"ON S.MUID = B.MUID "
strCounselorEmailQuery = strCounselorEmailQuery + " WHERE B.Semester= """ & [Forms]![frmEarlyEnrollmentImports].[cboMaintenanceImportFiles] & """ AND " & _
"(B.RWAR_Date=Date() OR B.CAA_Date=Date() OR B.CPRB_Date=Date() );"
Duane Hookom MVP
MS Access
----------------------------------------
To: MS_Access_Professionals@yahoogroups.com
From: paula_stroud@
Date: Mon, 10 Dec 2012 22:55:26 +0000
Subject: [MS_AccessPros] Re: Run-Time Error 3085 Using DAO
Thanks for the reply Bill.
Below is my code. Today I cannot replicate the DAO error; instead I get "3135 SYNTAX ERROR IN THE JOIN OPERATION". Or if I use the query instead of SQL (which can be found below with *******************), I get the error message "3061 too Few Parameters. Expected 1".
If this is too difficult to read (due to formatting when I paste it here), is there a way I can upload it as a WORD or .txt doc?
Thank you for any light you can shed on this. This works fine if I remove the criteria (gotten from the combo box selection) from either the SQL or query.
Paula
__________________________________________________________
Private Sub cmdSendEarlyEmail_Click()
On Error GoTo Err_cmdSendEarlyEmail
Dim strPrompt As String 'User prompt: Proceed or quite
Dim strSubject As String 'Subject line
Dim strMessage As String 'email body message ''
Dim qryCollegeActionHistorical_CounselorEmail As String '******** SELECT query alternative for looping thru.
Dim strSemester As String
' strSemester = "cboMaintenanceImportFiles"
strSemester = Me.cboMaintenanceImportFiles
MsgBox "Semester in combo box is: " & strSemester
'*** Open the query, so user can see who the students are that have RWAR or CAA changes.
'*** to aid in whether to send their SSS Counselor an email.
`******************** qryCollegeActionHistorical_CounselorEmail - - new query 12/6/2012
`******************** DoCmd.OpenQuery "qryCollegeActionHistorical_CounselorEmail", acViewNormal, acReadOnly
strPrompt = "Do you want to send an individual email " _
& vbNewLine & "to each SSS Counselors whose" _
& vbNewLine & "student has a change in his/her" _
& vbNewLine & "RWAR or CAA status with this file upload?"
If MsgBox(strPrompt, vbYesNo, "Possible Change in RWAR or CAA status -") = vbYes Then
Dim strCounselorEmailQuery As String
'the following gives SYNTAX ERROR in JOIN OPERATION:
strCounselorEmailQuery = "SELECT tblStudent.MUID, [FirstName] & "" "" & [LastName] AS StudentName, tblCollegeActionHistorical_RptB.Semester, tblCollegeActionHistorical_RptB.RWAR, tblCollegeActionHistorical_RptB.RWAR_Date, tblCollegeActionHistorical_RptB.CAA, tblCollegeActionHistorical_RptB.CAA_Date, tblCollegeActionHistorical_RptB.CPRB, tblCollegeActionHistorical_RptB.CPRB_Date, tblCollegeActionHistorical_RptB.CounselorFName AS CounselorFN, tblCollegeActionHistorical_RptB.CounselorEmail FROM tblStudent LEFT JOIN tblCollegeActionHistorical_RptB ON tblStudent.MUID = tblCollegeActionHistorical_RptB.MUID"
strCounselorEmailQuery = strCounselorEmailQuery + "WHERE (((tblCollegeActionHistorical_RptB.Semester)=[Forms]![frmEarlyEnrollmentImports].[cboMaintenanceImportFiles]) AND ((tblCollegeActionHistorical_RptB.RWAR_Date)=Date())) OR (((tblCollegeActionHistorical_RptB.Semester)=[Forms]![frmEarlyEnrollmentImports].[cboMaintenanceImportFiles]) AND ((tblCollegeActionHistorical_RptB.CAA_Date)=Date())) OR (((tblCollegeActionHistorical_RptB.Semester)=[Forms]![frmEarlyEnrollmentImports].[cboMaintenanceImportFiles]) AND ((tblCollegeActionHistorical_RptB.CPRB_Date)=Date()));"
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strCounselorEmailQuery)
'*************** Set rst = dbs.OpenRecordset("qryCollegeActionHistorical_CounselorEmail")
Do While Not rst.EOF
MsgBox " Student Name: " & rst![StudentName] _
& vbNewLine & " Semester: " & rst![Semester] _
& vbNewLine & " RWAR status: " & rst![RWAR] _
& vbNewLine & " CAA Status: " & rst![CAA] _
& vbNewLine & " CPRB Status: " & rst![CPRB] _
& vbNewLine & " Counselor Email: " & rst![CounselorEmail]
'* Subject line of email.
strSubject = "Possible change in RWAR status -or- CAA status -or- CPRB status"
'* Body of email message.
strMessage = "This is an automated email message." _
& vbNewLine & vbNewLine & rst![CounselorFN] & ", you're the SSS Counselor for student " & rst![StudentName] & ". " _
& vbNewLine & "It appears a recent change has occurred for Semester " & rst![Semester] _
& vbNewLine & "in one of these College Codes - other than ""N"" which stands for NO. " _
& vbNewLine & vbTab & "RWAR status (Required to Withdraw for Academic Reasons): " & rst![RWAR] _
& vbNewLine & vbTab & "CAA status (College Academic Alert): " & rst![CAA] _
& vbNewLine & vbTab & "CPRB status (College Academic Probation): " & rst![CPRB]
DoCmd.SendObject acSendNoObject, , , rst![CounselorEmail], "Director@", , strSubject, strMessage, True
rst.MoveNext
Loop
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing
Else: MsgBox "Action canceled."
Exit Sub
End If
--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" wrote:
You'd have to post the query's SQL so we can see what it looks like.
This Microsoft page might be of help:
http://support.microsoft.com/kb/180810
Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com
--- In MS_Access_Professionals@yahoogroups.com, "pstr0ud" wrote:
Hello all,
I have a form with a combo box that contains Semester choices.
The form has a button, with code ...
I want to open a recordset and loop through a query, using criteria. I can do this fine without criteria or with hard coded criteria in the query. BUT I want the Semester criteria to come from the combo box on the form where the button exists.
When I try this, I get this error message: Run-Time Error 3085 Using DAO
I can do this two ways, and both gives this error message:
1) in the query, where the combo box on the form is is referenced
2) instead of a query, put the SQL directly in the code behind the button.
Is there some issue with DAO and having criteria like this? Can anyone help?
Thank you.
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (21) |
Tidak ada komentar:
Posting Komentar