Close...
Try this:
Code:
Option Compare Database ' <<-- these two lines should be at the top of EVERY code module
Option Explicit ' <<-- these two lines should be at the top of EVERY code module
Private Sub Form_Load()
'==================================
'enter previous day ending number into current day start field
'==================================
Dim r As DAO.Recordset
Dim sSQL As String
Dim theDate As Date
theDate = Me.tbShiftDate
'query to get yesterday's ending numbers
sSQL = "SELECT [DW Finish], [D01A Finish], [D01B Finish], [D01C Finish]"
sSQL = sSQL & " FROM tbl_DW_Log_Archive"
sSQL = sSQL & " WHERE ShiftDate = #" & (Me.tbShiftDate - 1) & "# AND Shift = 'Nights';"
' Debug.Print sSQL
Set r = CurrentDb.OpenRecordset(sSQL)
If Not (r.BOF And r.EOF) Then
'don't want to overwrite existing entry in DW Start
If Len(Trim(Me.[DW Start] & "")) = 0 Then
Me.[DW Start] = Nz(r![DW Finish], 0)
End If
'don't want to overwrite existing entry in [D01A Start]
If Len(Trim(Me.[D01A Start] & "")) = 0 Then
Me.[D01A Start] = Nz(r![D01A Finish], 0)
End If
'don't want to overwrite existing entry in [D01B Start]
If Len(Trim(Me.[D01B Start] & "")) = 0 Then
Me.[D01B Start] = Nz(r![D01B Finish], 0)
End If
'don't want to overwrite existing entry in [D01C Start]
If Len(Trim(Me.[D01C Start] & "")) = 0 Then
Me.[D01C Start] = Nz(r![D01C Finish], 0)
End If
End If
'clean up
r.Close
Set r = Nothing
End Sub
You really should remove any spaces in object names.
If you want to separate letters, use
[DW_Finish]
[D01A_Finish]
[D01B_Finish]
[D01C_Finish]
SPACES are a Pain in the neck, only 3 feet lower (for me).