Results 1 to 2 of 2
  1. #1
    Tmoore4748 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    1

    Report formatting


    Probably covered, but I can't find anything. I need to generate a multiple choice test and answer key based on a query that randomizes the questions. I've tried grouping, relationships, etc, but nothing works. Due to the query, I can't do two separate reports without vb or macros (the two reports don't match because of the randomizing, the database sees it as two separate instances of the query). Is there a way to group the questions and answers on separate pages in the same report, or some other solution?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if your design is correct, then you would need vb to randomize.
    The field: [ORDERNUM], would get randomized.


    Code:
       
    Public Sub RandomOrder()
    Dim iTot As Long, iRec As Long
    Dim iSample As Integer
    Dim sTbl As String, sSql As String
    Dim rst  'As Recordset
    Dim i As Long
    Dim iRnd As Long, lNdx As Long
    Dim coll As New Collection
    Dim sKey As String
    
    DoCmd.SetWarnings False
    sTbl = "[tNAMES]"
    
       'clear the order#
    sSql = "update " & sTbl & " set [OrderNum] = null"
    DoCmd.RunSQL sSql
    
      'collect all keys to alter their order
    Set rst = CurrentDb.OpenRecordset("select [ClientID] from " & sTbl)
    With rst
       While Not .EOF
           sKey = rst.Fields(0).Value & ""
           coll.Add sKey, sKey
           
           .MoveNext
       Wend
    End With
    Set rst = Nothing
    
       'pick key at random then set new order for it
    i = 0
    While coll.Count > 0
        i = i + 1
        iRnd = Int(coll.Count * Rnd + 1)
        'Debug.Print iRnd
        
        sKey = coll(iRnd)
        lNdx = CLng(sKey)
        sSql = "update " & sTbl & " set [OrderNum] = " & i & " where [ClientID]= " & lNdx
        DoCmd.RunSQL sSql
    
        coll.Remove sKey
    nextNum:
    Wend
    
    DoCmd.SetWarnings True
    DoCmd.OpenTable sTbl
    MsgBox "done"
    Set coll = Nothing
    Exit Sub
    Resume nextNum
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Formatting Report
    By mp3909 in forum Reports
    Replies: 2
    Last Post: 02-25-2018, 03:28 PM
  2. Formatting report
    By ncub in forum Reports
    Replies: 7
    Last Post: 04-29-2015, 09:54 PM
  3. Report Formatting
    By School Boy Error in forum Reports
    Replies: 3
    Last Post: 12-06-2013, 04:52 AM
  4. Formatting a report
    By keydetpiper in forum Reports
    Replies: 4
    Last Post: 12-20-2012, 12:11 PM
  5. formatting the report
    By simba in forum Reports
    Replies: 1
    Last Post: 08-12-2011, 12:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums