Help with calling variables from other subs (1 Viewer)

S

shawnntjr

Registered User.
Local time
Today, 01:50
Joined
Oct 28, 2014
Messages
42
Hi all, forgive me if I use any wrong terms as my VBA isn't very strong.

I have been tasked to create a multi-keyword search form, however, my form isn't working right and only the first record of the table is opened.

Code: 
Public Sub txtSearch_AfterUpdate()
    Dim strWhere As String
    Dim strWord As String
    Dim varKeywords As Variant
    Dim i As Integer
    Dim IngLen As Long
    
    'If Me.Dirty Then
        'Me.Dirty = False
    'End If
    If IsNull(Me.txtSearch) Then
        If Me.FilterOn Then
            Me.FilterOn = False
        End If
    Else
        varKeywords = Split(Me.txtSearch, " ")
        If UBound(varKeywords) >= 99 Then
            MsgBox "Too many words."
        Else
            For i = LBound(varKeywords) To UBound(varKeywords)
            strWord = Trim$(varKeywords(i))
            If strWord <> vbNullString Then
                strWhere = strWhere & "Directory.FName Like ""*" & strWord & "*"" AND "
            End If
            Next
            IngLen = Len(strWhere) - 4
            If IngLen > 0 Then
                Me.Filter = Left(strWhere, IngLen)
                Me.FilterOn = True
            Else
                Me.FilterOn = False
            End If
        End If
    End If
End Sub

Code: 
Private Sub Command2_Click()
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim filepath As String
    Dim strSearch As String
    Dim rst As DAO.Recordset
    
    If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
        MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
        Me.txtSearch.BackColor = vbYellow
        Me.txtSearch.SetFocus
    Else
        Set rst = CurrentDb.OpenRecordset("SELECT Directory.Directory FROM Directory WHERE (('" & strWhere & "'))")
        If rst.EOF Then
            MsgBox "File does not exist."
        Else
            Me.txtSearch.BackColor = vbWhite
            Set wrdApp = CreateObject("Word.Application")
            wrdApp.Visible = True
            filepath = rst![Directory]
            Set wrdDoc = wrdApp.Documents.Open(filepath)
        End If
    End If
End Sub

These are the codes that I am using for my search form. I have a feeling that I am not calling the variable from the after update portion the right way. Can anyone help me correct this?
 
B

BlueIshDan

&#9760;
Local time
Today, 05:50
Joined
May 15, 2014
Messages
1,122
Honestly you slap a chunk of code at us and say "Variable"... "problem".
What can you expect us to do with that! lol
Please elaborate on your issue and maybe highlight the variable that is being called and where.
 
S

shawnntjr

Registered User.
Local time
Today, 01:50
Joined
Oct 28, 2014
Messages
42
BlueIshDan said:
Honestly you slap a chunk of code at us and say "Variable"... "problem".
What can you expect us to do with that! lol
Please elaborate on your issue and maybe highlight the variable that is being called and where.
Click to expand...

My bad, I was in a rush yesterday.

Ok, basically, I have a column called FName, that contains file names. I am able to search these files, but the search keywords must be in the exact sequence.

E.g. I have a file called red blue yellow, so I can only search either "red blue", "blue yellow" or "red blue yellow".

I want to make it such that I am able to search in any sequence like "blue red yellow".

The variable that I used to "attempt" to do this is strWhere.
 
J

JHB

Have been here a while
Local time
Today, 10:50
Joined
Jun 17, 2012
Messages
7,732
Does all keyword have to be in it, or only a part of it?
Should a file called "Grey Black Red" be found, (when you search for "red blue yellow")?
 
S

shawnntjr

Registered User.
Local time
Today, 01:50
Joined
Oct 28, 2014
Messages
42
JHB said:
Does all keyword have to be in it, or only a part of it?
Should a file called "Grey Black Red" be found, (when you search for "red blue yellow")?
Click to expand...

Thanks for the quick reply!

Preferably all the keywords. The users of my program know the names of the file, I'm just working in the sense that there may be a chance that even though they know what file to open, but when they search for it, they might jumble up the name of it.
 
J

JHB

Have been here a while
Local time
Today, 10:50
Joined
Jun 17, 2012
Messages
7,732
Move the variable "strWhere" outside the txtSearch_AfterUpdate procedure/sub.
Code: 
    Dim strWhere As String

Public Sub txtSearch_AfterUpdate()
     Dim strWord As String
     Dim varKeywords As Variant
     Dim i As Integer
     Dim IngLen As Long
..
 
B

BlueIshDan

&#9760;
Local time
Today, 05:50
Joined
May 15, 2014
Messages
1,122
click on my find similar values link. I think it might help. I made it a long time ago, but if you need any help just comment.
 
S

shawnntjr

Registered User.
Local time
Today, 01:50
Joined
Oct 28, 2014
Messages
42
JHB said:
Move the variable "strWhere" outside the txtSearch_AfterUpdate procedure/sub.
Code: 
    Dim strWhere As String
 
Public Sub txtSearch_AfterUpdate()
     Dim strWord As String
     Dim varKeywords As Variant
     Dim i As Integer
     Dim IngLen As Long
