Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 822
  • Last Modified:

Access VBA Problem

Experts - I am getting a Run-Time Error '7951':
              You entered an expression that has an invalid reference to the RecordsetClone property.

*The arrow indicates the line that is highlighted:

    [Form_Query2 subform].Visible = True

Dim sWhere As String
Dim sSql As String

sSql = "SELECT tblCompany.PolicyHolderName, tblEOI_Import.* FROM tblCompany LEFT JOIN tblEOI_Import ON tblCompany.[ControlNumber] = tblEOI_Import.Control"
sWhere = " WHERE "

If Not IsNull(Me.txtBadge) Then
    sSql = sSql & sWhere & " tblEOI_Import.EE_BadgeID Like '" & Me.txtBadge & "'"
    sWhere = " AND "
End If

If Not IsNull(Me.txtMember_SSN) Then
    sSql = sSql & sWhere & "tblEOI_Import.EE_SSN Like '" & Me.txtMember_SSN & "'"
    sWhere = " AND "
End If

If Not IsNull(Me.txtLastNameSearch) Then
    sSql = sSql & sWhere & "tblEOI_Import.EE_LastName Like '" & Me.txtLastNameSearch & "'"
    sWhere = " AND "
End If

[Form_Query2 subform].RecordSource = sSql

Dim rs As DAO.Recordset
----->Set rs = Me.Query2_subform.Form.Recordset<-----
'rs.MoveLast
If rs.RecordCount = 0 Then
    [Form_Query2 subform].Visible = False
     LblNoRecs.Visible = True
Else
    [Form_Query2 subform].Visible = True
    LblNoRecs.Visible = False
End If


End Sub
0
Sariff
Asked:
Sariff
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
Pigster14IT ConsultantCommented:
If SSql is the same as the recordsource, try replacing that line with the following:


Set rs = currentdb.OpenRecordset(ssql)
0
 
Pigster14IT ConsultantCommented:
It may not like you calling the recordsource of a form as a recordset.

Thanks.
0
 
Gustav BrockCIOCommented:
I guess it should read:

Set rs = Me.Query2_subform.Form.RecordsetClone

/gustav
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rey Obrero (Capricorn1)Commented:
or you can do this

Set rs = Me.Query2_subform.Form.RecordsetClone
0
 
SariffAuthor Commented:
that fixed the RunTime error...but now the Subform opens behind the Search form!! Instead of in the subform!

-Sariff
0
 
Rey Obrero (Capricorn1)Commented:
is your search form property popup set to Yes?
0
 
Gustav BrockCIOCommented:
Then call DoCmd:

DoCmd.SelectObject acForm, <YourFormName>

/gustav
0
 
GRayLCommented:
[Form_Query2 subform].RecordSource = sSql  - here you call it [Form_Query2 subform]

Shouldn't it be:
Me![Form_Query2 subform].Form.RecordSource = sSql

Dim rs As DAO.Recordset
----->Set rs = Me.Query2_subform.Form.Recordset<----- here you call it Query2_subform  

Are they both the same subfrom?
0
 
SariffAuthor Commented:
Thanks to all!!

-Sariff
0
 
GRayLCommented:
Thanks, glad to help.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now