Run Time Error 3075 - extra bracket

[Guest]

Hi, this is driving me crazy. I have copied some code generously provided by
some expert out there to build some list boxes where the user can select from
a list and display the selected items in another box (also filling these
selections into a junction table). There's a command button to add the items
and another command button to remove the items. The button to remove items
from the list gives a run-time error '3075': extra ) in query expression
'Near_Miss_ID=2 AND (Detail_Link_ID=)'.


Private Sub cmdRemoveOne_Click()
Dim strSQL As String
Dim strwhere As String
Dim db As DAO.Database
Dim varItem As Variant
For Each varItem In Me.SelectedDirectCause.ItemsSelected
strwhere = strwhere & "Detail_Link_ID=" &
Me.SelectedDirectCause.ItemData(varItem) & " OR"
Next varItem
strwhere = Left(strwhere, Len(strwhere) - 4)
strSQL = "Delete * from Near_Miss_Detail_TBL where Near_Miss_ID=" &
Me.Near_Miss_ID & " AND (" & strwhere & ");"
Set db = CurrentDb
db.Execute strSQL
Me.ListDirectCause.Requery
Me.SelectedDirectCause.Requery
Set db = Nothing
End Sub

I have tried removing some of the brackets, but I just generate other errors
about missing syntax. Can anyone spot my error here? Thanks as always!

BTW, not sure if it matters but there is a difference in that I built this
as a subform to go in a main form which is what the Near_Miss_ID relates to.
I have a Near_Miss_ID in both my Main Form and Subform (the current record
number is 2 which is what I'm assuming the =2 is referring to in the error
message)

 

[arthurjr07]

I guess the variable Me.SelectedDirectCause.ItemData(varItem) is an
empty string

To verify this try to insert a debug.print in the code.

Like this

Set db = CurrentDb
db.Execute strSQL
Debug.Print strSQL
<--------------------------------------------------
Me.ListDirectCause.Requery
Me.SelectedDirectCause.Requery
Set db = Nothing


In your intermediate window you can verify your SQL statement.

 

[Guest]

