Public Sub d2_mysql登録uriage2()
Dim SelCmd As String '選択用のSQLステートメント
Dim DelCmd As String '削除用のSQLステートメント
Dim n As Integer '注文明細の個数
Dim i As Integer 'カウンタ
'データベースの接続
Call a02_InODBC
'画面を固定
Application.ScreenUpdating = False
For ii = 1 To 2
Select Case ii
Case 1
'入力したデータ数を調べる
n = Application.WorksheetFunction.CountA(Range("h2:h11"))
If n = 0 Then
MsgBox "データがないため登録できません", vbOKOnly, "売上明細"
Exit Sub
End If
'新規登録
SelCmd = "SELECT * FROM uriage2"
rs.Open SelCmd, cn, adOpenKeyset, adLockOptimistic
For i = 0 To n - 1
rs.AddNew
rs!key1 = Range("b" & 2 + i).Value
rs!ngp = Range("c" & 2 + i).Value
rs!kikai = Range("d" & 2 + i).Value
rs!com = Range("e" & 2 + i).Value
rs!Name = Range("f" & 2 + i).Value
rs!uritan = Range("g" & 2 + i).Value
rs!renban = Range("h" & 2 + i).Value
rs!uriko = Range("i" & 2 + i).Value
rs!uriage = Range("j" & 2 + i).Value
On Error GoTo 警告
rs.Update
Next i
MsgBox Chr(13) & "総数" & i, vbOKOnly, "Case 1" 'case2へ続く
Case 2
'入力したデータ数を調べる
n = Application.WorksheetFunction.CountA(Range("h14:h23"))
If n = 0 Then
MsgBox "データがないため登録できません", vbOKOnly, "売上明細"
Exit Sub
End If
'新規登録
SelCmd = "SELECT * FROM uriage2"
'rs.Open SelCmd, cn, adOpenKeyset, adLockOptimistic
For i = 0 To n - 1
rs.AddNew
rs!key1 = Range("b" & 14 + i).Value
rs!ngp = Range("c" & 14 + i).Value
rs!kikai = Range("d" & 14 + i).Value
rs!com = Range("e" & 14 + i).Value
rs!Name = Range("f" & 14 + i).Value
rs!uritan = Range("g" & 14 + i).Value
rs!renban = Range("h" & 14 + i).Value
rs!uriko = Range("i" & 14 + i).Value
rs!uriage = Range("j" & 14 + i).Value
On Error GoTo 警告
rs.Update
Next i
MsgBox Chr(13) & "総数" & i, vbOKOnly, "Case 2"
End Select
Next
On Error GoTo 0
'後処理
Call a09_OutODBC
MsgBox "登録しました", vbOKOnly, "成功です"
'画面の固定を解除
Application.ScreenUpdating = True
Exit Sub
警告:
MsgBox "総数 " & i & Chr(13) & "key1重複その他エラー ", vbOKOnly, "登録できません!"
'後処理
Call a09_OutODBC
'画面の固定を解除
Application.ScreenUpdating = True
End Sub