Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo won't work with numbers (2KSr1)

    I have inserted a combo box to find a recordID by typing in a Number. The ID field is zero length, the second column is a number which the user types in. When I make this field a "text" field in the underlying table, the combo box works, but when I make it a number field (which is what I want), the combo box gives me the error "the text you entered isn't an item in the list". Why is it interpreting the input as if it should be text and how can I get around this?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Combo won't work with numbers (2KSr1)

    I'm sorry, but what you wrote doesn't make sense. To illustrate how a combo box is typically used in a situation you seemed to be describing, let's take a customer table as an example. The RowSource for the combobox is an SQL statement, something like this: "SELECT CustID, CustomerName FROM Customers ORDER BY CustomerName". You set the ColumnWidths of the combobox to something like: 0";2" (so user never sees CustID column, and types the name being sought).

    If you want to have the user keyin a number, then make the 1st column width > 0". And use an ORDER BY of CustID. The user keys in a number, and the combobox dropdown list displays the number and name.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo won't work with numbers (2KSr1)

    My appologies for not being clear. Consider a form intended to look up envelope numbers for a church congregation:
    field1 = [memberid]
    field2= [envelopenumber]
    These two fields are in a combo box intended to find the memberid when the user types in the envelope number. The problem is that the combo box doesn't work when the envelope number is a numeric field.

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo won't work with numbers (2KSr1)

    Is this Lookup based on a table or with the details typed into its Row Scource? I belive that data listed in the row scource is treated as text :-)

    Peter

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Combo won't work with numbers (2KSr1)

    Did you use the combo box wizard to build the combo box? And does it apply a filter to a form so that you see only the data associated with the member id? I think the trick may be to make the row source for the combo box a query which converts the numeric field envelopenumber to a string. Combo boxes do work using a string to find the record you are looking for. I don't know that I've ever tried to do exactly what you are attempting, but I'm not totally surprised at how it's behaving. So try the String function that converts a number to a string for display purposes and see if that solves your problem.
    Wendell

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo won't work with numbers (2KSr1)

    Ive tried it as based on a query and typed into its row scource.

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo won't work with numbers (2KSr1)

    I did use the wizard. No filter. Using a query as the row source makes no difference. Following is the query:
    SELECT Members.MemberID, Members.Envelope, Members.FirstName, Members.LastName
    FROM Members
    ORDER BY Members.Envelope;

    and the combo box:
    Private Sub Combo23_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[MemberID] = " & str(Me![Combo23])
    Me.Bookmark = rs.Bookmark

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Combo won't work with numbers (2KSr1)

    I tested this using an autonumber (primary key) and numeric field as 1st two columns of combo and it worked fine, using same generic code created by wizard. Did not have to convert any numeric values to text. The first column of combo (Autonumber) is set to 0". Not sure what is causing your problem, but would recommend modifying code created by wizard to something like: <pre> Private Sub Combo8_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As DAO.Recordset
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Me![Combo8]
    Me.Bookmark = rs.Bookmark
    rs.Close
    Set rs = Nothing
    End Sub
    </pre>

    Using "Dim rs as Object" is real slow, even on a small test table. And was not necessary to convert combo value with STR function. This is obvious, but are you sure the form's recordsource includes the MemberID field?

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Combo won't work with numbers (2KSr1)

    This is not any different from what I was describing. I assume you set the columnwidth to something like 0";1", so only the envelope# is displayed in the combo dropdown?

    There is no reason that this shouldn't work, with either or both of the fields being numeric. You say it doesn't work, but what do you mean by that? I'd have to see the code you are using to find the record once it has been selected by the user.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo won't work with numbers (2KSr1)

    I found the same thing when I tried it on a different database, It worked. What was different was that there was one record in this table that contained a null value. Therefore, the problem is now making it work when certain records will contain a null value in the [envelopenumber] field. I suppose one way would be to just have zero inserted in the default field but why will the code not overlook the null field?

  11. #11
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo won't work with numbers (2KSr1)

    Columnwidth is set as yous seay: 0,1. By doesn't work I mean as in original post: I get the error message "The text you entered isn't an item in the list". It does look up the value when using the mouse and clicking an item, but if you type a number and hit enter, you get the error message.

    There is now a new twist involving null value. I only get the error message when one or more records contain a null value in the [envelopenumber] field.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo won't work with numbers (2KSr1)

    You can't compare anything to a Null, so if you're tyring to do a find, a null field has to be ignored unless you use a function like IsNull rather than "=".

    You could add the Nz function to your code but it isn't clear to me whether the Null would come up in your combobox (where it would wind up as an empty string) or your recordset. Assuming it's in the recordset, try this:

    rs.FindFirst "Nz([MemberID],0) = " & Me![Combo8]

    I'm not sure where [envelopenumber] comes into this, since you didn't even mention that field until your latest post. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Combo won't work with numbers (2KSr1)

    Without seeing the SQL statement that is being used by the rowsource, I can't really help you any more. Is there an input mask defined, perhaps? If so, delete it. Like I said before, there are no inherent problems with using numbers; so you must be doing something, somewhere that is causing this problem.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  14. #14
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Combo won't work with numbers (2KSr1)

    I tried adding dummy records with null values in the numeric field and combo still worked OK. The only way I was able to duplicate error was by applying a number format to the numeric field in the underlying table, which resulted in Not in List error when typing number in combobox rather than selecting with mouse. As Mark L suggested, get rid of any input masks that may be defined, and also any formatting applied to this number field in table, query, or form. (I assume the Envelope No is a simple integer or long integer & requires no special formatting.)

  15. #15
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo won't work with numbers (2KSr1)

    Actually I did mention [envelopenumber]. See 23-Feb-02 04:08. Inserting the Nz function into the combo event won't do anything since that particular field is an "autonumber" field and never null. But it did inspire me to look elsewhere. As it turns out, the problem arises in the query where there is a sort on the [envelopenumber] field and of course it can't sort a null field. Creating a [EnvelopeNumber] field in the query with the Nz function and sorting on this solved the problem. Thanks. Once again, thanks to all who responded.

    new envelope number field: EnvelopeNum: CInt(Nz([EnvelopeNumber]))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •