Rowsource from External Access Database 1

[gnasher]

Acad 2000:

I would like to create us UserForm with a combo-box whos recordsource is based on data held in a secured MS Access 2000 database.

I know how to create the form and place the combo-box on it

I know how to connect to this db using adodb and can create a recordset using this method.

Can someone please point me in the right direction.

Im pretty ok with VBA, but have not done much with AutoCAD

 

[LochDhu]

You can loop through the ado recordset object to populate the combo box. In VBA, the combo box control does not have a Datasource property as is does in VB.

Do Until adoRs.EOF
ComboBox1.AddItem adoRs("FieldName&quot.Value
adoRs.MoveNext
Loop
adoRs.Close

Scott

 

[gnasher]

Thank-you Scott, works like a treat, just one more question if you may;

[vb]
Do Until adoRs.EOF
ComboBox1.AddItem adoRs("JobPK&quot.Value
adoRs.MoveNext
Loop
adoRs.Close
[/vb]

If I have 3 fields in the recordset:

JobPK
JobNumber
JobDescription

How simple is it to add these 3 as seperate columns using the .additem method?

 

[LochDhu]

Two ways.
1) Cocatenate them to appear as a single string:

Dim strTemp as string
Do Until adoRs.EOF
strTemp = adoRs("JobPK&quot.Value & " " & _
adoRs("JobNumber&quot.Value & " " & _
adoRs("JobDescription&quot.Value
ComboBox1.AddItem strTemp
adoRs.MoveNext
Loop
adoRs.Close



2) If you want three distinct colums in the ComboBox, her eis one way, there may be others.

Dim i as integer
Do Until adoRs.EOF
ComboBox1.AddItem
ComboBox1.List(i,0) = adoRs("JobPK&quot.Value
ComboBox1.List(i,1) = adoRs("JobNumber&quot.Value
ComboBox1.List(i,2) = adoRs("JobDescription&quot.Value
adoRs.MoveNext
i = i + 1
Loop
adoRs.Close


I did not test this, but it should work.

Scott

 

[gnasher]

Thanks all - works like a treat!

 

[gnasher]

Thank-you, for the record, here is the final code:

[vb]
Dim intColumnCount As Integer
Dim strProjectDetails, strJobNumber As String
intColumnCount = 0

Do Until recs.EOF

strProjectDetails = IIf(IsNull(recs!projectdetails), &quot;<NO DETAILS ENTERED!>&quot;, recs!projectdetails)
strJobNumber = IIf(IsNull(recs!JobNumber), &quot;<NO DETAILS ENTERED!>&quot;, recs!JobNumber)

frmJobDetails.cboJobnumber.AddItem
frmJobDetails!cboJobnumber.List(intColumnCount, 0) = recs!jobdetailspk
frmJobDetails!cboJobnumber.List(intColumnCount, 1) = StrConv(strJobNumber, vbUpperCase)
frmJobDetails!cboJobnumber.List(intColumnCount, 2) = StrConv(strProjectDetails, vbUpperCase)
recs.MoveNext
intColumnCount = intColumnCount + 1

Loop
[/vb]