Prev: Delete CDO page heading and following matter when printing an email document; to print the document only
Next: Resize query
From: dan dungan on 9 Apr 2010 16:34
For an electrical part number quoting application developed in
Excel 2000, I have a a userform (userform4) with a combobox (cboQpn)
to hold the quoted part number, and another combobox (cboFormula)
prepopulated with a named range (Partnum)

The user types in a part number in cboQpn and then chooses
the correct formula from cboFormula.

Now in access I have a table (tblDetail) that holds previously quoted
part numbers
and their details--the formula used in the previous quote, for
example.
The formula information is in column 5 in tblDetail

I want to emulate the manual selecting operation in cboFormula and use
the
result of a query to select the matching value in cboFormula.

In the code below, my variable to hold the results of the query
(rstFromQuery)
shows "", but the query returns the proper value in I debug in the
immediate
window and paste that into an acces query. Also in the locals window,
I see:

- : rstFromQuery : : Variant/Object/Recordset
+ : Fields : : Fields/Fields
- : Item 1 : : Variant/Object/Field
- : Properties : : Properties/Properties
: Item 1 : : Variant/Object/Property
Value : "115XXS-SST" : Variant/String

This is the value I expect to see.

How did I select that value in the combobox?

I've searched the newsgroup for "search combobox list", automate
combobox select, and vba select combobox value.

Sub CreateCboFormulaMatchRecordSet()
'On Error GoTo CreateRecordSetErrorHandler
Dim oldDbName As String
Dim wspDefault As Workspace
Dim dbsEAIQuote As Database
Dim strSQL As String
Dim strFormula As String
Dim strEAIPart As String
Dim rstFromQuery As Variant

'strFormula = UserForm4.txtCompNum.text
strEAIPart = UserForm4.cboQpn.text


'Set the path to the database
oldDbName = "K:/Customer Service/Quote/Database/Development/
EAIQuote.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 tblDetail.Formula " & _
"FROM tblDetail " & _
"WHERE (tblDetail.Part_Number= '" & strEAIPart & "')"

'Create a Snapshot Type Recordset from the SQL query
Set rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL,
dbOpenSnapshot)

'sub not completed yet.
.....
 | 
Pages: 1
Prev: Delete CDO page heading and following matter when printing an email document; to print the document only
Next: Resize query