Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2013
    Posts
    2

    Exclamation Unanswered: Problem with MSysObjects query in access

    Hello to all.
    I working to convert an old 2 part database(access 97) to new 2010(accdb).
    I old one front section that contains all forms, in main form i have a button that let to print a query results.
    I will attach all print form vba and a scheme of form and all other parts to be more clear.
    when form will be open we see a combo box that it's "row sourcre" is

    SELECT DISTINCTROW MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Name) Not Like "*sub*" And (MSysObjects.Name) Not Like "z*") AND ((MSysObjects.Type)=-32764)) ORDER BY MSysObjects.Name;

    this query work in old one but when i transferred this to 2010 one, it does not work and don's show results of query. I'm not familar with this "MSysObjects" table, but results in old one after press combo button is:

    Double 5262 Lables
    Double 5662 Lables
    Double Lables by Country
    Lables
    Long Listing by Country
    Medium Listing by Country
    Short Listing by Country

    I think maybe this values defined for this database use
    But this is only an idea
    now if any one know about this method, It's a bid help for me to know it's structure.
    I created new one for access 2010 from old one, some of it's feature not work, one of them is this, I followed old structure and commands but, it's not show combo box any value after its want to retrive informations.
    This is vba of this form:
    ReprotName is name of this combo box.
    I have 3 button for this form cancel, preview, print.
    That preview press invoke reporter"preview"funtion and print press invoke reporter"print" funtion.
    Thanks for each person reading this bad post, but I need solution, guys!
    Thanks again

    Option Compare Database 'Use database order for string comparisons
    Option Explicit

    Private Sub BtnCancel_Click()
    DoEvents
    DoCmd.Close
    End Sub

    Private Function Reporter(pstrButton)

    Dim WhereClause As Variant

    On Error GoTo ErrReporter

    Me.Visible = False
    WhereClause = GetWhere()
    If WhereClause <> "Cancel" Then
    SetReportTitle
    Select Case pstrButton
    Case "Print"
    DoCmd.OpenReport ReportName, A_NORMAL, , WhereClause
    Case Else
    DoCmd.OpenReport ReportName, A_PREVIEW, , WhereClause
    End Select
    DoEvents
    DoCmd.Close A_FORM, Me.Name
    Else
    Me.Visible = True
    End If

    ExitReporter:
    Exit Function

    ErrReporter:
    MsgBox Error$
    Resume ExitReporter

    End Function

    Private Sub ReportName_AfterUpdate()
    btnPrint.Enabled = True
    btnPreview.Enabled = True
    End Sub

    Private Sub SetReportTitle()
    Dim db As Database
    Dim Recset As Recordset
    Dim rstAddType As Recordset 'tblAddTypeCriteria
    Dim GroupNum As Integer
    Dim SQL As String
    Dim Title As String
    Dim strAddType As String 'Address Type spelled out
    Dim counter As Integer
    Dim lngAddTypeID As Long 'AddressTypeID from tblAddTypeCriteria table

    Set db = DBEngine(0)(0)
    Set rstAddType = db.OpenRecordset("tblAddTypeCriteria")
    lngAddTypeID = rstAddType.AddressTypeID
    rstAddType.Close

    GroupNum = DCount("GroupNameID", "Criteria")

    SQL = "SELECT DISTINCTROW GroupNames.Group "
    SQL = SQL & "FROM Criteria INNER JOIN GroupNames ON Criteria.GroupNameID = GroupNames.GroupNameID;"

    Set Recset = db.OpenRecordset(SQL)

    If Recset.RecordCount > 0 Then
    Recset.MoveFirst
    Title = "List of " & Recset.Group
    Select Case GroupNum
    Case 1
    Title = Title & " Group"
    Case 2
    Recset.MoveNext
    Title = Title & " and " & Recset.Group & " Groups"
    Case Else
    counter = 2
    Do Until counter = GroupNum
    Recset.MoveNext
    counter = counter + 1
    Title = Title & ", " & Recset.Group
    Loop
    Recset.MoveNext
    Title = Title & ", and " & Recset.Group & " Groups"
    End Select
    Else
    Title = ""
    End If
    Recset.Close

    If lngAddTypeID <> 0 Then
    strAddType = DLookup("AddressType", "tblAddressType", "[AddressTypeID] = " & lngAddTypeID)
    If Title = "" Then
    Title = strAddType & " Address Types Only"
    Else
    Title = Title & Chr(13) & Chr(10) & strAddType & " Address Types Only"
    End If
    End If

    'place info in Info table
    Set Recset = db.OpenRecordset("Info")
    Recset.Edit
    Recset.Misc = Title
    Recset.Update
    Recset.Close

    End Sub

  2. #2
    Join Date
    Aug 2013
    Posts
    2
    Hello to all
    Thanks for reading
    I can solve it, in my old database i have some reports as my guessed that with my requiring links but they were invisible, when i can see them i can copy and past them into my new one and problems solved.
    Regards
    hossein

Posting Permissions

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