finding records matching some or all of string 1

[Loktar]

If this question has been asked before, I'm sorry but I can't find an answer anywhere. My problem is that I'm trying to use DoCmd.OpenForm to open a new form based on a field partially or completely matching an input string.

Right now my code is as follows:

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frm_AllItems"
    stLinkCriteria = "[Item] = [Enter Item #:]"
    
    DoCmd.OpenForm stDocName, , , stLinkCriteria

This prompts the user for an item number and displays an exact hit (enter ABCD and displays ABCD only). However, I need the user to be able to input AB and have it display all items with AB in the "number" field (such as 12AB12, ABCD, AB0-3, etc.) Note that this field is a string, not a number as the name implies.

A SQL query to do this would look like

    WHERE [Item] Like "*" & [Enter Item #:] & "*"

but I'm having a hard time getting this into the VBA code.

Thanks in advance for your help.

 

[RoyVidar]

Hi!

If you relly need to, you could use an input box:

[tt]dim strCrit as String
strCrit = InputBox("Enter Item#", "Title")[/tt]

And use that in building the SQL string, but the preferred version seems to be more in the line of using a form control to input it (combo?), so that you can validate it before opening the form. And refer to that control in the string.

Roy-Vidar

 

[Loktar]

Thanks for the quick reply. I was thinking of using something like a combo box for it but then, as far as I can tell, I have the same problem as I had before of matching partial strings (AB for xAB001, xxAB002, etc.) If there's some trick I'm missing please fill me in

Thanks again,

Loktar

 

[RoyVidar]

Something like this usually works:

[tt]stLinkCriteria = "[Item] Like '*" & Me!txtBox.Value & "*'"
stLinkCriteria = "[Item] Like '*" & strMyString & "*'"[/tt]

On of the issues, might be that you must concatinate the value into the string, not the variable name.

Roy-Vidar

 

[Loktar]

That did the trick! I'm using a text box to get the search string and passing it like you said in your last post. You were right that using [Enter Item #:] wouldn't work. Thanks for the help!

Loktar