Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't create recordset to populate userform combobox
Hi,
So with Excel 2000, the code I'm using (shown below) doesn't populate the combobox. I'd like to type a competitor's part number in the textbox, txtCompNum.text and use that to find a match in the access database table, tblCompetitorScrubbed, and return my company part number. I believe the sql statement is correct because I typed my varialbe, ? strSQL, in the Immediate window. That returned an sql statement that worked when I copied that statement and manually created a new query in the access database with it, and the query returned the proper data. However, it appears to me the recordset is never created with this line: Set rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL, dbOpenSnapshot) and the combobox is not populated. Here's the code I'm using. Does anyone have a ideas how to debug? Thanks, Dan Sub CreateRecordSet() On Error GoTo CreateRecordSetErrorHandler Dim oldDbName As String Dim wspDefault As Workspace Dim dbsEAIQuote As Database Dim strSQL As String Dim strCompetitorPart As String Dim strEAIPart As String Dim rstFromQuery As Recordset strCompetitorPart = UserForm4.txtCompNum.text strEAIPart = UserForm4.cboQpn.text 'Set the path to the database oldDbName = "K:/Customer Service/Quote/Database/EAIQuote_be.mdb" 'Create a default workspace Object Set wspDefault = DBEngine.Workspaces(0) 'Create a Database object Set dbsEAIQuote = wspDefault.OpenDatabase(oldDbName) 'The SQL statement strSQL = "SELECT tblCompetitorScrubbed.EAIPartNumber " & _ "FROM tblCompetitorScrubbed " & _ "WHERE (tblCompetitorScrubbed.CompetitorNumber= '" & strCompetitorPart & "')" 'Create a Snapshot Type Recordset from the SQL query Set rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL, dbOpenSnapshot) 'load up combobox 'Show the number of fields returned MsgBox "there are " & rstFromQuery.Fields.Count & _ " fields that were returned" 'Move to the last record in the recordset rstFromQuery.MoveLast 'Put the EAI part number in Combobox2 Do While Not rstFromQuery.EOF UserForm4.cboQpn.AddItem rstFromQuery(1).Value rstFromQuery.MoveNext Loop ' Sheet6.ComboBox2.DropDown = rstFromQuery!EAIPartNumber 'Show the number of records returned ' MsgBox "there are " & rstFromQuery.RecordCount & _ ' " records that were returned" Exit Sub CreateRecordSetErrorHandler: End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't create recordset to populate userform combobox
Hi Dan
why have you got this in before you loop: 'Move to the last record in the recordset rstFromQuery.MoveLast ? That will take you straight to the end of your rs. What happens if comment out that line? Cheers Simon Excel development website: www.codematic.net dan dungan wrote: Hi, So with Excel 2000, the code I'm using (shown below) doesn't populate the combobox. I'd like to type a competitor's part number in the textbox, txtCompNum.text and use that to find a match in the access database table, tblCompetitorScrubbed, and return my company part number. I believe the sql statement is correct because I typed my varialbe, ? strSQL, in the Immediate window. That returned an sql statement that worked when I copied that statement and manually created a new query in the access database with it, and the query returned the proper data. However, it appears to me the recordset is never created with this line: Set rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL, dbOpenSnapshot) and the combobox is not populated. Here's the code I'm using. Does anyone have a ideas how to debug? Thanks, Dan Sub CreateRecordSet() On Error GoTo CreateRecordSetErrorHandler Dim oldDbName As String Dim wspDefault As Workspace Dim dbsEAIQuote As Database Dim strSQL As String Dim strCompetitorPart As String Dim strEAIPart As String Dim rstFromQuery As Recordset strCompetitorPart = UserForm4.txtCompNum.text strEAIPart = UserForm4.cboQpn.text 'Set the path to the database oldDbName = "K:/Customer Service/Quote/Database/EAIQuote_be.mdb" 'Create a default workspace Object Set wspDefault = DBEngine.Workspaces(0) 'Create a Database object Set dbsEAIQuote = wspDefault.OpenDatabase(oldDbName) 'The SQL statement strSQL = "SELECT tblCompetitorScrubbed.EAIPartNumber " & _ "FROM tblCompetitorScrubbed " & _ "WHERE (tblCompetitorScrubbed.CompetitorNumber= '" & strCompetitorPart & "')" 'Create a Snapshot Type Recordset from the SQL query Set rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL, dbOpenSnapshot) 'load up combobox 'Show the number of fields returned MsgBox "there are " & rstFromQuery.Fields.Count & _ " fields that were returned" 'Move to the last record in the recordset rstFromQuery.MoveLast 'Put the EAI part number in Combobox2 Do While Not rstFromQuery.EOF UserForm4.cboQpn.AddItem rstFromQuery(1).Value rstFromQuery.MoveNext Loop ' Sheet6.ComboBox2.DropDown = rstFromQuery!EAIPartNumber 'Show the number of records returned ' MsgBox "there are " & rstFromQuery.RecordCount & _ ' " records that were returned" Exit Sub CreateRecordSetErrorHandler: End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't create recordset to populate userform combobox
Hi Simon,
I commented that out, but the program still jumps to the end of the sub. Then I commented out the line: On Error GoTo CreateRecordSetErrorHandler and the line: Set rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL, dbOpenSnapshot) returned the error, run time error 13: type mismatch Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't create recordset to populate userform combobox
I changed the data type from Dim rstFromQuery As Recordset
to Dim rstFromQuery As Variant, and it seems to be working now. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
populate dropdown/combobox on userform with specific values from s | Excel Programming | |||
Populate userform combobox with option button | Excel Programming | |||
Populate A Userform ComboBox | Excel Programming | |||
Userform ComboBox populate using code? | Excel Programming | |||
create a userform to populate a diagram with the forms info | Excel Discussion (Misc queries) |