473,322 Members | 1,347 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Combo Box not working

418 256MB
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT 0 as ArtistID, "<ALL>" as ArtitstName FROM tblzNull UNION ALL SELECT tblArtists.ArtistID, tblArtists.ArtistName FROM tblArtists
  3. ORDER BYArtistName;
  4.  
  5.  
Sub Form 1 - sfrmCD
RecordingID
RecordingTitle
ArtistName

Command Button 1: cmdFIND

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdFIND_Click()
  3.   ' Update the record source
  4.     Me.sfrmCD.Form.RecordSource = "SELECT * FROM qryCD " & BuildFilter
  5.  
  6.     ' Requery the subform
  7.     Me.sfrmCD.Requery
  8. End Sub
  9.  
  10. Private Sub Form_Load()
  11.  
  12.     ' Clear the search form
  13.     cmdCLEAR_Click
  14.  
  15. End Sub
  16.  
  17. Private Function BuildFilter() As Variant
  18.     Dim varWhere As Variant
  19.     Dim varItem As Variant
  20.     Dim intIndex As Integer
  21.  
  22.     varWhere = Null  ' Main filter
  23.  
  24.  
  25.     ' Check for LIKE CD Title
  26.     If Me.txtCDTitle > "" Then
  27.         varWhere = varWhere & "[RecordingTitle] LIKE """ & Me.txtCDTitle & "*"" AND "
  28.     End If
  29.  
  30.     ' Check for ArtistID
  31.     If Me.cboArtist > 0 Then
  32.         varWhere = varWhere & "[ArtistName] = " & Me.cboArtist & " AND "
  33.     End If
  34.  
  35.  
  36.     ' Check if there is a filter to return...
  37.     If IsNull(varWhere) Then
  38.         varWhere = ""
  39.     Else
  40.         varWhere = "WHERE " & varWhere
  41.  
  42.         ' strip off last "AND" in the filter
  43.         If Right(varWhere, 5) = " AND " Then
  44.             varWhere = Left(varWhere, Len(varWhere) - 5)
  45.         End If
  46.     End If
  47.  
  48.     BuildFilter = varWhere
  49.  
  50. End Function
  51.  
  52.  
Sorry for the long note. But felt that details may help you understand my problem better. Many thanks.
Aug 28 '08 #1
10 2528
NeoPa
32,556 Expert Mod 16PB
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!
Aug 28 '08 #2
MNNovice
418 256MB
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT 0 as ArtistID,
  2.        "<ALL>" as ArtitstName
  3.  
  4. FROM tblzNull
  5.  
  6. UNION ALL SELECT ArtistID,
  7.                  ArtistName
  8.  
  9. FROM tblArtists
  10.  
  11. 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
Expand|Select|Wrap|Line Numbers
  1. SELECT 0 as ArtistID,
  2.        "<ALL>" as ArtitstName
  3.  
  4. FROM tblzNull
  5.  
  6. UNION ALL SELECT ArtistID,
  7.                  ArtistName
  8.  
  9. 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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFIND_Click()
  2.   ' Update the record source
  3.   Me.sfrmCD.Form.RecordSource = "SELECT * FROM qryCD " & BuildFilter
  4.  
  5.   ' Requery the subform
  6.   Me.sfrmCD.Requery
  7. End Sub
PLEASE HELP. Is it possible to attach a smaller version of my Database here?

I appreciate your time. Thanks.
Aug 28 '08 #3
NeoPa
32,556 Expert Mod 16PB
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).
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 28 '08 #4
NeoPa
32,556 Expert Mod 16PB
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.
Aug 28 '08 #5
MNNovice
418 256MB
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:
Expand|Select|Wrap|Line Numbers
  1. ' Check for RecordingArtistID
  2.     If Me.cboArtist > 0 Then
  3.         varWhere = varWhere & "[RecordingArtistName] = '" & Me.cboArtist.Column(1) & "' AND "
  4.     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.
Aug 30 '08 #6
NeoPa
32,556 Expert Mod 16PB
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.
Aug 30 '08 #7
MNNovice
418 256MB
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.
Sep 2 '08 #8
NeoPa
32,556 Expert Mod 16PB
So where is this other problem explained? If you drop in a link I will have a look.
Sep 2 '08 #9
MNNovice
418 256MB
Here is the link.

1. Please help with Running Count
Sep 3 '08 #10
NeoPa
32,556 Expert Mod 16PB
It looks complicated, but I'll have a longer look sometime and see if I can decipher it.
Sep 3 '08 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Sean | last post by:
Greetings all, I am attempting to make a form that will filter through several tables that (I believe) have refretial integrity. I am pulling data from several tables into the form and i would...
2
by: visionstate | last post by:
Hi there, I am working on a form that uses 3 text boxes and 3 combo boxes. When any data is entered into any of these, I click a command button and this requeries a sub query in the form and...
5
by: consonanza | last post by:
I am working on a report filter form. It has 2 combo boxes (cmboSelectSubject and cmboSelectCategory) to select criteria. Selecting an entry in combo 1 restricts the options available in combo 2....
3
by: John | last post by:
AC2007 I changed my combo's row source and then the autocomplete stopped working. The combo is two columns, bound to the first. First column is primary key (ID). Second column is a...
6
by: Dave | last post by:
I want to put the information that the user selects in my combo boxes into a subform that lies on the same form as the combo boxes. Thanks for your help already, Dave
2
by: buddyr | last post by:
Hello, I am having trouble with two combo boxes working together. two tables table1 table2 table1 fields -number and name table 2 fields -code and number ...
2
by: Dev1 | last post by:
All- I'm new to this forum and i've been working with acces for about 2 days now. I have a form in which I have two combo boxes and depending on what is selected on the first dropdown the second...
7
kcdoell
by: kcdoell | last post by:
I have three tables: One for the Division location: tblDivision DivisionID = Autonumber DivisionName = Text One for the Working Region: tblWrkRegion
1
by: Tim | last post by:
I'm working on a form where a combo box gets its list of values from a table. That same table also contains some "default notes" in other fields. My task is simple, but I'm having a hard time...
4
WyvsEyeView
by: WyvsEyeView | last post by:
I am doing the very standard thing of filtering the contents of one combo box based on another combo box. I've done it many times, but always on a main form. Now I'm trying to do it on a datasheet...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.