Building a VBA Global Keyword Search Box.

[DigitalAdrenaline]

Sorry Ajax, I should have said Combobox not texbox. I think your second sentence is correct, that it's searching the ID column (1), rather than column 2 where the actual AssetName is. To fix this, should I direct the VBA to read:

Me.Filter = "AssetNameFK.Column(2) LIKE '*"& Me.txtSearch &"*'"

Actually, I just ran this and it threw up an error:

[DigitalAdrenaline]

blank. unable to delete.

[CJ_London]

no, would be a lot easier if you made your textbox a combo box, same as assetnamefk but unbound rather than bound

[DigitalAdrenaline]

converted to combobox. Still no joy. Same error.

[CJ_London]

you took the .column off?

[DigitalAdrenaline]

No, I left it in.

[CJ_London]

you need to take it off

[DigitalAdrenaline]

Hi Ajax, I wasn't sure if you meant the .Column and leave (2), or both. I still got an error.

[CJ_London]

sigh - remove the (2) as well

[DigitalAdrenaline]

Sorry Ajax, I really appreciate yours and other's help. I will find this answer soon enough. Ok. Let me start from the very beginning and map out the pieces, then we'll start writing code.

What I Have:

tblTradeData 'Record Source to frmTradeEntry
frmTradeEntry
ComboBox - AssetNameFK 'displaying column2 text. column1 is hidden which has the unique ID from its table tblAssetName.
Text Box- txtSearch 'in form header
Button - btnSearch 'in form header

Ok. If someone can guide me through writing this VBA, I'll see if this can be nailed. The opening code is below. What should be written first so I can understand why. Thanks anyone who might assist.



Private Sub btnSearch_Click()






End Sub

[CJ_London]

so, for the purposes of this thread, you have two tables - tblTradeData and tblAssetName which have a relationship between AssetNameFK in tblTradeData and AssetNamePK? in tblAssetName. Both the PK and FK are Long number types.

Please clarify what the search is supposed to achieve. Using Like implies you expect to potentially return multiple records. Your form is a single form (per post #14) so you want the user to move from one record to another until they find the one they want - perhaps based on some factor other than a partial match on a name. Is this correct? or is it something else?

[DigitalAdrenaline]

Yes, you're exactly correct with both sentences.

in tblAssetName PK is number - Long Integer, as is FK in tblTradeData.

I would like to create search filter box in the form header where I can type a string of letters which will dynamically search the ComboBox AssetNameFK (in fact, the whole form) and filter the results, exactly as I can with the default Access search window per the screenshot below. But I would like a larger version of this in the form header. Then I can just scroll down through each record at the results, as it does now using the default search.



It kills me because I saw a video a few years ago achieving exactly this, and for the life of me, can't find it anywhere.

[orange]

You may find this video by Steve Bishop on Keyword search useful.

[DigitalAdrenaline]

Orange, I'm actually a fan of Steve Bishop and originally had watched his videos to get into Access. I was also just now seeing if he had an email address to possibly ask him. I can't remember if the original video I saw was one of his or another person creating a hotel management program that I remember. I've seen a couple of Steve's keyword search tutes, but wasn't the one I remember. I've watched the one in your link where he creates a Query linked to an embedded form. If all else failed, i was going to try that path but preferred the original video that's lost in memory.

[CJ_London]

in that case your approach needs to be completely different. The actions required are:

1. search tblAssetName for a list of partial matches on name
2. note all the different related PK values
3. apply these values as a filter against the FK in your form

Private Sub btnSearch_Click()
dim rst as dao.recordset
dim db as dao.database
dim fltrStr as string

'1. search tblAssetName for a list of partial matches on name
set db=currentdb
set rst=db.openrecordset("SELECT AssetNamePK FROM tblAssetName WHERE AssetName LIKE '*" & txtSearch & "*'")

'2. note all the different related PK values
fltrstr=""
while not rst.eof
    fltrStr=fltrStr & "," & rst!AssetNamePK
    rst.movenext
wend
rst.close
set rst=nothing

'3. apply these values as a filter against the FK in your form
if fltrStr<>"" then 
    me.filter="AssetNameFK IN (" & mid(fltrStr,2) & ")"
    me.filteron=true
end if

set db=nothing

End Sub