0

I am using Access 2016 VBA. All code works fine, otherwise.

Public Function PopUp()

   Dim strSQL As String
   Dim rs As DAO.Recordset
   Dim db As DAO.Database

   strSQL = "SELECT PopUpReminders.*, PopUpReminders.ReminderCompletion, PopUpReminders.ReminderStartDate, PopUpReminders.Employee FROM PopUpReminders WHERE (((PopUpReminders.ReminderCompletion)=False) AND ((PopUpReminders.ReminderStartDate)<=Now() AND ((PopUpReminders.Employee)='" & Forms![Login]![txtUserName] & "'));"
   Set db = CurrentDb
   Set rs = db.OpenRecordset(strSQL)

   If rs.RecordCount = 0 Then
      'Do Nothing
   Else
      If rs.RecordCount > 0 Then
      Do
         DoCmd.OpenForm "SFPopUpReminder"
      Loop Until rs!ViewedRecord = True
      End If
   End If

   rs.Close
   Set rs = Nothing

End Function

The error that appears is (copied exactly)

MS VB Run-time error 3075: Syntax error in query expression '(((PopUpReminders.ReminderCompletion)=False) And ((PopUpReminders.ReminderStartDate)<=Now() And ((PopUpReminders.Employee)='rerdeljac'));'.

Please note, "rerdeljac" is the logintext entered into the textbox on Forms![Login]![txtUserName] and which was matched to PopUpReminders.Employee; please note also that the error message does not include the remainder of the SQL code.

(PopUpReminders.Employee) is a field on a table filled only with text, and Forms![Login]![txtUserName] is a textbox on a form intended to be filled only with text characters.

The error occurs on the Set rs = db.OpenRecordset(strSQL) line.

Improve this question
8
  • Closing parenthesis missing? Commented Feb 16, 2017 at 14:57
  • 1
    Can't spot it directly, but suggest parenthesis counting. And I'm sure little Bobby Tables would like to visit your login page. Commented Feb 16, 2017 at 14:58
  • PopUpReminders.* selects everything, you then go on to list fields/columns which will be renamed. Commented Feb 16, 2017 at 15:00
  • Clicking Debug and then compile allows the SQL query to pass without errors, I've checked the parentheses, they seem to work. Fionnuala, I'm honored you responded; I've read many of your posts and they are always informative. However, I'm unsure how a SELECT statement will rename my fields/columns? My goal with the SQL query is to use it to pull a specific set of records from the table in question, and then use those records to create a recordset. Does that help? Commented Feb 16, 2017 at 15:13
  • You flatter me! I am saying why are you selecting all columns and then selecting specific columns? The columns will be renamed to include the table name, so you will have, for example, both table1.field1 and field1. Commented Feb 16, 2017 at 15:45

2 Answers 2

Reset to default
1

Your statement needs one more ) right after Now() if I am counting right. Your SQL statement is overly complicated (probably because you copied it from a query you made using the GUI). This is sufficient:

"SELECT * FROM PopUpReminders WHERE ReminderCompletion=False AND ReminderStartDate<=Now() AND Employee='" & Forms![Login]![txtUserName] & "'"

This will fail if one of your users decides to type a ' (single quote) in txtUserName. You should at least change it to Replace(Forms![Login]![txtUserName],"'","''")

Also RecordCount is not reliable. You should use rs.EOF=False OR rs.BOF=False to check if any records were returned and iterate through them with rs.MoveFirst and rs.MoveNext.

Improve this answer
Sign up to request clarification or add additional context in comments.

15 Comments

SunKnight, I love the simplicity of your code, and you are correct that I used the Create Query to create the SQL query, as my SQL is much worse than my VBA. When I tried your code, Access required parentheses, which I inserted. It then had a compile error on the comma immediately after [txtUserName], "expected )". Inserting the close parentheses prior to the comma gave the error: "Expected: end of statement" with the comma still highlighted; replacing the comma gave the same error with the "'" immediately following the close parentheses highlighted. Not sure how to fix.
um, side note since you brought it up, good point on the recordcount. thanks! (and thank goodness I've already written all that elsewhere so I can copy it!)
No parenthesis should be required, other than the ones in Now(). Something else is wrong. Try MsgBox strSQL to see what your query looks like.
Select all is generally deprecated, how about using an alias as well? say on the lines of SELECT p.ReminderCompletion, p.ReminderStartDate, p.Employee FROM PopUpReminders p WHERE p.ReminderCompletion=False AND p.ReminderStartDate<=Now() AND p.Employee='username' This works for me, of course you wil have to change username to the form reference.
That's unnecessary for a single table select. Specifying the table is only really needed for either clarity or if two tables linked together has a field with the same name (unless you know of another reason I don't?). Also based on the code he only really needs ViewedRecord in the SELECT so he could just use SELECT ViewedRecord FROM PopUpReminders WHERE... instead of the *.
|
0

Actually, it was a combination of Fionnuala's removal of column names and SunKnight0's addition of the parentheses after the Now() that cured the issue. I can't put the answer to both, and SunKnight went way above and beyond so he gets the mark. Here is the corrected code, for those who might need it in the future:

strSQL = "SELECT PopUpReminders.* FROM PopUpReminders WHERE (((PopUpReminders.ReminderCompletion)=False) AND ((PopUpReminders.ReminderStartDate)<=Now()) AND ((PopUpReminders.Employee)='" & Forms![Login]![txtUserName] & "'));"
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.