Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box Wizard gives Syntax Error (Access 2K)

    When I use the wizard to create a combo box on a form and check the option to retrieve a record based on the value that I picked (the 3rd option on the wizard screen) and then choose a field from a table, I get "Syntax Error in..." and the beginning of an SQL SELECT statement. It looks like the wizard is putting in an extra [ in front of the table name. Is there a fix for this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box Wizard gives Syntax Error (Access 2K)

    Do you have unusual table or field names? For instance, Access allows you to include double quotes in field names, but this will throw the code genrerated by the wizard out of whack.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Box Wizard gives Syntax Error (Access 2K)

    Post the SQL so we can see what the problem might be.
    Charlotte

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Wizard gives Syntax Error (Access 2K)

    I've seen this many times with my students. I think that it happens either when a student used a field called Name in their table or they are missing the Primary Key field which will be used to search. I've not been able to reliably reproduce the problem. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    As HansV has said, using names for fields which are not reserved words and not having spaces in field names seems to help.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Wizard gives Syntax Error (Access 2K)

    No, I don't have any spaces in field or table names, and I don't have any strange names for them either. The form is based on a table called 'Employees', and I want the combo box to show the EmpNo field. The wizard generated this error message:

    Syntax error in query expression '[SELECT [Employees]].[EmpNo]'.

    and then when you click OK, it quits.

    Hope this helps somebody to figure out the problem or at least a workaround. It sure looks to me like there's a bug in the wizard. I tried searching for this in the MS Knowledge Base and didn't find anything helpful.

    Thanks!

    Don.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Box Wizard gives Syntax Error (Access 2K)

    Post the code that the wizard generates.
    Then someone will help you overcome this problem.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Box Wizard gives Syntax Error (Access 2K)

    Well, I've never seen a wizard do this, so I suspect there's more to it. It's entirely possible that you have a corrupted form, table, or even wizard. And I do recall having to repair the wizards in Office 2000 once because the combobox wizard suddenly disappeared. Have you done a detect and repair on the database?
    Charlotte

  8. #8
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Wizard gives Syntax Error (Access 2K)

    I did a compact and repair, if that's what you mean. It didn't change anything.

    How do you repair the wizards?

    Don.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box Wizard gives Syntax Error (Access 2K)

    Hi Don,

    Compact and Repair (in Tools | Database Utilities) performs maintenance on a database. Detect and Repair (in the Help menu) checks and, if necessary, repairs the Access installation. Charlotte meant the latter.

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Combo Box Wizard gives Syntax Error (Access 2K

    I have been seeing something similar lately.

    I have taken over development of a db that uses reserved names for fields sometimes ( and I haven't fixed them all yet).

    If I try to build combo box on a table with a field called 'name', the wizard produces this error message and fails to produce any sql at all.

    I am not asking for any help with this, just thought I would add the observation to the discussion.
    Attached Images Attached Images
    Regards
    John



  11. #11
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Wizard gives Syntax Error (Access 2K

    Thanks Hans and Charlotte for your suggestions. I have done a compact and repair and also a detect and repair, but I still get the error. Attached is a small sample database that generates the error. To see the error:

    1. Open the Absences form in design view.
    2. From the Toolbox, start the combo box wizard.
    3. On the first screen, check the third radio button ("Find a record on my form based on.....")
    4. On the next screen, pick a field for the combo box. It doesn't matter which field you choose, but to make it interesting, don't pick the first field in the list ("First").
    5. When you click Next, you will get the error message. And notice that the SELECT statement that the wizard generates always refers to the first field, no matter which one you have asked the wizard to add!

    I think this must have something to do with either the presence of a subform or basing the main form on more than one table. I tried using a simple form based on a single table and with no subform, and the wizard worked just fine.

    All suggestions for resolving this error will be most appreciated!

    Don.
    Attached Files Attached Files

  12. #12
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Wizard gives Syntax Error (Access 2K

    You have a fields called date; first; middle; last (not good as these contain reserved words).
    I suspect your main issue is the lack of Primary keys. Access cannot determine a unique identifier to search on.
    Your main form - sub-form is wrong too. You should have the employee providing the main form (the one-end of the relationship) and the Absences providing the sub-form records (the many-end of the relationship)
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box Wizard gives Syntax Error (Access 2K

    As you suggest yourself, the Absences form should be based on one table only. Only employee data are shown in the main form. There is no need at all to include fields from absences in the main form. In Access 2002, the Combo Box Wizard didn't even present the third option ("Find a record on my form based on.....").

    DataTable should have a primary key on EmpNo; Absences should have a composite primary key on the combination of EmpNo and Date. When you have created these, remove the relationship between the tables and create it anew; set referential integrity and cascading updates (and if you like, cascading deletes.)

  14. #14
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Wizard gives Syntax Error (Access 2K

    Thanks Hans and Steve for the very helpful suggestions. I made all of them, but the wizard STILL doesn't work! I'm suspicious that it just isn't set up to deal with forms and subforms and their relationships. I suppose I could just do the whole thing manually by having the wizard generate the code in a situation where it does work and then copying and pasting that code into my form and adjusting the properties. Of couse, the wizard SHOULD work, but maybe that's too much to hope for! Anyway, the revised database is attached if you want to play around with it.

    Regards, and thanks again.

    Don.

    PS. I didn't say that I'm running Win XP. That shouldn't make any difference, but........?
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box Wizard gives Syntax Error (Access 2K

    For some reason, the wizard again displayed only the first two options in the database "out of the box". I changed the Record Source of the form to the EmployeeData table instead of an SQL string, and that seems to do the trick. I have attached the database with the form including combo box. I left it the way it was created by the wizard, even the bizarre control names set by the Dutch version of the wizard.

    Lesson: set the Record Source of a form or report to a table (or stored query, that works too) instead of an SQL string, if possible.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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