Combo Box not working

[MNNovice]

Hello all:

I could not get a combo box to display a list in a Search Form. My object is to select an artist's name and have his/her CDs listed on the subform through a qry. But I couldn't do it. When I tried to type an artist's name, I get an error message telling me the name does not exist in the list. I was successful in getting data by typing a CD title but couldn't get the Combo box for the Artist's search to work. Can someone please help?

Details follow:

Table 1: tblArtists
ArtistID - Auto (PK)
ArtistName- Text

Table2: tblCDDetails
RecordID - Auto (PK)
RecordingTitle - Text
ArtistID - FK

Table 3: tblzNull
ItemID - Auto (PK)
ItemName - Text

Query 1: qryCD
RecordingID
RecordingTitle
ArtistID
ArtistName


Main Form 1: frmFIND
Text box 1 = txtCDTitle (Unbound)
Combo Box 1 = cboArtist (Unbound) but has this code under Row Source

 
SELECT 0 as ArtistID, "<ALL>" as ArtitstName FROM tblzNull UNION ALL SELECT tblArtists.ArtistID, tblArtists.ArtistName FROM tblArtists
ORDER BYArtistName;
 
 

Sub Form 1 - sfrmCD
RecordingID
RecordingTitle
ArtistName

Command Button 1: cmdFIND

 
Private Sub cmdFIND_Click()
  ' Update the record source
    Me.sfrmCD.Form.RecordSource = "SELECT * FROM qryCD " & BuildFilter
 
    ' Requery the subform
    Me.sfrmCD.Requery
End Sub
 
Private Sub Form_Load()
 
    ' Clear the search form
    cmdCLEAR_Click
 
End Sub
 
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
 
    varWhere = Null  ' Main filter
 
 
    ' Check for LIKE CD Title
    If Me.txtCDTitle > "" Then
        varWhere = varWhere & "[RecordingTitle] LIKE """ & Me.txtCDTitle & "*"" AND "
    End If
 
    ' Check for ArtistID
    If Me.cboArtist > 0 Then
        varWhere = varWhere & "[ArtistName] = " & Me.cboArtist & " AND "
    End If
 
 
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
 
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
 
    BuildFilter = varWhere
 
End Function
 
 

Sorry for the long note. But felt that details may help you understand my problem better. Many thanks.

[NeoPa]

Have a look at Example Filtering on a Form and see if you can do it after that.

If not, please come back and let us know in here. We will expect you to be familiar with what's in that thread though.

Welcome to Bytes!

[MNNovice]

I read the instructions you sent CH 1 -7 plus the article by RABBIT. Thanks.

I still need help.

My Combo Box on the main form is called cboArtist

Column Count = 2
Bound Column = 1

Control Source = Unbound
Row Source Type = Table/Query
Row Source:

SELECT 0 as ArtistID,
       "<ALL>" as ArtitstName
 
FROM tblzNull
 
UNION ALL SELECT ArtistID,
                 ArtistName
 
FROM tblArtists
 
ORDER BY ArtistName;

It does not display any Artist’s name. When I try to execute the FIND button, I get this error message:

The ORDER BY expression (ArtistName) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression.

I am not at all GOOD with SQL commands and am learning as I go. My query name is qryCD lists ArtistName to be sorted. Why do I get this message then?

So I edited the SQL to read it as

SELECT 0 as ArtistID,
       "<ALL>" as ArtitstName
 
FROM tblzNull
 
UNION ALL SELECT ArtistID,
                 ArtistName
 
FROM tblArtists;

This allows me to see the list of Artists’ Names. But when I select one name and Click on FIND button I get this weird error message which I don’t understand at all.

Run-Time Error ‘2001’:
You canceled the previous operation
END DEBUG

I chose DEBUG which takes me to the VBA page and Line 3 (below) is highlighted in yellow.

Private Sub cmdFIND_Click()
  ' Update the record source
  Me.sfrmCD.Form.RecordSource = "SELECT * FROM qryCD " & BuildFilter
 
  ' Requery the subform
  Me.sfrmCD.Requery
End Sub

PLEASE HELP. Is it possible to attach a smaller version of my Database here?

I appreciate your time. Thanks.

[NeoPa]

I took the liberty, while I was editing your post to include the [ CODE ] tags, of reformatting your SQL code too (just for visual display - not to change the effect).

You will notice more easily now that the field [ArtitstName] on line #2 of each is misspelled.

In a UNION query, the field name is ALWAYS determined by the first query, so the subsequent query that's UNIONed with it, doesn't matter - even though it is spelt correctly there.

For your code breaking (at line #3 shown) I can't help too much as most of the information is not available to me. I have no info about qryCD or BuildFilter.

What I can say though, is that rather than changing the .RecordSource, it's probably easier simply to set the .Filter itself and call a .ReQuery. The .ReQuery you have on line #6 seems wrong to me too. It is the form that needs to be requeried and not the subform control (which can contain a subform).

Me.sfrmCD.Form.Requery

As to attaching a database, it is possible, but personally I would not do it unless requested. I see it as shorthand for "I don't want to go to the effort of explaining my problem clearly. I'd prefer if you spent you time trying to work it out instead".

Some experts do request it and that's absolutely fine, but if not requested then it's better not to in my view.

[NeoPa]

To get this, simply, when you have selected DEBUG, do the following :

1. Type Ctrl-G to open and select the Immediate Pane
2. Type in ?BuildFilter
3. Copy the results displayed in with your next post

It may be clear and simple from whatever it displays, but we may need to know a bit more about [qryCD] if it's not.

[MNNovice]

Thank you for taking the time to explain things to me. I sincerely appreciate. I am happy to report that I was able to fix this problem. All I needed is to add the column number to the SQL under ArtistID. In the event someone else could benefit from it, here is the revised code:

' Check for RecordingArtistID
    If Me.cboArtist > 0 Then
        varWhere = varWhere & "[RecordingArtistName] = '" & Me.cboArtist.Column(1) & "' AND "
    End If

Many thanks for your help. I am still struggling with the problem of creating a text box to automatically populate with running numbers.

[NeoPa]

Thank you for posting your answer. That always helps.

I'm not sure from your last what, if anything, you still want help with.

If there is still something you need perhaps you could explain it again to save me trying to determine by rereading everything from scratch.

[MNNovice]

Thank you and my apologies for not being clear.

I was able to solve the problem that you tried to help. I was referring to another problem I am still struggling with.

[NeoPa]

So where is this other problem explained? If you drop in a link I will have a look.

[MNNovice]

Here is the link.

1. Please help with Running Count