Thanks Arthur Jr. I put in the Depug.PrintSQL but still get the error
message (it's not printing anything different to the screen. Do I take out
the End Sub?

On another note, if I try to open the Main Form (with the subform in it) -
it doesn't even show the items that have been selected and instead seems to
dislike:

strwhere = Left(strwhere, Len(strwhere) - 4)

Perhaps this has something to do with the problem too? I'm still not very
good with code so all advice is appreciated.

 

[arthurjr07]

debug.print will not fixed the error. it is just like a message box.
The reason why i want you to insert a "Debug.Print strSQL " in that
code is,
for us to see what SQL statement looks like.

Try to test if the Me.SelectedDirectCause.ItemData(varItem) is an
empty string . I am sure that is the cause of error.

On another note, if I try to open the Main Form (with the subform in it) -
it doesn't even show the items that have been selected and instead seems to
dislike:

strwhere = Left(strwhere, Len(strwhere) - 4)

Yes it will cause an error because strwhere is an empty string, so if
you get
the Len(strwhere ) it is 0, then minus 4 it is -4. Left syntax do not
accept negative
values and that cause an error.

Same with if Me.SelectedDirectCause.ItemData(varItem) is an empty
string, your
SQL statement will be wrong.

 

[Douglas J Steele]

Inside your loop, you're adding " OR" to the end of each string. Afterwards,
you're taking off the last 4 characters. All " OR" adds is 3 characters.

You need to be adding " OR " inside the loop. (If you did have more than 1
item selected, you'd have got a different error due to the missing space)

 

[Guest]

Thanks a lot to both pf you Arthur & Douglas. Please forgive these probably
simple questions:

Arthur, I'm not sure what you mean by an empty string. I have put in the
Debug.StringSQL and thought that if I opened the subform and tried to remove
an item it would print my SQL in a box or something on the screen however,
all I get is the same error message that I was getting before. Am I looking
in the wrong spot? This seems like something I should probably be doing all
the time when muddling my way through code!

Doug, I changed my 4 to a 3 and now the subform works on it's own (I feel
like I'm almost there!) but when I open the Main Form it asks for Parameter
Value "Forms!Near_Miss_Detail_Subform!Near_Miss_ID". What does it mean to be
adding "OR" inside the loop?

Just in case, this is the SQL for my Row Source of SelectedDirectCause

SELECT [Detail_Link_TBL].[Detail_Link_ID],
[Near_Miss_Detail_TBL].[Detail_Link_ID],
[Near_Miss_Detail_TBL].[Near_Miss_ID], [Detail_Link_TBL].[Detail]
FROM Detail_Link_TBL INNER JOIN Near_Miss_Detail_TBL ON
[Detail_Link_TBL].[Detail_Link_ID] = [Near_Miss_Detail_TBL].[Detail_Link_ID]
WHERE
(((Near_Miss_Detail_TBL.Near_Miss_ID)=[forms]![Near_Miss_Detail_Subform]![Near_Miss_ID]));

 

[Richard Ling]

Does this work any better?

' #######
Public Sub Test()

Dim strSQL As String
Dim strWhere As String
Dim db As DAO.Database
Dim varItem As Variant

For Each varItem In Me.SelectedDirectCause.ItemsSelected
strWhere = strWhere & "Detail_Link_ID=" & _
Me.SelectedDirectCause.ItemData(varItem) & " OR"
Next varItem

strSQL = "Delete * from Near_Miss_Detail_TBL where Near_Miss_ID=" &
_
Me.Near_Miss_ID & _
IIf(Len(strWhere) > 0, " AND (" & Left(strWhere,
Len(strWhere) - 3) & ");", ";")

Set db = CurrentDb

db.Execute strSQL
Me.ListDirectCause.Requery
Me.SelectedDirectCause.Requery

Set db = Nothing

End Sub
' #####


Instead of taking 4 characters off the end, we are taking off 3
characters as the string " OR" which are you are adding on each
iteration of the loop is only 3 characters long.

Also included a check to see if strWhere is an empty string (contains
no characters, which could occur if no items were selected in the
listbox). If it is empty, then the AND section is not added.

 

[Richard Ling]

Whoops.. my mistake.. Douglas was of course correct, the " OR" should
actually read " OR " so..

For Each varItem In Me.SelectedDirectCause.ItemsSelected
strWhere = strWhere & "Detail_Link_ID=" & _
Me.SelectedDirectCause.ItemData(varItem) & " OR "
Next varItem

and the -3 should be -4 after all, so...

strSQL = "Delete * from Near_Miss_Detail_TBL where Near_Miss_ID=" & _
Me.Near_Miss_ID & _
IIf(Len(strWhere) > 0, " AND (" & Left(strWhere, Len(strWhere)
- 4) & ");", ";")

Sorry, guess i shouldn't try to help when my brains already fried from
coding all day! Hope thats sorted anyway

Rich

 

[Douglas J Steele]

You can't refer directly to a form that's being used as a subform. You need
to refer to its parent form, then to the subform control on the parent form
that holds the subform:

[forms]![Parent_Form]![Subform_Control_Name].Form![Near_Miss_ID]

Note that the name of the subform control that contains the form may or may
not be the same as the form it contains: it all depends how you created the
subform. If you did it by dragging the one form on top of the other, the
subform container will generally have the same name as the form being used.
If you created the subform by adding a subform control to the main form and
then using the wizard, the subform control will usually be named something
like Child0.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks a lot to both pf you Arthur & Douglas. Please forgive these probably
simple questions:

Arthur, I'm not sure what you mean by an empty string. I have put in the
Debug.StringSQL and thought that if I opened the subform and tried to remove
an item it would print my SQL in a box or something on the screen however,
all I get is the same error message that I was getting before. Am I looking
in the wrong spot? This seems like something I should probably be doing all
the time when muddling my way through code!

Doug, I changed my 4 to a 3 and now the subform works on it's own (I feel
like I'm almost there!) but when I open the Main Form it asks for Parameter
Value "Forms!Near_Miss_Detail_Subform!Near_Miss_ID". What does it mean to be
adding "OR" inside the loop?

Just in case, this is the SQL for my Row Source of SelectedDirectCause

SELECT [Detail_Link_TBL].[Detail_Link_ID],
[Near_Miss_Detail_TBL].[Detail_Link_ID],
[Near_Miss_Detail_TBL].[Near_Miss_ID], [Detail_Link_TBL].[Detail]
FROM Detail_Link_TBL INNER JOIN Near_Miss_Detail_TBL ON
[Detail_Link_TBL].[Detail_Link_ID] = [Near_Miss_Detail_TBL].[Detail_Link_ID]

WHERE

(((Near_Miss_Detail_TBL.Near_Miss_ID)=[forms]![Near_Miss_Detail_Subform]![Ne
ar_Miss_ID]));

Inside your loop, you're adding " OR" to the end of each string. Afterwards,
you're taking off the last 4 characters. All " OR" adds is 3 characters.

You need to be adding " OR " inside the loop. (If you did have more than 1
item selected, you'd have got a different error due to the missing space)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


provided
by select
from the
items relates
to.

 

[Guest]

Thanks to all of you! My subform is now working perfectly. Once again,
really appreciate all the help you provide here

You can't refer directly to a form that's being used as a subform. You need
to refer to its parent form, then to the subform control on the parent form
that holds the subform:

[forms]![Parent_Form]![Subform_Control_Name].Form![Near_Miss_ID]

Note that the name of the subform control that contains the form may or may
not be the same as the form it contains: it all depends how you created the
subform. If you did it by dragging the one form on top of the other, the
subform container will generally have the same name as the form being used.
If you created the subform by adding a subform control to the main form and
then using the wizard, the subform control will usually be named something
like Child0.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks a lot to both pf you Arthur & Douglas. Please forgive these probably
simple questions:

Arthur, I'm not sure what you mean by an empty string. I have put in the
Debug.StringSQL and thought that if I opened the subform and tried to remove
an item it would print my SQL in a box or something on the screen however,
all I get is the same error message that I was getting before. Am I looking
in the wrong spot? This seems like something I should probably be doing all
the time when muddling my way through code!

Doug, I changed my 4 to a 3 and now the subform works on it's own (I feel
like I'm almost there!) but when I open the Main Form it asks for Parameter
Value "Forms!Near_Miss_Detail_Subform!Near_Miss_ID". What does it mean to be
adding "OR" inside the loop?

Just in case, this is the SQL for my Row Source of SelectedDirectCause

SELECT [Detail_Link_TBL].[Detail_Link_ID],
[Near_Miss_Detail_TBL].[Detail_Link_ID],
[Near_Miss_Detail_TBL].[Near_Miss_ID], [Detail_Link_TBL].[Detail]
FROM Detail_Link_TBL INNER JOIN Near_Miss_Detail_TBL ON
[Detail_Link_TBL].[Detail_Link_ID] = [Near_Miss_Detail_TBL].[Detail_Link_ID]

WHERE

(((Near_Miss_Detail_TBL.Near_Miss_ID)=[forms]![Near_Miss_Detail_Subform]![Ne
ar_Miss_ID]));

Inside your loop, you're adding " OR" to the end of each string. Afterwards,
you're taking off the last 4 characters. All " OR" adds is 3 characters.

You need to be adding " OR " inside the loop. (If you did have more than 1
item selected, you'd have got a different error due to the missing space)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, this is driving me crazy. I have copied some code generously provided
by
some expert out there to build some list boxes where the user can select
from
a list and display the selected items in another box (also filling these
selections into a junction table). There's a command button to add the
items
and another command button to remove the items. The button to remove
items
from the list gives a run-time error '3075': extra ) in query expression
'Near_Miss_ID=2 AND (Detail_Link_ID=)'.


Private Sub cmdRemoveOne_Click()
Dim strSQL As String
Dim strwhere As String
Dim db As DAO.Database
Dim varItem As Variant
For Each varItem In Me.SelectedDirectCause.ItemsSelected
strwhere = strwhere & "Detail_Link_ID=" &
Me.SelectedDirectCause.ItemData(varItem) & " OR"
Next varItem
strwhere = Left(strwhere, Len(strwhere) - 4)
strSQL = "Delete * from Near_Miss_Detail_TBL where Near_Miss_ID=" &
Me.Near_Miss_ID & " AND (" & strwhere & ");"
Set db = CurrentDb
db.Execute strSQL
Me.ListDirectCause.Requery
Me.SelectedDirectCause.Requery
Set db = Nothing
End Sub

I have tried removing some of the brackets, but I just generate other
errors
about missing syntax. Can anyone spot my error here? Thanks as always!

BTW, not sure if it matters but there is a difference in that I built this
as a subform to go in a main form which is what the Near_Miss_ID relates
to.
I have a Near_Miss_ID in both my Main Form and Subform (the current record
number is 2 which is what I'm assuming the =2 is referring to in the error
message)