..
Click to expand...

What do you mean by move it? Remove it completely or move it into another sub?
 
J

JHB

Have been here a while
Local time
Today, 10:50
Joined
Jun 17, 2012
Messages
7,732
shawnntjr said:
What do you mean by move it? Remove it completely or move it into another sub?
Click to expand...
Move it to the top of the module:
Code: 
Option Compare Database
Option Explicit

Dim strWhere As String

Public Sub txtSearch_AfterUpdate()
     Dim strWord As String
     Dim varKeywords As Variant
     Dim i As Integer
     Dim IngLen As Long
..
 
S

shawnntjr

Registered User.
Local time
Today, 01:50
Joined
Oct 28, 2014
Messages
42
JHB said:
Move it to the top of the module:
Code: 
Option Compare Database
Option Explicit
 
Dim strWhere As String
 
Public Sub txtSearch_AfterUpdate()
     Dim strWord As String
     Dim varKeywords As Variant
     Dim i As Integer
     Dim IngLen As Long
..
Click to expand...

Hi, I just did as you said. I am able to search the keywords in any order now, but no matter what I search only the first file of the table opens.

These are my codes now:
Code: 
Option Compare Database
Dim strWhere As String

Code: 
Private Sub Command2_Click()
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim filepath As String
    Dim strSearch As String
    Dim rst As DAO.Recordset
    
    If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
        MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
        Me.txtSearch.BackColor = vbYellow
        Me.txtSearch.SetFocus
    Else
        Set rst = CurrentDb.OpenRecordset("SELECT Directory.Directory FROM Directory WHERE (('" & strWhere & "'))")
        If rst.EOF Then
            MsgBox "File does not exist."
        Else
            Me.txtSearch.BackColor = vbWhite
            Set wrdApp = CreateObject("Word.Application")
            wrdApp.Visible = True
            filepath = rst![Directory]
            Set wrdDoc = wrdApp.Documents.Open(filepath)
        End If
    End If
End Sub

Code: 
Public Sub txtSearch_AfterUpdate()
    Dim strWord As String
    Dim varKeywords As Variant
    Dim i As Integer
    Dim IngLen As Long
    
    'If Me.Dirty Then
        'Me.Dirty = False
    'End If
    If IsNull(Me.txtSearch) Then
        If Me.FilterOn Then
            Me.FilterOn = False
        End If
    Else
        varKeywords = Split(Me.txtSearch, " ")
        If UBound(varKeywords) >= 99 Then
            MsgBox "Too many words."
        Else
            For i = LBound(varKeywords) To UBound(varKeywords)
            strWord = Trim$(varKeywords(i))
            If strWord <> vbNullString Then
                strWhere = strWhere & "Directory.FName Like ""*" & strWord & "*"" AND "
            End If
            Next
            IngLen = Len(strWhere) - 4
            If IngLen > 0 Then
                Me.Filter = Left(strWhere, IngLen)
                Me.FilterOn = True
            Else
                Me.FilterOn = False
            End If
        End If
    End If
End Sub
 
J

JHB

Have been here a while
Local time
Today, 10:50
Joined
Jun 17, 2012
Messages
7,732
You need a loop so all file returned by the recordset opens, something like below, (code not tested).
Code: 
  ...
  Me.txtSearch.BackColor = vbWhite
  Set wrdApp = CreateObject("Word.Application")
  wrdApp.Visible = True
  Do
    filepath = rst![Directory]
    Set wrdDoc = wrdApp.Documents.Open(filepath)
    rst.MoveNext
  Loop Until rst.EOF
...
 
S

shawnntjr

Registered User.
Local time
Today, 01:50
Joined
Oct 28, 2014
Messages
42
JHB said:
You need a loop so all file returned by the recordset opens, something like below, (code not tested).
Code: 
  ...
  Me.txtSearch.BackColor = vbWhite
  Set wrdApp = CreateObject("Word.Application")
  wrdApp.Visible = True
  Do
    filepath = rst![Directory]
    Set wrdDoc = wrdApp.Documents.Open(filepath)
    rst.MoveNext
  Loop Until rst.EOF
...
Click to expand...
I just tested the code, now it opens every single document. I think there may be an error with my strWhere because when I hover my mouse over it in debug mode, it shows strWhere = "" instead of what I typed into the search box
 
J

JHB

Have been here a while
Local time
Today, 10:50
Joined
Jun 17, 2012
Messages
7,732
Are you having both Private Sub Command2_Click() and txtSearch_AfterUpdate() in same module, else the variable strWhere is empty?
 
You must log in or register to reply here.

Similar threads

mike60smart
Error 2501
2
Replies
29
Views
661
mike60smart
mike60smart
P
Solved Access vba Manage an Attachment field with vba
Replies
8
Views
402
Pat Hartman
P
M
Solved If .NoMatch Then , Doesn't work
Replies
4
Views
185
Mike Krailo
Mike Krailo
C
Solved Update a record with a recordset
Replies
10
Views
232
cheekybuddha
cheekybuddha
N
Failing to access the server from VBA
Replies
5
Views
253
nector
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom