Dynamic List Box - AM GOING INSANE :-((

[Mark]

Hello,

I have a list box which I am populating through code. Trouble is, the
columns of data seem mixed up and I can't work out why.

The VBA code I have is as follows:

Dim myDB As DAO.Database
Dim mySet As DAO.Recordset
Dim varSQL As String

cboSites.RowSourceType = "Value List"
cboSites.RowSource = ""
cboSites.RowSource = "0,<USE MAIN>,x,x,x,x,x;"

varSQL = "Select CustomerID, SiteName, Add1, Add2, Add3, TownCity, PostCode
from tblAddresses Where CustomerID = " & CLng(cboCompany)

Set myDB = CurrentDb
Set mySet = myDB.OpenRecordset(varSQL)

If mySet.EOF = True Then
'there are no multiple Addresses so nowt to do
Else
mySet.MoveLast
X = mySet.RecordCount
mySet.MoveFirst
For i = 0 To X - 1
cboSites.RowSource = cboSites.RowSource & mySet.Fields("CustomerID")
& " ," & mySet.Fields("SiteName") & " ," & mySet.Fields("Add1") & " ," &
mySet.Fields("Add2") & " ," & mySet.Fields("Add3") & " ," &
mySet.Fields("TownCity") & " ," & mySet.Fields("PostCode") & ";"
mySet.MoveNext
Next
cboSites.Visible = True
End If

HELP!! Where is it going wrong??

The Column Count property is 7. Am I missing something.

 

[Stuart McCall]

Hello,

I have a list box which I am populating through code. Trouble is, the
columns of data seem mixed up and I can't work out why.

The VBA code I have is as follows:

Dim myDB As DAO.Database
Dim mySet As DAO.Recordset
Dim varSQL As String

cboSites.RowSourceType = "Value List"
cboSites.RowSource = ""
cboSites.RowSource = "0,<USE MAIN>,x,x,x,x,x;"

varSQL = "Select CustomerID, SiteName, Add1, Add2, Add3, TownCity,
PostCode
from tblAddresses Where CustomerID = " & CLng(cboCompany)

Set myDB = CurrentDb
Set mySet = myDB.OpenRecordset(varSQL)

If mySet.EOF = True Then
'there are no multiple Addresses so nowt to do
Else
mySet.MoveLast
X = mySet.RecordCount
mySet.MoveFirst
For i = 0 To X - 1
cboSites.RowSource = cboSites.RowSource &
mySet.Fields("CustomerID")
& " ," & mySet.Fields("SiteName") & " ," & mySet.Fields("Add1") & " ," &
mySet.Fields("Add2") & " ," & mySet.Fields("Add3") & " ," &
mySet.Fields("TownCity") & " ," & mySet.Fields("PostCode") & ";"
mySet.MoveNext
Next
cboSites.Visible = True
End If

HELP!! Where is it going wrong??

The Column Count property is 7. Am I missing something.

No need to fill the combo that way. Change the RowSourceType from Value List
to Table/Query, then simply set the Rowsource to the sql value:

cboSites.Rowsource = varSQL

 

[Mark]

MAGIC - WHAT WAS I THINKING

 

[Stuart McCall]

MAGIC - WHAT WAS I THINKING

<snip>

Uh, not qualified to answer that, sorry.

BTW lose this line:

cboSites.RowSource = ""

Its not necessary and causes Access to requery the list/combo twice.
Whenever you assign a value to a RowSource you cause an automatic requery.

 

[Mark]

ah - there was a reason.

The 1st item in the combo box was to say <Any>.

The rest of the rows where to come from a table.

But the code was going to something else should <Any> have been selected.

It kind of throws a spanner in the works doing it like this.

 

[Douglas J. Steele]

Not a problem.

Dim varSQL As String

varSQL = "SELECT CustomerID, SiteName, Add1, Add2, Add3, TownCity, " & _
"PostCode FROM tblAddresses " & _
"WHERE CustomerID = " & CLng(cboCompany) & " " & _
"UNION" & _
"SELECT 0, ""<USE MAIN>"", Null, Null, Null, Null, Null " & _
"FROM tblAddresses"

cboSites.RowSource = varSQL

 

[Mark]

YES, YES - THAT'S THE KEY>

THANK YOU SO MUCH FOR THIS. I CAN SLEEP NOW )