Error: No Current Record

[Jamen98]

I'm running into a problem with running multiple queries. I have two tables that look like:

Risk_Mgmt_Table
Fields: (Values)
Filing_State: (CA)
Membership: (Yes)
Membership_Rate : (.95)
Filing_State: (CA)
Membership: (No)
Membership_Rate : (1)

When I run the query, I get the error "No Current Record" when "No" is selected from a combo box. My code is:

Option Compare Database
Dim ADA_Member_Factor As Double
Dim Risk_Mgmt_Factor As Double
Private Sub Calc_Final_Rate_Btn_Click()
On Error GoTo Err_Calc_Final_Rate_Btn_Click

    Call Run_ADA_Qry
    Call Run_RiskMgmt_Qry
            
    Dim stDocName As String
    stDocName = "Final_Rate_Form"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , ADA_Member_Factor

Exit_Calc_Final_Rate_Btn_Click:
    Exit Sub

Err_Calc_Final_Rate_Btn_Click:
    MsgBox Err.Description
    Resume Exit_Calc_Final_Rate_Btn_Click

End Sub
Sub Run_ADA_Qry()

    Dim db As Database
    Dim ws As Workspace
    Dim ADA_Member As Recordset
    Dim sql As String
               
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.Databases(0)
    
    sql = "SELECT Membership_Credit_Table.FILING_STATE, Membership_Credit_Table.Membership, Membership_Credit_Table.Membership_Rate FROM Membership_Credit_Table WHERE (((Membership_Credit_Table.FILING_STATE)='" & [Forms]![Credit_Debit_OCC_Form]![Filing_State] & "' ) AND ((Membership_Credit_Table.Membership)=" & [Forms]![Credit_Debit_OCC_Form]![OCC_ADA_Combo] & "));"
    Set ADA_Member = db.OpenRecordset(sql)
    ADA_Member_Factor = ADA_Member!Membership_Rate
            
    MsgBox ADA_Member_Factor, vbOKOnly, "Test text ADA Member"
    
End Sub
Sub Run_RiskMgmt_Qry()

    Dim db As Database
    Dim ws As Workspace
    Dim Risk_Member As Recordset
    Dim sql As String
           
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.Databases(0)
            
    sql = "SELECT Risk_Mgmt_Table.FILING_STATE, Risk_Mgmt_Table.Membership, Risk_Mgmt_Table.Membership_Rate FROM Risk_Mgmt_Table WHERE (((Risk_Mgmt_Table.FILING_STATE)='" & [Forms]![Credit_Debit_OCC_Form]![Filing_State] & "' ) AND ((Risk_Mgmt_Table.Membership)=" & [Forms]![Credit_Debit_OCC_Form]![OCC_RiskMgmt_Combo] & "));"
    Set Risk_Member = db.OpenRecordset(sql)
    Risk_Mgmt_Factor = Risk_Member!Membership_Rate
            
    MsgBox Risk_Mgmt_Factor, vbOKOnly, "Test text Risk Management"
    
End Sub

I'm running two queries that against tables that are almost exactly the same. The combo boxes are in a form named: Final_Rate_Form

It works perfectly when the combo boxes are set to "Yes", so I'm not sure what's going on.?.? Any info is appreciated!!!

[NeoPa]

How about popping the actual SQL in here just as it is before it's executed in lines #34 and #51.

Trying to determine what it is likely to be from your code when most of the relevant information is not there is more trouble than it need be.

[Jamen98]

Here's the SQL lines:



sql = "SELECT Membership_Cred it_Table.FILING _STATE, Membership_Cred it_Table.Member ship, Membership_Cred it_Table.Member ship_Rate FROM Membership_Cred it_Table WHERE (((Membership_C redit_Table.FIL ING_STATE)='" & [Forms]![Credit_Debit_OC C_Form]![Filing_State] & "' ) AND ((Membership_Cr edit_Table.Memb ership)=" & [Forms]![Credit_Debit_OC C_Form]![OCC_ADA_Combo] & "));"

[colintis]

have you tried the code in a query? with some variables replacing the form values for test purposes. Like this:

SELECT Membership_Credit_Table.FILING_STATE
, Membership_Credit_Table.Membership
, Membership_Credit_Table.Membership_Rate 
FROM Membership_Credit_Table 
WHERE (((Membership_Credit_Table.FILING_STATE)='CA' ) 
AND ((Membership_Credit_Table.Membership)=No));

[NeoPa]

Jamen98:
Here's the SQL lines:

You've misunderstood what I posted. I thought I must have said it wrong, but I checked and no - I didn't.

Please post the SQL. That is the contents of the string sql and not the VBA code that creates the string.

When you do, make sure you post it in the [ CODE ] tags provided (which are mandatory).

[Jamen98]

Figured it out... Stupid quotes!!! I was missing the ' on the second query criteria... Thanks colintis, that was a helpful troubleshooting technique. I'm a newby (I'm sure it's obvious) at VBA...

Thanks!