Using VBA to search based on wild cards (e.g. "*") and using the "Like" expression

[Pat]

Hi,
I am trying to do something that seems fairly easy, but I’m having some difficultly and hopefully someone can help. Here is a high level over of what I’m trying to do:

I have two tables, Table 1 named OLD and Table 2 named NEW. Table 1 is a small group of records and Table 2 has a large quantity of records. They are both very similar in their content where they both contain fields for a PART NUMBER, MANUFACTURER’S NAME, PIECE PRICE, etc..

My goal is to find all records in Table 2 whose values in the PART NUMBER field (PN) match or “closely match” the PART NUMBER (PN) value in Table 1. In most cases where the two records are actually a match, the PN data from the two tables won’t match exactly. So to find records in Table 2 that “closely match” Table I did the following using standard queries.

* I created 2 forms;
- Frm1 uses all the data from Table 1 (OLD)
- Frm2 uses all the data from Table 2 (NEW)
* First I open Frm1.
* The query behind Frm1 has the fields from Table 1, but it also has an expression named MidPN that is the mid$ of a field in the table, PN. In my example I’m using Mid$([PN],3,10). I can’t have 3 and 10 hard coded, more on that later.
* I have placed MidPN on Frm1
* While in Frm1 Form I click on a button to open up Frm2
* In Frm2's query I have a condition that looks at the mid$ expression from Frm1, MidPN, and only opens records in Frm2 that match
* The unique aspect of this is that I add wild cards to either side of the mid$ expression so it will find all "like" matches (Like "*"+[forms]![Frm1]![MidPN]+"*")
Using standard queries this works perfectly. I can scroll through Frm1, hit the "open Frm2" button and the list of matching records comes up. It works Great.


WHAT I WANT TO DO THUS WHY I NEED TO OPEN THE FORM IN VBA.

1) In Frm1 I'd like the mid$ variables to be user defined. Ideally they would set them before opening the Frm1 (say 3 characters in and length of 10). Then, as the user scrolls through Frm1 they could change these two values at any time before opening Frm2. This would allow them to either expand or narrow their search based on the mid$ values. When they went to the next record in Frm1 the values would revert to the initial setting (in this case 3 and 10). But again, if they needed to they could change before once again opening Frm2.

2) When they open Frm2 I'd like to keep Frm1 open and visible. They need both forms open because they must determine if the two records are actually a match. This is best done if they can view both sets of data. While both forms are open, if they determine there is a match, they need to be able to click on a check box in Frm2. Is it possible to tile forms horizontally and go back and forth between the two forms?

All this could be solved if there was a one to many link that could use the “like” expression, but I don’t think this is possible.



THIS IS SQL STATEMENT DIRECTLY FROM QUERY BEHIND FRM1:
SELECT Mid$([PN],1,8) AS MidPN, OLD.PN, OLD.Manufacturer, OLD.Description, OLD.Value, OLD.Designator, OLD.Quantity, OLD.Piece_Price, OLD.Vendor, OLD.Notes
FROM OLD;

THIS IS SQL STATEMENT DIRECTLY FROM QUERY BEHIND FRM2:
SELECT NEW.match, NEW.PN, NEW.Manufacturer, NEW.Description, NEW.Value, NEW.Designator, NEW.Quantity, NEW.Piece_Price, NEW.Vendor, NEW.Notes


FROM NEW
WHERE (((NEW.Value) Like "*"+[forms]![Frm1]![MidPN]+"*"));


BELOW IS MY VBA CODE WHERE I TRY TO DUPLICATE WHAT ACCURS IN THE QUERIES:
The key is the “WHERE” statement. In my example below it doesn’t try and use the “wild cards” with the mid$ statement because I couldn’t get it working. That is where I need help because I’ve tried a ton of different syntaxes, but continue to get errors.

Dim strSQL As String
'
strSQL = "SELECT New.[PN], New.[Manufacturer], New.[Value], New.[Description], New.[Designator], New.[Quantity], New.[Vendor], New.[Notes]" & _
"FROM New " & _
"WHERE New.[PN] ='" & [Forms]![Frm1]![MidPN] & "' ; "
'
Set rs = CurrentDb.OpenRecordset(strSQL)
DoCmd.OpenForm "frm2", , , , , acNormal
Forms!frm2.RecordSource = strSQL
Forms!frm2.Visible = True
'
End Sub


So, to recap my questions:
* How can I code the WHERE statement so it works like the wild card configuration in the standard query?

* How can I have the mid$ values set up as a variable so the user can change?

* Can I have both Frm1 and Frm2 open at the same time and can the user edit them?

Thanks in advance for your help. If I can get this working it will help me tremendously.

[pbaldy]

1) "WHERE New.[PN] Like '*" & [Forms]![Frm1]![MidPN] & "*'"

