Solved Boolean values in table controlled by list box (1 Viewer)

Smiley 3636

New member
Local time
Today, 18:31
Joined
Oct 15, 2023
Messages
8
Hi
I have list box attached to a table i am trying to achieve a treeview output.
dbtMenuSubList1.gif

What i am trying to acheive is that when i click on the list box it shows the main category with the sub caategorys in view.
for example:

OVERVIEW
CHART OF ACCOUNTS

to
OVERVIEW
- Immediate window
- Lodgement
- protential Leads
CHART OF ACCOUNTS
but keep comeing with a run time error 3075

Private Sub List3_Click()
'MS ACCESS DETERMINE THE LISTBOX ITEM CLICKED ON CLICKED EVENT
Dim rowIndex As Integer
Dim rowValue As String, iMLBid As String, isub As String
Dim rowIsSelected As Integer
Dim result As String
Dim i2F As Double, i2E As Double
' ListBox row index clicked
rowIndex = Me.List3.ListIndex

' Row value clicked
rowValue = Me.List3.Column(0)
iMLBid = Me.List3.Column(1)

i2F = iMLBid + 0.01
i2E = iMLBid + 0.99
' If row is selected return value is -1, if unselected return value 0
rowIsSelected = Me.List3.Selected(rowIndex)

If (rowIsSelected = -1) Then
result = rowValue & " selected"
'UPDATE dbtMenuSubList1 SET dbtMenuSubList1.CatSublist = True
'WHERE (((dbtMenuSubList1.MLBID) Between 1.01 And 1.99));
CurrentDb.Execute " UPDATE [dbtMenuSubList1] SET [dbtMenuSubList1].[CatSublist] = False " _
& " FROM [dbtMenuSubList1] WHERE ((([dbtMenuSubList1].[MLBID]) Between [iMLBid] >=i2F-1 and [iMLBid]<= i2E));"

Else
result = rowValue & " unselected"
'the selected boolean boxes on the table are true otherwise false
CurrentDb.Execute " UPDATE [dbtMenuSubList1] SET [dbtMenuSubList1].[CatSublist] = -1 " _
& " FROM [dbtMenuSubList1] WHERE ((([dbtMenuSubList1].[MLBID]) Between 'i2F' and 'i2E'));"

End If


End Sub
 
why don't you just use a Treeview control if that is what you want.
 
Error 3075 is "Syntax Error (Missing Operator)" and it occurs when you have two items that COULD be literal text OR variable names with a space or a line break between them. I'm GUESSING that it might be a field name with a space in the name, a case that should be surrounded by brackets [] - but was not.
 
I changed the sql statement and it works by the debug. Print but still has a syntax error

Private Sub List3_Click()
'MS ACCESS DETERMINE THE LISTBOX ITEM CLICKED ON CLICKED EVENT
Dim rowIndex As Integer
Dim rowValue As String, iMLBid As String, isub As String
Dim rowIsSelected As Integer
Dim iMenuSQl As String
Dim i2F, i2E
' ListBox row index clicked
rowIndex = Me.List3.ListIndex

' Row value clicked
rowValue = Me.List3.Column(0)
iMLBid = Me.List3.Column(1)

i2F = iMLBid + 0.01
i2E = iMLBid + 0.99
' If row is selected return value is -1, if unselected return value 0
rowIsSelected = Me.List3.Selected(rowIndex)

If (rowIsSelected = -1) Then
iMenuSQl = "UPDATE dbtMenuSubList1" & vbCrLf _
& "SET dbtMenuSubList1.CatSublist = 0" & vbCrLf _
& "FROM dbtMenuSubList1" & vbCrLf _
& "WHERE (((dbtMenuSubList1.MLBID) Between " & i2F _
& " AND " & i2E & "))"

Else
iMenuSQl = "UPDATE dbtMenuSubList1" & vbCrLf _
& "SET dbtMenuSubList1.CatSublist = -1" & vbCrLf _
& "FROM dbtMenuSubList1" & vbCrLf _
& "WHERE (((dbtMenuSubList1.MLBID) Between " & i2F _
& " AND " & i2E & "))"
End If
Debug.Print iMenuSQl
DoCmd.RunSQL iMenuSQl
End Sub

the error statement is as follows;
Run-time error '3075:

Syntax error (missing operator) in query expression '-1
FROM dbtMenuSubList1':
 
Code:
iMenuSQl = "UPDATE dbtMenuSubList1" & vbCrLf _
                   & "SET dbtMenuSubList1.CatSublist = 0" & vbCrLf _
                   & "FROM dbtMenuSubList1" & vbCrLf _
                   & "WHERE (((dbtMenuSubList1.MLBID) Between " & i2F _
                   & " AND " & i2E & "))"

First, don't insert vbCrLf into an SQL string. SQL is not something you format for display. It is something you use for computation, but the vbCrLf is a style of display punctuation. All that will do is confuse SQL.

Second, once you drop the carriage control stuff, you have concatenated the text of your query across multiple lines but you have no spaces at the end or the beginning of the line segments that you are adding to build the query. I don't see exactly what causes the 3075 error but having the wrong number of (in this case, insufficient) spaces or having extraneous elements in a line usually crop up as syntax errors.
 
Seeing as you are placing a copy of the generated SQL statement in the immediate window at this line:- Debug.Print iMenuSQl

You could easily open the immediate window with Ctrl G, copy and paste the SQL statement here. It is often easier to see the errors this way....
 
iMenuSQl = "UPDATE dbtMenuSubList1" & vbCrLf _ & "SET dbtMenuSubList1.CatSublist = 0" & vbCrLf _ & "FROM dbtMenuSubList1" & vbCrLf _ & "WHERE (((dbtMenuSubList1.MLBID) Between " & i2F _ & " AND " & i2E & "))"

Second point , I draw your attention to Richards post here:-

See how the code is nicely formatted and presented in the forum thread.

It's much easier to read than the mono block of code you have in your threads.

The Software has a feature where if you surround a section of text with code tags it is displayed in a more easily read format.
 
I did but unfortunately the do command fails
the debug print out put is as follows
UPDATE [dbtMenuSubList1] SET [dbtMenuSubList1].[CatSublist] = True FROM [dbtMenuSubList1] WHERE [dbtMenuSubList1].[MLBID] Between 1.01 AND 1.99
 
Moderator Edit:- (Code Tags Added)
Code:
iMenuSQl = " UPDATE [dbtMenuSubList1] " & _
                     " SET [dbtMenuSubList1].[CatSublist] = True " & _
                     " FROM [dbtMenuSubList1] " & _
                     " WHERE [dbtMenuSubList1].[MLBID] Between " & i2F & _
                     " AND " & i2E & ""
    End If
Debug.Print iMenuSQl
DoCmd.RunSQL iMenuSQl

Now that looks more professional. .

the debug print code seems fine until its executed. Sorry my lack understanding, that a main reason for understanding SQL VBA. And yes i configured the SQL in the Queries module and it worked.
 
Last edited by a moderator:
here is a simple demo of what you are trying to do.
open form1 and click on the list.
 

Attachments

Thanks every one especially the demo from arnelgp. That was simple compared to my over grown mix
 
Why don't you use the QBE and then copy and amend that for VBA?

I would just guess you would want

Code:
UPDATE [dbtMenuSubList1] SET [dbtMenuSubList1].[CatSublist] = True WHERE [dbtMenuSubList1].[MLBID] Between 1.01 AND 1.99
 

Users who are viewing this thread

Back
Top Bottom