Combo box .additem

Question

Post reply

Combo box .additem

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

PaulSp SSCrazy Points: 2499 More actions · Answer 1

Thanks, Chris

Why didn't I think of that?? That is the answer.

Many thanks again

Paul

Karma-343206 SSCrazy Points: 2104 More actions · Answer 2

Hi,

Could someone explain how that happens?

I tested that senario just to make sure there wasn't some quirky behaviour in adp's vs mdb's.

I found as i suspected if you have a data as follows

00001, aaaaaaaaaaaaaaaaaaaa

00002, bbbbbbbbbbb;bbbbbbbbb

00003, ccccccccccccccccccccc

You simply truncate the data at the point of the inteloping ;

The combobox is set to display only 2 columns. Because each row is processed individually the output of the code below will be

stritem = "00002; bbbbbbbbbbb;bbbbbbbbb"

As this is effectively 3 columns the last column is simply ignored

Thanks

K.

Private Sub Form_Load()

Dim cnn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim stritem As String

Set cnn = New ADODB.Connection

' Open a connection by referencing the ODBC driver.

cnn.ConnectionString = "Trusted_Connection=yes;driver=SQL

Server;server=*********;database=TESTADP;"

cnn.Open

' Create a Recordset by executing an SQL statement.

Set rs = cnn.Execute("Select * From TBL_TEST")

Do While rs.EOF() = False

stritem = rs.Fields("TESTID").Value & ";" _

& rs.Fields("TESTDESC").Value

Me.Combotest.AddItem stritem

rs.MoveNext

Loop

rs.Close

Set rs = Nothing

End Sub

PaulSp SSCrazy Points: 2499 More actions · Answer 3

Hi Karma

I'm not sure how it works, but it certainly has done the trick.

I just added replace([fieldname],',','') and it all worked.

Best wishes

Paul

Scott MacCready SSC Eights! Points: 991 More actions · Answer 4

I notice that you are pulling Oracle via SS2K.

Perhaps you have created a linked server which implies OLEDB [openquery()]which begs the question: which OLEDB provider are you using? The MS OLEDB driver for Oracle has not and won't be updated past Oracle 8. Even the Oracle drivers through version 10.2 have serious bugs. Eg. integers (precision 0 numerics) must be converted to varchar2 within the pass-through PL1 select to successfully pull them into SQL Server.

OR, you are using ODBC [openrowset()]. I know less about this but know that at least some vintages of the Oracle driver transform numeric types to Nvarchar (varchar2 in Oracle-speak) when pulling into Access.

Not that these are your problems. However, you statement implies that you aren't necessarily actually interacting with SQL Server on the back end but may be using it as an intermediary to Oracle.

IF you were using DAO (not ADO in an adp), you could in principle connect directly to the Oracle server via ODBC and use that source to populate your lookup (is that what you are doing?) before commiting the selection to SS. Elsewhere, the advantages of this 'old-school' approach have begun to hold sway over the formerly 'inevitable' ADO methods. If you are not too far along, you might think about this. You may have notieced that the MSACCESS product team has stopped innovating on ADO and is re-investing lately on JET (renamed to ACE) on the client side.

Also, you might find it advantageous to consider SQL Express ('05 or '08) as an upgrade path. SS2K, while still much in use, is aging out. This, just in case you are seeing a glitch (that won't get fixed) in SS2k.

PaulSp SSCrazy Points: 2499 More actions · Answer 5

Hi Scott

Sorry I missed this - as all is now working - and thanks for the interesting info.

I am soon going to start the process of changing over from Access.adp (which I have found to be excellent) to VS2005/8 - as .adps are no longer supported in Access 2007. I'm sure this will throw up a whole load of new problems.

Many thanks again for taking the time to contribute.

Paul

Naked Ape Ten Centuries Points: 1095 More actions · Answer 6

Hi,

I've had similar problems populating combo-boxes before. Even binding it to a query, rather than using code, can cause the problem.

I have found this usually occurs because data being returned includes commas or semicolons. Access interprets them as field/column delimiters and splits the data - the end result being that all the data from that point on moves over one column. You may need to remove such characters from the data before populating the combo-box.

HTH.

🙂

Chris

PaulSp SSCrazy Points: 2499 More actions · Answer 7

Thanks, Chris

This definitely turned out to be the problem. Chris Quinn pointed it out earlier - though I should of thought of it myself, because it's such a logical explanation.

Thanks again for taking the time to comment, Chris.

Paul