I have a form in Access that calculates the total value of a subassembly for
use in an estimating worksheet. Since we want to keep the underlying math
visible in Excel, the form creates a string for the formula, which we
currently cut and paste into the worksheet, of the form:

qty1 & "*" & unitprice1 &"+" & _
qty2 & "*" & unitprice2 &"+" & _
....
qtyn & "*" & unitpricen &"+" & _

The form has combo boxes for the ProductType and Product that gets its rows
from Access tables where we store the standard costs.

I'd like to move this form into Excel, so that the user can invoke it from
our custom toolbar or menu, and, by pressing a button, insert the formula
into the active cell. But I'd like to keep the costs in Access.

Is this possible? Can I create combo boxes within Excel that get their Rows
from Access tables?

Any help is appreciated.
Sprinks

Hi Sprinks,

You can't move an Access form into Excel, but you can have code running
in Excel look up data in Access (and other) databases. To populate a
combobox on an Excel UserForm you could use something like this untested
air code in the form's Initialise event, having set a reference to the
Microsoft DAO 3.6 library:

Dim dbE As New DAO.DBEngine
Dim dbD As DAO.Database
Dim rsR AS DAO.Recordset

Set dbD = dbE.OpenDatabase("C:\folder\file.mdb")
Set rsR = dbD.OpenRecordset("SELECT Field1, Field2 FROM TheTable ORDER
BY Field2;", dbOpenSnapshot)
Do Until rsR.EOF
cboXXX.AddItem blah blah 'you'll need to work out
'what to put here depending on what you need.
'Also, you'll need to set the various properties of the
'combo box on the form in design mode
rsR.MoveNext
Loop
rsR.Close
set rsR = Nothing
dbd.Close
set dbD = Nothing
set dbE = nothing

You can also put comboboxes directly on worksheets, or use Excel data
validation to have in-cell dropdowns. In the latter case, I think you'd
use other Excel features to link a range in a worksheet to a database
table, and then use Excel's lookup features.


On Fri, 4 Feb 2005 11:21:02 -0800, "Sprinks"
<(E-Mail Removed)> wrote:

>I have a form in Access that calculates the total value of a subassembly for
>use in an estimating worksheet. Since we want to keep the underlying math
>visible in Excel, the form creates a string for the formula, which we
>currently cut and paste into the worksheet, of the form:
>
> qty1 & "*" & unitprice1 &"+" & _
> qty2 & "*" & unitprice2 &"+" & _
> ....
> qtyn & "*" & unitpricen &"+" & _
>
>The form has combo boxes for the ProductType and Product that gets its rows
>from Access tables where we store the standard costs.
>
>I'd like to move this form into Excel, so that the user can invoke it from
>our custom toolbar or menu, and, by pressing a button, insert the formula
>into the active cell. But I'd like to keep the costs in Access.
>
>Is this possible? Can I create combo boxes within Excel that get their Rows
>from Access tables?
>
>Any help is appreciated.
>Sprinks

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.