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_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] & "));"

[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!