July 14, 2008 at 4:56 am
Hi
I have Access 2003 .adp fronting SQL Server 2000. I have created a combo box (CheckCC) which uses the following code to add items to a value list. It all works fine for about half of the list of items, then, for no apparent reason, it changes the order. That is, the content for Special Title becomes the content for Course code and vice versa. Very strange. has anybody got any ideas.
Many thanks
Paul
Do While rs.EOF() = False
strItem = rs.Fields("Course_code").Value & ";" _
& rs.Fields("SpecialTitle").Value
CheckCC.AddItem strItem
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
July 14, 2008 at 7:17 am
Hi,
This is not something i've heard of -or can find mention of else where - so i have three suggestions.
1. Check that the datasource is correct i.e. that the data populating the recordset isn't the problem.
2. Add a second combobox to the form with the same datasource and see if it reproduces the behaviour
3. If it does try a new combobox on a new form.
I suggest 2 and 3 because very occasionally a control or form can become corrupted in which case the only real answer is to rebuild them.
Other than that i haven't got any more ideas sorry.
K.
July 14, 2008 at 7:36 am
Hi Karma
I much appreciate you taking the time to help.
I've been having another look, and tried another form etc.. What I've discovered is that the longer text field was in some cases extremely long, and was, quite surprisingly, wrapping into the other field. I've cut down the length of text displayed and it seems to be working.
Many thanks
Paul
July 14, 2008 at 8:38 am
Random! well glad you got it sorted 🙂
K.
July 15, 2008 at 4:01 am
Nope, it wasn't that. It is the strangest thing. I've created a new form. Checked the data by creating a recordset from the code - which all proved fine. It is no doubt that the combo box itself creates the error. Whatever changes I make to the column widths and list width, about half way down the list, the second column wraps into the first. If I just take the first (Course_code) column, then all is fine. Unfortunately, this is where I've had to leave it.
If anybody has had this problem before, or can suggest anything, I would be most grateful.
Paul
July 15, 2008 at 5:09 am
Hi,
I created a simple test to try and replicate the problem.
New adp project TESTADP
1 table TBLTEST
2 columns TESTID: char(5), TESTDESC char(200)
11 rows of data
TESTID
5 chars 00001 to 00011
TESTDESC
aaaa - 200 times bbbb 200 times etc
1 form TESTFORM unbound no recordsource
1 combobox TESTCOMBO both columns are visible i.e. 2cm;10cm;
recordset populates combobox on load event of form.
I've only got 11 rows of data but i have no problems displaying up to 5 and 200 characters in the two columns
if you give me a bit more detail i'll try to make mine a bit more realistic.
The only other question is are you on the latest SP?
Thanks
K.
July 15, 2008 at 8:00 am
I'm first trying to understand why you want to have code to populate a combo box from a recordset ??? Why not just make the query the Record Source? If that source of data needs to change, just update the underlying tables and then use the Requery method of the combo box.
Also, I think the problem you're having IS the data. You have one column with up to 200 characters, so any seriously lengthy data might not fit, and then the behavior of the combo box might be the problem. Ask yourself if it really matters if ALL 200 of the characters need to appear in the box. If not, then perhaps just adjust the query to return, say, the 1st 40.... It's either that or have a VERY WIDE combo box, which might require a higher video resolution just to make the box fit on the form. Just my two cents on that...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 15, 2008 at 9:14 am
Hi,
@smunson, the OP didn't actually specify 200 characters that was just me seeing if i could re-create the problem using an unusually large column size. Secondly; yes I agree under most circumstances binding the control to a query is a good idea however that won't necessarily fix the problem which is of interest in and of itself.
Just my 2p 🙂
K.
July 15, 2008 at 9:16 am
Thanks, Karma, Steve
The text chars of the columns is 7, and a max of 30. The data is drawn from an Oracle server via SQL Server 2000 and needs to be live to view for several users. As a recordset all works fine - both columns are quite intact. The problem is definitely the combo box.
Karma, I have sp3. I have also created a new .adp and form. Exactly the same happens.
Steve, yes, I could just go with a recordset and re-jig to suit. However, I was just looking to create a small disconnected lookup database.
I think I'm going to have to leave the list at one column - that works fine.
Many thanks again
Paul
July 15, 2008 at 9:20 am
One last thought. Limit the number of chars in the rightmost column, AND set the width of that column in the properties of the combo box such that even an ALL CAPS entry there of the maximum # of chars is still visible... ???
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 15, 2008 at 9:39 am
Hi,
Got me beat i have to say. I have no problem displaying over 5 and 200 chars in 2 columns. I am up to sp3 as well.
To add to Steve's suggestions, I'd be interested to know what happens if you run this on another pc.
K.
July 16, 2008 at 2:38 am
I have tried to limit the length of the description (course title) string (down to 10 chars). Then, in stages, increased the width of the columns in the combo up to something large. However, about half way through it still wraps. It is an interesting problem, but like all these sort of things, there is only a certain amount of time you can spend on them. I hope that one day I will find the solution.
Karma, Steve - I much appreciate your help and excellent suggestions.
Paul
July 16, 2008 at 8:26 pm
I've found Access Projects have exhibited unexpected behaviour before - including data access pages (DAPs) that become corrupt for no apparent reason, and then have to be recreated. The strangest 'bug' that I've found though was when updating / changing hyperlinks on a DAP - I would change one hyperlink only to find that Access had changed one or more other hyperlinks (took a long time to find out what was going on in that case) - in the end I just lost confidence in Access and avoid it like the plague nowadays.
my 2c
July 18, 2008 at 2:41 am
Hi Ivanna
Thanks for your reply. Personally I've found Access .adp a brilliant working environment. It has all the ease of use and quick development of an mdb, plus the ability to work natively with SQL Server tables - and the tremendous power of the SQL Server environment.
I'm pretty sorry to have to move away from .adp because of the changes in Access 2007. I'm looking at moving to Visual Studio. I've had a good play with it, but it sure doesn't have the easy database development of Access.
Could I ask, Ivanna, what you have moved to?
I'd be really interested to hear what other uses have or are planning to move to.
Many thanks
Paul
July 21, 2008 at 4:10 am
Could it be the contents of the data itself? I have had problems with multicolumn listboxes in the past where a comma or semicolon in the data throws off the columns, making data appear in the wrong place. I haven't tried this with combos, but under the covers, a combo is basically just a text box with an associated listbox.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply