Hmmm

[TheOldGuy]

Hello all,

Looked at the othe threads re listbox.
I have a simular problem see vba

Private Sub Combo2_AfterUpdate()
Dim STRSQL As String, STRENG As String
Dim ENGNUM As Integer
Dim db As Database

Set db = CurrentDb

STRSQL = "DELETE * FROM WHAT_EQUIP;"
db.Execute STRSQL
STRSQL = "DELETE * FROM WHAT_TYPE;"
db.Execute STRSQL

STRENG = Me.Combo2.Column(3)
ENGNUM = Me.Combo2.Column(4)
'Fill Temp WHAT_EQUIP
STRSQL = "INSERT INTO WHAT_EQUIP ( TYPEID, TYPE, ENGINEER, ENGID )SELECT Engineers_Types.ID, Engineers_Types.TYPE, Engineers_Types.ENGINEER, Engineers_Types.ENGID FROM Engineers_Types WHERE ENGID = " & ENGNUM & ""
db.Execute STRSQL
'Fill Temp WHAT_TYPE
STRSQL = "INSERT INTO WHAT_TYPE ( TYPEID, TYPE, ENGID) SELECT WHAT_EQUIP.TYPEID, WHAT_EQUIP.TYPE, WHAT_EQUIP.ENGID FROM WHAT_EQUIP WHERE ENGID = " & ENGNUM & ""
db.Execute STRSQL


' setting combobox rowsource property to SELECT Engineers_Types.* FROM Engineers_Types; works fine (Set to table/query)
' setting listbox rowsource property to SELECT WHAT_TYPE.* FROM WHAT_TYPE; Does not work (Set to table/query)
Set db = Nothing

End Sub

I would like to resolve this but failing, can anyone help or point me to a differant thread

 

[theDBguy]

What exactly was the problem? Sorry if it’s wasn’t obvious to me.

 

[TheOldGuy]

Sorry about that.

Cannot get the listbox to work from the data supplied to table

 

[pbaldy]

Is the table getting populated as expected? This might help:

http://www.baldyweb.com/ImmediateWindow.htm

Plus adding this may reveal an error occuring:

db.Execute STRSQL, dbFailOnError

 

[TheOldGuy]

Thanks to all who replied
Issue now solved
By setting the rowsource from the code the newbox is now populated

Private Sub List15_Click()
Dim STRSQL As String, STRENG As String
Dim ENGNUM As Integer
Dim db As Database

Set db = CurrentDb

STRENG = Me.List15.Column(0)
ENGNUM = Me.List15.Column(1)
STRSQL = "SELECT DISTINCT Engineers_Types.GRADE, Engineers_Types.TYPE, Engineers_Types.ENGID FROM Engineers_Types WHERE ENGID = " & ENGNUM & ""
Me.List17.RowSource = STRSQL


Set db = Nothing
End Sub

Many Thanks

 

[pbaldy]

Glad you got it sorted.