Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009

    Unanswered: DoCmd.RunSQL UPDATE with Me!Listbox.Column(varitem) giving errors


    I have an access 2007 database that stores information about keys that have been loaned out to faculty members. Each key has an expiry date, and I have created a form that collects these items in a list box and then sends an email to the keyholders.

    When I press a button, It runs a VB script that generates a custom email to the user and then updates the entry in the table where this info is stored. I want it to add the current date (Date()) to a column titled [Email Sent]. Here is the code that I have come up with so far:

    DoCmd.RunSQL "UPDATE DataTable SET [Email Sent]=Date() WHERE [First Name]=" & Me!lstContacts.Column(2, varItem) & " AND [Last Name]=" & Me!lstContacts.Column(1, varItem) & " AND [Department]=" & Me!lstContacts.Column(6, varItem) & " AND [Due Date]=" & Me!lstContacts.Column(0, varItem) & " AND [Permit Number]=" & Me!lstContacts.Column(4, varItem) & " AND [Key Number]=" & Me!lstContacts.Column(5, varItem) & " AND [Email Address]=" & Me!lstContacts.Column(3, varItem) & ";"

    When I run this script, it will return an error that reads:

    Syntax error (missing operator) in query expression '[First Name]=first AND [Last Name]=last AND [Department]=dept AND [Due Date]=01/01/2009 AND [Permit Number]=01234 AND [Key Number]=1111 AND [Email Address]=None'.

    I think I am on the right track, could anybody help me out please?

  2. #2
    Join Date
    May 2005
    I see you haven't decided to normalize after all

    You're missing single quotes and pound signs, as you need to tell Access that you're looking for a string and a date respectively, not a constant or a variable or something else. So, like:
    [First Name]='first' AND [Last Name]='last' AND [Department]='dept' AND [Due Date]=#01/01/2009# AND [Permit Number]=01234 AND [Key Number]=1111 AND [Email Address]='None'

    Also, I assume you're going to fix the "none" criteria?
    Me.Geek = True

  3. #3
    Join Date
    Feb 2009

    It Works!

    I have been struggling with this database for so long that I just want to get it done. That is why I have no motivation to normalize the data... Its almost doing exactly what I want it to do so I will leave it how it is. Maybe for my next database project...

    As for the "None", its just my default value for any email addresses that I havent yet put into the database

    Thanks again for the super fast reply!

  4. #4
    Join Date
    Dec 2004
    Madison, WI
    I'd also copy things such as Me!lstContacts.Column(6, varItem) into variables first and use those variables in constructing your SQL statement versus the actual Me!lstContacts.Column(6, varItem).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts