Search from Combo box

[Tezdread]

Hi, I've created a database and it's being used to store FAQ's that people can search.

The way the search currently works is like this.

I have one table with fields like subject, category, name, question and answer and one other field called 'keyword' in the keyword field I enter all the possible keywords that may relate to the Q&A.

I've a keyword query that uses a text box in the form, this then searches the keyword field and opens a new form showing all the records that use thos keywords.

I have another query that allows people to first do a search on suject and then keyword. This doesn't use a taxt box just a dialog box for the subject and another for the keyword.

What I am trying to do is have a combo box populated with the information that is contained within the suject field of the main table.

The first problem I ran into is that when I use the subject field to populte the combo box I get duplicates. I tried using Totals - Group By in the query but there are still duplicates?

Appreciate any help!

 

[dgm]

When you design your query for the combobox, right click in the top half of the query window and select properties.
Change the "Unique Values" property to YES. This is assuming you only have one field in the query (subject)?

 

[Tezdread]

Thanks for your help.

For some reason I have two fields in the query?
When I created the combo box using the wizard I only specified the subject field but the query contains the subject and the UID? When I remove the UID from the query and go back to the combo box there are no entries.

Is there another way to achieve this?

 

[dgm]

You probably have your combo box set to display 2 columns, with the first column hidden. In your combo box's properties, make sure the column count is set to 1 and column width is not 0 (can be blank).

Dave

 

[Tezdread]

That's got it sorted! Thanks dgm...

Now I just need to know how I can use this within a search capacity.

What I have so far is a text box that I can use as a search box, I enter keywords into the text box then press the cmd button, this then opens a form that runs a query and displays all the records that contain the keyword entered.

Now I also have this combo box. What I am trying to do is use the combo box in conjuntion with the text box so that a user could choose a subject from the combo box and then enter a keyword, when they press the cmd button the query runs and searches two fields within the main table.

Combo box - subject - Sci-Fi
Text box - keyword - Time Travel

This would search the subject field for Sci-Fi and then all the records that have Sci-Fi in the suject field would be searched for the word Time Travel in the keyword field.

Can anyone help me accomplish this?


(I have seen info on this in another forum, but a full solution hasn't been reached and the way it was going, extra funtionality needed to be added to each computer that needed to use this functionality...

"Microsoft 3.6 DAO Object Library" needs to be installed to get some code to be run and this would need to be done on each database that runs the search...)

 

[dgm]

One way of doing this is to use an SQL Statement to open the form with the specified records.

Your command button:

Private Sub cmdOpen_Click()
    Dim strSQL As String
    
    If cboSubject <> "" And txtKeyword <> "" Then
        strSQL = "SELECT Test.testID, Test.keyword, Test.subject FROM Test " & _
                "WHERE (((Test.subject) = '" & cboSubject & "' ) AND " & _
                "((Test.keyword) like '*" & txtKeyword & "*'))"
        DoCmd.OpenForm "test", , , , , , strSQL
    Else
        MsgBox "Enter keyword & subject"
    End If
End Sub

*Test is the name of my table

Your form's open event:

Private Sub Form_Open(Cancel As Integer)
    If OpenArgs <> "" Then
        Me.RecordSource = OpenArgs
    End If
End Sub

Alternatively, you could display the results in a listbox on your first form, and when you double click on a row in the listbox it opens up your second form displaying that particular record.

Dave