This may be a little tricky to follow without the form and other code, but
it's the simplest examples I have from working code.
I use ADO for database connection. There are other options, but I've found
ADO to be very reliable, simple enough for me to use and it is on basically
all windows systems, at this point. This would of course require a
reference to "Microsoft ActiveX Data Objects 2.7 Library". There are
different versions, which could potentially cause trouble, but I don't do
anything fancy with ADO.
The database connection function:
Private Function OpenDbConn() As ADODB.Connection
Dim sConStr As String
Dim oRetCon As ADODB.Connection
sConStr = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"DATA SOURCE=" & kDbFile & ";" & _
"USER ID=admin;PASSWORD=;"
Set oRetCon = New ADODB.Connection
oRetCon.Open sConStr
If oRetCon Is Nothing Then
MsgBox "Error connecting to Plot log database!" & vbCrLf & _
"Unable to connect PlotLogTransaction."
Else
Set OpenDbConn = oRetCon
End If
Set oRetCon = Nothing
End Function
The following returns a variant containing the DISTINCT (SQL term) items
from the specified column, which in this case, are the categories, which the
user selects from a combobox.
Public Function GetCategories() As Variant
Dim oCon As ADODB.Connection, oRs As ADODB.Recordset, sSql As String
Dim vRet As Variant
Set oCon = OpenDbConn
sSql = "SELECT DISTINCT DetailCategory FROM DetailBlocks"
sSql = sSql & " ORDER BY DetailCategory"
Set oRs = oCon.Execute(sSql)
If Not oRs.EOF Then
vRet = oRs.GetRows
oRs.Close
End If
Set oRs = Nothing
oCon.Close: Set oCon = Nothing
GetCategories = vRet
End Function
The following is within the Form_Load event of the form, which actually
places the category items within the combobox:
iCatCnt = -1
vCategories = GetCategories()
On Error Resume Next
iCatCnt = UBound(vCategories)
On Error GoTo 0
If iCatCnt < 0 Then
MsgBox "There is a serious problem with the Detail database...Call IT!"
Unload Me
Exit Sub
End If
For iCatCnt = 0 To UBound(vCategories, 2)
Me.cbxCategory.AddItem vCategories(0, iCatCnt)
Next
Me.cbxCategory.ListIndex = 0
Note that I'm not a full time programmer, so the code I'm posting isn't
necessarily elegant and there are likely *better* ways to do some of the
above. I can tell you that it gets the job done and is very reliable. (If
you're new to SQL, be careful with it!!! The basics are quite simple, but
it's very powerful. You can totally wreck a database in seconds, with no
'undo' command......don't ask me how I know that....hehehe.)
wrote in message news:5081796@discussion.autodesk.com...
I'm really new to this. Been searching for hours - getting close but have
ground to a halt..
Using Map5 (AutoCAD 2002 based) & MS Access 97. Know virtually nothing about
VBA.
Scenario : we need to insert streetmaps into our drawings. I've already
written a Lisp routine where users enter the grid ref of a street at the
command prompt. The Lisp routine finds & inserts the correct map. However,
users have to first look up the grid ref from a paper-based streetmap. I
want to make this simpler by using a database containing all street names in
the city. The database contains one table. Field names are StreetName,
District & GridRef. There are 7000+ records
I want to create a form in VBA containing a combobox populated with the
fields StreetName & District. Users can then type the street name in the
combobox; scroll through the list if necessary; select the correct street
name, press "OK", wham bam my lisp routine inserts the map.
My questions are 1. How to connect to the database. 2. How to populate the
combobox with the streetnames.
I've tried using answers from other posts but so far just have a blank
combobox staring back at me.
