How to select combobx value with vba?

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.
.....
0
dan
4/9/2010 8:34:27 PM
excel.programming 6508 articles. 2 followers. Follow

0 Replies
616 Views

Similar Articles

[PageSpeed] 12

Reply:

Similar Artilces:

.IQY loop in VBA?
I am using Excel 2000 and XP. I have just learnt how to use an .iqy file to extract data from the web. To really benefit from automation I want to create a loop using slightly different web adresses each time. I have recorded the query as a macro and get: Sub Macro1() With Selection.QueryTable .Connection = "URL;http://www.skyscanner.net/gbp/flights/ABZ/ LTN" .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingAll .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = Fa...

Option Box radio button wont select
I'm must be missing something very simple but... I have an option box in the footer of a continous form. I'll use the option box to set the filter property on the continous form. The problem is the first radio buttton in the group is selected when the form opens and I'm not able to select any of the options. The option group is unbound, nothing in the Control source. Also if I remove the default value of 1 I am not able to select any radio buttons in the group. Thanks, Rick I would create a second option group next to the 1st, make it just basic, ie don't go to fancy ...

Is it possible to create and publish Outlook Custom Form through VBA
Hi All, I have created COM-Addin for Outlook in VB. But there are lot of limitations in VBScript like to handle all events(other than click event) of controls(ListView,Textbox) on outlook custom form. Can we handle these events using VBA so that i can distribute that application? Or Is it possible to create and publish that form through VB.? Thanks, Hi, Why don't you ask "Sue Mosher's" Forum - www.outlookcode.com - dedicated to OL add in programmers. -- Hope this helps you more Regards APK "ujwalabamishte@gmail.com" wrote: > Hi All, > I have created C...

Select top 10 in a list
Hi, I'm looking for a way to search a range in a list and pick the top 10 from that list. Once this has been done, I then need to copy the data to another sheet elsewhere For example, I have a sheet called "MainList" (http:// joemaldon.googlepages.com/), I would like to pick the top 10 from the totals colum and then take each entry and copy into another sheet called "Top10". So, copy ID, Name and Total to sheet called "Top10". Also then add rank positions including the joint places. Can anyone please offer a solution how this can be done please? Note there...

How can I minimalize the difference between extreme values on a c.
I'm making a chart with two values for class. One of the values is very small (3) and the other is very large (317). How can I make the axis values "break" so to speak, so that the difference between them is minimalized. For example, I need a portion that goes from 1-7 and another that goes from 300-700. I can't put them on different charts. I describe one technique on this page, and include links to others: http://peltiertech.com/Excel/Charts/BrokenYAxis.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutio...

