Web Development

Ask something
Discussion Locked

Access VB Error

0 helpful
5 replies

by jkeav634

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Access 2000.
I am attempting to make a sub-form with 2 list boxes.The sub for is going into a employee info database. One list box contains data(list1 will contain all department names) from a table, One list is empty(list2 will contain the departments that are selected from list 1). I have set up a cmd button to add a user selected entry from list 1 into the empty list two (the info from list two will populate a many to many table).
I have started copying the code from a database that has this same setup.

I am receiving the following error:
"COMPILE ERROR-----SYNTAX ERROR"
The code that is causing the error is as follows:
"& "Where (((consumerprogram_tbl.Consumerid) = & str(currentconsumerid) & ")) "
& "ORDER BY prim_prog_tbl.programid;"

Are there any obvious errors? Is there an easier way to do this?
Any help is appreciated

5 replies

by dushy_ant

"<< Your Statement >>"
"& "Where (((consumerprogram_tbl.Consumerid) = & str(currentconsumerid) & ")) "
& "ORDER BY prim_prog_tbl.programid;"

"<< Statement should be >>"
"& "Where (((consumerprogram_tbl.Consumerid) =" & str(currentconsumerid)& ")) "
& "ORDER BY prim_prog_tbl.programid"

You have forgot to place a Qoutation mark before "& str" at the second line. Also , the semi colon at the end is not required.

by bob sellman

Actually, the first answer is only partially correct. When using a variable as part of building a SQL code you must do two things:

1. Since you want the value of the variable (or control), your code should be (as the first answer mentions) "where [field] = " & variable & " order by ....". In other words, the variable should not be within the double quotes.

2. The second criteria is that you must surround the variable with the proper indicators, based on the type of variable. The indicators used are:

number: no indicator
string: single quote (')
date: pound sign (#)

Because you are using (I assume) a string variable, you must surround it with double quotes. As a result, your statement should read:

"Where (((consumerprogram_tbl.Consumerid) = '" & str(currentconsumerid) & "' ORDER BY prim_prog_tbl.programid"

(Obviously, wherever you are continuing to a new line you must insert additional double quotes and an underline to continue the statement on the next line.)

I do question why you are converting currentconsumerid to a string. I ask this because I would assume that consumerprogram_tbl.Consumerid would be the same type of variable (I assume a number field?) as currentconsumerid. If my assumption iscorrect, you don't need to convert currentconsumerid to a string with the str() function and you then would not have to surround the value with single quotes, since it is a number.

This discussion is closed to new replies.