2) You would simply build the statement: "SELECT Mid$([PN]," & Whatever & "," & Whatever & ") AS MidPN, ..."

where Whatever was a form reference or other user input.

3) You can have 2 forms open and the ability to edit would be dependent on their record source and settings, like normal. You can probably arrange them programmatically, but I might lean towards a big form with 2 embedded subforms.

[Pat]

Hi Paul,
Thanks a ton for the help, I'm getting much closer. I've used your input to do the following:


In Frm1 I added two text boxes for the mid$ variables. I then use VBA to populate a third text box, [text22] with the mid$ value. While in Frm1 the user then clicks on a command button that runs the following code to open Frm2

Private Sub Command13_Click()
Dim strSQL As String
'
strSQL = "SELECT New.[PN], New.[Manufacturer], New.[Value], New.[Description], New.[Designator], New.[Quantity], New.[Vendor], New.[Notes]" & _
"FROM New " & _
"WHERE New.[PN] Like '*" & [Forms]![Frm1]![Text22] & "*'"
'
Set rs = CurrentDb.OpenRecordset(strSQL)
DoCmd.OpenForm "frm2", acFormDS
Forms!frm2.RecordSource = strSQL
Forms!frm2.Visible = True
'
End Sub

This all works perfectly!!!! However, I can’t edit Frm2. I haven’t figured out to open both forms in the same window. Frm1 is open, but not visible. Can you provide guidance on how to make Frm2 so I can edit the data?

On a side note, I did create a new form and inserted both forms 1 and 2, but it wasn’t working properly. Is there anything special I need to do in order for that to work properly? Thank you so much for the help.

[pbaldy]

New is a table? Did you check the Allow Edits property of the form? Can you post the db here?

[Pat]

Part Lookup EXPERIMENTAL.zip

Hi Paul,
Yes, new is a table and both the table and form allow edits. Attached is the database. Please note that this database was started for other reasons and "migrated" into this version. I only say this becaues if you look deeper into the code you will see code that isn't related to what I"m doing. I'm sure none of this old code is effecting what I'm tryng to do her. Thanks

P.S. First time I've tried to upload a file, not sure if I did it correctly.

[pbaldy]

I'm not clear on the problem. I clicked on the Open Subform button and had no problem editing the displayed record, or deleting it.

[Pat]

Hi Pual,
I can now edit Frm2. Insteading of using the SQL statement in VBA I used a standard query to generate the record set for Frm2. You gave me the insight on how to build the mid$& in Frm1 which then eliminated the requirement for VBA when opening Frm2. For future reference I wish I knew why the SQL statement didn't work. However, my big challenge is getting both forms open in the same window. As I mentioned, I created a new form and "dragged and dropped" Frm1 and Frm2 into this Master Form. They are both open, but I don't know how to update Frm2 with the correct list of records once the user selects the mid$ values for the search. Could you give me some additional things to try? Thanks again.

[pbaldy]

Changing its record source property should still result in an editable form, as long as the SQL statement results in an editable recordset. Can you post the db that fails with the subforms? Here's the syntax to refer to a subform if you need it:

Forms Refer to Form and Subform properties and controls

[Pat]

LOOKUP DATABASE OVERVIEW.docHi Paul,
It is easier to explain and illustrate. To accomplish this I have included a Word document showing what I am after.

Page 1 of the Word Document:
This is an example of how I need the two forms to look. The first record comes up, the user either adjusts the mid$ variables or stays with the default values, then clicks on the OPEN SUB FORM button and it updates Frm2 and lists any matching records. Page 1 of the Word doc shows that both forms are visible but no matching records were found in Frm2.

Page 2 of the Word Document:
This shows what happens if the user broadens the search by narrowing the mid$ values. After they hit OPEN SUB FORM, Frm2 lists three possible matches.

Page 3 of the Word Document:
The user needs Frm1 open because they are evaluating the candidate listed in Frm2 to determine if any are a match to the PN in Frm1. If one of the parts listed in Frm2 is a match the click on the check box, MATCH. This is one reason why I needed to be able and edit Frm2. As I mentioned in a previous post, using a “standard” query to create the record set for Frm2 has fixed that problem.

Page 4 of the Word Document:
This is a snapshot of the last step in the process. If the user decides one of the records in Frm2 matches the record displayed in Frm1 and they check the MATCH check box, I need a field in that record automatically populated with something to link it to the record in Table 1 that the user said was a match. The easiest method would be to add the PN from Table 1 into this LINK field in Table 2. I’d have this code run as an AFTER UPDATE event on the MATCH field, but don’t know how to pull that field value and populate the LINK field.

After the user has evaluated all the records in Table 1 they can create a report, or export data that links the matching records between the two tables. I hope this clarifies and please let me know if I need to expand on anything else. Thanks so much.