CODEWITHSUNDEEP
ms-accessvba
theCowardlyFrench
theCowardlyFrench

Reputation: 228

db.OpenRecordset returns something that isn't a recordset; but run as queries they do

I'm trying to generate a bill by route, so I've broken it down by customers belonging to a specific route, and then for each customer totaling their weekly rates to compile a monthly rate.

The problem is, even opening a recordset with a SELECT * IN [table] returns nothing, so there must be some glaring error. Here's my code, I'd be very appreciative if someone could set me straight.

Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim custNo As Integer
Dim month_total_v As Integer
Dim weekTotal As Integer
Dim weekStart As Date
Dim sql As String

'sql = "SELECT cust_no FROM Roster WHERE route = Forms![routeBill]![route]"

Set rs = CurrentDb.OpenRecordset("SELECT CUST_NO FROM Roster WHERE ROUTE = 'Forms![routeBill]![route]'")

month_total_v = 0

MsgBox ("Boop.")

If Not (rs.EOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
    MsgBox ("Boop.")
        custNo = rs!CUST_NO
        Set rs2 = CurrentDb.OpenRecordset("SELECT wk_rate, wk_strt_dt FROM Roster WHERE wk_strt_dt >= Forms![routeBill]![Text53] AND wk_strt_dt <= Forms![routeBill]![Text4] AND cust_no = custNo")
        If Not (rs2.EOF And rs2.BOF) Then
            rs2.MoveFirst
            Do Until rs2.EOF = True
                MsgBox "Boop."
                weekStart = WK_STRT_DT
                month_total_v = month_total_v + rs2!WK_RATE
                Set rs3 = CurrentDb.OpenRecordset("SELECT * FROM monthTotal where cust_no = custNo and billMonth=month(weekStart) and billYear=year(weekStart)") 'specify date ranges to pick from to shorten query
                If rs3.EOF Then
                    sql = "INSERT INTO monthTotal (cust_no, month_total, billMonth, billYear) VALUES (custNo, month_total_v, month(weekStart), year(weekStart))" 'Append, record does not exist
                    DoCmd.RunSQL sql
                Else
                    sql = "UPDATE monthTotal SET month_total = month_total_v WHERE cust_no = custNo AND billMonth = month(weekStart) AND billYear = year(weekStart)" 'Update, record exists
                    DoCmd.RunSQL sql
                End If
                rs2.MoveNext
            Loop
        Else
            'pass
        End If
    rs.MoveNext
    Loop
End If

Upvotes: 2

Views: 3046

Answers (3)

HansUp
HansUp

Reputation: 97101

This query will not return any records when none of the stored ROUTE values contain the literal text, 'Forms![routeBill]![route]' ...

SELECT CUST_NO FROM Roster WHERE ROUTE = 'Forms![routeBill]![route]'

Elsewhere you have a WHERE clause which includes AND cust_no = custNo. But, since custNo is a VBA variable, the db engine doesn't know anything about it and will interpret it to be the name of a parameter for which you haven't supplied a value.

You can avoid those types of problems by using a parameter query in a DAO.QueryDef. Then supply the parameter values (from form controls, VBA variables, whatever ...) and use the QueryDef.OpenRecordset method to load your recordset.

Here is a simple example ...

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSelect As String

strSelect = "SELECT CUST_NO FROM Roster WHERE ROUTE = [which_route]"
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strSelect)
qdf.Parameters("which_route").Value = Forms![routeBill]![route]
Set rs = qdf.OpenRecordset
With rs
    If .BOF And .EOF Then
        MsgBox "no matches found"
    Else
        .MoveLast
        MsgBox .RecordCount & " matches"
    End If
    .Close
End With

Note the parameter query technique avoids the need to add quotes around text values (and then also cope with text values which may include quotes within them) and format Date/Time values and enclose them within # delimiters.

Upvotes: 4

Gustav
Gustav

Reputation: 55816

You also need to get the dates right:

WHERE wk_strt_dt >= #" & Format(Forms![routeBill]![Text53], "yyyy\/mm\/dd") & "# AND wk_strt_dt  ... etc

Upvotes: 0

PSVSupporter
PSVSupporter

Reputation: 348

The problem is here:

FROM Roster WHERE wk_strt_dt >= Forms![routeBill]![Text53] AND wk

You should outquote Forms![routeBill]![Text53]:

FROM Roster WHERE wk_strt_dt >= " & Forms![routeBill]![Text53] & "  AND wk

Upvotes: 2

Related Questions