Combo Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On opening project/database my combo boxes loose auto expand and do not find things that are in the list even though they are there. They can be found by clicking on them in the list. The problem can be fixed by refreshing the table links or doing anything to any table. I am running odbc queries but even with these deleted it does not fix the problem. Any suggestions
Darcy
 
I would make sure that the combo box has a sort order on it?

And, combo boxes can be a bit slow if you use a linked table..and then set
the order on that. For example:

linked table name = tblCustomers

Combo box Source = select CompanyName from tblCusomters order by
CompanyName

In the above access has to:

combo box ->Process above sql, get linked table name, open linked table,
sort linked table

You could just place the name of the linked table right into the combo box

tblCustomers

Of course, then above will save a few steps..and load much faster

combo box->linked table

Ie, a query does not have to be processed via a linked table. So, the best
solution is create a view on the server side..set the order in that
view..and then use a linked table right to that view.

vtblCustomersByCo

With the above you get:

combo box->linked table

It will run much more snappy...and likely your order problems will be fixed.
And, likely your auto expanded will work better also.
 
Thanks for the reply
I need to explain a bit more. There are several combo boxs on 5 forms. The ones with odbc queries run fine
All the ones that use tables/Query, do not auto expand and do not see what is typed in (not in list message) even though you can focus on it by clicking on it in the drop down list. Refresh linked tables and they operate ok untill complete close down. As they are all affected and some only have a 3 line table under them, I suspect something is stoping them recieving proper focus on load of project.
 
Hum..that is strange!

So, you do have the sort order set for those combos...right?
 
Not all
I have been deleting different sections but subject to a rebuilt I am unable to correct the problem. I have 3 other projects in working situations with similar forms etc with no problems. Is there a way with vba i can refresh the links on load. With run comand I only open the link table manager and can get no further (in vb). I have tried with the tables as part of the database (not linked) but no improvement. The projects are for stock control, quoting and back costing. The odbc connects to sage our major accounting program(dos based). They need to be BE as 15 operaters need to access the project
Hope you can save a complete rebuild as a lot of vba is involve
Darcy
 
Not all.
Click to expand...

Ah, ok...then give setting the sort idea a try. I can't really see how auto
complte makes sense for a combo box that is not ordered? Which way does it
search?

So, I would try setting the order of the combos...as that may very well fix
this problem (you lucky it works in some cases).
Is there a way with vba i can refresh the links on load.
Click to expand...

I don't think refrdsing the actaul data links is a sotlion. You have some
problem here..but it not clear what that is just yet.

If you must have some rope to hang your self with..then can grab the code at
www.mvps.org/access that will refresh you links..but refresing can take a
very very long time in some cases.

You will find some code examples of re-linking here:

Relink Access tables from code
http://www.mvps.org/access/tables/tbl0009.htm

Relink ODBC tables from code
http://www.mvps.org/access/tables/tbl0010.htm

Having to re-link is most certainly worse medicine then the cure. I suppose
the above code examples might tide you over until a proper solution is
found..but I certainly would NOT use the above as a permanent solution to
your problem.
 
Thank you you very much for you replies
With much frustration I worked through all venues. The one that worked was to open Link manager and shut it again but only a sticking plaster. Deleting things one at a time did not improve anything. Finally I started from scratch and rebuilt the batabase and it works fine. I can only think that somewhere on the origonal build something went wrong with the mulipule deletion and rebuilds and the database inherited the fault. Just to say after being a little shy to use access for mainstream programs I am finding it to be very good for the type of bussiness we are in as there is no ideal pre written software available for our operation
Thanking you
Darcy
 
Back
Top