Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select spaces from a combo box (Access 2003 SP2)

    I have a combo box to select codes, one of which is a string of 6 spaces. Although listed in the dropdown list I cannot actually select the 6 space entry. Has anyone come across this before?

    Regards
    Graham

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select spaces from a combo box (Access 2003 SP2)

    What is the row source of the combo box? A table/query/SQL statement or a manually typed list of entries?

  3. #3
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select spaces from a combo box (Access 2003 SP2)

    Hi Hans

    The row source is a query.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select spaces from a combo box (Access 2003 SP2)

    In that case, how did you get the spaces into it? Normally, Access will clear any trailing spaces in a field, so if you enter 6 spaces, the field will end up blank.

  5. #5
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select spaces from a combo box (Access 2003 SP2)

    Hans

    The query is based on a linked table from an AS/400 through ODBC. One of the (valid) entries in the AS/400 table is a field of 6 spaces. The combo box row source query just sorts the fields. I can see the entry in the dropdown, but when I select it Access put nothing into the text box part of the combo.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select spaces from a combo box (Access 2003 SP2)

    This is related to the way Access works; i already mentioned it in my previous reply. Access automatically removes all trailing spaces from text values, so if you have an entry consisting of 6 spaces only, nothing remains.
    A workaround is as follows:
    Insert a calculated column in the query in front of the column you now use, like this:

    T: IIf([FieldName]=Space(6),"6 Spaces",[FieldName])

    where FieldName is the name of the field.
    Increase the Column Count by 1.
    Set the Column Widths to something like 0",1".

  7. #7
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select spaces from a combo box (Access 2003 SP2)

    Hans
    This works fine for getting the combo box working, but I'm using the combo box on a form header which is part of the foreign key to a sub form. In records I enter on the sub form the field linked from the combo box contains "6 Spaces" instead of a string of 6 spaces. Is there any way around this?

    Thanks
    Graham

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select spaces from a combo box (Access 2003 SP2)

    I'm not sure whether it'll work, but try setting the Bound Column property of the combo box to 2 instead of 1. This means that the value of the combo box will be the value from the second column (the displayed value).

  9. #9
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select spaces from a combo box (Access 2003 SP2)

    That doesn't work. Access strips the spaces from the combo and I'm left with a null value again.

    Graham

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select spaces from a combo box (Access 2003 SP2)

    I was afraid of that. I fear that Access can't handle a string of spaces as a key. The only workaround I can think of is to replace the six spaces with a more sensible value in the original data (AS/400), if possible. If that is out of the question, I'm out of ideas.

  11. #11
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select spaces from a combo box (Access 2003 SP2)

    Unfortunately I'm stuck with the spaces as the key. One thing I've just tried is to link the sub form to a text box whose control source is combo.column(1). However, when I start editing the sub form record I get "The Microsoft Jet database engine cannot find a record in the table 'tParts' with key matching field(s) 'WMPNUM' error twice before letting me supply a value for the WMPNUM field. After that the record updates fine. Do you know what is causing this?

    Graham

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select spaces from a combo box (Access 2003 SP2)

    Sorry, not without seeing the database, but that is obviously not possible since it is linked to AS/400.

  13. #13
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select spaces from a combo box (Access 2003 SP2)

    I've made a cut down version with the AS/400 tables imported into Access. It appears that when the sub form is linked to the text box instead of the combo box the after update event is fired every time a field in the sub form is changed. I would greatly appreciate you looking at this.

    Thanks
    Graham

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select spaces from a combo box (Access 2003 SP2)

    I will look at it later today.

  15. #15
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select spaces from a combo box (Access 2003 SP2)

    Hans

    I've found a work around. Instead of referencing the combo box column in the text box control source I set the text box value from the after update event of the combo. Thanks for your time.

    Cheers
    Graham

Posting Permissions

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