Full Version: Combobox Add Item
UtterAccess Forums > Microsoft® Access > Access Forms
bin
hi every one,
Now I am making a combobox when drop down It will list all the name of tables in Database (User).
Ocan get all the name of tables from the database
but I do not know how to add it in the combobox.
Pls help me
This is my code
Private Sub Form_Load()

Dim Cnn_Db As Connection 'Connection
Dim Rec_Temp As Recordset
Dim Str_SQL As String 'SQL command
Dim Str_Item As String
Dim Lng_Cnt As Long 'Loop counter
Dim numField As Long 'Number of field in a table
Dim intloop As Integer
Set Cnn_Db = CurrentProject.Connection

Set Rec_Temp = New ADODB.Recordset

Str_SQL = "Select Table_Name from INFORMATION_SCHEMA.TABLES where table_Type='BASE TABLE' and Table_Name <> 'dtProperties'"
Rec_Temp.Source = Str_SQL
Rec_Temp.CursorType = adOpenStatic
Rec_Temp.LockType = adLockOptimistic
Rec_Temp.ActiveConnection = Cnn_Db
Rec_Temp.Open

While Not Rec_Temp.EOF

Me.Combo_CSV.AddItem.Fields (0)
Rec_Temp.MoveNext
Wend
End Sub
thank you very much
bin
RoyVidar
The AddItem method of combos and lists became available starting with version 2002 (xp).
The syntax for adding one item to a combo/list would be something like
Me!Combo_CSV.AddItem Rec_Temp.Fields (0)
For prior versions, you'd need to concatenate the rowsourcestring someway, either:
dim sRowSource s string
do while not Rec_Temp.eof
sRowSource=sRowSource & Rec_Temp.Fields (0) & ";"
Rec_Temp.movenext
loop
Me!Combo_CSV.rowsource=sRowSource
FOr you could try the getstring method of the recordset:
Me!Combo_CSV.rowsource = Rec_Temp.getstring(adclipstring,,";",";")
BTW - Welcome to UA!
bin
Dear Roy-Vidar,
thank you very much for your help
Oalready did it.
Best Regards
NTPhong
thephong
Dear Roy-Vidar,
When I apply your code to my form,
The error is that, the string is too long, the limit is 128
any suggest?
Best Regards
NTPhong
RoyVidar
What version is this? In access 2000, the rowsource limit is 2048 characters, and in 2002+ it is 32 750 charachers.
ost your current code, and tell whic line it bombs on, which event you're using...
thephong
Dear Roy-Vidar ,
Onow use access 2000 with VBA and Microsoft Sql Server2000 for creating Database.
I want to design a form to put the Data in a table to CSV file. so that I create a combobox that will select all the tables in database for user to choose which one they want to put into CSV file....
I attach my project for you to check it OK.
Please kindly take a look and tell me what wrong with it.
Best Regards
NTPhong
Edited by: thephong on Mon Oct 18 22:45:49 EDT 2004.
RoyVidar
Sorry, I don't know much about ADP's, perhaps ADP's have different limitations?
see you have "Table/View/StoredProc" selected as RowSourceType, try changing to "Value List" (which is what we're trying to build here), perhaps the limitiation is that names of table, views or stored procedures cannot exceed 128?
If it doesn't work, I'd suggest trying to base the combo on a view or SP in stead, but as I said, I know to little about ADP's...
thephong
Hi Roy-Vidar
Now i am doing a prgram that export data from tables to excel file.
for example, My database has 20 tables. and I need to export 5 tables in 20 talbes into excel file.
When I click on OK button and with the path folder input before for example *(c:ExcelExport\table1; table2..... ) I will have 5 excel files correlative with 5 tables in the folder.
Any suggest???
Best Regards
NTPhong
thephong
Dear Roy-Vidar
Now I have a field 'receiveDate' type Int. for example :20041022
But in the form user input the date following the format 2004/10/22.
I want to compare the 'receiveDate' with the dat which user input.
How can i do, any suggest
Best regards
NTPhong
NoahP
Roy,
never use Value List. Too big a pain to change every instance of a combo box throughout the db. Why wouldn't you want to use a table/query as the RowSource for a combo box?
I'm just curious here....
Noah
RoyVidar
Quote NoahP "I never use Value List. Too big a pain to change every instance of a combo box throughout the db." - I've never had any problems with them, they are perfectly simple and easy to handle once one understand them.
utoe NoahP "Why wouldn't you want to use a table/query as the RowSource for a combo box?" - please reread my third reply. FYI - views and SP (Stored Procedure) are "roughly" the ADP/SQL-server equivalence of stored queries in mdb's.
What makes me curious is why you choose to question something, which is answered in the thread, in stead of assisting bin/thephong? Does it in any way relate to this?
bin/thephong
I think you'll stand a better chance of getting an answer to your questions if you post a new question. It seems neither me nor NoahP have the necessary experience to help you further here, sorry.
Good Luck!
NoahP
No Roy, it's not related to anything else. It was a simple question. I understand value lists. I just wanted your take on them as you've appeared to be knowledgeable. Sorry to have bothered you. Just forget it.
oah
thephong
Hi Noah ,
got this code from the internet. this function use to put data from table into Excel file. But it's wrong when the number of fileds in the table is more than 26 (>26). It is Ok if the number of fileds of the table is less than 26 (<26). Could you correct it for me, and please kindly explain this code to me OK.
Omean write some comment for me.
Thank You very much
NTPhong
Public Function SetNameExcelCol(ByVal numcol As Long) As String()
Dim i As Long
ReDim x(numcol) As String
Dim inchar As String
Dim low, up, t1, t2 As Long
low = 1
up = numcol
inchar = ""
If numcol <= 26 Then
x1:
For i = low To up
x(i) = inchar + Chr(i - low + 65)
Next i
If numcol < 26 Then
GoTo endsub
Else
GoTo x2
End If
Else
x2:
t1 = numcol Mod 26 'Modulo
If t1 = 0 Then t1 = 26
t2 = (numcol - t1) / 26 'devide
up = numcol - 1
low = numcol - t1
If t2 >= 1 Then
inchar = Chr(64 + t2) 'A...
Else
inchar = ""
End If
numcol = numcol - t1
GoTo x1
End If
endsub:
SetNameExcelCol = x
End Function
Public Sub Exp2Exl(rs As ADODB.Recordset)
'
On Error GoTo [censored]
If rs Is Nothing Then Exit Sub
If rs.BOF Then Exit Sub
Dim Eapp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim numcol As Long
Set Eapp = New Excel.Application
On Error Resume Next
Eapp.Workbooks.Add
rs.MoveFirst
Set wb = Eapp.ActiveWorkbook
Set ws = wb.ActiveSheet
Eapp.Visible = True
numcol = rs.Fields.count
Dim i, j As Integer
ReDim a(numcol) As String
a = SetNameExcelCol(numcol)
'column name
For i = 1 To numcol
wb.Worksheets("sheet1").Range(a(i) & CStr(1)).value = rs.Fields(i - 1).name
Next i
'format excel col
For i = 1 To numcol
If IsDate(rs.Fields(i - 1).value) Then
ws.Range(a(i) + ":" + a(i)).NumberFormat = "dd/MMM/yyyy"
'default as text
ElseIf Not IsNumeric(rs.Fields(i - 1).value) Then
ws.Range(a(i) + ":" + a(i)).NumberFormat = "@"
End If
Next i
j = 2
While Not rs.EOF
For i = 1 To numcol
'wb.Worksheets("sheet1").Range(a(i) & CStr(j)).Value2 = rs.Fields(i - 1).value
ws.Range(a(i) & CStr(j)).value = rs.Fields(i - 1).value
Next i
j = j + 1
rs.MoveNext
Wend
Set ws = Nothing
Set wb = Nothing
Set Eapp = Nothing
Exit Sub
[censored]:
MsgBox Err.Description
End Sub
PaulBrand
You can write a query to retrieve table names from the db. you need to view system tables in the db window (Tools>Options), then set criteria for the MSysObjects ID to filter your data.
se this query as you recordsource and it will always return all tables in the db.
HTH
Paul
thephong
Hi every one,
ow, I making a "For loop " and I want to increase a FromDate
for example FromDate = 2004/08/10 ---> 2004/09/10.
Is there any function to make FromDate to return for example :
2004/08/10 ----> 2004/08/01
2004/08/10 ----> 2004/08/31
Any suggest
Thanks
NTPhong
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.