Solved Boolean values in table controlled by list box

[Smiley 3636]

Hi
I have list box attached to a table i am trying to achieve a treeview output.

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

 

[Gasman]

And 3075 is?

 

[arnelgp]

why don't you just use a Treeview control if that is what you want.

 

[Smiley 3636]

why don't you just use a Treeview control if that is what you want.

because i want stayaway from the active x controls

 

[Uncle Gizmo]

@MajP is the resident expert on treeview and I believe he has a VBA driven version which avoids activex ..

I suggest you start with this post here:-

https://www.access-programmers.co.u...ee-views-and-a-custom-class-to-assist.309753/

 

[Uncle Gizmo]

And in the thread I posted I found this comment:-

Also included is a demo of Tree Views using MS Forms instead of the Active X.

 

[MajP]

The JKP treeview is an all MS Forms developed treeview so does not have the ActiveX issues

Excel, Access: An MSForms (all VBA) treeview

jkp-ads.com: Treeview control

jkp-ads.com

Here is a demo adding to their demo.

 

[The_Doc_Man]

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.

 

[Smiley 3636]

And 3075 is?

 

[Smiley 3636]

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':

 

[The_Doc_Man]

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.

 

[Uncle Gizmo]

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

 

[Uncle Gizmo]

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.

 

[Smiley 3636]

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

 

[Smiley 3636]

Moderator Edit:- (Code Tags Added)

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.

 

[arnelgp]

here is a simple demo of what you are trying to do.
open form1 and click on the list.

 

[Smiley 3636]

Thanks every one especially the demo from arnelgp. That was simple compared to my over grown mix

 

[Gasman]

Why don't you use the QBE and then copy and amend that for VBA?

I would just guess you would want

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