Select query returning the string instead of the value

johnkrytus

Registered User.
Local time
Yesterday, 21:48
Joined
Mar 7, 2013
Messages
91
Why is strCreditCheck returning the actual query string and not the result? Of course, this is not working. Please help.

Code:
Private Sub b_PrintWorkOrder_Click()
    Me.Requery
    Dim strCreditCheck As String
    Dim strCompany_id As Integer
    Dim stlinkCriteria As String
    
    stlinkCriteria = "[EntryId]=" & [Forms]![People_Enter]![People_Enter_Assignments].Form!EntryId
    strCompany_id = [Forms]![People_Enter]![People_Enter_Assignments].Form!company_id
    strCreditCheck = "SELECT TermsID FROM companies WHERE company_id = " & strCompany_id
    
    Debug.Print ([strCreditCheck])
    
    If strCreditCheck <> 4 Then
        If stlinkCriteria <> "" Then
            DoCmd.OpenReport "WorkOrder", acPreview, , stlinkCriteria
            DoCmd.Close acForm, "People_Enter"
        End If
    Else
        MsgBox "This company is not yet approved for credit"
    End If
End Sub
 
Last edited:
Hi

strCreditCheck = "TermsId IN (SELECT TermsID FROM companies WHERE company_id = " & strCompany_id & ")"
 
Paul, I'm getting a "type mismatch" on rs in the If statement. More troubling to me is that I cannot get my debug.print to return the result of the query. That errors as well.

Part of my problem is that I'm simply copying code. I don't really understand what DAO is (and I'm having a difficult time finding a good tutorial).

The other thing that (or kind of related) is that my query returns one single value-not a set of values. Why do I need a recordset?

Code:
Private Sub b_PrintWorkOrder_Click()
    Me.Requery
    Dim CreditCheckQry As String
    Dim strCompany_id As Integer
    Dim stlinkCriteria As String

    strCompany_id = [Forms]![People_Enter]![People_Enter_Assignments].Form!company_id
    CreditCheckQry = "SELECT TermsID FROM companies WHERE company_id = " & strCompany_id
    
    Dim db As DAO.Database
    Set db = CurrentDb()
    
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset(CreditCheckQry, dbOpenDynaset)

    Debug.Print rs
    
    If rs <> 4 Then
        stlinkCriteria = "[EntryId]=" & [Forms]![People_Enter]![People_Enter_Assignments].Form!EntryId
        DoCmd.OpenReport "WorkOrder", acPreview, , stlinkCriteria
        DoCmd.Close acForm, "People_Enter"
    Else
        MsgBox "This company is not yet approved for credit"
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub
 
The recordset is an object that you don't really refer to directly like that. Your test would be:

If rs!TermsID <> 4 Then

Similarly, the Debug.Print would refer to a field.
 
Happy to help!
 

Users who are viewing this thread

Back
Top Bottom