asked on

Going from field values to Me.Controls() text boxes - why does the variable comes up empty?

Need help with the following VBA coding that has a variable that comes up empty.

I'm trying to convert from reading field values directly to text box - Me.Control values - but the dblWorkHours variable comes up empty or blank in the second version.

Currently - dblWorkHours will come up and read the correct values with this version:

Set rstTemp = CurrentDb.OpenRecordset("tempTimesheetWeek")
   With rstTemp
      .MoveLast
      .MoveFirst
      lngCount = .RecordCount
      ''debug.Print lngCount & " records to write"
      
      If lngCount > 0 Then
         'Attempt to find matching record in tblTimeSheetData
         'Create or edit one record in tblTimeSheetData for each weekday
         'that has hours worked
         Do While Not .EOF
         
            lngJobTicketID = Nz(![JobTicket], 0)
            lTimeTypeID = Nz(![TimeTypeID])
            If lTimeTypeID = 0 Then lTimeTypeID = 1
            sDesc = Nz(![WorkDescription], "")
            lProjectID = Nz(![JobTicket])
            
            For i = 1 To 8
            
              Select Case i
              
              Case 1
                'if value is blank ..
                'don't disturb the hours for that previous week also
                
                dblWorkHours = Nz(![01WorkHours])
              
              Case 2
                dblWorkHours = Nz(![02WorkHours])
              
              Case 3
                dblWorkHours = Nz(![03WorkHours])
                
              Case 4
                dblWorkHours = Nz(![04WorkHours])
              Case 5
                dblWorkHours = Nz(![05WorkHours])
                
              Case 6
                dblWorkHours = Nz(![06WorkHours])
                
              Case 7
                dblWorkHours = Nz(![07WorkHours])
                
              Case 8
              
                'if value is blank ..
                'don't disturb the hours for that previous week also
                
                dblWorkHours = Nz(![08WorkHours])
              
              End Select

Open in new window

However when I try to shorten the code and go at the text box values - dblWorkHours always comes up blank.

After giving this question some thought - I now realize that going through the text boxes won't work - I have to do it through the recordset.
Thus how do I change the below to review the field values?

I need it to do so because I'm going from a subform from 8 fields to 36 and repeating Case Select for each one is not efficient.

Set rstTemp = CurrentDb.OpenRecordset("tempTimesheetMonth")
   With rstTemp
      .MoveLast
      .MoveFirst
      lngCount = .RecordCount
      sHeader = "Writing data..."
      sStatus = "Total records to assign hours: " & lngCount
      lWidth = lWidth + 10
      Call Forms("fProgress").UpdateStatus(sStatus, lWidth, sHeader)
      DoEvents
      ''debug.Print lngCount & " records to write"
      
      If lngCount > 0 Then
          Do While Not .EOF
         
            lngJobTicketID = Nz(![JobTicket], 0)
            lTimeTypeID = Nz(![TimeTypeID])
            If lTimeTypeID = 0 Then lTimeTypeID = 1
            sDesc = Nz(![WorkDescription], "")
            lProjectID = Nz(![JobTicket])
            
            For i = 1 To 36
            
              sCounter = ""
              If i < 10 Then
                sCounter = "0"
              End If
              sCounter = sCounter & Trim(Str(i))
              
              sControlHrs = "txt" & sCounter & "WorkHours"
              dblWorkHours = Nz(Me.Controls(sControlHrs), 0)

Open in new window

Martin Liss

Try this.

Dim bMatch As Boolean
Set rstTemp = CurrentDb.OpenRecordset("tempTimesheetWeek")
   With rstTemp
      .MoveLast
      .MoveFirst
      lngCount = .RecordCount
      ''debug.Print lngCount & " records to write"
      
      If lngCount > 0 Then
         'Attempt to find matching record in tblTimeSheetData
         'Create or edit one record in tblTimeSheetData for each weekday
         'that has hours worked
         Do While Not .EOF
         
            lngJobTicketID = Nz(![JobTicket], 0)
            lTimeTypeID = Nz(![TimeTypeID])
            If lTimeTypeID = 0 Then lTimeTypeID = 1
            sDesc = Nz(![WorkDescription], "")
            lProjectID = Nz(![JobTicket])
            
            bMatch = False
            
            For i = 1 To 8
              If bMatch Then
                 Exit For
              End If
              Select Case i
              
              Case 1
                'if value is blank ..
                'don't disturb the hours for that previous week also
                
                dblWorkHours = Nz(![01WorkHours])
                bMatch = True
              Case 2
                dblWorkHours = Nz(![02WorkHours])
                bMatch = True
              Case 3
                dblWorkHours = Nz(![03WorkHours])
                bMatch = True
              Case 4
                dblWorkHours = Nz(![04WorkHours])
                bMatch = True
              Case 5
                dblWorkHours = Nz(![05WorkHours])
                bMatch = True
              Case 6
                dblWorkHours = Nz(![06WorkHours])
                bMatch = True
              Case 7
                dblWorkHours = Nz(![07WorkHours])
                bMatch = True 
              Case 8
              
                'if value is blank ..
                'don't disturb the hours for that previous week also
                
                dblWorkHours = Nz(![08WorkHours])
                bMatch = True
              End Select

Open in new window

ASKER CERTIFIED SOLUTION

stephenlecomptejr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fabrice Lambert

If i < 10 Then
    sCounter = "0"
End If
sCounter = sCounter & i

Open in new window

This is easilly replaced by:

sCounter = Format(i, "00")

Open in new window

Finally, give up hungarian notation, it provide Nothing usefull.
(last time I checked, nobody was calling you HomoSapienSapienMale_stephenlecomptejr).