Continuous Form Text Box Partial Dloookup?

[misscrf]

I have a continuous form that has (for translation purposes) records in a given project. The table just has record id's which have a 6 digit prefix of their project. What I need to do is allow users to filter the form to just the records for a given project. The difficulty is that we have a view which contains the project ID, and a related project ID. See we have 2 project ID's for each project. A user will want to filter the form by either one. The view has a string which contains both project ID's and a short name for the project e.g. [projectID1]ProjectNameABC([projectID2]) or 123456CocaCola(987654). Here the user might want to search 123456 or 987654. Either way, the view can pull up all records based on that view field. That view contains a 2nd column that has the record prefix.

Here is what I have been working on, but I think I am doing it wrong:

Private Sub cmdFilter_Click()
Dim strFilter As String
 

If Me.txtProjectFilter = Null Then
    MsgBox "Please enter a Project name", vbOKOnly, "No Filter To Apply"
Else
    strFilter = " * & Me.txtProjectFilter & * Like '*" & DLookup("[vw_Projectgroups.MyProjectKeyName]", "vw_Projectgroups") & "*'"
    'strFilter = "*& DLookup(""[vw_Projectgroups.MyProjectKeyName]"", ""vw_Projectgroups"")& * Like '*" & Me!txtProjectFilter & "*'"
    Me.Filter = strFilter
    Me.FilterOn = True
    Me.txtLblProjectKey = DLookup("[vw_Projectgroups.MyProjectKeyName]", "vw_Projectgroups", strFilter)
    Me.Form.Requery
End If
End Sub

If anyone has had to do this before and/or can help, I would appreciate it.

Thank you.



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

Something like this ?

If Trim(Me!txtProjectFilter & "") = "" Then
    MsgBox "Please enter a Project name", vbOKOnly, "No Filter To Apply"
Else
    strFilter = "MyProjectKeyName Like '*" & Me!txtProjectFilter & "*'"
    Me.Filter = strFilter
    Me.FilterOn = True
    Me.Requery
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[misscrf]

Thank you for your reply, PHV. The issue is that the text box would let the person type in a value. That value needs to be looked up to a SQL view. Once it finds a value in 1 column of the view, it returns a 2nd column of that view. The view, itself, is a union which gives us both possible project IDS in the 1 column.

For the record id, only 1 of the project ID's is used for the prefix of each record in the case, so that is in the 2nd column. I may sound a little convoluted, but that is why I have a union with a key containing both project id's and a second column holding the id that is used for the records in that project.

Does that make sense?

misscrf

It is never too late to become what you could have been ~ George Eliot