Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    395

    Angry Unanswered: Re: Problem in usining different recordsource in Main & Sub forms

    Hi,
    I was wondering if someone can help me with problem in changing the recordsource in main/subfor form.
    In Main & Sub forms’ open events depending which table exist, the form and subform execute a diff recordsource. Basically if tableA doesn’t exist use tableB.

    There is a linking field between Main and Sub forms

    Error occurred in
    Me.RecordSource = sqlstr in Sub open form event when clicking on Main form
    MS VB
    Run-time error ‘2757’:
    There was a problem accessing a property or method of the OLE object.

    Tested w/o problems
    Sub form open w/ recordsource
    The first If statement executed w/o problem

    Sub form:
    Private Sub Form_Open(Cancel As Integer)
    str = "SELECT COUNT(NAME) AS CNT FROM SYSOBJECTS OBJ WHERE OBJ.NAME= '" & tableName & "' AND XTYPE = 'U'"
    str = str & "GROUP BY NAME"
    rs.Open str, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    If rs.RecordCount > 0 Then
    sqlstr = "SELECT TOP 100 PERCENT dbo.tblRMR_desc_defects.num_defects, ..
    sqlstr = sqlstr & "WHERE (dbo.tblBag_results.sampling_data_id = " & sampling_id & ") " 'AND dbo.tblBag_results.bag_num = 148 "
    sqlstr = sqlstr & "AND (tblTmp_inspec.partial > 0 Or tblTmp_inspec.complete > 0)"
    sqlstr = sqlstr & "ORDER BY dbo.tblRMR_desc_defects.num_defects, dbo.tblPart_defects.defect_desc"
    Else
    sqlstr = "SELECT TOP 100 PERCENT dbo.tblRMR_desc_defects.num_defects, …
    sqlstr = sqlstr & "ON dbo.tblBag_results.bag_num = dbo.tblInspection_dc.bag_num "
    sqlstr = sqlstr & "WHERE (dbo.tblBag_results.sampling_data_id = 267) AND (dbo.tblInspection_dc.inspection_completed_by = 10646) "
    sqlstr = sqlstr & "AND (dbo.tblInspection_dc.inspection_status = 1) ORDER BY dbo.tblRMR_desc_defects.num_defects, dbo.tblPart_defects.defect_desc"

    End If

    Me.RecordSource = sqlstr

    Your help is greatly appreciated.
    Alice

  2. #2
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Is that code exactly how you have in the procedure? Looks like you are missing some spaces...because of the way forms bind to tables (not using ADO) the error message you are getting probably isn't the actual error, because ADO won't raise an error of that sort. If your code is exactly like that in the database, it may be that the SQL string is invalid. Whenever you concantenate strings like that for a SQL statement, make sure there are spaces in right places.

    Is "SYSOBJECTS OBJ" the name of a table? If there is a space in the name, you need to surround it with square brackets []. Is "SYSOBJECTS OBJ" = "OBJ"?

    Are the table names actually "dbo.tblRMR_desc_defects" or "tblRMR_desc_defects"? A good idea is to always surround the table and field names with [].

    Assuming dbo is part of the table name, try using this:

    Code:
    str = "SELECT COUNT([NAME]) AS CNT FROM [SYSOBJECTS OBJ] WHERE [OBJ].[NAME]= '" & tableName & "' AND [XTYPE] = 'U'"
    str = str & " GROUP BY [NAME];"
    
    rs.Open str, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    
    If Not rs.EOF Then
       sqlstr = "SELECT TOP 100 PERCENT [dbo.tblRMR_desc_defects].[num_defects]"
       sqlstr = sqlstr & " WHERE ([dbo.tblBag_results].[sampling_data_id] = " & sampling_id & ") AND [dbo.tblBag_results].[bag_num] = 148"
       sqlstr = sqlstr & " AND ([tblTmp_inspec].[partial] > 0 Or [tblTmp_inspec].[complete] > 0)"
       sqlstr = sqlstr & " ORDER BY [dbo.tblRMR_desc_defects].[num_defects], [dbo.tblPart_defects].[defect_desc];"
    Else
       sqlstr = "SELECT TOP 100 PERCENT [dbo.tblRMR_desc_defects].[num_defects],"
       sqlstr = sqlstr & " ON [dbo.tblBag_results].[bag_num] = [dbo.tblInspection_dc].[bag_num]"
       sqlstr = sqlstr & " WHERE ([dbo.tblBag_results].[sampling_data_id] = 267) AND ([dbo.tblInspection_dc].[inspection_completed_by] = 10646)"
       sqlstr = sqlstr & " AND ([dbo.tblInspection_dc].[inspection_status] = 1) ORDER BY [dbo.tblRMR_desc_defects].[num_defects], [dbo.tblPart_defects].[defect_desc];"
    
    End If
    
    Me.RecordSource = sqlstr

  3. #3
    Join Date
    May 2002
    Posts
    395

    Re: Problem in usining different recordsource in Main & Sub forms

    Thanks for your reply.
    I tried it with [] but still doesn't work. I ended up placing the SQL statements in a stored procedure and the form's do open to the right recordsource. Thanks for your suggesstion though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •