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

    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
    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()
    End Sub

    Private Function Reporter(pstrButton)

    Dim WhereClause As Variant

    On Error GoTo ErrReporter

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

    Exit Function

    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

    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
    Title = "List of " & Recset.Group
    Select Case GroupNum
    Case 1
    Title = Title & " Group"
    Case 2
    Title = Title & " and " & Recset.Group & " Groups"
    Case Else
    counter = 2
    Do Until counter = GroupNum
    counter = counter + 1
    Title = Title & ", " & Recset.Group
    Title = Title & ", and " & Recset.Group & " Groups"
    End Select
    Title = ""
    End If

    If lngAddTypeID <> 0 Then
    strAddType = DLookup("AddressType", "tblAddressType", "[AddressTypeID] = " & lngAddTypeID)
    If Title = "" Then
    Title = strAddType & " Address Types Only"
    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.Misc = Title

    End Sub

  2. #2
    Join Date
    Aug 2013
    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.

Posting Permissions

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