Query Language (1 Viewer)

[Liberti]

How can I base a query on a keyword input by the user. I have code in the criteria field however it is unsuccessful. Here it is

Like [frmOrdinanceInfo] & "'*" & [txtKeyword] & "'*"

Is this proper?

 

[David R]

Are these two fields on a popup form, or is frmOrdinanceInfo the field and txtKeyword the search string?

For the first, in your query put:
OrdinanceField, Criteria: Forms.FormName.frmOrdinanceInfo
KeywordField, Criteria: Like "*" & [txtKeyword] & "*"

For the second:
OrdinanceField, Criteria: Like "*" & [txtKeyword] & "*"

No single quotes are needed if you're building this in the query design window.

 

[Liberti]

Thank you very much.

Now when I go from design view to form view the keyword is searched. However when I click the Search button an error message occurs.
"You can't reference a property or method for a control unless the control has the focus."

How do I set the focus, and where in the code do I place it?

Thank you very much.

 

[David R]

I'm not sure, can you post the code you're using thus far?

[This message has been edited by David R (edited 05-28-2002).]

 

[Liberti]

OK now the set focus is OK. but now it is saying object required. Do I have to define my database and field names?

THank you so much for your help
Liberti

 

[David R]

Do you want to pull all records matching the keyword, or just go to the first one?

One of my search forms has the following behind it:

Private Sub button_Search_Click()
On Error GoTo Err_button_Search_Click
    Dim boo As Boolean
    Dim searchString As String
    
    Select Case Frame_OptionGroup
    
'snipped for brevity
    Case 3
        If (Not IsNull(LookupBox_AgencyName)) Then
            searchString = "[AgencyName] Like '*" & Me.LookupBox_AgencyName & "*'"
        Else
            MsgBox ("You must enter a name to search for.")
            boo = True
        End If
'snipped for more brevity
    End Select
    
    If (Not boo) Then
        Application.Echo False
        DoCmd.Hourglass True
                DoCmd.OpenForm "Contacts", , , searchString
        DoCmd.Close acForm, "Search for Contact"
        Application.Echo True
        DoCmd.Hourglass False
    
    End If
    
Exit_button_Search_Click:
    Exit Sub

Err_button_Search_Click:
    MsgBox Err.Description
    Resume Exit_button_Search_Click
    
End Sub

This opens all possible matches. To go to the first one, I've used:

Private Sub ParticipantID_Lookup_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Dim PIL As String
    
    If (IsNull(Me.ParticipantID_Lookup)) Or (Me.ParticipantID_Lookup = "") Then Exit Sub
    
    'Include check for Len(ParticipantID) = 6
    PIL = Left(Me.ParticipantID_Lookup, 6)
        If (Len(PIL) < 6) Then
        MsgBox ("Make sure the ParticipantID has 6 characters.")
        Exit Sub
    End If
    
    Set rs = Me.Recordset.Clone
    
    rs.FindFirst "[ParticipantID] = '" & PIL & "'"
    
    If rs.Nomatch = True Then
        MsgBox "No matching record found"
    Else
      'Go to the matching record
       Me.Bookmark = rs.Bookmark
       
    End If
    
    Me.ParticipantID_Lookup.Requery
    Set rs = Nothing
End Sub

And yes, in this case you'll have to define your recordset. Hope that helps.

 

[Liberti]

I do not understand why I am getting an object required error when I click my command buttons.

Is that related to the record set?

Thank you

 

[David R]

Sorry, I think I left something out.

Because the recordset method we are using uses DAO code, not ADO 9the default standard for Access 2k and later), you'll sometimes get weird answers. The solution is to go into Tools>References from the VBA window, and check the entry for (I think) "Microsoft Jet. DAO 3.6 Engine", or something similar.

Hopefully that helps. You can also look up the ADO way to do this but it's not as well documented yet.

 

[Liberti]

The closest thing was

"Microsoft Jet & REplication objects 2.1 library"

Is this the one that needs to be checked.

Thanks,
Liberti

 

[Liberti]

I already have
"Microsoft DAO 3.6 object library"
checked

Is that the one?

 

[Liberti]

OK

I am really starting to hate this database stuff.

When I try to compile the language the VB program will not recognize ".recordset" as a command.

WHY?????
Liberti

 

[The_Doc_Man]

The problem might be that in AC2K you should use not DAO.Recordset but ADO.Recordset. And you might have enough going on inside your system that you should probably qualify all objects.

The "missing object" error has always, in my experience, been due to either

(a) a spelling error in the object name where you are manipulating properties of something or calling methods of something, or

(b) an error in the object definition that caused it to fail to be created properly, or

(c) an object name conflict that needs to be qualified (by a prefix such as ADO. or DAO.) to specify which type of object something needs to be.

 

[Liberti]

I need to print the contents of the listbox. I have created a report that works on its own when it prompts you for the parameter. When I am in the form when you click the print button it still asks for the parameter that is already entered in the textbox. How can I just print the list in the listbox and not the entire database.

Next, Is there a way that I can confirm that the user wants to print the entire database. I am wasting a lot of paper. Or that when the textbox is null do not print?




[This message has been edited by Liberti (edited 06-07-2002).]