Hi Gary, Access 2000 solution below:
First, you'll need a table like the following. I tried several field types and filled in the ones that came up in my sample table. You can modify to suit your needs:
<table border="1"><col width="75"><col width="124"><col width="78"><tr height="17" style="height:12.75pt"><td height="17" width="75" style="height:12.75pt;width:56pt">fldTypeNum</td><td width="124">fldEnumDesc</td><td width="78" >fldTypeDesc</td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>0</td><td>adEmpty</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>2</td><td>adSmallInt</td><td>Integer</td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>3</td><td>adInteger</td><td>Long Integer</td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>4</td><td>adSingle</td><td>Single</td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>5</td><td>adDouble</td><td>Double</td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>6</td><td>adCurrency</td><td>Currency</td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>7</td><td>adDate</td><td>Date</td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>8</td><td>adBSTR</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>9</td><td>adIDispatch</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>10</td><td>adError</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>11</td><td>adBoolean</td><td>Yes/No</td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>12</td><td>adVariant</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>13</td><td>adIUnknown</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>14</td><td>adDecimal</td><td>Decimal</td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>16</td><td>adTinyInt</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>17</td><td>adUnsignedTinyInt</td><td>Byte</td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>18</td><td>adUnsignedSmallInt</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>19</td><td>adUnsignedInt</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>20</td><td>adBigInt</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>21</td><td>adUnsignedBigInt</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>64</td><td>adFileTime</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>72</td><td>adGUID</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>128</td><td>adBinary</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>129</td><td>adChar</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>130</td><td>adWChar</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>131</td><td>adNumeric</td><td>Decimal</td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>132</td><td>adUserDefined</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>133</td><td>adDBDate</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>134</td><td>adDBTime</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>135</td><td>adDBTimeStamp</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>136</td><td>adChapter</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>138</td><td>adPropVariant</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>139</td><td>adVarNumeric</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>200</td><td>adVarChar</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>201</td><td>adLongVarChar</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>202</td><td>adVarWChar</td><td>Text</td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>203</td><td>adLongVarWChar</td><td>Memo</td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>204</td><td>adVarBinary</td><td x:str></td></tr><tr height="17" style="height:12.75pt"><td height="17"style="height:12.75pt" x:num>205</td><td>adLongVarBinary</td><td x:str></td></tr></table>
The reason for this table is that the field type is output as a number. I named my table tblFieldTypes (I have pasted a comma-delimited text file below - if you copy this into a text file, you can import it directly into a new Access table in your database).
Ok, now in a regular module* paste the following code. Note that I changed my ComboBox to a ListBox so that I could see both columns at the same time. You can put them in a ComboBox, but a CB will only show the first column when you are not "dropping down" the CB (I didn't change the name, so my LB is named Combo0):
<hr align=left width=500><font color='#000080'><pre><font color='#000000'><font color='#000080'>Option</font> <font color='#000080'>Compare</font> Database
<font color='#000080'>Option</font> <font color='#000080'>Explicit</font>
<font color='#000080'>Function</font> GetTableFieldTypes(strTblName <font color='#000080'>As</font> String) <font color='#000080'>As</font> <font color='#000080'>String</font>
<font color='#000080'>Dim</font> cnn <font color='#000080'>As</font> ADODB.Connection
<font color='#000080'>Dim</font> rst <font color='#000080'>As</font> ADODB.Recordset
<font color='#000080'>Dim</font> fld <font color='#000080'>As</font> ADODB.Field
<font color='#000080'>Dim</font> strRowSource <font color='#000080'>As</font> <font color='#000080'>String</font>
<font color='#000080'>Set</font> cnn = New ADODB.Connection
<font color='#000080'>Set</font> cnn = CurrentProject.Connection
<font color='#000080'>Set</font> rst = New ADODB.Recordset
rst.Open "Select * FROM " & strTblName, cnn, adOpenStatic
<font color='#000080'>With</font> rst
<font color='#000080'>For</font> <font color='#000080'>Each</font> fld In .Fields
<font color='#000080'>ReDim</font> <font color='#000080'>Preserve</font> strArray(1 To 2, 1 To lngI)
strRowSource = strRowSource & fld.Name & ";" & DLookup("[fldTypeDesc]", _
"tblFieldTypes042203", "[fldTypeNum] = " & fld.Type) & ";"
<font color='#000080'>Next</font> fld
<font color='#000080'>End</font> <font color='#000080'>With</font>
GetTableFieldTypes = strRowSource
rst.Close
<font color='#000080'>Set</font> rst = Nothing
<font color='#000080'>Set</font> cnn = Nothing
<font color='#000080'>End</font> <font color='#000080'>Function</font>
</font></pre>
<hr align=left width=500><font color='#000080'>
And then in one of your form's events (Open, etc.), put something like this:
<pre><font color='#000000'><hr align=left width=500><font color='#000080'>Private</font> <font color='#000080'>Sub</font> Form_Open(Cancel <font color='#000080'>As</font> Integer)
Me.Combo0.RowSource = GetTableFieldTypes
<font color='#000080'>End</font> <font color='#000080'>Sub</font>
<hr align=left width=500><font color='#000080'></font></pre>
*you can put the above module in the form module instead, or you could even just put the function inside the event in which you assign the values to the list box.
Below is the text file that you can import. Note that if you change the name of the table, you need to change the name in the code as well.
HTH,
Russell
-------------------------------------------------
"fldTypeNum","fldEnumDesc","fldTypeDesc"
0.00,"adEmpty",
2.00,"adSmallInt","Integer"
3.00,"adInteger","Long Integer"
4.00,"adSingle","Single"
5.00,"adDouble","Double"
6.00,"adCurrency","Currency"
7.00,"adDate","Date"
8.00,"adBSTR",
9.00,"adIDispatch",
10.00,"adError",
11.00,"adBoolean","Yes/No"
12.00,"adVariant",
13.00,"adIUnknown",
14.00,"adDecimal","Decimal"
16.00,"adTinyInt",
17.00,"adUnsignedTinyInt","Byte"
18.00,"adUnsignedSmallInt",
19.00,"adUnsignedInt",
20.00,"adBigInt",
21.00,"adUnsignedBigInt",
64.00,"adFileTime",
72.00,"adGUID",
128.00,"adBinary",
129.00,"adChar",
130.00,"adWChar",
131.00,"adNumeric","Decimal"
132.00,"adUserDefined",
133.00,"adDBDate",
134.00,"adDBTime",
135.00,"adDBTimeStamp",
136.00,"adChapter",
138.00,"adPropVariant",
139.00,"adVarNumeric",
200.00,"adVarChar",
201.00,"adLongVarChar",
202.00,"adVarWChar","Text"
203.00,"adLongVarWChar","Memo"
204.00,"adVarBinary",
205.00,"adLongVarBinary",
