how do you squeeze this thru looping
I've tried this and i get an error MISMATCH
Thanks!
Code:
Private Sub cmdSubmit_Click()
Dim t As TextBox
Set rs = db.OpenRecordset("SELECT ID, EmpNo, StartDate, EndDate, " & _
"Mon1, Tue1, Wed1, Thu1, Fri1, Sat1, Sun1, " & _
"Mon2, Tue2, Wed2, Thu2, Fri2, Sat2, Sun2 " & _
"FROM tbl1Timesheets " & _
"WHERE EmpNo = " & txtEmpNo & _
" AND StartDate = #" & Format(txtStart, "mm/dd/yyyy") & "#;")
'Improve this by looping thru controls
With rs
If rs.BOF And rs.EOF Then
.AddNew
!EmpNo = txtEmpNo
!StartDate = txtStart
!EndDate = txtEnd
!Mon1 = txtMon1
!Tue1 = txtTue1
!Wed1 = txtWed1
!Thu1 = txtThu1
!Fri1 = txtFri1
!Sat1 = txtSat1
!Sun1 = txtSun1
!Mon2 = txtMon2
!Tue2 = txtTue2
!Wed2 = txtWed2
!Thu2 = txtThu2
!Fri2 = txtFri2
!Sat2 = txtSat2
!Sun2 = txtSun2
.Update
MsgBox "Submit successfull!", vbOKOnly + vbInformation, "Message"
Else
.Edit
!EmpNo = txtEmpNo
!StartDate = txtStart
!EndDate = txtEnd
!Mon1 = txtMon1
!Tue1 = txtTue1
!Wed1 = txtWed1
!Thu1 = txtThu1
!Fri1 = txtFri1
!Sat1 = txtSat1
!Sun1 = txtSun1
!Mon2 = txtMon2
!Tue2 = txtTue2
!Wed2 = txtWed2
!Thu2 = txtThu2
!Fri2 = txtFri2
!Sat2 = txtSat2
!Sun2 = txtSun2
.Update
MsgBox "Edit successfull!", vbOKOnly + vbInformation, "Message"
End If
End With
ExitSub:
Set rs = Nothing
Exit Sub
End Sub
I've tried this and i get an error MISMATCH
Code:
'====================================
'== ATTEMP TO LOOP THRU TEXTBOXES ===
'====================================
'Private Sub OpenTimesheetTable()
' Set rs = db.OpenRecordset("SELECT ID, EmpNo, StartDate, EndDate, " & _
' "Mon1, Tue1, Wed1, Thu1, Fri1, Sat1, Sun1, " & _
' "Mon2, Tue2, Wed2, Thu2, Fri2, Sat2, Sun2 " & _
' "FROM tbl1Timesheets " & _
' "WHERE EmpNo = " & txtEmpNo & _
' " AND StartDate = #" & Format(txtStart, "mm/dd/yyyy") & "#;")
'End Sub
'
'Private Sub PopulateTable()
' Dim c As Control
' Dim i As Integer
'
' i = 0
'
' OpenTimesheetTable
'
' With rs
' .AddNew
' For Each c In Me.Controls
' If c.ControlType = acTextBox Then
' If Not (c.Name = "txtID") Then
' .Fields.Count = c.Value
' End If
' End If
' i = i + 1
' Next c
' .Update
' End With
'End Sub
Thanks!