sort and select based on criteria
I have a spreadsheet with the following data, what I would like to do is: 1. sort this in descending order by number of sales (got the macro doing this) 2. Determine the total sales ( a simple SUM in an adjacent cell. (done) 3. Calculate what 90% of total sales are. ( done in another adjacent cell) 4. Determine which parts make up the 90% of sales. (HELP) 5. Graph only the 90% parts. Graph is already created but how do I capture the 90% dataset. (HELP) The idea is to take the entire dataset, sort by number of sales and then graph only those parts that make up 90% of the sales. ...

Show all fields from table in select query ?
Hi all. I have 3 tables - INVENTORY, IN and OUT I made a select query to calculate the quantity of inventory on stock, but I only see the inventory items that are in both IN or OUT tables. If I select to show all fields from Inventory and only those related from other tables a get null values. Is there a way to have a field showing 0 (zero) instead of empty field ? You can use an outer join instead of an inner join (the default.) Details in: The Query Lost My Records! (Nulls) at: http://allenbrowne.com/casu-02.html A better solution might be to combine the IN and OUT into one...

How do I lookup several values and concatenate the results?
I have data that comes out of the system of record like this. Item # Description 1234 Widget 1234 Blue 1234 Large 2345 Thingy 2345 Purple 3456 Gizmo 3456 Red 3456 Large 3456 Square 3456 With Buttons How do I combine the variable number of descriptions into one field for each item #? Each item doesn't have the same amount of descriptions. I want the output to be like this. Item # Description 1234 "Widget,Blue,Large" 2345 "Thingy,Purple" 3456 "Gizmo,Red,Large,Square,With Buttons" You could use a macro. I'm guessing that you reall...

negative time values
not to worry, have found the answer but thanks anyway Mike ...

Total columns Based on another cells value
Good Day, I trying to use VBA to calculate totals in multiple columns when it see uniques values in another Column..in the example below column "A" contains user names while columns "B, C, D" contain the values I want totaled based on the user name in column "A"...An additional thing would be have it place the values with the user name on a new worksheet. Col A Col B Col C Col D John Smith 1.0 1.0 1.0 John Smith 1.25 1.25 2.0 Mary Johnson .75 1.0 2.5 Jack Johnson ...

OL2003: Selecting Multiple "Blocked Senders"?
In Outlook 2000, I was able to Ctrl-click multiple messages, then click on "Junk Senders List" and all the senders would be added to my spammers file. Under OL2003, I can't figure out if it's possible to designate more than one message at a time to be added to the Blocked Senders list. Is it? If so, how? {Jonathan} ...

Excel 2003: In a Macro,how to select a variable row length table
I wish to create a macro that will perform functions upon multiple rows of data. The number of rows of data will change every time. Problem : I create a macro by recording. I select rows of data using CTRL/down arrow on the last column and then selecting back to the first column of the first data row. This appears to 'hard code' the number of rows of data to be the number of rows that exists at the time of macro creation. When I run the macro another time it then selects the same number of rows as when the macro was created, which will then not match the new current data. i...

How move a picture by code (VBA)?
Hi, I want to move a picture to a position, say, ColumnB and Row2 (or Cell B2) with its up-left coner the same as the cell. And I need resize the picture to fit in cell(B2:C3). What the VBA code to do this? I tried using record Macro, but the code is not easy to use. The following code will move picture 1 to fit over cells B2:C3 -- '======================= Sub MovePicture() Dim ws As Worksheet Dim rng As Range Set ws = Sheets("Sheet1") Set rng = ws.Range("B2:C3") With ws.Shapes("Picture 1") .LockAspectRatio = msoFalse .Top = rng.Top .Left = rng.L...

faq: How to obtain the select plain text?
Hi, Just like the notepade.exe in windows, I type some plain text and select it using mouse with left button pressed, and copy it to the clipboard by select copy command in the menu Edit. How to do that? Thanks very much. http://msdn2.microsoft.com/en-us/library/80db3kax(VS.80).aspx http://www.codeproject.com/editctrl/editctrltutorial.asp Also, take a look at CEdit::Copy() CEdit::Paste() CEdit::Cut(). They should do what you'd like. http://msdn2.microsoft.com/en-us/library/75bh1f1t(VS.80).aspx Tom "fcvcnet" <fcvcnet@163.com> wrote in message news:fhb5as$ghh$1@ne...

VBA return all objects
I am trying to use an if...then statement to show a message box. I want to say "if the chart legend contains this legend entry, then show MessageBox X." What I need is a way to include all legend entries in my if...then statement, instead of referencing each one seperately. In the example below, instead of "...LegendEntries (1).Font..." I want something like "...LegendEntries (all).Font..." Is there a way to do this? Many thanks!! Worksheets("sheet1").ChartObjects(1).Chart _ .Legend.LegendEntries(1).Font.Italic = True How about using some...

Selecting a namespace-prefixed node
Hi I am pretty much an xml beginner; hopefully someone can easily answer this one... I want to select a node representing a worksheet in an xml document for the Office Web Components spreadsheet. The file uses xml namespaces, of which I know very little. But it does seem clear that I can't select the <ss:Worksheet> element by name only; the xpath "//Worksheet" does not match any nodes Extract from the file, in case this is needed to come up with the solution <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"><!-- ... there's more, but...

Using Functions in Queries/Assigning Values to Non-numerical Entri
I would like to sum values across multiples fields but the values in the fields I need are letters. So, is it possible to assign a value to each letter and then take the sum? For example, if this was an attendance record. Week 1 = A Week 2 = P Week 3 = P Week 4 = TA And I would want to count the number of Absences (A) for these four weeks so I would need to assign A=1, P=0, and TA=.5 (since 2 Tardies = 1 Absence). Can I do this? And how? How many values do you have? And how is the data structured? If you have many values, I would build a table of equivalence. Lette...

in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to the newly created copy of this sheet?
in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to the newly created copy of this sheet? Hi Daniel, Try something like: Dim WB As Workbook Dim WS As Worksheet Set WB = ActiveWorkbook With WB .Sheets("Mysheet").Copy Before:=.Sheets(1) End With Set WS = ActiveSheet --- Regards, Norman "Daniel" <softwareengineer98037@yahoo.com> wrote in message news:OdVZVvmgFHA.3088@TK2MSFTNGP10.phx.gbl... > in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference > to > th...

Uniqueness only for some attribute value
Hi, I want to add uniqueness constraint for an attribute, but only for a certain value, for example, the attribute can be 'true' or 'false' or none, I want to allow only one 'true' value. How should I do? I tried to use like this: <xs:key name="attributeKey"> <xs:selector xpath="./MyElement[@myAttribute = 'true']"/> <xs:field xpath="@myAttribute"/> </xs:key> but this gives validation error in xsd. Any help is appreciated. Thanks. kate wrote: > I want to add uniqueness constraint for an attribute...

Selecting unique record from duplicate
Hi, please help I've just run a 'find duplicates' query on a table. Now I'm trying to select a single unique record from each duplicate. One of my fields is called 'OBSERVER' - this contains either a persons name OR 'unknown' if the persons name is not known. If one of the duplicate records contains a persons name and the other duplicate is 'unknown', how do I select the record with the persons name? If both duplicates contain 'unknown' or both contain a name, i don't mind which record gets selected. See example below... Uni...

Lookup Values
Hello all, In crm 3.0, we can choose different fields in different entities for some lookup objects. For example, we can choose either contact or account for parent customer field in contact form. In the same way, for recipient field in a phone call activity, we can choose lead, contact, user or account. My problem is that i dont want to see some entities for lookup objects. For example, i just want to choose contact or lead records for recipient field, and i dont want to see the others(account or user). How can i do this? Thanks for all suggestions. Michael Hoehne has a script you can add...

Equal list values.
I have a list of names in column A with numerical values in column B Example: A B Alan 1234 Darren 2433 Tammi 2055 Carol 1999 Rose 2001 Reese 2411 Is it possible to equally split the list into 2 columns so that the number totals (B) are the same (or as near as)? Sort the list by ColB and assign subsequent rows to each set. -- Jacob (MVP - Excel) "Darren" wrote: > I have a list of names in column A with numerical values in column B > > Example: > A B > Alan 1234 > Darren ...

Assigning Ascii value to CString
Hi, I need to send an ascii value (specifically ascii(26) - Ctrl+z) to a CString in order to send it as a command through a serial port. Could someone please tell me how I can do this? Regards, Steph Steph Sharp wrote: > Hi, > > I need to send an ascii value (specifically ascii(26) - Ctrl+z) to a > CString in order to send it as a command through a serial port. > > Could someone please tell me how I can do this? > > Regards, > Steph CString str((TCHAR)26); -- Scott McPhillips [VC++ MVP] OR, CString str((TCHAR)('Z'-'@')) or CString str(&q...

Operation of Asian text font selection in the Font dialog
I have a document that has been translated into Japanese, in which all of the translated text appears in MS Gothic font. This displays most of the characters correctly, but not all. Apparently, they are correct if the font is changed to MS PMincho. I'm trying to do this globally by changing the Font settings in the various styles, but no matter what I set in the Asian Text Font and Font boxes, the Japanese text remains stuck on MS Gothic, although any latin text changes to PMincho. I can apply PMincho as a character style, but that's tedious. How do I get the required ...

how to invoke select names form
hi all... i want to invoke the form(Select Names form that will be opened when we click the To button in the new mail message) programatically in my form ... how can i do this ... I don't think you can as it is not a form but a cache of addresses = previously used in the TO: field - however, you can always ask in a = programming group "down the hall." --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, Malli asked...