Loop thru Text Values and save to Recordset (1 Viewer)

jmq

Registered User.
Local time
Today, 09:28
Joined
Oct 4, 2017
Messages
87
how do you squeeze this thru looping

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!
 

Attachments

  • TimesheetTable.PNG
    TimesheetTable.PNG
    4.5 KB · Views: 69

Auntiejack56

Registered User.
Local time
Tomorrow, 03:28
Joined
Aug 7, 2017
Messages
175
Hi, I think I can see what you're trying to do. You need the format rs("NameOfField") to do this:

Code:
        rs.AddNew
        For Each c In Me.Controls
            If c.ControlType = acTextBox Then
                If Not (c.Name = "txtID") Then
                    rs(mid(c.name,4)) = c.Value
                End If
            End If
            i = i + 1
        Next c
        rs.Update

I think you'll have to dump the 'With' to do this.

Jack
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:28
Joined
Feb 28, 2001
Messages
26,999
Your question is a bit abstracted and betrays various design issues. For instance, that record is SO not normalized as to be almost painful. (When considering how much typing is going to be required down the line, e.g.)

However, you asked so I'll take a shot at it. You can't do the looping the way you want because the context you used cannot concatenate names. HOWEVER, ...

Code:
Dim Fld as Field
Dim RS as Recordset
Dim txtFld as String
...
If rs.BOF And rs.EOF Then
    rs.AddNew
Else
    rs.Edit
End If
On Error Resume Next
For Each Fld In RS.Fields
    txtFld = Fld.Name
    RS.Fields(txtFld) = Me.Controls( "txt" & txtFld )
Next Fld
RS.Update
...

This will work because you can use the concatenation operator in "collection" context {e.g. Me.Controls()} to name a member of the collection. The error trap catches the cases where there is no control named "txtxxxx" (as, for example, EmpID... just don't name that field's text box with the prefix "txt").

Be it noted that I showed you a way to do what you wanted but also be it noted that you will have trouble down the line with that record layout.
 

jmq

Registered User.
Local time
Today, 09:28
Joined
Oct 4, 2017
Messages
87
Auntiejack56:

I think this will do.
it will look like this
Code:
rs(mon1) = c. value
rs(tue1) = c. value
..

right?

then i'll just manually input the empno right?
 

jmq

Registered User.
Local time
Today, 09:28
Joined
Oct 4, 2017
Messages
87
@ the record layout: if you were the one designing, how would you do the layout?
@ normalised: i thought my table is normalised?
 

Cronk

Registered User.
Local time
Tomorrow, 03:28
Joined
Jul 4, 2013
Messages
2,770
Firstly, you would not have
rs(mon1) = c. value 'this would give a runtime error

You would have
rs("mon1") = c.value

A normalized design would have tblEmployees and a tblWorkTimes (or whatever name) with the latter having fields EmpID, WorkDate, WorkTime
 

jmq

Registered User.
Local time
Today, 09:28
Joined
Oct 4, 2017
Messages
87
so you mean the solution of Auntiejack56 will generate a runtime error?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:28
Joined
May 7, 2009
Messages
19,169
i've seen your posted db and
you are using unbound form.
and you are recording only
one employee at a time on the
timesheet.
so it would be best to just use
SQL insert/update query:

Code:
Private Sub cmdSubmit_Click()
Dim intCount As Integer
Dim strSQL As String
'* search employee if already
'* exists in timesheet
'* if exists, update the record
'* if not add it.
'*
'* first check if already exists
intCount = DCount("*","tbl1Timesheets","EmpNo=" & [txtEmpNo] & " And [StartDate]=#" & _
	Format([txtStart],"mm/dd/yyyy") & "# And [EndDate]=#" & _
	Format([txtEnd],"mm/dd/yyyy") & "#")
If intCount>0 Then
	strSQL = "Update tbl1Timesheets Set " & _
			[Mon1]=" & [Mon1].Value & "," & _
			[Tue1]=" & [Tue1].Value & "," & _
			[Wed1]=" & [Wed1].Value & "," & _
			[Thu1]=" & [Thu1].Value & "," & _
			[Fri1]=" & [Fri1].Value & "," & _
			[Sat1]=" & [Sat1].Value & "," & _
			[Sun1]=" & [Sun1].Value & "," & _
			[Mon2]=" & [Mon2].Value & "," & _
			[Tue2]=" & [Tue2].Value & "," & _
			[Wed2]=" & [Wed2].Value & "," & _
			[Thu2]=" & [Thu2].Value & "," & _
			[Fri2]=" & [Fri2].Value & "," & _
			[Sat2]=" & [Sat2].Value & "," & _
			[Sun2]=" & [Sun2].Value & " Where " & _
			"EmpNo=" & [txtEmpNo] & " And [StartDate]=#" & _
			Format([txtStart],"mm/dd/yyyy") & "# And [EndDate]=#" & _
			Format([txtEnd],"mm/dd/yyyy") & "#"
Else
	strSQL = "Insert Into tbl1Timesheets (EmpNo, StartDate,EndDate," & _
			[Mon1],[Tue1],[Wed1],[Thu1],[Fri1],[Sat1],[Sun1]," & _
			[Mon2],[Tue2],[Wed2],[Thu2],[Fri2],[Sat2],[Sun2]) " & _
			"SELECT " & [txtEmpNo].Value & ",#" & Format([txtStart].Value,"mm/dd/yyyy") & "#," & _
			#" & Format([txtEnd],'mm/dd/yyyy") & "#," & _
			[Mon1].Value & "," & [Tue1].Value & "," & [Wed1].Value & "," & _
			[Thu1].Value & "," & [Fri1].Value & "," & [Sat1].Value & "," & [Sun1].Value & "," & _
			[Mon2].Value & "," & [Tue2].Value & "," & [Wed2].Value & "," & _
			[Thu2].Value & "," & [Fri2].Value & "," & [Sat2].Value & "," & [Sun2].Value
End If
DbEngine(0)(0).Execute strSQL
End Sub
 

jmq

Registered User.
Local time
Today, 09:28
Joined
Oct 4, 2017
Messages
87
So that rs.addnew and rs.edit is best for what kind of operation/process?
 

jmq

Registered User.
Local time
Today, 09:28
Joined
Oct 4, 2017
Messages
87
what's the difference between my previous code "rs.addnew and .edit" with your sql strings?
 

Similar threads

Users who are viewing this thread

Top Bottom