Good Morning,
I am new to VBA and having an issue with a macro which a form. I have added a range button that will accept Two(2) values entered by a user in a and then access data from the database then add it to a Filter List. Currently working on adding functionality for the "Add Range" button. That will take the values entered in "Range1.Text" & "Range2.Text" and find all the item numbers between them, then Add them to the "Filters Added" textbox, was working on a loop,will need to add a connection to database as well. I am not sure if I am not setting up connection to the database properly as nothing is being printed in the Filters Added textbox.
Please Let me know if you have any suggestions on how to approach this issue. I have attached some snippets of the Add RangeButton Sub below:
Private Sub AddRangeButton_Click()
'Use Range1.Text & Range2.Text, add items to AddFilter list
If btnIgnoreList.Caption = "Add Range" Then
If Trim(Range1.Text) <> "" And Trim(Range2.Text) <> "" Then
Set Connection = New ADODB.Connection
Set Recordset = New ADODB.Recordset
Dim doesExist As Boolean
'query = "INSERT INTO [dbo].[ignore_list]([itemno]) VALUES ('" & ilItemno & "')"
query = "SELECT ITEMNO FROM [SHLDAT].[dbo].[ICITEM] WHERE ITEMNO BETWEEN " & Range1.Value & "AND" & Range2.Value & " "
Connection.connectionString = connectionString
Connection.ConnectionTimeout = 10
Connection.Open
Recordset.Open query, Connection
Recordset.ActiveConnection = Nothing
Connection.Close
'Call getItemList
lbFilter.AddItem Range2.Value
' addToIgnoreList (txtIgnoreList.Text)
End If
End If
'SELECT ITEMNO FROM [SHLDAT].[dbo].[ICITEM] WHERE ITEMNO BETWEEN " & Range1.Value & "AND" & Range2.Value & "ORDER BY ITEMNO DESC
' Dim dbs As DAO.Database
' Dim rst As DAO.Recordset
' Dim rstFiltered As DAO.Recordset
' Dim TempItemNo As String
' Dim Connection As ADODB.Connection
' Dim Recordset As ADODB.Recordset
' Dim query As String
'Dim Temp As Variant
' Set Connection = New ADODB.Connection
' Connection.Open
' Set Recordset = New ADODB.Recordset
' Set dbs = New DAO.Database
'
' Set dbs = CurrentDb
'
'
' Set rest = dbs.OpenRecordset("SELECT ITEMNO FROM [SHLDAT].[dbo].[ICITEM] WHERE ITEMNO BETWEEN " & Range1.Value & "AND" & Range2.Value & "ORDER BY ITEMNO DESC")
'
' Do While Not rst.EOF
' TempItemNo = rst!itemno
'
' rst.filter = "ItemNo:'" & TempItemNo & " '"
' Set rstFiltered = rst.OpenRecordset
'
'
' Do While Not rstFiltered.EOF
' rstFiltered.Edit
' rstFiltered!ToBeVisited = True
' rstFiltered.Update
' rstFiltered.MoveNext
' Loop
'
' Exit Do
' rst.MoveNext
'
'
' Loop
'
' rstFiltered.Close
' rst.Close
'
' Set rstFiltered = Nothing
' Set rst = Nothing
' query = "SELECT itemno FROM [SHLDAT].[dbo].[ICITEM] WHERE itemno BETWEEN 'Range1.Value' AND 'Range2.Value'"
' Connection.connectionString = connectionString
' Connection.ConnectionTimeout = 10
' Connection.Open
' Recordset.Open query, Connection
' If AddRangeButton.Caption = "Add Range" Then
'
' If Trim(Range1.Text) <> "" And Trim(Range2.Text) <> "" Then
'
'
'
' Recordset.Open (query)
'DoCmd.OpenQuery "query"
'Set Recordset = Connect.Execute(query)
' For i = 0 To lbFilter.ListCount - 1
'
' Temp = Trim(Range1.Value)
'
'
'
'
' If Trim(Range1.Value) >= Temp And Trim(Range2.Value) < Temp Then
'
'
' lbFilter.List(i) = Temp
' If lbFilter.List(i) = Trim(Range1.Value) Or Trim(Range2.Value) Then
'
' GoTo DoNotAddDuplicates
'
'
' End If
' lbFilter.AddItem Recordset
'End If
'Next i
'lbFilter.AddItem Temp
'DoNotAddDuplicates:
' Range1.Text = "" Or Range2.Text = ""
' End If
'
' End If
'
'
' Recordset.Close
' Recordset.ActiveConnection = Nothing
' Connection.Close