Combo Box Wizard gives Syntax Error (Access 2K)

**DonH** · 2003-05-15 19:45

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?

**HansV** · 2003-05-15 20:05

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.

**charlotte** · 2003-05-16 01:32

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

**SteveH** · 2003-05-16 08:22

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.

**DonH** · 2003-05-16 12:58

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.

**patt** · 2003-05-16 19:36

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

**charlotte** · 2003-05-16 20:50

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?

**DonH** · 2003-05-19 12:31

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

How do you repair the wizards?

Don.

**HansV** · 2003-05-19 12:42

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.

**johnhutchison** · 2003-05-20 00:58

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.

**DonH** · 2003-05-20 14:23

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.

**SteveH** · 2003-05-20 15:05

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)

**HansV** · 2003-05-20 15:39

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.)

**DonH** · 2003-05-20 18:14

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........?

**HansV** · 2003-05-20 18:36

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.

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

Thread Tools