CODEWITHSUNDEEP
ms-accessvbams-access-2007
Abah7
Abah7

Reputation: 23

Searching For a file name inside a table based on the list double click event

Hello Fellow programmers, I am absolutely desperate as I can not figure out how to solve (maybe) simple problem. I have two tables. First one [Files] with two fields: [FName](file name) and [FPath](file path) and second one [Reports] with [DocNo] [Title]...blah blah...

FName string consists of [DocNo] [Title](but the whole title string is not as a file path)

Example: [DocNo] Smith/RT/2000/001
[Title] Assessment of modified aluminothermic welds in 68kg/m head hardened rail for BHP Iron Ore Pty Ltd

[FName] SmithRT2000001 Assessment of modified aluminothermic welds .pdf

I have a form which has a search list on it. this list brings up records which are in [Reports]. By double clicking on a specific record, it fires up doubleclick event. in the Event I get the value of DocNo and Title and search into Files table for the Fname to match. But surprisingly it doesn't return anything when I put the sql search or even in the design mode for query?
BUT the funny thing is that when I hard code to find the record, both of ways will find it. how is that?

Here is the VBA to check out:

Private Sub SearchResults_DblClick(Cancel As Integer)

'Initializing the string variables
Dim strSQL As String
Dim strFileName As String
Dim strTitle As String
Dim DocumentNo As String
Dim titleLeng As Integer

DocumentNo = Me.SearchResults.Column(0)
DocumentNo = Replace(DocumentNo, "/", "")
strTitle = Me.SearchResults.Column(1)
Debug.Print (DocumentNo)

SrchText = DocumentNo

SearchResults.RowSourceType = "Table/Query"
SearchResults.RowSource = "QRY_OpenFile"

Debug.Print (strTitle)

strTitle = Left(strTitle, 10)
SrchText = strTitle

Debug.Print (SrchText)
SearchResults.RowSource = "QRY_OpenFile"

Dim rst As Recordset
Dim db As DAO.Database
Set db = CurrentDb()
strSQL = "SELECT Files.FName FROM Files WHERE Files.FName Like " * " & strTitle & " * ";"
Debug.Print (strSQL)
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

Application.FollowHyperlink strFileName, , True, False, , , "John Smith"

I have tried every variation in SQL string, changing outer " " to ' ' does not work. But if I change strTitle with "Assessment" string or "SmithRT2000001" it will finds it. DO not know why?

This does not work in the query design window where you put criteria: Like "* & Forms![Search For Reports]![SrchText] & *" But as soon as I change it something static it will work. Going crazy!!

Can you guide me as what to do or how to achieve my goal which is opening the file in FILE table??

Upvotes: 0

Views: 311

Answers (2)

Abah7
Abah7

Reputation: 23

Okay, After testing 3 different approaches at the end one of them gave a good response and what I wanted. I changed the "like" command in my query to:
Like "*" & [Forms]![Search For Reports]![SrchText] & "*"
and suddenly it worked. Also I found out that SQL Select query doesn't work from VBA specially with Double click event. Here is the final code:

Private Sub SearchResults_DblClick(Cancel As Integer) 'Initializing the string variables Dim strTitle As String Dim DocumentNo As String

DocumentNo = Me.SearchResults.Column(0)
DocumentNo = Replace(DocumentNo, "/", "")
strTitle = Me.SearchResults.Column(1)
strTitle = Replace(strTitle, "'", "''")
SrchText.Value = DocumentNo

SearchResults.RowSourceType = "Table/Query"
SearchResults.RowSource = "QRY_OpenFile"

End Sub

I could not get the path and name from the list, to put them together and fire up a hyperlink to Acrobat...What I had to do was sending an event via a button to get the values from the list. For some reason after SearchResults.RowSource = "QRY_OpenFile" the list.Column(index) was returning null.

Anyway thanks for reading my question and thinking about it.

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91376

Did that code actually run? You have the SQL string in a tangle:

''You need to watch out for quotes in the string, so
strTitle = Replace(strtile, "'", "''")
strSQL = "SELECT Files.FName FROM Files WHERE Files.FName Like '*" _
       & strTitle & "*';"

The point of this line:

Debug.Print (strSQL)

Is to get an SQL string to test in the query design window. Use it.

Upvotes: 0

Related Questions