有例子吗?我发觉用循环写入的话,好像不行,有好办法吗?谢谢 With vsflexgrid1 For intRow = 1 To .Rows - 1 For intCol = 0 To .Cols - 1
.row = intRow .col = intCol If Trim(.Text) <> "" Then rs.Fields(intCol).Value = Trim(.Text) rs.update ' End If
End If Next intCol
Next intRow
End With
'我一般的做法是这样的,输入数据的时候不做保存处理,只有用户按下保存按纽时才保存数据,对于修改的情况,如果明细表中如果有id的话,就用update语句替换,如果明细表中没有id字段,就采取删除原来的数据,然后按照此次用户的修改做新增保存. 对于删除,在用户录入时,我是通过设置隐隐藏来实现,在保存时才判断,如果是隐藏行,就将对应记录删除(有id的情况,无id不存在)下面是我常用的方法 '保存改变 Private Function SaveChange!(Optional ByVal AskToSave As Boolean = True) Dim I&, OldSel As SelRange If Tbar.Buttons("save").Enabled = False Then GoTo SaveOk If AskToSave Then Select Case MsgBox("是否保存当前数据?", vbYesNoCancel + vbQuestion) Case vbNo GoTo SaveOk Case vbCancel GoTo CancelSave Case Else End Select End If With Vsf .GetSelection OldSel.x1, OldSel.y1, OldSel.x2, OldSel.y2 .SetFocus SendKeys "{TAB}" DoEvents IsLock = True '锁定表的改变 If IdCol = -1 Then I = SaveWithoutId Else I = SaveWithId End If IsLock = False '取消锁定表的改变 If I <> vbOK Then SaveChange = I MainS.CurrentDatabase.JbZl.Requery Exit Function Else For I = .Rows - 1 To 1 Step -1 If .RowHidden(I) Then .RemoveItem I Next I Vsf.SetFocus 'SendKeys "{ESC}" On Error Resume Next .Select OldSel.x1, OldSel.y1, OldSel.x2, OldSel.y2 End If End With SaveOk: If Vsf.Rows = 1 Then Tbar_ButtonClick Tbar.Buttons("add") SaveChange = vbOK MainS.CurrentDatabase.JbZl.Requery Tbar.Buttons("save").Enabled = False Exit Function CancelSave: SaveChange = vbCancel MainS.CurrentDatabase.JbZl.Requery End Function'表中有id的保存方法 Private Function SaveWithId() As Long On Error GoTo SaveErr Dim SqlStr$, SqlHead$, SqlEnd$, ErrMsg$ Dim iRow&, iCol&, maxCol& With MainS.CurrentDatabase.DataBase .BeginTrans SqlHead = " insert into " & TbKmPd SqlEnd = " where " & Vsf.ColKey(IdCol) & "=" maxCol = Vsf.Cols - 1 For iRow = Vsf.FixedRows To Vsf.Rows - 1 If Vsf.RowHidden(iRow) Then SqlStr = "delete from " & TbKmPd & SqlEnd & Vsf.TextMatrix(iRow, IdCol) 'Vsf.RemoveItem iRow Else If Vsf.Cell(flexcpText, iRow, 0, iRow, maxCol) = String(maxCol, vbTab) Then SqlStr = "" Vsf.RowHidden(iRow) = True 'Vsf.RemoveItem iRow Else If Vsf.TextMatrix(iRow, IdCol) = "" Then SqlStr = SaveRowSql(iRow, True) If SqlStr = "" Then GoTo SaveErr1 SqlStr = SqlHead & SqlStr Else If Vsf.RowStatus(iRow) = 2 Then SqlStr = SaveRowSql(iRow, False) If SqlStr = "" Then GoTo SaveErr1 SqlStr = " update " & TbKmPd & " set " & SqlStr & SqlEnd & Vsf.TextMatrix(iRow, IdCol) End If End If End If End If If SqlStr <> "" Then .Execute SqlStr SqlStr = "" End If Next iRow End With SaveOk: MainS.CurrentDatabase.DataBase.CommitTrans SaveWithId = vbOK Exit Function SaveErr: If ErrMsg = "" Then ErrMsg = fGetErrMsg("保存数据时发生下列错误:") MsgBox ErrMsg, 48 SaveErr1: MainS.CurrentDatabase.DataBase.RollbackTrans SaveWithId = vbNo End Function'无id的保存处理 Private Function SaveWithoutId() As Long On Error GoTo SaveErr Dim SqlStr$, SqlHead$, ErrMsg$ Dim iRow&, iCol&, maxCol&
With MainS.CurrentDatabase.DataBase .BeginTrans SqlStr = "truncate table " & TbKmPd .Execute SqlStr SqlHead = "insert into " & TbKmPd maxCol = Vsf.Cols - 1 For iRow = Vsf.FixedRows To Vsf.Rows - 1 If Vsf.RowHidden(iRow) Then 'Vsf.RemoveItem iRow Else If Vsf.Cell(flexcpText, iRow, 0, iRow, maxCol) = String(maxCol, vbTab) Then SqlStr = "" Vsf.RowHidden(iRow) = True 'Vsf.RemoveItem iRow Else SqlStr = SaveRowSql(iRow, True) If SqlStr = "" Then GoTo SaveErr1 SqlStr = SqlHead & SqlStr End If End If If SqlStr <> "" Then .Execute SqlStr SqlStr = "" End If Next iRow If Vsf.Rows = 1 Then Tbar_ButtonClick Tbar.Buttons("add") End With SaveOk: MainS.CurrentDatabase.DataBase.CommitTrans SaveWithoutId = vbOK Exit Function SaveErr: If ErrMsg = "" Then ErrMsg = fGetErrMsg("保存数据时发生下列错误:") MsgBox ErrMsg, 48 SaveErr1: MainS.CurrentDatabase.DataBase.RollbackTrans SaveWithoutId = vbNo End Function
'得到保存SQL语句 Private Function SaveRowSql(ByVal iRow&, Optional ByVal IsAdd As Boolean = True) As String Dim iCol&, fdList$, fdValue$, TempFd$, TempValue$, Bz$, AddFd As Boolean Dim fdtype&
With Vsf For iCol = 0 To .Cols - 1 If .ColHidden(iCol) = False Then TempFd = .ColKey(iCol) TempValue = .TextMatrix(iRow, iCol) Select Case .ColDataType(iCol) Case flexDTDate, flexDTString, flexDTStringC, flexDTStringW Bz = "'" Case Else Bz = "" End Select fdtype = .Cell(flexcpData, .FixedRows - 1, iCol) Select Case fdtype Case 1, 5 If TempValue = "" Then AddFd = Not (IsAdd Or IdCol = -1) Else If fdtype = 5 Then .Row = iRow .Col = iCol If .Cell(flexcpData, iRow, iCol) = -1 Then MsgBox "第" & iRow & "行[" & TempFd & "]无效,请重新选择!", 48 GoTo ChkErr End If End If AddFd = True End If Case 2, 6 If TempValue = "" Then MsgBox "第 " & iRow & "行[" & TempFd & "]的内容不能为空!", 48 GoTo ChkErr Else If fdtype = 6 Then If .Cell(flexcpData, iRow, iCol) = -1 Then MsgBox "第" & iRow & "行[" & TempFd & "]无效,请重新选择!", 48 GoTo ChkErr End If End If AddFd = True End If Case Else End Select If AddFd Then If IsAdd Then fdList = fdList & "," & TempFd fdValue = fdValue & "," & Bz & TempValue & Bz Else fdList = fdList & "," & TempFd & "=" & Bz & TempValue & Bz End If End If End If Next iCol End With If IsAdd Then SaveRowSql = "(" & Mid(fdList, 2) & ") values (" & Mid(fdValue, 2) & ")" Else SaveRowSql = Mid(fdList, 2) End If Exit Function ChkErr: Vsf.Select iRow, iCol, iRow, iCol SaveRowSql = "" End Function
起辅助作用罢了
With vsflexgrid1
For intRow = 1 To .Rows - 1
For intCol = 0 To .Cols - 1
.row = intRow
.col = intCol
If Trim(.Text) <> "" Then
rs.Fields(intCol).Value = Trim(.Text)
rs.update
' End If
End If
Next intCol
Next intRow
End With
对于删除,在用户录入时,我是通过设置隐隐藏来实现,在保存时才判断,如果是隐藏行,就将对应记录删除(有id的情况,无id不存在)下面是我常用的方法
'保存改变
Private Function SaveChange!(Optional ByVal AskToSave As Boolean = True)
Dim I&, OldSel As SelRange
If Tbar.Buttons("save").Enabled = False Then GoTo SaveOk
If AskToSave Then
Select Case MsgBox("是否保存当前数据?", vbYesNoCancel + vbQuestion)
Case vbNo
GoTo SaveOk
Case vbCancel
GoTo CancelSave
Case Else
End Select
End If
With Vsf
.GetSelection OldSel.x1, OldSel.y1, OldSel.x2, OldSel.y2
.SetFocus
SendKeys "{TAB}"
DoEvents
IsLock = True '锁定表的改变
If IdCol = -1 Then
I = SaveWithoutId
Else
I = SaveWithId
End If
IsLock = False '取消锁定表的改变
If I <> vbOK Then
SaveChange = I
MainS.CurrentDatabase.JbZl.Requery
Exit Function
Else
For I = .Rows - 1 To 1 Step -1
If .RowHidden(I) Then .RemoveItem I
Next I
Vsf.SetFocus
'SendKeys "{ESC}"
On Error Resume Next
.Select OldSel.x1, OldSel.y1, OldSel.x2, OldSel.y2
End If
End With
SaveOk:
If Vsf.Rows = 1 Then Tbar_ButtonClick Tbar.Buttons("add")
SaveChange = vbOK
MainS.CurrentDatabase.JbZl.Requery
Tbar.Buttons("save").Enabled = False
Exit Function
CancelSave:
SaveChange = vbCancel
MainS.CurrentDatabase.JbZl.Requery
End Function'表中有id的保存方法
Private Function SaveWithId() As Long
On Error GoTo SaveErr
Dim SqlStr$, SqlHead$, SqlEnd$, ErrMsg$
Dim iRow&, iCol&, maxCol&
With MainS.CurrentDatabase.DataBase
.BeginTrans
SqlHead = " insert into " & TbKmPd
SqlEnd = " where " & Vsf.ColKey(IdCol) & "="
maxCol = Vsf.Cols - 1
For iRow = Vsf.FixedRows To Vsf.Rows - 1
If Vsf.RowHidden(iRow) Then
SqlStr = "delete from " & TbKmPd & SqlEnd & Vsf.TextMatrix(iRow, IdCol)
'Vsf.RemoveItem iRow
Else
If Vsf.Cell(flexcpText, iRow, 0, iRow, maxCol) = String(maxCol, vbTab) Then
SqlStr = ""
Vsf.RowHidden(iRow) = True
'Vsf.RemoveItem iRow
Else
If Vsf.TextMatrix(iRow, IdCol) = "" Then
SqlStr = SaveRowSql(iRow, True)
If SqlStr = "" Then GoTo SaveErr1
SqlStr = SqlHead & SqlStr
Else
If Vsf.RowStatus(iRow) = 2 Then
SqlStr = SaveRowSql(iRow, False)
If SqlStr = "" Then GoTo SaveErr1
SqlStr = " update " & TbKmPd & " set " & SqlStr & SqlEnd & Vsf.TextMatrix(iRow, IdCol)
End If
End If
End If
End If
If SqlStr <> "" Then
.Execute SqlStr
SqlStr = ""
End If
Next iRow
End With
SaveOk:
MainS.CurrentDatabase.DataBase.CommitTrans
SaveWithId = vbOK
Exit Function
SaveErr:
If ErrMsg = "" Then ErrMsg = fGetErrMsg("保存数据时发生下列错误:")
MsgBox ErrMsg, 48
SaveErr1:
MainS.CurrentDatabase.DataBase.RollbackTrans
SaveWithId = vbNo
End Function'无id的保存处理
Private Function SaveWithoutId() As Long
On Error GoTo SaveErr
Dim SqlStr$, SqlHead$, ErrMsg$
Dim iRow&, iCol&, maxCol&
With MainS.CurrentDatabase.DataBase
.BeginTrans
SqlStr = "truncate table " & TbKmPd
.Execute SqlStr
SqlHead = "insert into " & TbKmPd
maxCol = Vsf.Cols - 1
For iRow = Vsf.FixedRows To Vsf.Rows - 1
If Vsf.RowHidden(iRow) Then
'Vsf.RemoveItem iRow
Else
If Vsf.Cell(flexcpText, iRow, 0, iRow, maxCol) = String(maxCol, vbTab) Then
SqlStr = ""
Vsf.RowHidden(iRow) = True
'Vsf.RemoveItem iRow
Else
SqlStr = SaveRowSql(iRow, True)
If SqlStr = "" Then GoTo SaveErr1
SqlStr = SqlHead & SqlStr
End If
End If
If SqlStr <> "" Then
.Execute SqlStr
SqlStr = ""
End If
Next iRow
If Vsf.Rows = 1 Then Tbar_ButtonClick Tbar.Buttons("add")
End With
SaveOk:
MainS.CurrentDatabase.DataBase.CommitTrans
SaveWithoutId = vbOK
Exit Function
SaveErr:
If ErrMsg = "" Then ErrMsg = fGetErrMsg("保存数据时发生下列错误:")
MsgBox ErrMsg, 48
SaveErr1:
MainS.CurrentDatabase.DataBase.RollbackTrans
SaveWithoutId = vbNo
End Function
Private Function SaveRowSql(ByVal iRow&, Optional ByVal IsAdd As Boolean = True) As String
Dim iCol&, fdList$, fdValue$, TempFd$, TempValue$, Bz$, AddFd As Boolean
Dim fdtype&
With Vsf
For iCol = 0 To .Cols - 1
If .ColHidden(iCol) = False Then
TempFd = .ColKey(iCol)
TempValue = .TextMatrix(iRow, iCol)
Select Case .ColDataType(iCol)
Case flexDTDate, flexDTString, flexDTStringC, flexDTStringW
Bz = "'"
Case Else
Bz = ""
End Select
fdtype = .Cell(flexcpData, .FixedRows - 1, iCol)
Select Case fdtype
Case 1, 5
If TempValue = "" Then
AddFd = Not (IsAdd Or IdCol = -1)
Else
If fdtype = 5 Then
.Row = iRow
.Col = iCol
If .Cell(flexcpData, iRow, iCol) = -1 Then
MsgBox "第" & iRow & "行[" & TempFd & "]无效,请重新选择!", 48
GoTo ChkErr
End If
End If
AddFd = True
End If
Case 2, 6
If TempValue = "" Then
MsgBox "第 " & iRow & "行[" & TempFd & "]的内容不能为空!", 48
GoTo ChkErr
Else
If fdtype = 6 Then
If .Cell(flexcpData, iRow, iCol) = -1 Then
MsgBox "第" & iRow & "行[" & TempFd & "]无效,请重新选择!", 48
GoTo ChkErr
End If
End If
AddFd = True
End If
Case Else
End Select
If AddFd Then
If IsAdd Then
fdList = fdList & "," & TempFd
fdValue = fdValue & "," & Bz & TempValue & Bz
Else
fdList = fdList & "," & TempFd & "=" & Bz & TempValue & Bz
End If
End If
End If
Next iCol
End With
If IsAdd Then
SaveRowSql = "(" & Mid(fdList, 2) & ") values (" & Mid(fdValue, 2) & ")"
Else
SaveRowSql = Mid(fdList, 2)
End If
Exit Function
ChkErr:
Vsf.Select iRow, iCol, iRow, iCol
SaveRowSql = ""
End Function