问题1:往CombBox1中添加格式为“YY-MM”的日期,我用的笨方法是: With CombBox1
CombBox1.AddItem Format(Now, "YY") & "-" & "01"
......
CombBox1.AddItem Format(Now, "YY") & "-" & "12"
End With还有什么简单的方法吗?请高手明示。问题2:往CombBox_2......CombBox_n中分别添加表A......表N中某个字段的数据,我用的方法是使用多个实例,很占时间也笨得太有水平:Call OpenConn '添加客户类型
rs.Open "select 字段名 from 表A", cn, 1, 1
rs.MoveFirst
Do While Not rs.EOF
CombBox_2.AddItem rs.Fields("字段名")
rs.MoveNext
Loop
Call CloseConn Call OpenConn '添加产品型号
rs.Open "select 字段名 from 表B", cn, 1, 1
rs.MoveFirst
Do While Not rs.EOF
CombBox_3.AddItem rs.Fields("字段名")
rs.MoveNext
Loop Call CloseConn
Call OpenConn '添加区域
rs.Open "select 字段名 from 表N", cn, 1, 1
rs.MoveFirst
Do While Not rs.EOF
CombBox_n.AddItem rs.Fields("字段名")
rs.MoveNext
Loop
Call CloseConn如果换用例如rs.Open "select 表A,字段名,表B,字段名,表N,字段名 from 表A,表B,表N", cn, 1, 1这样的语句,只用一个实例,我应该修改过程中的什么地方呢?
CombBox1.AddItem Format(Now, "YY") & "-" & "01"
......
CombBox1.AddItem Format(Now, "YY") & "-" & "12"
End With还有什么简单的方法吗?请高手明示。问题2:往CombBox_2......CombBox_n中分别添加表A......表N中某个字段的数据,我用的方法是使用多个实例,很占时间也笨得太有水平:Call OpenConn '添加客户类型
rs.Open "select 字段名 from 表A", cn, 1, 1
rs.MoveFirst
Do While Not rs.EOF
CombBox_2.AddItem rs.Fields("字段名")
rs.MoveNext
Loop
Call CloseConn Call OpenConn '添加产品型号
rs.Open "select 字段名 from 表B", cn, 1, 1
rs.MoveFirst
Do While Not rs.EOF
CombBox_3.AddItem rs.Fields("字段名")
rs.MoveNext
Loop Call CloseConn
Call OpenConn '添加区域
rs.Open "select 字段名 from 表N", cn, 1, 1
rs.MoveFirst
Do While Not rs.EOF
CombBox_n.AddItem rs.Fields("字段名")
rs.MoveNext
Loop
Call CloseConn如果换用例如rs.Open "select 表A,字段名,表B,字段名,表N,字段名 from 表A,表B,表N", cn, 1, 1这样的语句,只用一个实例,我应该修改过程中的什么地方呢?
private sub Init_Date_From_Table(byval FField as string,byval tablename as string,optional filter as string)
dim strsql as string
on error goto err
call OpenConn
strsql = "select " & FField & " from " & tablename & " where 1 = 1 "
select case len(filter)
case 0
case else
strsql = strsql & filter
end select
rs.open strsqlcall CloseConn
exit sub
err:
end sub
For i = 1 To Format(Date, "mm")
ZQComb.AddItem Format(Now, "YY") & "-" & Format(i, "00")
Next i
或
For i = 1 To 12
ZQComb.AddItem Format(Now, "YY") & "-" & Format(i, "00")
Next i
应该没问题吧,但是我发现怎么For i = 1 To Format(Date, "mm")比 For i = 1 To 12更占资源呢?
这两个循环的结果会有不同,假设运行程序的系统时间是准确的。那么,第一个循环只能添加从今年1月到当前月的项目。比如今天是2008年11月,那么此段代码只能添加到"08-11"。而第二段总是从1月到12月,不知楼主想实现哪一个?
还好整个循环最多只有12次,否则你应该改写一下,以减少计算次数。
Dim strYear as string
dim i as longstrYear=Format(Date,"YY") & "-"For i=1 to 12
ZQComb.AddItem strYear & Format(i, "00")
Next如果你不想添加12个月,只是想添加到当前月,可以这样:
Dim strYear as string
Dim m as long
dim i as longstrYear=Format(Date,"YY") & "-"
m=clng(month(date,m))
For i=1 to m
ZQComb.AddItem strYear & Format(i, "00")
Next
'所以能用Long的地方尽量用,重复使用相同的数据能用变量尽量用,重复用函数来取同样的值并不是很好。
Dim YearStr As String
Dim ForCount As Long
Dim NowInfo As Date
Dim i As Long
NowInfo = Now
YearStr = Right(Year(NowInfo), 2)
ForCount = Month(NowInfo)
For i = 1 To ForCount
ZQComb.AddItem YearStr & "-" & Format(i, "00")
Next i
2、建议你采用这种结构,这样做避免反复连接数据库操作。Dim cn As Connection '作为整个窗口的变量,当然,如果你整个程序都是用一个数据库,可以在模块中用Public定义Private Sub Command1_Click()
CombBox_2.Clear
rs.Open "select 字段名 from 表A", cn, 1, 1
rs.MoveFirst
Do While Not rs.EOF
CombBox_2.AddItem rs.Fields("字段名")
rs.MoveNext
Loop
rs.Close CombBox_3.Clear
rs.Open "select 字段名 from 表B", cn, 1, 1
rs.MoveFirst
Do While Not rs.EOF
CombBox_3.AddItem rs.Fields("字段名")
rs.MoveNext
Loop
rs.Close CombBox_n.Clear
rs.Open "select 字段名 from 表N", cn, 1, 1
rs.MoveFirst
Do While Not rs.EOF
CombBox_n.AddItem rs.Fields("字段名")
rs.MoveNext
Loop
rs.Close
End SubPrivate Sub Form_Load()
'为了避免没事就去连接数据库,启动连接一次就可以了
Call OpenConn '添加客户类型
End SubPrivate Sub Form_Terminate()
'窗口卸载时关闭数据库连接
Call CloseConn
End SubPrivate Sub OpenConn()
Set cn = New Connection
cn.Open "你的SQL连接语句"
End SubPrivate Sub CloseConn()
If cn.State <> 0 Then cn.Close
Set cn = Nothing
End Sub
Call OpenConn '添加客户类型
rs.Open "select " & 字段名 & " from " & 表名, cn, 1, 1
rs.MoveFirst
Do While Not rs.EOF
Comb名.AddItem rs.Fields(字段名)
rs.MoveNext
Loop
Call CloseConn
end sub
然后进行调用:
Myaddcomboxitem 字段名, 表名, 要显示该字段的Combbox控件名
Dim sql As String
Dim i As Integersql = "select distinct 经办人 from 出库 order by 经办人 desc" '查询出出库表中的经办人名单
rs_find.CursorLocation = adUseClient
rs_find.Open sql, conn, adOpenKeyset, adLockPessimistic
If rs_find.EOF = False Then '添加经办人
With rs_find
Do While Not .EOF
Combo1.AddItem .Fields("经办人")
.MoveNext
Loop
End With
End If
rs_find.Close
sql = "select distinct 品名 from 出库 order by 品名 desc"
rs_find.CursorLocation = adUseClient
rs_find.Open sql, conn, adOpenKeyset, adLockPessimistic
If rs_find.EOF = False Then '添加品名
With rs_find
Do While Not .EOF
Combo2.AddItem .Fields("品名")
.MoveNext
Loop
End With
End IfFor i = 2000 To 2015 '添加年份
comboy.AddItem i
Next i
For i = 1 To 12 '添加月份
combom.AddItem i
Next i
For i = 1 To 31 '添加日期
combod.AddItem i
Next i
End Sub
InitCombos Array(CombBox_2, CombBox_3, ... CombBox_N), _
Array("表A", "表B", ... "表N")Private Sub InitCombos(aCombos() As Variable, aTables() As Variable)
Dim cbo As ComboBox, i As Long, rs As ADODB.Recordset
Call OpenConn
Set rs = New ADODB.Recordset
For i = 0 To UBound(aCombos)
Set cbo = aCombos(i)
cbo.Visible = False '数据量大时不显示可以加快速度
cbo.Clear
rs.Open "select 字段名 from " & aTables(i), cn, 1, 1
Do While Not rs.EOF
cbo.AddItem rs.Fields("字段名")
rs.MoveNext
Loop
rs.Close
cbo.Visible = True
Next
Call CloseConn
End Sub
得到你不止一次的指点了,谢谢!
Private Sub Form_Load()
'为了避免没事就去连接数据库,启动连接一次就可以了
Call OpenConn '添加客户类型
End Sub
如果软件一天要连续使用近12小时的话,连接一直打开会不会造成不必要的系统开销?数据库的性能会不会一次而下降?
看见过UBound的使用,忘记了,真是有用,尤其是在对数组中一些非连续的值进行操作的时候。
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Integer
Dim j As Integer
Dim strSql As String
Dim a() As String
cnn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=YC_SmartCard;Data Source=."
strSql = "select * from A Union All select '-' Union All select * from B"
Set rs = cnn.Execute(strSql)
While Not rs.EOF
If rs.Fields(0).Value = "-" Then
j = j + 1
Else
Combo1(j).AddItem rs.Fields(0).Value
End If
rs.MoveNext
Wend
End Sub