Single lstbox with 3 text boxes

[Techie1201]

Hi all,

Still trying to get a grip on lstboxes. This is my latest syntax error.

Form is setup this way.

one unbound multi-select lstbox (lsttechnique) populates correctly set to extended column count 1 bound column 1.

3 unbound txtboxes as follows:

txtbrfid dtatype number
txtfreq datatype number
txttotdur datatype number

brfid is pulled from form that opened this form and comes in correctly
freq and totdur are typed in by end user.
lsttechnique is various techniques used

cmdaddtech has the follwing code:

Private Sub cmdaddtech_Click()
Dim db As DAO.Database
Dim varItem As Variant


Set db = CurrentDb


With Me.lsttechnique
For Each varItem In .ItemsSelected


db.Execute _
"INSERT INTO tblNSCIPRLessRestrictive " & _
"(Brfid, Technique, Freq, totdur) " & _
"VALUES (" & _
.ItemData(varItem) & ", " & _
Chr(34) & Me.txtFreq & Chr(34) & ", " & _
Chr(34) & Me.txtTotdur & Chr(34) & ", " & _
Chr(34) & Me.txtBRFID & Chr(34) & ", " & _
dbFailOnError


Next varItem
End With

myReply = MsgBox("Are you sure you want to add this/these techniques?", vbYesNo)
If myReply = vbYes Then
DoCmd.Requery
End If
End Sub

When I click it I get a runtime error 3075 with missing operator in query expression with the selection I choose in the lsttechnique in single quotes.

exact error is Syntax error (missing operator) in query expression 'One are release'.

Thanks in advance

[robjens]

Your SQL like this won't work. As the the program says, there is something in your query that is wrong. I always check my queries inside the querybuilder sql view to make sure it returns the right values and make sure the syntaxis is correct.

First comment, you are using double quotes in the VBA SQL string, that won't work like this. You need to use single quotes for text values, none for numbers or boolean and sharp for dates. You also don't need Chr(34) for this.

For readability I would always use a short named variable but not required tho. Also I advise to take a look at your naming conventions. The table name is a bit off. Last, no need to use With here is there?

This query should work:

db.Execute _
("INSERT INTO MyTable (fldString1, fldInt2, fldBool3) VALUES " & _
('" & strValue1 & "', " & intValue2 & ", " & IsChecked3 & ";")
dbFailOnError

[Techie1201]

Thanks for the reply, but I couldn't get that to work. I copied your example in, changed it to my values and got compile error. Doesn't like this.

'" & strValue1 & "'

I will play with it some more. Just learning so forgive me if I am missing the obvious.