Unanswered: Visual Basic Express fill combo box from SQL qry
Hi - I am a pretty remedial visual studio express user. I recently understood how to do cascading comboboxes using the binding sources of the related tables, eg tbl 1 Make, Table 2 model, so as eg only ford models displayed in cbo2 if Ford selected in cbo1 - thanks to Beth Massi!
However I'm having some trouble with the following scenario. I have a 'tblUser' table with fields: Name, Name_id, Firstname and Surname. I have a tblAsset' table with fields: 'AssetNo' and 'Description'. On form I have 4 controls: 2 x cbo and 2 x txt. Now, there is no direct link between Asset and User tables, because our administrators don't put the current user User IDs or names in the Asset container in AD!...at least not in their own field... Just some daft free text field called 'Description', which has the current user of an asset as part of the description string, eg "SOH-12345, Room 2b, Melanie Jones". So what I successfully did in access was use a LIKE statement to get (eg) all the 'Jones' entries in a combobox (using the Surname field on the form) then select the correct one, and use the related AssetNo. (The reason for this was so I could use the resulting AssetNo as the value to remote control a PC using Remote Access/MSRA in Windows). So basically the SQL says something like SELECT tblAsset.Description WHERE Description LIKE "*" & txtSurname & "*".
I'm a bit stumped, and I guess it's a lack of knowledge about creating a dataset from this query, i'm not sure. What I need to do is use cboUser to select a user (Eg Mel Jones), that user's Surname appears in txtSurname. Then cboUserAsset needs to display all the 'Jones's' entries in the 'Description' field of the Asset table, and I select the one that says "SOH-54321, Room 2b, Mel Jones". When I select that, txtAsset is populated with "SOH-54321", and I can use that to run a connect command in MSRA.
So it looks like cboUser would fire off the SQL string and populate cboUserAsset, but I'm not sure how to get that data in that control. Sorry this is long-winded. I'm looking all over, and will continue to, but can anyone shed some light on what I'm not 'getting'? Many Thanks.