dLookup Problems 1

[access345]

I have created a form to lookup components in a table. The problem I am having is the table I have has multiple designation in the same field. Ex CR2,CR4,CR18
If I am looking up a field that has one designation in it I am fine. But if the field has more than one designation in it my Dlookup returns no information. Some of these fields have more than 50 designations in them. So I am trying to avoid creating new individual records. I will if I have to thou.

 

[rjoubert]

Change the WHERE clause in your DLOOKUP function to use [FieldName] LIKE "*CR2*

 

[access345]

Private Sub txtReference_Designator_1_Exit(Cancel As Integer)
Dim X, Y As String
On Error GoTo ERR_txtRef_Desig_1
Forms![frmFailureUpdate]![txtPart_Number_1] = DLookup("LIKE *[COMPANY_PN]*", "Tbl" & [lbCode_Name] & "PartsList", "[REF_DESIG] = Forms![frmFailureUpdate]!txtReference_Designator_1")
Exit Sub
ERR_txtRef_Desig_1:
MsgBox "Information not available. Contact Tom Donahue"
Exit Sub
End Sub

I added your suggestion into my code (above) now nothing works.

 

[rjoubert]

Can you go back to your original DLookup code? The Like parameter of your DLookup is the third parameter...you changed the first parameter.

 

[rjoubert]

http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[rjoubert]

The field [REF_DESIG] is where CR2, CR4, CR18 codes are stored, correct? And the Forms![frmFailureUpdate]!txtReference_Designator_1 field is where the user enters a code to search for, right? If both are correct, you want to change the LAST parameter of DLookup call "[REF_DESIG] LIKE '*" & Forms![frmFailureUpdate]!txtReference_Designator_1 & "*'

 

[access345]

Your right I picked the wrong place to put the like statement! You are also right that the ref desig field is where th CR1 .. etc is stored.

This is my current code:

Forms![frmFailureUpdate]![txtPart_Number_1] = DLookup("[COMPANY_PN]", "Tbl" & [lbCode_Name] & "PartsList", "[REF_DESIG]LIKE '*"" = Forms![frmFailureUpdate]!txtReference_Designator_1")
Exit Sub

I had to add the second " mark after the Like '* or I got a compile error.


Original code:
Forms![frmFailureUpdate]![txtPart_Number_1] = DLookup("[COMPANY_PN]", "Tbl" & [lbCode_Name] & "PartsList", "[REF_DESIG] = Forms![frmFailureUpdate]!txtReference_Designator_1")
Exit Sub

 

[rjoubert]

Forms![frmFailureUpdate]![txtPart_Number_1] = DLookup("[COMPANY_PN]", "Tbl" & [lbCode_Name] & "PartsList", "[REF_DESIG] LIKE '*" & Forms![frmFailureUpdate]!txtReference_Designator_1 & "*'")

 

[rjoubert]

Just copy and paste the line of code I just posted...it may be tough to distinguish the single and double quotes.

 

[access345]

Thanks ! Your code works great. I tried 5 or 6 different parts and they all worked great!