General discussion

Locked

Access VB Error

By jkeav634 ·
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

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access VB Error

by Dushy_ant In reply to Access VB Error

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

Collapse -

Access VB Error

by jkeav634 In reply to Access VB Error

Poster rated this answer

Collapse -

Access VB Error

by Bob Sellman In reply to Access VB Error

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.

Collapse -

Access VB Error

by jkeav634 In reply to Access VB Error

Poster rated this answer

Collapse -

Access VB Error

by jkeav634 In reply to Access VB Error

This question was closed by the author

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums