What to do with a no current record query

[lawdy]

The sub works good as long as there are records to locate. But when there isn't, I get a No Current Record error. The internet says the cure is to use dsum with the name of the query. I not sure what the name of the query is. Please help. The code is:


Sub modCal_addJobsToCal() ' put job names on frmCal_2025


Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strJob As String
Dim lngWeek As Long
Dim lngDay As Long
Dim strDay As String
Dim lngLine As Long
Dim strPosition As String
Dim dateDate As Date

For lngWeek = 1 To 1 ' 5
For lngDay = 1 To 7 ' 14


strDay = CStr(lngDay)
Select Case strDay
Case 1, 2, 3, 4, 5, 6, 7, 8, 9: strDay = "0" & strDay
Case Else: strDay = strDay
End Select
For lngLine = 101 To 103 ' 120
dateDate = DateValue(Forms![frmCal_2025].Controls("tbD1" & strDay))
strPosition = "tbJ" & CStr(lngWeek) & CStr(lngDay) & CStr(lngLine)

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT tblCal_2025.fDate, tblCal_2025.fStrLineNo, tblCal_2025.fLngProposalNo, " _
& "tblCal_2025.fTxtService, tblProposal.fTxtPropertyName " _
& "From tblCal_2025 INNER JOIN tblProposal " _
& "ON tblCal_2025.fLngProposalNo = tblProposal.fLngProposalNo " _
& "WHERE (((tblCal_2025.fDate) = #" & dateDate & " # " _
& "AND (tblCal_2025.fStrLineNo) = '" & CStr(lngLine) & "'))" _
& "Order BY tblCal_2025.fDate, tblCal_2025.fStrLineNo", _
dbOpenSnapshot, dbReadOnly)

With rs
Debug.Print "date " & rs!fDate & " ProposalNo " & rs!fLngProposalNo & " LineNo " & rs!fStrLineNo
If Not (.BOF And .EOF) Then
.MoveFirst
End If
strJob = ![fDate] & " " & ![fLngProposalNo] & " " & !fTxtPropertyName ' this makes the value of the line
End With
With Forms![frmCal_2025].Controls(strPosition)
.Value = strJob
.BackColor = 14810879
End With
Next lngLine
Next lngDay
Next lngWeek
Set rs = Nothing
Set db = Nothing
End Sub

[Welshgasman]

Check the .RecordCount property?
For any domain function the criteria would be

"WHERE (((tblCal_2025.fDate) = #" & dateDate & "#" & " AND (tblCal_2025.fStrLineNo) = '" & CStr(lngLine) & "'))"

I do not believe you should have spaces before the second # ?, and I hope you use mm/dd/yyyy format in your system? That would not work here in the UK.

[moke123]

frmCal_2025
tblCal_2025

Real bad idea if your intention is to add a new table and form every year.

[xps35]

Please post code like this

Dim db As DAO.DatabaseDim rs As DAO.Recordset


Dim strJob As String
Dim lngWeek As Long
Dim lngDay As Long
Dim strDay As String
Dim lngLine As Long
Dim strPosition As String
Dim dateDate As Date


For lngWeek = 1 To 1 ' 5
    For lngDay = 1 To 7 ' 14
        strDay = CStr(lngDay)
        Select Case strDay
            Case 1, 2, 3, 4, 5, 6, 7, 8, 9: 
                strDay = "0" & strDay
            Case Else: 
                strDay = strDay
        End Select
        
        For lngLine = 101 To 103 ' 120
            dateDate = DateValue(Forms![frmCal_2025].Controls("tbD1" & strDay))
            strPosition = "tbJ" & CStr(lngWeek) & CStr(lngDay) & CStr(lngLine)


            Set db = CurrentDb
            Set rs = db.OpenRecordset("SELECT tblCal_2025.fDate, tblCal_2025.fStrLineNo, tblCal_2025.fLngProposalNo, " _
                        & "tblCal_2025.fTxtService, tblProposal.fTxtPropertyName " _
                        & "From tblCal_2025 INNER JOIN tblProposal " _
                        & "ON tblCal_2025.fLngProposalNo = tblProposal.fLngProposalNo " _
                        & "WHERE (((tblCal_2025.fDate) = #" & dateDate & " # " _
                        & "AND (tblCal_2025.fStrLineNo) = '" & CStr(lngLine) & "'))" _
                        & "Order BY tblCal_2025.fDate, tblCal_2025.fStrLineNo", _
                        dbOpenSnapshot, dbReadOnly)


            With rs
                Debug.Print "date " & rs!fDate & " ProposalNo " & rs!fLngProposalNo & " LineNo " & rs!fStrLineNo
                If Not (.BOF And .EOF) Then
                    .MoveFirst
                End If


                strJob = ![fDate] & " " & ![fLngProposalNo] & " " & !fTxtPropertyName ' this makes the value of the line
            End With


            With Forms![frmCal_2025].Controls(strPosition)
                .Value = strJob
                .BackColor = 14810879
            End With
        Next lngLine
    Next lngDay
Next lngWeek


Set rs = Nothing
Set db = Nothing

I have no idea what you are trying to do. You start with a for/next loop: For lngWeek = 1 To 1. Why a loop if you only perform it once?

After that you have For lngDay = 1 To 7 with a case select

strDay = CStr(lngDay)        
Select Case strDay
  Case 1, 2, 3, 4, 5, 6, 7, 8, 9: 
     strDay = "0" & strDay
  Case Else: 
     strDay = strDay
End Select

Why the SELECT CASE if lngDay/strDay never is bigger than 7?

[orange]

lawdy,

As others have suggested, it might be helpful if you could describe in plain English what you are trying to do. Once everyone understands the issue, you are likely to get more, focused responses.

[jojowhite]

you can test this:

Sub modCal_addJobsToCal() ' put job names on frmCal_2025
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strJob As String
Dim lngWeek As Long
Dim lngDay As Long
Dim strDay As String
Dim lngLine As Long
Dim strPosition As String
Dim dateDate As Date


Set db = CurrentDb


For lngWeek = 1 To 1 ' 5
        For lngDay = 1 To 7 ' 14
                strDay = CStr(lngDay)
                Select Case strDay
                Case 1, 2, 3, 4, 5, 6, 7, 8, 9: strDay = "0" & strDay
                Case Else: strDay = strDay
                End Select
                
                For lngLine = 101 To 103 ' 120
                        dateDate = DateValue(Forms![frmCal_2025].Controls("tbD1" & strDay))
                        strPosition = "tbJ" & CStr(lngWeek) & CStr(lngDay) & CStr(lngLine)
                        
                        Set rs = db.OpenRecordset("SELECT tblCal_2025.fDate, tblCal_2025.fStrLineNo, tblCal_2025.fLngProposalNo, " _
                        & "tblCal_2025.fTxtService, tblProposal.fTxtPropertyName " _
                        & "From tblCal_2025 INNER JOIN tblProposal " _
                        & "ON tblCal_2025.fLngProposalNo = tblProposal.fLngProposalNo " _
                        & "WHERE (((tblCal_2025.fDate) = #" & dateDate & " # " _
                        & "AND (tblCal_2025.fStrLineNo) = '" & CStr(lngLine) & "'))" _
                        & "Order BY tblCal_2025.fDate, tblCal_2025.fStrLineNo", _
                        dbOpenSnapshot, dbReadOnly)
                        
                        strJob = ""
                        
                        With rs
                                Debug.Print "date " & rs!fDate & " ProposalNo " & rs!fLngProposalNo & " LineNo " & rs!fStrLineNo
                                If Not (.BOF And .EOF) Then
                                        .MoveFirst
                                        strJob = ![fDate] & " " & ![fLngProposalNo] & " " & !fTxtPropertyName ' this makes the value of the line
                                End If
                                .Close
                        End With
                        With Forms![frmCal_2025].Controls(strPosition)
                                .Value = strJob
                                .BackColor = 14810879
                        End With
                        
                Next lngLine
        Next lngDay
Next lngWeek
Set rs = Nothing
Set db = Nothing
End Sub