Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Front End/Back End Data Sequencing (Access 2000)

    Seemingly simple scenario.
    Data Tables are in the backend, everything else is in the frontend.
    In the backend, in Table A, there is a Text Field X using Table B for lookup purposes.
    The data in Table B has been entered in a random, non-alphabetilized order.
    In the frontend, a query has been created using Table A.
    In the frontend, Form Y is using this query as a data source.
    A field in Form Y is using Text Field X as a combo to select data.
    How would I make the combo dropdown display the text data in alphabetical sequence?
    Thanks in advance for any help.
    Cheers,
    Andy

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

    Re: Front End/Back End Data Sequencing (Access 2000)

    You can make the rowsource for the combobox either a saved query or a SQL select statement that returns the rows in the order you want to see them. Is the backend Access or SQL Server?
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Front End/Back End Data Sequencing (Access 2000)

    Hi Charlotte:
    In this case the backend is Access. When I attempt to use the combobox, I get the message:

    The record source 'SELECT [qry_Models_LotTypes].LotType from [qry_Models_LotTypes]' specified on this form or report does not exist. You misspelled the name, or it was deleted or renamed in the current database, OR IT EXISTS IN A DIFFERENT DATABSE. In the form or report's design view, display the property sheet by clicking the Properties button, and then set the Recordsource property to an existing table or query.

    The Backend mdb is called GPA_Tables.mdb and the frontend mdb is GPA_Sys.mdb. I think there has to be a reference to the mdb names. This is where my problem lies. I'm not sure how the exact reference is spelled out in the Recordsource property. If you could help with this, I'd be much appreciative.
    Cheers,
    Andy

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

    Re: Front End/Back End Data Sequencing (Access 2000)

    Hmmmm - somethings amiss here. Is your query qry_Models_LotTypes in the front-end or the back-end .mdb file? It won't work if it's in the back-end - you can't link to a query. If it's in the front-end then you may be dealing with a corrupted database. Another approach to this is to go to the properties dialog box for the combobox, click in the rowsource and then click the builder button on the right-hand side. That should open up the query in design mode. If it complains that it can't find the query then try deleting the property, and then select the table you want to use, and click the builder. It will ask if you want to create a SQL query string - respond yes, and the query grid will appear. Apply the sorts and criteria you want, click the close button and it will ask if you want to save it. Click yes and you have the query string as the property. Hope this helps.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Front End/Back End Data Sequencing (Access 2000)

    Wendell:
    Bingo! It's surprising at how long you can stare at something and not see it. The "create a SQL query string" routine was the correct response. Thank you kindly for taking the time to answer my as usual, convoluted question. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Cheers,
    Andy

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

    Re: Front End/Back End Data Sequencing (Access 2000)

    >>'SELECT [qry_Models_LotTypes].LotType from [qry_Models_LotTypes]' <<

    You said your rowsource for your combo box was the above SQL string. Unless there is alot going on in [qry_Models_LotTypes], this seems rather inefficient. This is a query of a query, so Access first has to resolve the inner query before it does the second one. Is there a Model_LotType table that you could go against directly?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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